[SQL] Comparing Numeric and Double Precision (float8)..
Hello all, i DON'T know what is the proper forum to throw this question and I must to insist in this "feature". Sorry. I have a lot of tables from Oracle 8i Databases with a lot of columns with numeric(x,0) definition. Ok.. I am traslating my oracle tables to PostgreSQL tables. But I am having a serious problem with my client aplications. When I compare a numeric(x,0) field with a float8 field I have an error on PostgreSQL what I didn't have with Oracle. I mean: CREATE test (one numeric(2,0)); SELECT * FROM test WHERE one = 1.0; This runs fine on my Oracle Systems.. but I have problems with my PostgreSQL system. I have tried to create an operator to workaround this inconvenience: numeric '=' float8 with CREATE OPERATOR command and calling to a function to return a boolean. Ok.. great.. It is running now. But when it runs.. I have another problems comparing numeric with integers and so on. So I must to DROP OPERATOR.. I don't understand what is the problem and what options I have to workaround it (without re-write a lot of client applications). I have a lot of code I don't want to modify. The question is: Why we cannot compare numeric with double precision ? And why Oracle or SQL can do it without problems ? Thanks a lot. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Comparing Numeric and Double Precision (float8)..
On Mon, Nov 04, 2002 at 09:11:30 +0100, Terry Yapt <[EMAIL PROTECTED]> wrote: > > When I compare a numeric(x,0) field with a float8 field I have an error > on PostgreSQL what I didn't have with Oracle. I mean: > > CREATE test (one numeric(2,0)); > > SELECT * FROM test WHERE one = 1.0; With 7.3b3 the above works after correcting the create statement. bruno=> create table test (one numeric(2,0)); CREATE TABLE bruno=> SELECT * FROM test WHERE one = 1.0; one - (0 rows) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Comparing Numeric and Double Precision (float8)..
Great I don't know if my customers can wait until 7.3 official release, but I'll try to distract them a bit... :-\ Thanks a lot Bruno... Bruno Wolff III wrote: > > On Mon, Nov 04, 2002 at 09:11:30 +0100, > Terry Yapt <[EMAIL PROTECTED]> wrote: > > > > When I compare a numeric(x,0) field with a float8 field I have an error > > on PostgreSQL what I didn't have with Oracle. I mean: > > > > CREATE test (one numeric(2,0)); > > > > SELECT * FROM test WHERE one = 1.0; > > With 7.3b3 the above works after correcting the create statement. > bruno=> create table test (one numeric(2,0)); > CREATE TABLE > bruno=> SELECT * FROM test WHERE one = 1.0; > one > - > (0 rows) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problem with Auto Increment
On Mon, 4 Nov 2002, Ben Kassel wrote: > When I try to create a new row in this table and do not explicitly > define a unique value for datadefindex I get the following error > message: > More information : If I DROP the database, recreate it, and enter > values into the table manually, the autoincrement works on this table. > It seems that the problem arises after I reload the data into the table > using the \i command on a file which was created using the pg_dump > command. Was that a data only dump that you were reloading? I'd guess that a data only dump is assuming that you have/will set the sequence's next value correctly yourself. You might just try finding the max value and using setval on the sequence to get the sequence in the right place. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] owner of type 'mmm' apperars to be invalid
When I'm trying to make pg_dump on a data base I have this message: WARNING: owner of type 'mmm' apperars to be invalidWARNING: owner of type 'eee' apperars to be invalidWARNING: owner of type 'refcursor' apperars to be invalid Where are these object and how can I destroy its?
Re: [GENERAL] [SQL] Database Design tool
On Mon, 2002-11-04 at 15:00, Patrick Bakker wrote: > I had gASQL working once but that was awhile ago. gASQL has now been renamed > as Mergeant and is being developed in conjunction with the gnome-db > libraries as far as I know. I don't think there has been a stable release of > it yet, although it is under active development. I've seen it in FreeBSD ports collection, if that matters to anyone. It seems to work. LER > > Patrick > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:pgsql-general-owner@;postgresql.org]On Behalf Of Dan Hrabarchuk > > Sent: Wednesday, October 30, 2002 7:31 AM > > To: Johannes Lochmann > > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: Re: [GENERAL] [SQL] Database Design tool > > > > > > gASQL is a gnome-db client that looks like it has a lot of > > promise. The > > only problem is I've never been able to get the application to run > > properly. I'm using RedHat 8.0 on my desktop. The last > > official version > > does not install properly. If I grab a CVS copy, I go through > > dependency > > hell. Has anyone ever actually gotten gASQL to work? > > > > Dan > > > > On Wed, 2002-10-30 at 06:35, Johannes Lochmann wrote: > > > On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote: > > > > > > Hi, > > > > > > (which list should this go to? I guess it is OT on both...) > > > > > > > Can anybody take me reference on Database design tool > > with PostgreSQL > > > > support. > > > > > > Dia and dia2sql (or something similar...) Google knows more :-) > > > > > > HTH > > > > > > Johannes Lochmann > > > > > > > > > ---(end of > > broadcast)--- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to > > [EMAIL PROTECTED]) > > > > > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] counting text matches - any recipes?
Hey all - I'm working on the query interface to a system that stores a bunch of (realtively) small text documents, with titles and keywords, etc. For the basic interface, we have the user types some words into a textbox, then just search 'everything'. I'm weighting the results, so that hits on the title count more than on keywords, which count more than on the abstract, which count more than in the body. This is working reasonably well, but I'm stuck on one counting problem: what's the natural way to count multiple matches in SQL? As an example, let's use the title a.k.a. 'name' of an article (called modules, in the schema). I've already generating a big select with a stanza for each class of match, unioned together, weighted and summed. Here's a typical stanza for the name: select moduleid, name, version, created, revised, abstract, count(*)*100 as weight from current_modules cm, abstracts a where cm.abstractid = a.abstractid and ( name ~* 'Fourier' or name ~* 'series' ) group by moduleid, name, version, created, revised, abstract Obviously, this will give one hit on a module with the name 'Fourier Series', as well as one for 'Fourier Transforms', and one for 'Time Series Analysis'. It's probably blindingly obvious, but how would I structure this to get _two_ hits for 'Fourier Series', that'll still scale to, say, a dozen search terms entered? I've thought of the subselect route, as so: select moduleid, name, version, created, revised, abstract, count(*)*100 as weight from ( select moduleid, abstractid, name, version, created, revised from current_modules cm where name ~* 'Fourier' union all select moduleid, abstractid, name, version, created, revised from current_modules cm where name ~* 'series' ) as bar, abstracts a where bar.abstractid = a.abstractid group by moduleid, name, version, created, revised, abstract But I'm not sure how well that'll scale, since this is already a subselect, so I'd be nesting two deep. Ross ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] counting text matches - any recipes?
Ross, > For the basic interface, we have the user types some words into a textbox, > then just search 'everything'. I'm weighting the results, so that hits > on the title count more than on keywords, which count more than on the > abstract, which count more than in the body. Before you re-invent the wheel, have you checked out OpenFTS? www.openfts.org (I think). > > This is working reasonably well, but I'm stuck on one counting problem: > what's the natural way to count multiple matches in SQL? Within a single text field? There isn't. 2 choices: 1) Use a Full Text Searching engine, such as the simple one in /contrib or a more full-featured one like OpenFTS. Both will allow you to do counts on "hits" for a keyword. 2) Write a function in PL/perl which will count the number of keyword matches in a text string. Potentially slower than #1, but easier to implement. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Referential integrity Freeze
Hello, I have two 4 table with referential constraint's that are hanging when I try to delete from them. I have a, users table, ( 3 rows ) suburbs table ( 16000 rows ), regions table ( 54 rows )and a bus_pc_idc table ( business type ) ( 3 rows ) Here is my integrity rules: create table bus_pc_idc ( id serial, user_idint4 REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, sub_id int4 REFERENCES suburbs(sub_id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, idc_id int4 REFERENCES idc(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, active bool NOT NULL DEFAULT 'f'::bool ); As the system is still being developed I want to 'empty' out the database from titme to time and rebuild it. I think that when I delete from the users table the delete should cascade through the bus_pc_idc table. However it's justing hanging when I delete all from the users table. When the database is hanging the CPU is 99% for the Postgres process. I can't see what's wrong. Perhaps I'm missing something in the referential integrity. Thanks in advance Cheers Rudi. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Referential integrity Freeze
> Hello, > > I have two 4 table with referential constraint's that are hanging when I > try to delete from them. > > I have a, > users table, ( 3 rows ) > suburbs table ( 16000 rows ), > regions table ( 54 rows )and > a bus_pc_idc table ( business type ) ( 3 rows ) > > Here is my integrity rules: > > create table bus_pc_idc ( > id serial, > user_idint4 REFERENCES users(user_id) ON DELETE CASCADE ON > UPDATE CASCADE NOT NULL, > sub_id int4 REFERENCES suburbs(sub_id) ON DELETE CASCADE ON > UPDATE CASCADE NOT NULL, > idc_id int4 REFERENCES idc(id) ON DELETE CASCADE ON UPDATE > CASCADE NOT NULL, > active bool NOT NULL DEFAULT 'f'::bool > ); > > As the system is still being developed I want to 'empty' out the > database from titme to time and rebuild it. > I think that when I delete from the users table the delete should > cascade through the bus_pc_idc table. > However it's justing hanging when I delete all from the users table. > When the database is hanging the CPU is 99% for the Postgres process. > > I can't see what's wrong. Perhaps I'm missing something in the > referential integrity. It shouldn't be hanging, however, do you have indexes on the *_id fields in bus_pc_idc? Otherwise it's going to be doing 1 sequence scan over bus_pc_idc for each row in users. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Referential integrity Freeze
Stephan, Thanks for your reply. No I don't have indexes on the *_id fields in bus_pc_idc. Now that you point it out it make perfect sense why this query would take a while and give the impression it's hanging - it's all those seq scans. Thank you kindly, I really appreicate it. Regards Rudi. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] query optimization question
The query below is slow because both the lots table and the deficiency_table table have thousands of records. Can anyone tell me how to do the second subselect (lot_count) by some method of a join instead of a sub - subselect OR any other method I can use to optimize this query to make it faster? The objective of the query is: Tell me for each project, the total number of deficiencies in the project, and the total number of lots with 1 or more deficiencies in the project. SELECT project_id, marketing_name, (SELECT count(lots.lot_id) AS lot_count FROM deficiency_table AS dt, lots WHERE dt.lot_id = lots.lot_id AND lots.division_id = proj.division_id AND lots.project_id = proj.project_id ) AS def_count, (SELECT count(lots.lot_id) AS lot_counter FROM lots WHERE lots.division_id = proj.division_id AND lots.project_id = proj.project_id AND EXISTS (SELECT 1 FROM deficiency_table AS dt WHERE dt.lot_id = lots.lot_id) ) AS lot_count FROMprojects AS proj WHERE proj.division_id = '#variables.local_division_id#' AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE menu_code = 'WA' AND division_id = proj.division_id AND project_id = proj.project_id AND status = 'I') ORDER BY proj.project_id Thanks in advance Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Problem with Auto Increment
When I try to create a new row in this table and do not explicitly
define a unique value for datadefindex I get the following error
message:
ERROR: Cannot insert a duplicate key into unique index datadef_pkey
Here is the INSERT statement that generated the error:
tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat,
datadefunits, datadefdescription)
VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a new
row without an explicit datadefindex');
Here is the table definition:
tmdb=# \d datadef
Table "datadef"
Column | Type |
Modifiers
+---+---
-
datadefindex | integer | not null default
nextval('datadef_
datadefindex_seq'::text)
cfgmgmtid | integer |
datadefname| character varying(80) | not null
datadefformat | character varying(80) | not null
datadefunits | character varying(80) | not null
datadefdescription | text | not null
Primary key: datadef_pkey
Unique keys: datadefname_idx
Triggers: RI_ConstraintTrigger_19507,
RI_ConstraintTrigger_19509,
RI_ConstraintTrigger_19511,
RI_ConstraintTrigger_19513,
RI_ConstraintTrigger_19515,
RI_ConstraintTrigger_19659,
RI_ConstraintTrigger_19661,
RI_ConstraintTrigger_19663,
RI_ConstraintTrigger_19665,
RI_ConstraintTrigger_19667
And finally here is the entry in the datadef_datadefindex_seq table:
tmdb=# select * from datadef_datadefindex_seq;
sequence_name | last_value | increment_by | max_value |
min_value |
cache_value | log_cnt | is_cycled | is_called
--++--++
---+
-+-+---+---
datadef_datadefindex_seq | 8 |1 | 2147483647 |
1 |
1 | 32 | f | t
(1 row)
Notice that last_value = 8, owever the current number of rows in the
datadef table = 67.
My current workaround is to do a MAX(datadefindex) on datadef,
increment it by one and explicitly place that value as the
datradefindex for the new row, however I am worried about the database
stability.
More information : If I DROP the database, recreate it, and enter
values into the table manually, the autoincrement works on this table.
It seems that the problem arises after I reload the data into the table
using the \i command on a file which was created using the pg_dump
command.
I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.
Thanks in advance,
ben
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
