Обсуждение: trigger that needs a PK
Hi, I'm sure this question has been ask before but I could not find anything on google. I most likely did not enter the right text into the google search. I have a parent table that requires that an insert into a child table happen. The problem is I can not determine what the parent pk is for the insert into the child because it hasn't happen yet - if I set the trigger to before insert. So I guess I need something that works with after insert into the parent so the pkid can be created. The parent has: pkid serial lot varchar 30 the child: pkid serial fk_parent int other fields.... The question is how can I get the pk and insert it into the child. Of course I'm new so if I missed something just provide the link or tutorial. Thanks in advance! -- John Fabiani
am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes: > Hi, > I'm sure this question has been ask before but I could not find anything on > google. I most likely did not enter the right text into the google search. > > I have a parent table that requires that an insert into a child table happen. > The problem is I can not determine what the parent pk is for the insert into > the child because it hasn't happen yet - if I set the trigger to before > insert. So I guess I need something that works with after insert into the > parent so the pkid can be created. You don't need a TRIGGER, you need currval(). I will explain with an example: test=# create table master (id serial primary key); NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for serial column "master.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" CREATE TABLE test=*# create table slave (fk int references master); CREATE TABLE test=*# insert into master (id) values(default); INSERT 0 1 test=*# insert into master (id) values(default); INSERT 0 1 test=*# insert into master (id) values(default); INSERT 0 1 test=*# insert into slave (fk) values(currval('master_id_seq')); INSERT 0 1 test=*# select * from master; id ---- 1 2 3 (3 rows) test=*# select * from slave; fk ---- 3 (1 row) http://www.postgresql.org/docs/current/static/functions-sequence.html HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Tuesday 12 February 2008 11:32:24 pm A. Kretschmer wrote: > am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes: > > Hi, > > I'm sure this question has been ask before but I could not find anything > > on google. I most likely did not enter the right text into the google > > search. > > > > I have a parent table that requires that an insert into a child table > > happen. The problem is I can not determine what the parent pk is for the > > insert into the child because it hasn't happen yet - if I set the trigger > > to before insert. So I guess I need something that works with after > > insert into the parent so the pkid can be created. > > You don't need a TRIGGER, you need currval(). I will explain with an > example: > > test=# create table master (id serial primary key); > NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for > serial column "master.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "master_pkey" for table "master" > CREATE TABLE > test=*# create table slave (fk int references master); > CREATE TABLE > test=*# insert into master (id) values(default); > INSERT 0 1 > test=*# insert into master (id) values(default); > INSERT 0 1 > test=*# insert into master (id) values(default); > INSERT 0 1 > test=*# insert into slave (fk) values(currval('master_id_seq')); > INSERT 0 1 > test=*# select * from master; > id > ---- > 1 > 2 > 3 > (3 rows) > > test=*# select * from slave; > fk > ---- > 3 > (1 row) > > > http://www.postgresql.org/docs/current/static/functions-sequence.html > > > HTH, Andreas But how do I do automaticly??? You code implies that I just string two inserts together. I was hoping to use rules or some other way to do it automaticly. -- John Fabiani
am Tue, dem 12.02.2008, um 23:46:41 -0800 mailte johnf folgendes: > > http://www.postgresql.org/docs/current/static/functions-sequence.html > > > > > > HTH, Andreas > > But how do I do automaticly??? > > You code implies that I just string two inserts together. I was hoping to use > rules or some other way to do it automaticly. currval() returns the last inserted value within the current session, it is the usual way to insert into the parent table and use currval() to obtain the last inserted serial value for inserts into the child table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wednesday 13 February 2008 12:35:27 am A. Kretschmer wrote: > am Tue, dem 12.02.2008, um 23:46:41 -0800 mailte johnf folgendes: > > > http://www.postgresql.org/docs/current/static/functions-sequence.html > > > > > > > > > HTH, Andreas > > > > But how do I do automaticly??? > > > > You code implies that I just string two inserts together. I was hoping > > to use rules or some other way to do it automaticly. > > currval() returns the last inserted value within the current session, it > is the usual way to insert into the parent table and use currval() to > obtain the last inserted serial value for inserts into the child table. > > > Andreas After much reading I have the following code - please help me review: -- Function: addrectolots() -- DROP FUNCTION addrectolots(); CREATE OR REPLACE FUNCTION addrectolots() RETURNS "trigger" AS $BODY$DECLARE next_aglot CURSOR FOR select currval('aglots_pkid_seq1') as fkey; --cur_aglot CURSOR FOR select aglots.pkid from public.aglots where aglots.clot = new.clot; myaglot_id public.aglots.pkid%TYPE; BEGIN --IF tg_op = 'INSERT' THEN insert into aglots (clot,fk_species,fk_variety,fk_agpoptrs) values (new.clot,new.fk_species,new.fk_variety,new.pkid); OPEN next_aglot ; FETCH next_aglot INTO myaglot_id; new.fk_aglots := myaglot_id; --END IF; RETURN new; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION addrectolots() OWNER TO johnf; GRANT EXECUTE ON FUNCTION addrectolots() TO public; GRANT EXECUTE ON FUNCTION addrectolots() TO johnf; -- John Fabiani
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes: > am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes: >> The problem is I can not determine what the parent pk is for the insert into >> the child because it hasn't happen yet - if I set the trigger to before >> insert. So I guess I need something that works with after insert into the >> parent so the pkid can be created. > You don't need a TRIGGER, you need currval(). I think this advice is entirely misleading. As best I can tell, the OP's problem is best solved with an ON INSERT trigger on the parent table, and all he's got to do is look at the NEW field for the PK. He is mistaken to think that the value won't have been assigned yet when the trigger is fired (and if he was right, then currval would be no solution either). AFAICS using currval is just useless complication. regards, tom lane
On Wednesday 13 February 2008 07:25:02 am Tom Lane wrote: > "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes: > > am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes: > >> The problem is I can not determine what the parent pk is for the insert > >> into the child because it hasn't happen yet - if I set the trigger to > >> before insert. So I guess I need something that works with after insert > >> into the parent so the pkid can be created. > > > > You don't need a TRIGGER, you need currval(). > > I think this advice is entirely misleading. As best I can tell, the > OP's problem is best solved with an ON INSERT trigger on the parent > table, and all he's got to do is look at the NEW field for the PK. > He is mistaken to think that the value won't have been assigned yet > when the trigger is fired (and if he was right, then currval would > be no solution either). AFAICS using currval is just useless > complication. > > regards, tom lane Thanks Tom, I was able to discover the new had my PK after all. I decided to use the trigger and all appears to be working for the moment. -- John Fabiani
Hello, I'm struggling my brain for some days without success ... I have three tables: cages reserved_cages reserved_days Inside cages, I want to display all the id > 0 and animal_type_id=1, and I want to display all of them no matter if it has some reserved_cages related, so I have to use a LEFT JOIN. Ok, now, the reserved_cages must exist only when there are rows in the reserved_days table. What I'm getting, sometimes, is only the cages that has some reserved_cages (because they have some reserved_days), and when I try to display all of the cages, I can't exclude the ones that have id>0 or animal_type_id=1, I get all of them, so it seems the WHERE clausule is not working ... here are the code: here is the initial cages that I want to display, no matter if they have related data or not: SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY order_position this seems to work, but I get ALL the cages, no matter if they are cages_type_id<>1 (I only want to display=1) SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') ORDER BY order_position if I change the first AND for a WHERE, like this: SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') ORDER BY order_position I get only the cages that has some reservations on the date performed. The relations between tables are: cages: id reserved_cages: cage_id reserved_days: reserved_cage_id So I have to query for a given day if there are reservations, pass those rows to the reserved_cages (where I only store the date_in and date_out). I think I can use an extra field in the reserved_days adding a cage_id, the SELECT would be much simpler and I think much faster, but I'm trying to avoid duplicated data, and at the same time, learning postgresql and try to find more or less the limitations, maybe those limitations (if they're limitations) come from my head or from sql ... as always, thanks for your help ! regards, raimon
Rai Developer wrote: > Hello, > > > I'm struggling my brain for some days without success ... > > I have three tables: > > cages > reserved_cages > reserved_days > > > Inside cages, I want to display all the id > 0 and animal_type_id=1, and > I want to display all of them no matter if it has some reserved_cages > related, so I have to use a LEFT JOIN. I would think that every cage has a valid id - meaning the id>0 should not be needed. > Ok, now, the reserved_cages must exist only when there are rows in the > reserved_days table. > > What I'm getting, sometimes, is only the cages that has some > reserved_cages (because they have some reserved_days), and when I try to > display all of the cages, I can't exclude the ones that have id>0 or > animal_type_id=1, I get all of them, so it seems the WHERE clausule is > not working ... > > here are the code: > > > here is the initial cages that I want to display, no matter if they have > related data or not: > > SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND c.id > > 0) ORDER BY order_position > > > this seems to work, but I get ALL the cages, no matter if they are > cages_type_id<>1 (I only want to display=1) > > SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON > (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id IN > (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') > ORDER BY order_position SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') ORDER BY order_position The main problem is the where clause from above got merged into the left join clause. That would give you the cages with a reservation on the date. Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..." to get the ones without a reservation. I changed the AND r.id to AND c.id - I feel certain you want the cage id not the id of the reservation entry matched against the reserved_cage_id from reserved_days. > > > if I change the first AND for a WHERE, like this: > > SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON > (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND r.id IN > (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') > ORDER BY order_position > > I get only the cages that has some reservations on the date performed. > > > The relations between tables are: > > cages: id > reserved_cages: cage_id > reserved_days: reserved_cage_id > > So I have to query for a given day if there are reservations, pass those > rows to the reserved_cages (where I only store the date_in and date_out). > > I think I can use an extra field in the reserved_days adding a cage_id, > the SELECT would be much simpler and I think much faster, but I'm trying > to avoid duplicated data, and at the same time, learning postgresql and > try to find more or less the limitations, maybe those limitations (if > they're limitations) come from my head or from sql ... > > as always, thanks for your help ! reserved_cages would seem to be unnecessary as you can get the list of reserved cage id's from the SELECT - SELECT c.id FROM cages c WHERE ( c.cages_type_id=1 AND c.id > 0) AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') ORDER BY order_position If you are using the reserved_cages to hold data for who has reserved it, I would think it be better stored in the reserved_days table. (at least the id of the customer reserving the cage) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Hello, On 16/02/2008, at 14:23, Shane Ambler wrote: > Rai Developer wrote: >> Hello, >> I'm struggling my brain for some days without success ... >> I have three tables: >> cages >> reserved_cages >> reserved_days >> Inside cages, I want to display all the id > 0 and >> animal_type_id=1, and I want to display all of them no matter if it >> has some reserved_cages related, so I have to use a LEFT JOIN. > > I would think that every cage has a valid id - meaning the id>0 > should not be needed. yes, I need it, because there are special cages that have a negative id >> Ok, now, the reserved_cages must exist only when there are rows in >> the reserved_days table. >> What I'm getting, sometimes, is only the cages that has some >> reserved_cages (because they have some reserved_days), and when I >> try to display all of the cages, I can't exclude the ones that have >> id>0 or animal_type_id=1, I get all of them, so it seems the WHERE >> clausule is not working ... >> here are the code: >> here is the initial cages that I want to display, no matter if they >> have related data or not: >> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND >> c.id > 0) ORDER BY order_position >> this seems to work, but I get ALL the cages, no matter if they are >> cages_type_id<>1 (I only want to display=1) >> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id IN >> (SELECT reserved_cage_id FROM reserved_days WHERE >> date='2008-02-15') ORDER BY order_position > > SELECT c.*, r.* > FROM cages c > LEFT JOIN reserved_cages r ON (c.id=r.cage_id) > > WHERE ( c.cages_type_id=1 AND c.id > 0) > AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE > date='2008-02-15') > > ORDER BY order_position > > > The main problem is the where clause from above got merged into the > left join clause. > > That would give you the cages with a reservation on the date. > Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..." > to get the ones without a reservation. I need to display ALL the cages, no matter if they have a reservation or not on the passed day > > I changed the AND r.id to AND c.id - I feel certain you want the > cage id not the id of the reservation entry matched against the > reserved_cage_id from reserved_days. well, the id from IN (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15') is the id of the reserved_cages, I can't change into this AND r.id to AND c.id becaue they are different id values. basically, what I want is display the name of the customer who has the reservation on the current cage and in the passed period. in the reserved_cage I have the date_in and date_out of the reservation, and in the reserved_days I have one row for each day the reservations occur, with other data that I need. maybe I can change the SELECT for using only two tables, using .... WHERE (reserved_cage.date_in <= '2008-02-15') AND (reserved_cage.date_out >= '2008-02-15') maybe this also works, and I use only two tables: SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER BY order_position but again, I think the WHERE clausule is affecting the LEFT JOIN .... and I'm only getting the cages with some reservation ... any idea on how to change it for showing all the cages, no matter if they have reservation or not ? thanks again, raimon > >> if I change the first AND for a WHERE, like this: >> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND r.id >> IN (SELECT reserved_cage_id FROM reserved_days WHERE >> date='2008-02-15') ORDER BY order_position >> I get only the cages that has some reservations on the date >> performed. >> The relations between tables are: >> cages: id >> reserved_cages: cage_id >> reserved_days: reserved_cage_id >> So I have to query for a given day if there are reservations, pass >> those rows to the reserved_cages (where I only store the date_in >> and date_out). >> I think I can use an extra field in the reserved_days adding a >> cage_id, the SELECT would be much simpler and I think much faster, >> but I'm trying to avoid duplicated data, and at the same time, >> learning postgresql and try to find more or less the limitations, >> maybe those limitations (if they're limitations) come from my head >> or from sql ... >> as always, thanks for your help ! > > reserved_cages would seem to be unnecessary as you can get the list > of reserved cage id's from the SELECT - > > SELECT c.id > FROM cages c > > WHERE ( c.cages_type_id=1 AND c.id > 0) > AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE > date='2008-02-15') > > ORDER BY order_position > > > If you are using the reserved_cages to hold data for who has > reserved it, I would think it be better stored in the reserved_days > table. (at least the id of the customer reserving the cage) > > > > -- > > Shane Ambler > pgSQL (at) Sheeky (dot) Biz > > Get Sheeky @ http://Sheeky.Biz > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Sorry for replying on top ... I can do it like this ... CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ; SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) ORDER BY order_position DROP TABLE d_reserved_cages; but I would prefer a direct solution without using/creating extra tables, I think it shouldn't be too complicated ... but at least it is for me ... thanks, raimon On 16/02/2008, at 15:41, Rai Developer wrote: > Hello, > > > > On 16/02/2008, at 14:23, Shane Ambler wrote: > >> Rai Developer wrote: >>> Hello, >>> I'm struggling my brain for some days without success ... >>> I have three tables: >>> cages >>> reserved_cages >>> reserved_days >>> Inside cages, I want to display all the id > 0 and >>> animal_type_id=1, and I want to display all of them no matter if >>> it has some reserved_cages related, so I have to use a LEFT JOIN. >> >> I would think that every cage has a valid id - meaning the id>0 >> should not be needed. > > yes, I need it, because there are special cages that have a negative > id > > >>> Ok, now, the reserved_cages must exist only when there are rows in >>> the reserved_days table. >>> What I'm getting, sometimes, is only the cages that has some >>> reserved_cages (because they have some reserved_days), and when I >>> try to display all of the cages, I can't exclude the ones that >>> have id>0 or animal_type_id=1, I get all of them, so it seems the >>> WHERE clausule is not working ... >>> here are the code: >>> here is the initial cages that I want to display, no matter if >>> they have related data or not: >>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND >>> c.id > 0) ORDER BY order_position >>> this seems to work, but I get ALL the cages, no matter if they are >>> cages_type_id<>1 (I only want to display=1) >>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id >>> IN (SELECT reserved_cage_id FROM reserved_days WHERE >>> date='2008-02-15') ORDER BY order_position >> >> SELECT c.*, r.* >> FROM cages c >> LEFT JOIN reserved_cages r ON (c.id=r.cage_id) >> >> WHERE ( c.cages_type_id=1 AND c.id > 0) >> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >> date='2008-02-15') >> >> ORDER BY order_position >> >> >> The main problem is the where clause from above got merged into the >> left join clause. >> >> That would give you the cages with a reservation on the date. >> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..." >> to get the ones without a reservation. > > I need to display ALL the cages, no matter if they have a > reservation or not on the passed day > > >> >> I changed the AND r.id to AND c.id - I feel certain you want the >> cage id not the id of the reservation entry matched against the >> reserved_cage_id from reserved_days. > > well, the id from IN (SELECT reserved_cage_id FROM reserved_days > WHERE date='2008-02-15') is the id of the reserved_cages, I can't > change into this AND r.id to AND c.id becaue they are different id > values. > > basically, what I want is display the name of the customer who has > the reservation on the current cage and in the passed period. in the > reserved_cage I have the date_in and date_out of the reservation, > and in the reserved_days I have one row for each day the > reservations occur, with other data that I need. > > maybe I can change the SELECT for using only two tables, using .... > WHERE (reserved_cage.date_in <= '2008-02-15') AND > (reserved_cage.date_out >= '2008-02-15') > > maybe this also works, and I use only two tables: > > SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON > (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND > (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER > BY order_position > > but again, I think the WHERE clausule is affecting the LEFT > JOIN .... and I'm only getting the cages with some reservation ... > > any idea on how to change it for showing all the cages, no matter if > they have reservation or not ? > > > thanks again, > > > raimon > > > >> >>> if I change the first AND for a WHERE, like this: >>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND r.id >>> IN (SELECT reserved_cage_id FROM reserved_days WHERE >>> date='2008-02-15') ORDER BY order_position >>> I get only the cages that has some reservations on the date >>> performed. >>> The relations between tables are: >>> cages: id >>> reserved_cages: cage_id >>> reserved_days: reserved_cage_id >>> So I have to query for a given day if there are reservations, pass >>> those rows to the reserved_cages (where I only store the date_in >>> and date_out). >>> I think I can use an extra field in the reserved_days adding a >>> cage_id, the SELECT would be much simpler and I think much faster, >>> but I'm trying to avoid duplicated data, and at the same time, >>> learning postgresql and try to find more or less the limitations, >>> maybe those limitations (if they're limitations) come from my head >>> or from sql ... >>> as always, thanks for your help ! >> >> reserved_cages would seem to be unnecessary as you can get the list >> of reserved cage id's from the SELECT - >> >> SELECT c.id >> FROM cages c >> >> WHERE ( c.cages_type_id=1 AND c.id > 0) >> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >> date='2008-02-15') >> >> ORDER BY order_position >> >> >> If you are using the reserved_cages to hold data for who has >> reserved it, I would think it be better stored in the reserved_days >> table. (at least the id of the customer reserving the cage) >> >> >> >> -- >> >> Shane Ambler >> pgSQL (at) Sheeky (dot) Biz >> >> Get Sheeky @ http://Sheeky.Biz >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On 16/02/2008, at 20:53, Rai Developer wrote: > Sorry for replying on top ... > > I can do it like this ... > > CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r > WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ; > > SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON > (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) ORDER BY > order_position There was a mistake, the first AND must be WHERE ... SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY order_position r. > DROP TABLE d_reserved_cages; > > but I would prefer a direct solution without using/creating extra > tables, I think it shouldn't be too complicated ... but at least it > is for me ... > > thanks, > > > raimon > > > On 16/02/2008, at 15:41, Rai Developer wrote: > >> Hello, >> >> >> >> On 16/02/2008, at 14:23, Shane Ambler wrote: >> >>> Rai Developer wrote: >>>> Hello, >>>> I'm struggling my brain for some days without success ... >>>> I have three tables: >>>> cages >>>> reserved_cages >>>> reserved_days >>>> Inside cages, I want to display all the id > 0 and >>>> animal_type_id=1, and I want to display all of them no matter if >>>> it has some reserved_cages related, so I have to use a LEFT JOIN. >>> >>> I would think that every cage has a valid id - meaning the id>0 >>> should not be needed. >> >> yes, I need it, because there are special cages that have a >> negative id >> >> >>>> Ok, now, the reserved_cages must exist only when there are rows >>>> in the reserved_days table. >>>> What I'm getting, sometimes, is only the cages that has some >>>> reserved_cages (because they have some reserved_days), and when I >>>> try to display all of the cages, I can't exclude the ones that >>>> have id>0 or animal_type_id=1, I get all of them, so it seems the >>>> WHERE clausule is not working ... >>>> here are the code: >>>> here is the initial cages that I want to display, no matter if >>>> they have related data or not: >>>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND >>>> c.id > 0) ORDER BY order_position >>>> this seems to work, but I get ALL the cages, no matter if they >>>> are cages_type_id<>1 (I only want to display=1) >>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id >>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>> date='2008-02-15') ORDER BY order_position >>> >>> SELECT c.*, r.* >>> FROM cages c >>> LEFT JOIN reserved_cages r ON (c.id=r.cage_id) >>> >>> WHERE ( c.cages_type_id=1 AND c.id > 0) >>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>> date='2008-02-15') >>> >>> ORDER BY order_position >>> >>> >>> The main problem is the where clause from above got merged into >>> the left join clause. >>> >>> That would give you the cages with a reservation on the date. >>> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN >>> (SELECT..." to get the ones without a reservation. >> >> I need to display ALL the cages, no matter if they have a >> reservation or not on the passed day >> >> >>> >>> I changed the AND r.id to AND c.id - I feel certain you want the >>> cage id not the id of the reservation entry matched against the >>> reserved_cage_id from reserved_days. >> >> well, the id from IN (SELECT reserved_cage_id FROM reserved_days >> WHERE date='2008-02-15') is the id of the reserved_cages, I can't >> change into this AND r.id to AND c.id becaue they are different id >> values. >> >> basically, what I want is display the name of the customer who has >> the reservation on the current cage and in the passed period. in >> the reserved_cage I have the date_in and date_out of the >> reservation, and in the reserved_days I have one row for each day >> the reservations occur, with other data that I need. >> >> maybe I can change the SELECT for using only two tables, using .... >> WHERE (reserved_cage.date_in <= '2008-02-15') AND >> (reserved_cage.date_out >= '2008-02-15') >> >> maybe this also works, and I use only two tables: >> >> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND >> (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER >> BY order_position >> >> but again, I think the WHERE clausule is affecting the LEFT >> JOIN .... and I'm only getting the cages with some reservation ... >> >> any idea on how to change it for showing all the cages, no matter >> if they have reservation or not ? >> >> >> thanks again, >> >> >> raimon >> >> >> >>> >>>> if I change the first AND for a WHERE, like this: >>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND >>>> r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>> date='2008-02-15') ORDER BY order_position >>>> I get only the cages that has some reservations on the date >>>> performed. >>>> The relations between tables are: >>>> cages: id >>>> reserved_cages: cage_id >>>> reserved_days: reserved_cage_id >>>> So I have to query for a given day if there are reservations, >>>> pass those rows to the reserved_cages (where I only store the >>>> date_in and date_out). >>>> I think I can use an extra field in the reserved_days adding a >>>> cage_id, the SELECT would be much simpler and I think much >>>> faster, but I'm trying to avoid duplicated data, and at the same >>>> time, learning postgresql and try to find more or less the >>>> limitations, maybe those limitations (if they're limitations) >>>> come from my head or from sql ... >>>> as always, thanks for your help ! >>> >>> reserved_cages would seem to be unnecessary as you can get the >>> list of reserved cage id's from the SELECT - >>> >>> SELECT c.id >>> FROM cages c >>> >>> WHERE ( c.cages_type_id=1 AND c.id > 0) >>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>> date='2008-02-15') >>> >>> ORDER BY order_position >>> >>> >>> If you are using the reserved_cages to hold data for who has >>> reserved it, I would think it be better stored in the >>> reserved_days table. (at least the id of the customer reserving >>> the cage) >>> >>> >>> >>> -- >>> >>> Shane Ambler >>> pgSQL (at) Sheeky (dot) Biz >>> >>> Get Sheeky @ http://Sheeky.Biz >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 4: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Rai Developer wrote: > maybe this also works, and I use only two tables: > > SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON > (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND > (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER > BY order_position > > but again, I think the WHERE clausule is affecting the LEFT JOIN .... > and I'm only getting the cages with some reservation ... > > any idea on how to change it for showing all the cages, no matter if > they have reservation or not ? So I take it that date_out will be the date it goes to the customer and date_in is when it returns to you. I had a bit more of a look this time and tested this one - SELECT c.*,r.* FROM cages c LEFT JOIN reserved_cages r ON (c.id=r.cage_id) AND ('2008-02-15' BETWEEN r.date_out AND r.date_in) WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY order_position Two things I can think of this way - is if the cage is advanced reserved for 208-02-17 - 2008-02-19 and you search for 2008-02-15 and they want it from 2008-02-15 for four days you won't see the advanced booking which will clash. Also if the date_out is set but not the date_in you won't see that it is out indefinitely. So you may want to use sub-selects (I merged the reservation detail into one column for simplicity) - SELECT c.*, (SELECT r.date_out||' '||r.customer FROM reserved_cages r WHERE c.id=r.cage_id AND ( ('2008-02-15' BETWEEN r.date_out AND r.date_in) OR (r.date_out<='2008-02-15' AND r.date_in IS NULL) OR (r.date_out>='2008-02-15') ) ORDER BY r.date_out LIMIT 1 ) as reserved FROM cages c WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY order_position (I'm sure some of the pro's can come up with something better though) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Sat, 16 Feb 2008, Rai Developer wrote: > Sorry for replying on top ... > > I can do it like this ... > > CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r > WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ; > > SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON > (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) ORDER BY > order_position You should be able to put what you have as the temp table definition query as a subselect in join probably. So something like the following: SELECT c.*,r.* FROM cages c LEFT JOIN (SELECT * from reserved_cages r WHERE r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') r ON (c.id=r.cage_id) WHERE (c.cages_type_id=1 AND c.id>0) ORDER BY order_position;
Shane and Stephan, thanks for your ideas, I'be veen very busy but I'm going to try them as soon as possible ... regards, raimon On 16/02/2008, at 21:39, Rai Developer wrote: > > On 16/02/2008, at 20:53, Rai Developer wrote: > >> Sorry for replying on top ... >> >> I can do it like this ... >> >> CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages >> r WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ; >> >> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON >> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) ORDER BY >> order_position > > There was a mistake, the first AND must be WHERE ... > > SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON > (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY > order_position > > > r. > > > >> DROP TABLE d_reserved_cages; >> >> but I would prefer a direct solution without using/creating extra >> tables, I think it shouldn't be too complicated ... but at least it >> is for me ... >> >> thanks, >> >> >> raimon >> >> >> On 16/02/2008, at 15:41, Rai Developer wrote: >> >>> Hello, >>> >>> >>> >>> On 16/02/2008, at 14:23, Shane Ambler wrote: >>> >>>> Rai Developer wrote: >>>>> Hello, >>>>> I'm struggling my brain for some days without success ... >>>>> I have three tables: >>>>> cages >>>>> reserved_cages >>>>> reserved_days >>>>> Inside cages, I want to display all the id > 0 and >>>>> animal_type_id=1, and I want to display all of them no matter if >>>>> it has some reserved_cages related, so I have to use a LEFT JOIN. >>>> >>>> I would think that every cage has a valid id - meaning the id>0 >>>> should not be needed. >>> >>> yes, I need it, because there are special cages that have a >>> negative id >>> >>> >>>>> Ok, now, the reserved_cages must exist only when there are rows >>>>> in the reserved_days table. >>>>> What I'm getting, sometimes, is only the cages that has some >>>>> reserved_cages (because they have some reserved_days), and when >>>>> I try to display all of the cages, I can't exclude the ones that >>>>> have id>0 or animal_type_id=1, I get all of them, so it seems >>>>> the WHERE clausule is not working ... >>>>> here are the code: >>>>> here is the initial cages that I want to display, no matter if >>>>> they have related data or not: >>>>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND >>>>> c.id > 0) ORDER BY order_position >>>>> this seems to work, but I get ALL the cages, no matter if they >>>>> are cages_type_id<>1 (I only want to display=1) >>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>>>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) AND r.id >>>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>>> date='2008-02-15') ORDER BY order_position >>>> >>>> SELECT c.*, r.* >>>> FROM cages c >>>> LEFT JOIN reserved_cages r ON (c.id=r.cage_id) >>>> >>>> WHERE ( c.cages_type_id=1 AND c.id > 0) >>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>> date='2008-02-15') >>>> >>>> ORDER BY order_position >>>> >>>> >>>> The main problem is the where clause from above got merged into >>>> the left join clause. >>>> >>>> That would give you the cages with a reservation on the date. >>>> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN >>>> (SELECT..." to get the ones without a reservation. >>> >>> I need to display ALL the cages, no matter if they have a >>> reservation or not on the passed day >>> >>> >>>> >>>> I changed the AND r.id to AND c.id - I feel certain you want the >>>> cage id not the id of the reservation entry matched against the >>>> reserved_cage_id from reserved_days. >>> >>> well, the id from IN (SELECT reserved_cage_id FROM reserved_days >>> WHERE date='2008-02-15') is the id of the reserved_cages, I can't >>> change into this AND r.id to AND c.id becaue they are different id >>> values. >>> >>> basically, what I want is display the name of the customer who has >>> the reservation on the current cage and in the passed period. in >>> the reserved_cage I have the date_in and date_out of the >>> reservation, and in the reserved_days I have one row for each day >>> the reservations occur, with other data that I need. >>> >>> maybe I can change the SELECT for using only two tables, >>> using .... WHERE (reserved_cage.date_in <= '2008-02-15') AND >>> (reserved_cage.date_out >= '2008-02-15') >>> >>> maybe this also works, and I use only two tables: >>> >>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND >>> (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') >>> ORDER BY order_position >>> >>> but again, I think the WHERE clausule is affecting the LEFT >>> JOIN .... and I'm only getting the cages with some reservation ... >>> >>> any idea on how to change it for showing all the cages, no matter >>> if they have reservation or not ? >>> >>> >>> thanks again, >>> >>> >>> raimon >>> >>> >>> >>>> >>>>> if I change the first AND for a WHERE, like this: >>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON >>>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND >>>>> r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>>> date='2008-02-15') ORDER BY order_position >>>>> I get only the cages that has some reservations on the date >>>>> performed. >>>>> The relations between tables are: >>>>> cages: id >>>>> reserved_cages: cage_id >>>>> reserved_days: reserved_cage_id >>>>> So I have to query for a given day if there are reservations, >>>>> pass those rows to the reserved_cages (where I only store the >>>>> date_in and date_out). >>>>> I think I can use an extra field in the reserved_days adding a >>>>> cage_id, the SELECT would be much simpler and I think much >>>>> faster, but I'm trying to avoid duplicated data, and at the same >>>>> time, learning postgresql and try to find more or less the >>>>> limitations, maybe those limitations (if they're limitations) >>>>> come from my head or from sql ... >>>>> as always, thanks for your help ! >>>> >>>> reserved_cages would seem to be unnecessary as you can get the >>>> list of reserved cage id's from the SELECT - >>>> >>>> SELECT c.id >>>> FROM cages c >>>> >>>> WHERE ( c.cages_type_id=1 AND c.id > 0) >>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE >>>> date='2008-02-15') >>>> >>>> ORDER BY order_position >>>> >>>> >>>> If you are using the reserved_cages to hold data for who has >>>> reserved it, I would think it be better stored in the >>>> reserved_days table. (at least the id of the customer reserving >>>> the cage) >>>> >>>> >>>> >>>> -- >>>> >>>> Shane Ambler >>>> pgSQL (at) Sheeky (dot) Biz >>>> >>>> Get Sheeky @ http://Sheeky.Biz >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 4: Have you searched our list archives? >>>> >>>> http://archives.postgresql.org >>>> >>> >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 9: In versions below 8.0, the planner will ignore your desire to >>> choose an index scan if your joining column's datatypes do not >>> match >>> >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >