[SQL] EXIST / NOT EXIST
Hi, I would like to know if the keyword EXIST can be used with PostgreSQL ? I have search in the Reference Manuel et tried a query using EXIST in pgsql, but no result... Thanks a lot Rachel ** [EMAIL PROTECTED] Laboratoire de prehistoire du Lazaret 33 bis bd Franck Pilatte 06300 Nice tel:04-92-00-17-37/fax:04-92-00-17-39 Windows a bug's life ---(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] EXIST / NOT EXIST
At 09:01 AM 3/12/2002 +0100, Rachel.Vaudron wrote: I would like to know if the keyword EXIST can be used with PostgreSQL ? EXISTS is supported (ie. trailing 'S'). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] EXIST / NOT EXIST
On Tue, 3 Dec 2002, Rachel.Vaudron wrote: > Hi, > > I would like to know if the keyword EXIST can be used with PostgreSQL ? > I have search in the Reference Manuel et tried a query using EXIST in > pgsql, but no result... it is EXISTS . > > Thanks a lot > > Rachel > > ** >[EMAIL PROTECTED] > Laboratoire de prehistoire du Lazaret > 33 bis bd Franck Pilatte 06300 Nice > tel:04-92-00-17-37/fax:04-92-00-17-39 > Windows a bug's life > > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] EXIST / NOT EXIST
> it is EXISTS I'm a very little shamefull !!! But Thanks at all. Rachel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] EXIST / NOT EXIST
> EXISTS is supported (ie. trailing 'S'). I'm a little shamefull ;) Thanks a lot. Rachel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date trunc in UTC
Hi all
Finally, I am using a plpgsql procedure that accomplish that in
PostgreSQL 7.2.1. The code follows:
CREATE OR REPLACE FUNCTION utc_date_trunc (TEXT, TIMESTAMP) RETURNS
TIMESTAMP AS '
DECLARE
utcts TIMESTAMP WITHOUT TIME ZONE;
utcdt TIMESTAMP WITHOUT TIME ZONE;
BEGIN
--> First get the indicated timestamp at UTC <--
utcts := $2 AT TIME ZONE ''UTC'';
--> Get the date trunc <--
utcdt := DATE_TRUNC ($1, utcts);
--> Return the result <--
RETURN (utcdt at time zone ''UTC'');
END
' LANGUAGE 'plpgsql';
I guess that it can be adapted for other time functions with ease, but I
do not need them yet.
Thanks to everybody involved,
Thrasher
Tom Lane wrote:
Thrasher <[EMAIL PROTECTED]> writes:
The biggest point that I see is that it would be nice to have some kind
of function that works with UTC values, regarding of which timezone the
user has set.
You can do that in 7.3, using the AT TIME ZONE construct. Observe:
regression-# begin;
BEGIN
regression=# select now();
now
---
2002-11-22 09:59:48.706508-05 'now' in local time (EST)
(1 row)
regression=# select now() at time zone 'UTC';
timezone
2002-11-22 14:59:48.706508 'now' in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC');
date_trunc
-
2002-11-01 00:00:00month start in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
timezone
2002-10-31 19:00:00-05month start in local time
(1 row)
regression=# commit;
This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
function.
AT TIME ZONE was less functional, and very poorly documented, in 7.2.
The 7.3 version is described at
http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL QUERY
I have the following function: CREATE FUNCTION public.auto_incremento() RETURNS trigger AS 'beginnew.id = (select (max(id) + 1) from teste);return new;end;' LANGUAGE 'plpgsql' I created a trigger that uses this function, but i want to the function be usefull for all tables and not only to tbale teste. Someone know how ??? Pedro Igor
Re: [SQL] SQL QUERY
On Tue, 3 Dec 2002, Pedro Igor wrote: > I have the following function: > CREATE FUNCTION public.auto_incremento() RETURNS trigger AS ' > begin > new.id = (select (max(id) + 1) from teste); > return new; > end;' LANGUAGE 'plpgsql' > > I created a trigger that uses this function, but i want to the function be usefull >for all tables and not only to tbale teste. > > Someone know how ??? Take a look at sequences. (In Addition, they are autimatically created when a column is of type 'SERIAL'). > > > > Pedro Igor > > > > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] [OT] Inventory systems (private)
Hi All, is there any whitepaper, document or website that can point me to how to setup a inventory system? I'm particulary interested how other people solve the problem of a unknown number of attributes to a inventory item. example: BAL<-- Inventory Item - Color <- Attribute - Diameter <- Attribute - Weight <- Attribute Car<-- Inventory Item - Speed <- Attribute - Size <- Attribute - Weight <- Attribute - Color <- Attribute Computer<-- Inventory Item - Brand <- Attribute - Weight <- Attribute - Windows/Linux <- Attribute I can ofcource add any number of columns to a table but for a lot of items there will be a lot of NULL values and currently I don't know how many attrubutes one item can have (possible between 10 and 20). This can even change in feature opon request. Ries ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL QUERY
On Tue, 3 Dec 2002, Pedro Igor wrote: > But i want implement a trigger that auto-increments the id field. This > options is personal, i don´t like to depend from the database. > Then use table name as parameter to your function. But trully its an error prone policy the one you try to follow, what if a table has a primary key named "fobarid"?? Your function will not work correctly. Or you could programatically read the system tables and find out which column is the primary key for a given table. But unfortunately that way you depend on the database even worse than simply and nicely use sequences. > Pedro Igor > > - Original Message - > From: "Achilleus Mantzios" <[EMAIL PROTECTED]> > To: "Pedro Igor" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, December 03, 2002 11:05 AM > Subject: Re: [SQL] SQL QUERY > > > > On Tue, 3 Dec 2002, Pedro Igor wrote: > > > > > I have the following function: > > > CREATE FUNCTION public.auto_incremento() RETURNS trigger AS ' > > > begin > > > new.id = (select (max(id) + 1) from teste); > > > return new; > > > end;' LANGUAGE 'plpgsql' > > > > > > I created a trigger that uses this function, but i want to the function > be usefull for all tables and not only to tbale teste. > > > > > > Someone know how ??? > > > > Take a look at sequences. > > (In Addition, they are autimatically created when a column is of type > > 'SERIAL'). > > > > > > > > > > > > > > Pedro Igor > > > > > > > > > > > > > > > > == > > Achilleus Mantzios > > S/W Engineer > > IT dept > > Dynacom Tankers Mngmt > > Nikis 4, Glyfada > > Athens 16610 > > Greece > > tel:+30-10-8981112 > > fax:+30-10-8981877 > > email: [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] [OT] Inventory systems (private)
While I'm not expert what I would do is something like this Items table fields: item_id item_name Attributes table fields: attribute_id attribute_name Item_Attr table fields: item_id attribute_id attribute_value Now an item can be associated with any number of attributes. 03/12/2002 13:17:15, "Ries van Twisk" <[EMAIL PROTECTED]> wrote: >Hi All, > >is there any whitepaper, document or website that can point me to how to >setup a inventory system? >I'm particulary interested how other people solve the problem of a unknown >number of attributes to a inventory item. > >example: >BAL<-- Inventory Item > - Color <- Attribute > - Diameter <- Attribute > - Weight <- Attribute > >Car<-- Inventory Item > - Speed <- Attribute > - Size <- Attribute > - Weight <- Attribute > - Color <- Attribute > >Computer<-- Inventory Item > - Brand <- Attribute > - Weight <- Attribute > - Windows/Linux <- Attribute > > >I can ofcource add any number of columns to a table but for a lot of items >there will be a lot of NULL values and currently I don't know how many >attrubutes one item can have (possible between 10 and 20). This can even >change in feature opon request. > >Ries > > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [OT] Inventory systems (private)
Troy, Andy,
you both are right and it was my first tought. My only concern is that would
this system be fast enough for large tables (for me large is around 250.000
unique items) and thus my attribute table would be around 2.500.000 and
5.000.000 entrys. A record for one attribute is small I think around 128
byte in size.
One thing is that every attribute must be in it's own domain. For weight for
example I have three different meanings ( 1] Pull weight 2] push weight 3]
weight of the items itself) but using the method troy suggested that would
not be a problem if I create some sort of a domain table.
Anyway so far thangs for the quick responses, I've got something to work on.
best regards,
Ries van Twisk
-Oorspronkelijk bericht-
Van: Troy [mailto:[EMAIL PROTECTED]]
Verzonden: dinsdag 3 december 2002 15:47
Aan: Ries van Twisk
CC: [EMAIL PROTECTED]
Onderwerp: Re: [SQL] [OT] Inventory systems (private)
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
'matchbox tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Weight', 20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy KorjuslommiTksoft Inc.
[EMAIL PROTECTED]
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL<-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car<-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer<-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] problem with view in 7.3
Hi , I am Hristo Neshev From Bulgaria In Version 7.2 this statement works : create view v_impr_check(ead_no,ead_date, e_no,e_date,sid,number, s_order,cdt,cdt_description, original_qty,used_qty,waste_qty,rest_qty,original_used_qty)ASselect distinct md_custom_number, md_custom_date, md_number ,md_date ,sni_sid ,sni_number , snig_ord_number_in,cdt_part_number, cdt_description, snig_quantity,(select sum(impo_quantity) from impr_out where impo_store_note_in = snig_store_note_in and impo_ord_number_in = snig_ord_number_in),(select sum(impow_quantity) from impr_out_waste where impow_store_note_in= sni_sid and impow_ord_number_in = snig_ord_number_in),(select sum(iwnog_quantity) from impr_waste_note_out_goods where iwnog_store_note_in = sni_sid and iwnog_ord_number_in = snig_ord_number_in and iwnog_impr_note_out not in (select impow_iwnog_impr_note_out from impr_out_waste where impow_iwnog_impr_note_out is not null) ),snig_used_quantityfrom store_note_in join md_header on md_sni_sid = sni_sidjoin store_note_in_goods join commodity on (snig_commodity = cdt_sid) on sni_sid = snig_store_note_in Wheresni_type_doc = 303; In 7.3 when i am trying to recreate the view I received the folowing error : find_expr_references_walker: bogus varno 8 The problem is in subselect statements
Re: [SQL] problem with view in 7.3
"Alphasoft" <[EMAIL PROTECTED]> writes: > In 7.3 when i am trying to recreate the view I received the folowing error : > find_expr_references_walker: bogus varno 8=20 This is a bug, but I can't fix it unless you give me a complete test case. I don't have time to try to reverse-engineer your schema... "pg_dump -s" is a quick way to make a script that just creates your tables without any data. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Backend message type 0x50 arrived while idle
To all- I am using Postgres version 6.5.1 in a multithreaded program which is written in C. All inserts and updates into any tables are done within the child process, and there are retrieves from tables both in the parent and child processes. Additionally, the child process will insert into and update a table, and the parent process will retrieve from the same table. There are loops in both the parent and child processes, so every postgres call may be made multiple times, with a significant amount of time (seconds or minutes) between calls. Invariably, I get a message "backend message type 0x50 arrived while idle" before the process completes, although the point where I get this message can vary somewhat. Can anyone tell me what this message means, why I am getting it, and/or how to avoid getting it in the future? Thanks in advance, Phil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] recreating table and foreign keys
Hi I use Postgresql 7.2.2. Sometimes I have to remove a column from my tables (psql): 1. alter table master_table rename to x; 2. \i tables.sql 3. insert into master_table select f1,f2,... from x 4. drop table x I think, foreign keys referring from detail_table(s) to master_table have gone. How to recreate foreign keys? I want to keep my database in better integrity, than now... Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [OT] Inventory systems (private)
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text, int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE ON
UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type', 'matchbox
tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color', 'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100, 'Weight',
20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100 AND
textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy KorjuslommiTksoft Inc.
[EMAIL PROTECTED]
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL<-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car<-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer<-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(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] recreating table and foreign keys
On Tue, 3 Dec 2002, Tomasz Myrta wrote: > Hi > I use Postgresql 7.2.2. > Sometimes I have to remove a column from my tables (psql): > 1. alter table master_table rename to x; > 2. \i tables.sql > 3. insert into master_table select f1,f2,... from x > 4. drop table x > > I think, foreign keys referring from detail_table(s) to master_table > have gone. > How to recreate foreign keys? I want to keep my database in better > integrity, than now... You'll probably end up using ALTER TABLE ADD CONSTRAINT to add the constraints to the new master_table. It's possible that you might be able to hack something with the system tables, but that sounds dangerous. Or you can upgrade to 7.3 which lets you run alter table drop column. :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Query for filtering records
I'm having trouble subtracting groups from other groups. I've got a data model that has the following essential features: create table contacts (num int, properties); create table groups (groupNum int, contactNum int); Where not all contacts will be in a group, some groups will contain most contacts, and there will be something like hundreds of groups and tens of thousands of contacts. I allow people to build groups using criteria, which I need to programatically translate to sql. One somewhat common pattern is: Select all contacts in group a, who have property b, and who aren't in groups c,d,e,f... My first shot was subqueries: select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where groups.groupNum=a and contact.p3=b and not num in (select contactNum from groups where groupNum=c) and not num in (select contactNum from groups where groupNum=d) and not num in (select contactNum from groups where groupNum=e) and not num in (select contactNum from groups where groupNum=f) This is slow. agonizingly so. With an inner join, I'm not convinced that the subtraction is actually correct., but it is much faster. Unfortunatley, faster incorrect answers are rarely helpful. Outer joins seem even worse than subselects for speed, but it does appear to give the correct answer. (example with a single join.) select num from contacts left outer join groups on (contacts.num=groups.contactNum and groups.groupNum=b) where dl_groupDonor._groupNum is null and p3=c I've got to be missing something here, because this is much slower from the (slow) procedural system that I'm porting from. I've been avoiding using union / intersect since I don't really ever know what columns are going to be in the query. perhaps I should revisit that decision and try to work around it. eric ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Analyzing the 7.3 SQL92 Schema
I needed theses Statements, and i think tey could be usefull to others. I hate the non-oracle outer joins, so i implemented the the nullable fields wit a subselect. If someone will rewrite that, please mail the new statements to c.gausepohl(at)arcusx.com so here comes the script: --list of tables for user loged on select tablename from pg_tables where tableowner=user order by tablename; --primary keys from table xyz? select cl.relname, co.conname, apk.attname as pk_attribute_0, (select attname from pg_attribute where attnum=co.conkey[2] and attrelid=cl.oid) as pk_attribute_1, (select attname from pg_attribute where attnum=co.conkey[3] and attrelid=cl.oid) as pk_attribute_2, (select attname from pg_attribute where attnum=co.conkey[4] and attrelid=cl.oid) as pk_attribute_3, (select attname from pg_attribute where attnum=co.conkey[5] and attrelid=cl.oid) as pk_attribute_4, (select attname from pg_attribute where attnum=co.conkey[6] and attrelid=cl.oid) as pk_attribute_5, (select attname from pg_attribute where attnum=co.conkey[7] and attrelid=cl.oid) as pk_attribute_6, (select attname from pg_attribute where attnum=co.conkey[8] and attrelid=cl.oid) as pk_attribute_7 from pg_constraint co, pg_class cl, pg_attribute apk where contype = 'p' and cl.oid = co.conrelid and cl.relname = 'xyz' and apk.attrelid=cl.oid and apk.attnum = co.conkey[1]; --alternate keys from table xyz? select ak.relname, att.attname as Column_Name0, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[1]) as Column_Name1, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[2]) as Column_Name2, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[3]) as Column_Name3, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[4]) as Column_Name4, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[5]) as Column_Name5, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[6]) as Column_Name6, (SELECT attname FROM pg_attribute WHERE attrelid = relation.oid and attnum=ind.indkey[7]) as Column_Name7 from pg_index ind, pg_class relation, pg_class ak, pg_attribute att where ind.indrelid=relation.oid and relation.relname = 'xyz' and ind.indisprimary = 'f' and ind.indisunique = 't' and ind.indexrelid = ak.oid and att.attnum = ind.indkey[0] and att.attrelid = relation.oid --detailed list for columns of table xyz? select att.attname as column_name, att.attnotnull as nullable, typ.typname as data_type, att.attlen, typ.typrelid, att.atttypmod-4 as data_length, (att.atttypmod-4)/65535 as data_precision, (att.atttypmod-4)%65536 as data_scale, d.description as description, att.attnum as column_id from pg_attribute att, pg_class cls, pg_type typ, pg_description d where d.objoid = cls.oid and att.attrelid = cls.oid and typ.oid = att.atttypid and att.attnum>=1 and cls.relname = 'xyz' order by cls.relname, att.attnum; --out references for table xyz select rel.relname as table_name, fk.conname as fk_constraint_name, frel.relname as target_table_name, fk.conkey[1], fk.confkey[1], att_fk0.attname as fk_part_0, att_pk0.attname as pk_part_0, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[2]) as fk_part_1, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[2]) as pk_part_1, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[3]) as fk_part_2, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[3]) as pk_part_2, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[4]) as fk_part_3, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[4]) as pk_part_3, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[5]) as fk_part_4, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[5]) as pk_part_4, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[6]) as fk_part_5, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[6]) as pk_part_5, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = rel.oid and att.attnum=fk.conkey[7]) as fk_part_6, (SELECT att.attname FROM pg_attribute att WHERE att.attrelid = frel.oid and att.attnum=fk.confkey[7]) as pk_part_6, (SELECT att.att
Re: [SQL] [OT] Inventory systems (private)
You might create a seperate attribute tables. table: items id name table: attributes id description table: item_attributes item_id attribute_id SELECT items.name, attributes.description FROM items, attributes, item_attributes WHERE items.id=item_attributes.item_id AND item_attributes.item_id = attributes.id; If some items are also attributes of other items (a car has an engine) you might look at tree structures. Of course there are already existing inventory systems that it might be cheaper to use than to grow your own. On Tue, 3 Dec 2002, Ries van Twisk wrote: > Hi All, > > is there any whitepaper, document or website that can point me to how to > setup a inventory system? > I'm particulary interested how other people solve the problem of a unknown > number of attributes to a inventory item. > > example: > BAL<-- Inventory Item > - Color <- Attribute > - Diameter <- Attribute > - Weight <- Attribute > > Car<-- Inventory Item > - Speed <- Attribute > - Size <- Attribute > - Weight <- Attribute > - Color <- Attribute > > Computer<-- Inventory Item > - Brand <- Attribute > - Weight <- Attribute > - Windows/Linux <- Attribute > > > I can ofcource add any number of columns to a table but for a lot of items > there will be a lot of NULL values and currently I don't know how many > attrubutes one item can have (possible between 10 and 20). This can even > change in feature opon request. > > Ries > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] problem with view in 7.3
Hi , I am Hristo Neshev From Bulgaria In Version 7.2 this statement works : create view v_impr_check(ead_no,ead_date, e_no,e_date,sid,number, s_order,cdt,cdt_description, original_qty,used_qty,waste_qty,rest_qty,original_used_qty)ASselect distinct md_custom_number, md_custom_date, md_number ,md_date ,sni_sid ,sni_number , snig_ord_number_in,cdt_part_number, cdt_description, snig_quantity,(select sum(impo_quantity) from impr_out where impo_store_note_in = snig_store_note_in and impo_ord_number_in = snig_ord_number_in),(select sum(impow_quantity) from impr_out_waste where impow_store_note_in= sni_sid and impow_ord_number_in = snig_ord_number_in),(select sum(iwnog_quantity) from impr_waste_note_out_goods where iwnog_store_note_in = sni_sid and iwnog_ord_number_in = snig_ord_number_in and iwnog_impr_note_out not in (select impow_iwnog_impr_note_out from impr_out_waste where impow_iwnog_impr_note_out is not null) ),snig_used_quantityfrom store_note_in join md_header on md_sni_sid = sni_sidjoin store_note_in_goods join commodity on (snig_commodity = cdt_sid) on sni_sid = snig_store_note_in Wheresni_type_doc = 303; In 7.3 when i am trying to recreate the view I received the folowing error : find_expr_references_walker: bogus varno 8 The problem is in subselect statements
[SQL] Large Query Question. (Slow Select while using 'IN')
Hi all, I hope someone can help me out. I'm doing single-table select statements on a large table and I could use some help in speeding it up. My query is of the form: SELECT col, count(col) FROM tab WHERE id IN (3, 4,7,2, ...) GROUP BY COL ORDER BY count for a very large number of rows. I have an index on id, so the explain looks like: Aggregate (cost=12.12..12.14 rows=1 width=5) -> Group (cost=12.12..12.13 rows=4 width=5) -> Sort (cost=12.12..12.12 rows=4 width=5) col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) So, it does a separate index scan for each row in the IN statement, which takes forever. How do I force the query parser to emulate the behaviour displayed by this query: SELECT col, count(col) FROM tab WHERE (0 = id % 5) GROUP BY COL ORDER BY count Aggregate (cost=3.75..3.86 rows=2 width=5) -> Group (cost=3.75..3.81 rows=21 width=5) -> Sort (cost=3.75..3.75 rows=21 width=5) -> Index Scan using col_id_idx2 on tab (cost=0.00..3.29 rows=21 width=5) Which only does one index scan for an equivelant number of records. Thanks for any help. Please cc to my e-mail. ---(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] recreating table and foreign keys
> You'll probably end up using ALTER TABLE ADD CONSTRAINT to add the > constraints to the new master_table. It's possible that you might > be able to hack something with the system tables, but that sounds > dangerous. Or you can upgrade to 7.3 which lets you run alter table > drop column. :) If it is the only way, I can create >20 "alter table add constraint...", but is rather a boring finish :-( I found, I can dump database - data only. After this I can create all tables and restore data. Any other solution? Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Query for filtering records
On Tue, Dec 03, 2002 at 11:01:33AM -0800, eric soroos wrote: > I'm having trouble subtracting groups from other groups. > > > I've got a data model that has the following essential features: > > create table contacts (num int, properties); > create table groups (groupNum int, contactNum int); > > Where not all contacts will be in a group, some groups will contain most contacts, >and there will be something like hundreds of groups and tens of thousands of >contacts. I allow people to build groups using criteria, which I need to >programatically translate to sql. > > One somewhat common pattern is: > > Select all contacts in group a, who have property b, and who aren't in groups >c,d,e,f... > > My first shot was subqueries: > > select num, p1,p2 ... from contacts > inner join groups using (contacts.num=groups.contactNum) > where groups.groupNum=a > and contact.p3=b > and not num in (select contactNum from groups where groupNum=c) > and not num in (select contactNum from groups where groupNum=d) > and not num in (select contactNum from groups where groupNum=e) > and not num in (select contactNum from groups where groupNum=f) > > This is slow. agonizingly so. I'd say so! Something like: SELECT * ... FROM ... WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or groupnum='d' OR ... ) is bound to be _much_ faster! And even better is SELECT * FROM ... contacts c1 WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum) EXISTS is almost always faster in PG. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(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] Query for filtering records
Eric try: select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where groups.groupNum=a and contact.p3=b and not exists ( select 1 from groups g2 where g2.contactNum = groups.contactNum and g2.groupNum != a); or select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where groups.groupNum=a and contact.p3=b and groups.groupNum in ( select contactNum from groups group by contactNum having count(*) = 1); The IN version may still be faster as the sub-select should be evaluated only once; JLL eric soroos wrote: > > I'm having trouble subtracting groups from other groups. > > I've got a data model that has the following essential features: > > create table contacts (num int, properties); > create table groups (groupNum int, contactNum int); > > Where not all contacts will be in a group, some groups will contain most contacts, >and there will be something like hundreds of groups and tens of thousands of >contacts. I allow people to build groups using criteria, which I need to >programatically translate to sql. > > One somewhat common pattern is: > > Select all contacts in group a, who have property b, and who aren't in groups >c,d,e,f... > > My first shot was subqueries: > > select num, p1,p2 ... from contacts > inner join groups using (contacts.num=groups.contactNum) > where groups.groupNum=a > and contact.p3=b > and not num in (select contactNum from groups where groupNum=c) > and not num in (select contactNum from groups where groupNum=d) > and not num in (select contactNum from groups where groupNum=e) > and not num in (select contactNum from groups where groupNum=f) > > This is slow. agonizingly so. > > With an inner join, I'm not convinced that the subtraction is actually correct., but >it is much faster. Unfortunatley, faster incorrect answers are rarely helpful. > > Outer joins seem even worse than subselects for speed, but it does appear to give >the correct answer. (example with a single join.) > > select num from contacts >left outer join groups > on (contacts.num=groups.contactNum > and groups.groupNum=b) >where >dl_groupDonor._groupNum is null >and p3=c > > I've got to be missing something here, because this is much slower from the (slow) >procedural system that I'm porting from. > > I've been avoiding using union / intersect since I don't really ever know what >columns are going to be in the query. perhaps I should revisit that decision and try >to work around it. > > eric > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] recreating table and foreign keys
On Tue, 3 Dec 2002, Tomasz Myrta wrote: > > You'll probably end up using ALTER TABLE ADD CONSTRAINT to add the > > constraints to the new master_table. It's possible that you might > > be able to hack something with the system tables, but that sounds > > dangerous. Or you can upgrade to 7.3 which lets you run alter table > > drop column. :) > If it is the only way, I can create >20 "alter table add constraint...", but > is rather a boring finish :-( > I found, I can dump database - data only. After this I can create all tables > and restore data. Yeah, dump and restore is generally a pretty big thing though. > Any other solution? You might be able to write a function that would duplicate constraints from the table you want to dispose of to the new table, but it might be hairy. If you do this alot, you might want to upgrade to 7.3 (which requires one dump and reload). ---(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] Query for filtering records
> SELECT * ... > FROM ... > WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or > groupnum='d' OR ... ) > > is bound to be _much_ faster! Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that. > And even better is > > SELECT * > FROM ... contacts c1 > WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or > groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum) > > > EXISTS is almost always faster in PG. Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort. Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine. thanks eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How does postgres handle non literal string values
In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around using Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism. 11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >Actually, we use JDBC Prepared Statements for this type of work. You >put a query with '?' in as placeholders and then add in the values and >the library takes care of the encoding issues. This avoids the double >encoding of (encode X as String, decode string and encode as SQL X on >the line). There was a good article about a framework that did this in >JavaReport about a 18 months ago. > >We have gleaned some ideas from that article to create a framework >around using PreparedStatements as the primary interface to the >database. I'd suggest looking at them. They really make your code much >more robust. > >Charlie > > >>"')..." >> >>You *will* want to escape the username and password otherwise I'll be able to >>come along and insert any values I like into your database. I can't believe >>the JDBC classes don't provide >> >>1. Some way to escape value strings >>2. Some form of placeholders to deal with this >> >> >> > >-- > > >Charles H. Woloszynski > >ClearMetrix, Inc. >115 Research Drive >Bethlehem, PA 18015 > >tel: 610-419-2210 x400 >fax: 240-371-3256 >web: www.clearmetrix.com > > > > > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
