Re: [SQL] join question - three tables, two with foreign keys to the first
maybe just to qualify, I get this: select emp.name, lv.from_date, lv.to_date, pay.amount from employee as emp left join employee_leave as lv on emp.id = lv.employee_id left join employee_pay as pay on emp.id = pay.employee_id where emp.id = 1; name | from_date | to_date | amount --+++ dim | 2002-10-05 | 2002-05-14 |100 dim | 2002-10-05 | 2002-05-14 |100 dim | 2002-10-06 | 2002-06-14 |100 dim | 2002-10-06 | 2002-06-14 |100 (4 rows) but would expect the results to be name | from_date | to_date | amount --+++ dim | 2002-10-05 | 2002-05-14 | (null) dim | 2002-10-05 | 2002-05-14 | (null) dim | (null) | (null)|100 dim | (null) | (null)|100 (4 rows) am I missing something? ta again dim - Original Message - From: "Dmitri Colebatch" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 14, 2002 12:13 AM Subject: [SQL] join question - three tables, two with foreign keys to the first > hey all, > > here's my situation. three tables: > > create table employee (id integer primary key, name varchar(32)); > create table employee_leave (id integer primary key, employee_id integer, > from_date date, to_date date, constraint emp_leave_fk foreign key > (employee_id) references employee (id)); > create table employee_pay (id integer primary key, employee_id integer, > amount integer, constraint emp_pay_fk foreign key (employee_id) references > employee (id)); > > and some sample data: > > insert into employee (id, name) values (1, 'dim'); > insert into employee_leave (id, employee_id, from_date, to_date) values (2, > 1, '10-05-2002', '14-05-2002'); > insert into employee_leave (id, employee_id, from_date, to_date) values (1, > 1, '10-06-2002', '14-06-2002'); > insert into employee_pay(id, employee_id, amount) values (1, 1, 100); > insert into employee_pay(id, employee_id, amount) values (2, 1, 100); > > and I want to retrieve the information for an employee (all pay, and all > leave) in one query here's what I've got > > select emp.name, lv.from_date, lv.to_date, pay.amount > from employee as emp > left outer join employee_leave as lv on emp.id = lv.employee_id > left outer join employee_pay as pay on emp.id = pay.employee_id > where emp.id = 1 > > problem is that I dont get the null values I expect I want to be able to > iterate through the resultset and determine if the record is from the leave > table or pay table - but because I dont get null results, I cant > > any pointers/suggestions would be welcome. > > cheers > dim > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(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] Rule problem
Svenn Helge Grindhaug <[EMAIL PROTECTED]> writes: > create rule a_delete as > on delete to a do > delete from b where id1 = old.id1; > create rule c_delete as > on delete to c do > delete from a where id1 = (select id1 from b where id2 = old.id2); The a_delete rule is run before the actual "DELETE FROM a" command, so by the time the latter runs, the b row is gone, and no match exists. I'd be inclined to think about doing this with foreign keys (set up for ON DELETE CASCADE) instead of handmade rules. If you really want to do it by hand, triggers will probably work better than rules. regards, tom lane ---(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] join question - three tables, two with foreign keys to the first
On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch" <[EMAIL PROTECTED]> wrote: >select emp.name, lv.from_date, lv.to_date, pay.amount >from employee as emp >left outer join employee_leave as lv on emp.id = lv.employee_id >left outer join employee_pay as pay on emp.id = pay.employee_id >where emp.id = 1 > >problem is that I dont get the null values I expect Dmitri, why do you expect nulls? SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2; returns nulls for the b-columns in the select list, if you have a row in t1 with a value t1.col1, that does not appear as col2 in any row of t2. In your example, however, you select a single row from emp with id = 1, and there are two rows in lv with employee_id = 1 and two rows in pay with employee_id = 1. And I doubt, you want to get the same row from lv more than once, only because there are multiple matches in pay, and vice versa. Add lv.id and pay.id to your SELECT to see what I mean. You may expect to get 4 rows, but what you get is not 2+2, but 2*2. Add some more rows and test again. Isn't there any relationship between lv and pay? I don't know if I understand your problem. Propably you want: SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id WHERE emp.id = 1 UNION ALL SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id WHERE emp.id = 1; or, if lv and pay are unrelated, why not two queries? SELECT emp.name, lv.from_date, lv.to_date FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id WHERE emp.id = 1; SELECT emp.name, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id WHERE emp.id = 1; HTH. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] join question - three tables, two with foreign keys to
On Fri, 14 Jun 2002, Dmitri Colebatch wrote: > maybe just to qualify, I get this: > > select emp.name, lv.from_date, lv.to_date, pay.amount > from employee as emp > left join employee_leave as lv on emp.id = lv.employee_id > left join employee_pay as pay on emp.id = pay.employee_id > where emp.id = 1; > > name | from_date | to_date | amount > --+++ > dim | 2002-10-05 | 2002-05-14 |100 > dim | 2002-10-05 | 2002-05-14 |100 > dim | 2002-10-06 | 2002-06-14 |100 > dim | 2002-10-06 | 2002-06-14 |100 > (4 rows) > > but would expect the results to be > > name | from_date | to_date | amount > --+++ > dim | 2002-10-05 | 2002-05-14 | (null) > dim | 2002-10-05 | 2002-05-14 | (null) > dim | (null) | (null)|100 > dim | (null) | (null)|100 > (4 rows) > > am I missing something? I don't see why you'd expect that. Both rows in employee_leave match and both rows in employee_pay match. They're not unrelated joins, you're asking to join employee with employee_leave and then join the results of that with employee_pay. Perhaps you want a union? Something like: select emp.name, lv.from_date, lv.to_date, null as amount from employee as emp, employee_leave as lv where emp.id= lv.employee_id union select emp.name, null, null, pay.amount from employee as emp, employee_pay as pay where emp.id= pay.employee_id If you want to get a row for an employee even when they have neither leave nor pay, you can use left joins above, but that'll give you some rows that'll be like NULL NULL NULL. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] how do i provide array parameters for my functions in php
i have just finished creating a function that has an array of integers as its parameter. what i would like to know is how do i declare/execute my function in php. i tried several syntax e.g. $arrvalue - an array of integers $strquery = "select functionname($arrvalue)"; $strquery = "select functionname($arrvalue[])"; what is the right syntax TIA joseph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Another postgres 'file not found' error
Ian, > This is the second time within a week I've been hit by a peculiar > 'file not > found' error. I've been replacing a few existing Oracle servers with > Postgres. I'm wondering if that was a wise decision :/ Not if you don't know how to run PostgreSQL or your machines, no. Migrating to a new platform without sufficient testing or expertise is always unwise (sorry, but you deserved that for the dig at Postgres). > inbound=# update store set state = 0, timestamp = 'now' where > timestamp < > (CURRENT_TIMESTAMP - interval '30 seconds') and accountid = '6'; > ERROR: cannot open segment 1 of relation store_ix1 (target block > 1576985508): No such file or directory Either: 1. You have a bad PostgreSQL install. What OS are you using? 2. You are out of disk space on the PostgreSQL partition. 3. You are having hardware errors, such as a corrupt HDD. 4. Your OS is having disk or file management errors. Regardless, you'd better shutdown Postgres and defer all work on the database until you've diagnosed your hardware/configuration problem. I hope you have a previous backup, as you may find that you need to revert to an earlier backup to restore your data. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Another postgres 'file not found' error
> Not if you don't know how to run PostgreSQL or your machines, no. > Migrating to a new platform without sufficient testing or expertise > is always unwise (sorry, but you deserved that for the dig at > Postgres). Heh. Well it's most certainly the case I'm still learning about Postgres (aren't we all?), but I did run it for a few months on unimportant stuff and to be honest, this application I'm having trouble with at the moment is not real important even though it's production. The database is being continuously populated and drained by different applications (its a spool area mainly), so activity is high even though volume is not. > Either: > 1. You have a bad PostgreSQL install. What OS are you using? Debian Woody with 2.4.18 Linux kernel. Postgres install from apt-get (7.2.1). > 2. You are out of disk space on the PostgreSQL partition. Approx 15gig free. > 3. You are having hardware errors, such as a corrupt HDD. No other untoward signs. > 4. Your OS is having disk or file management errors. No other untoward signs. > Regardless, you'd better shutdown Postgres and defer all work on the > database until you've diagnosed your hardware/configuration problem. I > hope you have a previous backup, as you may find that you need to > revert to an earlier backup to restore your data. In this instance, simply dropping the index and recreating it solved the problem. I'll continue using this configuration whilst trying to find the problem, however I'll defer any future migrations though until I've got to the root of it. -- Ian Cass ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] make a unique index for foreign keys?
Chris/ Josh- OK-response to Chris below. Synopsis heresimply by creating a foreign key will not create an index. On large tables I SHOULD put a non-unique index on the foreign key (right?) Hmm...nope. Creating a foreign key doesn't create an index. However, creating a primary key does - sure you're not getting mixed up there? You are absolutely right! I am confused I did the actual test //create the tables beth=> create table parent(pk INTEGER primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'parent_pkey' for table 'parent' CREATE beth=> create table child (fk INTEGER references parent(pk) ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE // now get the index oid for the parent table (there should only be 1 index for the primary key) beth=> select pg_index.* from pg_index, pg_class where pg_class.oid = pg_index. indrelid and pg_class.relname='parent'; indexrelid | indrelid | indproc | indkey | indclass | indisclustered | indisunique | indisprimary | indreference | +--+-++--+-- --+-+--+--+- 26271 |26269 | - | 1 | 1978 | f | t | t|0 | (1 row) // get the index for the parent table--and it is the index for the primary key beth=> select pg_get_indexdef(26271); pg_get_indexdef -- -- CREATE UNIQUE INDEX parent_pkey ON parent USING btree (pk) (1 row) //now try that same thing for the child table and there are NO indices (also confirmed by \di) beth=> select pg_index.* from pg_index, pg_class where pg_class.oid = pg_index. indrelid and pg_class.relname='child'; indexrelid | indrelid | indproc | indkey | indclass | indisclustered | indisunique | indisprimary | indreference | +--+-++--+-- --+-+--+--+- (0 rows) Postgres doesn't create indices for you, unless as part of a primary key or unique constraint. OK-I've got it now!!! Thank you so much Beth ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how do i provide array parameters for my functions in php
On Thu, 13 Jun 2002 [EMAIL PROTECTED] wrote:
> i have just finished creating a function that has an array of integers as its
> parameter. what i would like to know is how do i declare/execute my function
> in php. i tried several syntax
> e.g.
> $arrvalue - an array of integers
>
> $strquery = "select functionname($arrvalue)";
> $strquery = "select functionname($arrvalue[])";
If you wanna do a select i think is safe to construct the select statement
as a string (including your arrays).
E.g.
$strquery = "select func('{1,2,3,4}')";
Now if your function outputs an array, thats a little bit more complicated
lies in the php field and you need to ask pgsql-php or something like
that.
>
> what is the right syntax
> TIA
> joseph
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
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] make a unique index for foreign keys?
Beth Gatewood wrote: > > Chris/ Josh- > > OK-response to Chris below. Synopsis heresimply by creating a foreign > key will not create an index. On large tables I SHOULD put a non-unique > index on the foreign key (right?) Right, because on DELETE or UPDATE to the primary key, the RI triggers have to check that this doesn't leave orphaned references or do the required referential action (CASCADE). Sometimes this works faster with an index... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Another postgres 'file not found' error
Ian, > Debian Woody with 2.4.18 Linux kernel. Postgres install from apt-get > (7.2.1). Hmmm. I might suggest polling both the Debian mailing lists and the pgsql-hackers mailing list. I remember vaguely hearing about some bug with Postgres on Debian, but I can't remember where I heard it. It's also possible that one or more of the accessory applications you are using is playing "fast and loose" with the filesystem, and in the process damaging some of the Postgres files. -- -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Isn't there a better way?
Folks,
Given the following tables:
--DROP TABLE teams_desc;
create table teams_desc (
teams_id INT4 NOT NULL DEFAULT NEXTVAL('users_user_id_sq') PRIMARY KEY,
teams_name VARCHAR(75) NOT NULL,
teams_code VARCHAR(20) NOT NULL,
notes TEXT NULL );
--drop table teams_tree;
create table teams_tree (
teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,
treeno INT4 NOT NULL,
constraint pk_teams_tree PRIMARY KEY (teams_id, treeno)
);
--drop table teams_users;
create table teams_users (
teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,
user_id INT4 NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
leader BOOLEAN NOT NULL DEFAULT FALSE,
constraint teams_users_pk PRIMARY KEY ( teams_id, user_id )
);
drop view teams;
create view teams as
select teams_id, teams_name, teams_code, notes,
min(treeno) as lnode, max(treeno) as rnode
from teams_desc JOIN teams_tree USING (teams_id)
group by teams_id, teams_name, teams_code, notes;
I need to construct a query that will delete all duplicate users within a tree
barnch, leaving only the user references which are "lowest" on the tree. The
best I've been able to come up with is:
v_left := current branch left node
v_right := current branch right node
DELETE FROM teams_users
WHERE EXISTS (SELECT teams.team_id
FROM teams JOIN teams_users tu2 USING (team_id)
WHERE EXISTS (SELECT MAX(tm.lnode), MIN(tm.lnode), user_id
FROM teams_users tu JOIN teams tm USING (team_id)
WHERE ((tm.lnode > v_left and tm.rnode < v_right)
OR (tm.lnode < v_left AND tm.rnode > v_right))
GROUP BY user_id
HAVING MIN(tm.lnode) < MAX(tm.lnode) AND
tu.user_id = tu2.user_id
AND MAX(tm.lnode) > teams.lnode)
AND teams_users.team_id = tu2.team_id and teams_users.user_id = tu2.user_id);
But that's a nested WHERE EXISTS clause, with an aggregate referenceing the
same aggregated view twice. It seems like there must be a more efficient
way to build this query, but I can't think of one. Suggestions?
-Josh Berkus
P.S. This is based on Joe Celko's Linear Nested Model of tree construction.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] serial column vs. explicit sequence question
I'm having a problem and there seems to be 2 solutions. It is simple and straighforward, but will take several paragraphs to explain. I have a schema with a master-detail design. The master table does not have an expicit id, so I have a column of type serial. Lets say I need to insert a row into the master table and N rows into the detail table. After inserting a row into master, and before detail, I need to read the master table to obtain the value of the id for the row just inserted, so I can insert this id as the foreign key value for the N rows in the detail table. This seems like a poor solution because I have to write and then read the master table each time. With lot of activity on these tables, I don't know how well this will scale. Additionally, the only way that I can guarantee that I am getting the id of the most recent row inserted into master is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because otherwise, if other processes are inserting rows into master/detail concurrently, I may pick up the id from an incorrect row (the technique used to get the correct id is to include a timestamp column on the insert into master and then query for the latest row). A better solution would seem to use a sequence explicitly, rather than a id column of type serial. I would obtain the id value from the sequence, and then insert this id into the master table and into the detail table. This way, I wouldn't be writing/reading the same table constantly -- I would only be writing to it, and, I would guarantee that I would be using the correct id in both master and detail without have to SET TRANSACTION ISOLATION LEVEL SERIALIZEABLE. Any comments on which solution you would choose, or is there a better solution ? Thanks, Charlie ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Another postgres 'file not found' error
"Ian Cass" <[EMAIL PROTECTED]> writes: > inbound=# update store set state = 0, timestamp = 'now' where timestamp < > (CURRENT_TIMESTAMP - interval '30 seconds') and accountid = '6'; > ERROR: cannot open segment 1 of relation store_ix1 (target block > 1576985508): No such file or directory and later: > In this instance, simply dropping the index and recreating it solved the > problem. Yeah, it would seem that you had a bad tuple pointer in an index entry. While this might have been induced by a software bug, I do not recall many (if any) prior reports of such failures, so I'm leaning towards a hardware glitch having caused it. I'd recommend running some memory and disk diagnostics ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Please help me out on this insert error
Command:
Insert into profile (userid, haveChildren)values('id98', 'No');
Error:
ERROR: Relation 'profile' does not have attribute 'havaChildren'
Table:
Table "profile"
Column| Type | Modifiers
--+---+--
userid | character varying(25) | not null
sex | character(1) | not null
dob | date | not null
pob | character varying(20) |
status | character varying(20) | not null
ethnicity| character varying(20) | not null
religion | character varying(20) | not null
bodytype | character varying(20) | not null
height | numeric(3,0) | not null
weight | numeric(2,0) | not null
education| character varying(20) |
occupation | character varying(20) | not null
incomelow| numeric(6,0) | not null
incomeup | numeric(6,0) | not null
haveChildren | character varying(20) | not null
wantChildren | character varying(20) | not null
drinking | character varying(20) |
smoking | character varying(20) | not null
narration| text |
Primary key: pro_pkey
What is wrong here?
Thanks.
---(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] serial column vs. explicit sequence question
On Thu, 13 Jun 2002, Charlie Toohey wrote: > I'm having a problem and there seems to be 2 solutions. It is simple and > straighforward, but will take several paragraphs to explain. > > I have a schema with a master-detail design. The master table does not have > an expicit id, so I have a column of type serial. > > Lets say I need to insert a row into the master table and N rows into the > detail table. After inserting a row into master, and before detail, I need to > read the master table to obtain the value of the id for the row just > inserted, so I can insert this id as the foreign key value for the N rows in > the detail table. > > This seems like a poor solution because I have to write and then read the > master table each time. With lot of activity on these tables, I don't know > how well this will scale. Additionally, the only way that I can guarantee > that I am getting the id of the most recent row inserted into master is to > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because otherwise, if other > processes are inserting rows into master/detail concurrently, I may pick up > the id from an incorrect row (the technique used to get the correct id is to > include a timestamp column on the insert into master and then query for the > latest row). > > A better solution would seem to use a sequence explicitly, rather than a id > column of type serial. I would obtain the id value from the sequence, and > then insert this id into the master table and into the detail table. This > way, I wouldn't be writing/reading the same table constantly -- I would only > be writing to it, and, I would guarantee that I would be using the correct id > in both master and detail without have to SET TRANSACTION ISOLATION LEVEL > SERIALIZEABLE. > > Any comments on which solution you would choose, or is there a better > solution ? Well, serial really is just an integer with a default value pulling from a sequence, so right now you can use currval on the sequence (which I think gets named something like __seq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] serial column vs. explicit sequence question
Charlie,
> Lets say I need to insert a row into the master table and N rows into the
> detail table. After inserting a row into master, and before detail, I need
to
> read the master table to obtain the value of the id for the row just
> inserted, so I can insert this id as the foreign key value for the N rows in
> the detail table.
Hey, what about CURRVAL('sequence_name')?
--
-Josh Berkus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] serial column vs. explicit sequence question
Charlie Toohey <[EMAIL PROTECTED]> writes:
> A better solution would seem to use a sequence explicitly, rather than a id
> column of type serial. I would obtain the id value from the sequence, and
> then insert this id into the master table and into the detail table.
Yup. But there's no need to change how you create the id column; serial
is just fine, since all it is is a sequence and a DEFAULT nextval('foo').
You just do something like
select nextval('name-of-id-columns-sequence') into $masterid;
insert into master(id, ...) values ($masterid, ...);
insert into detail ... $masterid ...;
rather than letting the default expression do it for you.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Another postgres 'file not found' error
On Thu, 2002-06-13 at 19:01, Josh Berkus wrote: > > Debian Woody with 2.4.18 Linux kernel. Postgres install from apt-get > > (7.2.1). > > Hmmm. I might suggest polling both the Debian mailing lists and the > pgsql-hackers mailing list. I remember vaguely hearing about some bug with > Postgres on Debian, but I can't remember where I heard it. I do not think there is any Debian bug report that looks like this. The full bug list is at http://bugs.debian.org/cgi-bin/pkgreport.cgi?pkg=postgresql -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Whether therefore ye eat, or drink, or whatsoever ye do, do all to the glory of God." I Corinthians 10:31 signature.asc Description: This is a digitally signed message part
Re: [SQL] Please help me out on this insert error
Vernon Wu wrote:
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
>
> Error:
>
> ERROR: Relation 'profile' does not have attribute 'havaChildren'
^^^
From the error message, looks like you spelled haveChildren wrong.
HTH,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] serial column vs. explicit sequence question
If session A and session B are concurrently doing the same master-detail transaction, wouldn't currval possibly reflect the sequence value used by the other session ? Or are you saying that since this will be an explicit transaction that currval won't reflect the fact that the sequence may have been incremented by another session ? On Thursday 13 June 2002 02:06 pm, Stephan Szabo wrote: > On Thu, 13 Jun 2002, Charlie Toohey wrote: > > I'm having a problem and there seems to be 2 solutions. It is simple and > > straighforward, but will take several paragraphs to explain. > > > > I have a schema with a master-detail design. The master table does not > > have an expicit id, so I have a column of type serial. > > > > Lets say I need to insert a row into the master table and N rows into the > > detail table. After inserting a row into master, and before detail, I > > need to read the master table to obtain the value of the id for the row > > just inserted, so I can insert this id as the foreign key value for the N > > rows in the detail table. > > > > This seems like a poor solution because I have to write and then read the > > master table each time. With lot of activity on these tables, I don't > > know how well this will scale. Additionally, the only way that I can > > guarantee that I am getting the id of the most recent row inserted into > > master is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because > > otherwise, if other processes are inserting rows into master/detail > > concurrently, I may pick up the id from an incorrect row (the technique > > used to get the correct id is to include a timestamp column on the insert > > into master and then query for the latest row). > > > > A better solution would seem to use a sequence explicitly, rather than a > > id column of type serial. I would obtain the id value from the sequence, > > and then insert this id into the master table and into the detail table. > > This way, I wouldn't be writing/reading the same table constantly -- I > > would only be writing to it, and, I would guarantee that I would be using > > the correct id in both master and detail without have to SET TRANSACTION > > ISOLATION LEVEL SERIALIZEABLE. > > > > Any comments on which solution you would choose, or is there a better > > solution ? > > Well, serial really is just an integer with a default value pulling from a > sequence, so right now you can use currval on the sequence (which I think > gets named something like __seq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] serial column vs. explicit sequence question
Charlie Toohey <[EMAIL PROTECTED]> writes:
> I'm having a problem and there seems to be 2 solutions. It is simple
> and straighforward, but will take several paragraphs to explain.
>
> I have a schema with a master-detail design. The master table does
> not have an expicit id, so I have a column of type serial.
>
> Lets say I need to insert a row into the master table and N rows
> into the detail table. After inserting a row into master, and before
> detail, I need to read the master table to obtain the value of the
> id for the row just inserted, so I can insert this id as the foreign
> key value for the N rows in the detail table.
>
> This seems like a poor solution because I have to write and then
> read the master table each time. With lot of activity on these
> tables, I don't know how well this will scale. Additionally, the
> only way that I can guarantee that I am getting the id of the most
> recent row inserted into master is to SET TRANSACTION ISOLATION
> LEVEL SERIALIZABLE --- because otherwise, if other processes are
> inserting rows into master/detail concurrently, I may pick up the id
> from an incorrect row (the technique used to get the correct id is
> to include a timestamp column on the insert into master and then
> query for the latest row).
>
> A better solution would seem to use a sequence explicitly, rather
> than a id column of type serial. I would obtain the id value from
> the sequence, and then insert this id into the master table and into
> the detail table. This way, I wouldn't be writing/reading the same
> table constantly -- I would only be writing to it, and, I would
> guarantee that I would be using the correct id in both master and
> detail without have to SET TRANSACTION ISOLATION LEVEL
> SERIALIZEABLE.
>
> Any comments on which solution you would choose, or is there a
> better solution ?
>
> Thanks,
> Charlie
The SERIAL type is a thin veneer over an underlying conglomeration of
a unique index and a sequence, nothing more, nothing less. I still
prefer to use the old syntax that spelled this out explicitly (mostly
because it reminded me that I needed to drop the sequences as well as
the table if I made changes during the development phases of my
project). Instead of using a serial type I have a whole pile of
scripts that contain bits that look like this:
DROP TABLE prod_journal;
DROP SEQUENCE prod_journal_id_seq;
CREATE SEQUENCE prod_journal_id_seq;
CREATE TABLE prod_journal (
id int PRIMARY KEY
DEFAULT nextval('prod_journal_id_seq'),
...
);
The SERIAL type does precisely the same sort of thing. The only
difference is that PostgreSQL thinks up the sequence name for you
(currently PostgreSQL tries to choose a name that looks precisely like
the one I have chosen in this example). The reason that I bring this
up is A) it makes me happy to think that I have been using PostgreSQL
long enough that my PostgreSQL memories predate the SERIAL type, and
B) to point out that there is not really a difference between using
the SERIAL type and using sequences explicitly.
What you *really* need is to get acquainted with the nifty sequence
functions currval and nextval. They hold the secret to sequence
Nirvana. See Chapter 4 Section 11 of the PostgreSQL User's Guide for
the full scoop. The short story is that curval gives the current
value of the sequence (for whichever backend you are connected to) and
nextval will give you the next value of the sequence.
Now let's say that you had two simple tables foo for the master record
and bar for the detail records.
test=# create table foo (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL column
'foo.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table
'foo'
CREATE
test=# create table bar (master int references foo, detail text);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
You could then insert into these tables using something like this:
test=# begin;
BEGIN
test=# insert into foo (name) values ('Jason');
INSERT 67423220 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Does this
work');
INSERT 67423221 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Apparently
So!');
INSERT 67423222 1
test=# commit;
COMMIT
As long as you hang onto your connection to the back end you don't
even have to wrap this as one transaction. Currval is connection
dependent, and so as long as you have the same connection currval will
give the correct answer, and currval is very very fast.
Hope this was helpful,
Jason
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] serial column vs. explicit sequence question
that definitely helps ! thank you Jason --- the key thing that I didn't
undertand, and you have now enlightened me, is that currval was connection
dependent --- I didn't think this would be guaranteed to work with concurrent
transactions, but now I understand.
Just prior to receiving your message, I posted a reply basically asking how
currval would work if there were concurrent updates --- please ignore that
response.
thanks everyone --- I now feel "empowered" to carry on with my project
On Thursday 13 June 2002 03:01 pm, Jason Earl wrote:
> Charlie Toohey <[EMAIL PROTECTED]> writes:
> > I'm having a problem and there seems to be 2 solutions. It is simple
> > and straighforward, but will take several paragraphs to explain.
> >
> > I have a schema with a master-detail design. The master table does
> > not have an expicit id, so I have a column of type serial.
> >
> > Lets say I need to insert a row into the master table and N rows
> > into the detail table. After inserting a row into master, and before
> > detail, I need to read the master table to obtain the value of the
> > id for the row just inserted, so I can insert this id as the foreign
> > key value for the N rows in the detail table.
> >
> > This seems like a poor solution because I have to write and then
> > read the master table each time. With lot of activity on these
> > tables, I don't know how well this will scale. Additionally, the
> > only way that I can guarantee that I am getting the id of the most
> > recent row inserted into master is to SET TRANSACTION ISOLATION
> > LEVEL SERIALIZABLE --- because otherwise, if other processes are
> > inserting rows into master/detail concurrently, I may pick up the id
> > from an incorrect row (the technique used to get the correct id is
> > to include a timestamp column on the insert into master and then
> > query for the latest row).
> >
> > A better solution would seem to use a sequence explicitly, rather
> > than a id column of type serial. I would obtain the id value from
> > the sequence, and then insert this id into the master table and into
> > the detail table. This way, I wouldn't be writing/reading the same
> > table constantly -- I would only be writing to it, and, I would
> > guarantee that I would be using the correct id in both master and
> > detail without have to SET TRANSACTION ISOLATION LEVEL
> > SERIALIZEABLE.
> >
> > Any comments on which solution you would choose, or is there a
> > better solution ?
> >
> > Thanks,
> > Charlie
>
> The SERIAL type is a thin veneer over an underlying conglomeration of
> a unique index and a sequence, nothing more, nothing less. I still
> prefer to use the old syntax that spelled this out explicitly (mostly
> because it reminded me that I needed to drop the sequences as well as
> the table if I made changes during the development phases of my
> project). Instead of using a serial type I have a whole pile of
> scripts that contain bits that look like this:
>
> DROP TABLE prod_journal;
> DROP SEQUENCE prod_journal_id_seq;
>
> CREATE SEQUENCE prod_journal_id_seq;
>
> CREATE TABLE prod_journal (
>id int PRIMARY KEY
> DEFAULT nextval('prod_journal_id_seq'),
>...
> );
>
> The SERIAL type does precisely the same sort of thing. The only
> difference is that PostgreSQL thinks up the sequence name for you
> (currently PostgreSQL tries to choose a name that looks precisely like
> the one I have chosen in this example). The reason that I bring this
> up is A) it makes me happy to think that I have been using PostgreSQL
> long enough that my PostgreSQL memories predate the SERIAL type, and
> B) to point out that there is not really a difference between using
> the SERIAL type and using sequences explicitly.
>
> What you *really* need is to get acquainted with the nifty sequence
> functions currval and nextval. They hold the secret to sequence
> Nirvana. See Chapter 4 Section 11 of the PostgreSQL User's Guide for
> the full scoop. The short story is that curval gives the current
> value of the sequence (for whichever backend you are connected to) and
> nextval will give you the next value of the sequence.
>
> Now let's say that you had two simple tables foo for the master record
> and bar for the detail records.
>
> test=# create table foo (id serial primary key, name text);
> NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL
> column 'foo.id' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
> index 'foo_pkey' for table 'foo' CREATE
>
> test=# create table bar (master int references foo, detail text);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s) CREATE
>
> You could then insert into these tables using something like this:
>
> test=# begin;
> BEGIN
> test=# insert into foo (name) values ('Jason');
> INSERT 67423220 1
> test=# insert into bar (master, detail) values (currval('foo_id_seq'),
> 'Does this wor
Re: [SQL] serial column vs. explicit sequence question
On Thu, 13 Jun 2002, Charlie Toohey wrote: > If session A and session B are concurrently doing the same master-detail > transaction, wouldn't currval possibly reflect the sequence value used by the > other session ? Or are you saying that since this will be an explicit > transaction that currval won't reflect the fact that the sequence may have > been incremented by another session ? currval is defined to give the last value given to your session. The only cases you have to watch out for are other potential nextvals in your session (triggers/rules), but usually you can find those pretty easily. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Please help me out on this insert error
On Thu, 13 Jun 2002, Vernon Wu wrote: > I, however, didn't use double quote mark when I created the table at all. If you used an interface to generate the table def, alot of them add the quote marks behind your back when they do the creation. In general, it's safer to just use all lowercase names. :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Please help me out on this insert error
On Thu, 13 Jun 2002, Vernon Wu wrote:
>
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
You presumably used double quotes when creating the column, so
you need to use them to refer to the column from that point on:
insert into profile(userid, "haveChildren") ...
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Please help me out on this insert error
On Thu, 13 Jun 2002 13:16:29 +0800, Vernon Wu
<[EMAIL PROTECTED]> wrote:
>
>Command:
>
>Insert into profile (userid, haveChildren)values('id98', 'No');
>
>Error:
>
>ERROR: Relation 'profile' does not have attribute 'havaChildren'
^
cut'n'paste error here ?
>Table:
> Table "profile"
>Column| Type | Modifiers
>--+---+--
> userid | character varying(25) | not null
> [...]
> haveChildren | character varying(20) | not null
Anyway, try
Insert into profile (userid, "haveChildren") values('id98', 'No');
Servus
Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Please help me out on this insert error
You are right, Steve. It needs the double quote mark. After I use the double quote
mark, an error message is:
ERROR: ExecAppend: Fail to add null value in not null attribute ...
which is right since I don't have non-null value to non-null field yet.
I, however, didn't use double quote mark when I created the table at all.
Now, I need to figure out how to add double quote mark in Java code query string.
v.
6/14/2002 6:12:18 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote:
>On Thu, 13 Jun 2002, Vernon Wu wrote:
>
>>
>> Command:
>>
>> Insert into profile (userid, haveChildren)values('id98', 'No');
>
>You presumably used double quotes when creating the column, so
>you need to use them to refer to the column from that point on:
>
>insert into profile(userid, "haveChildren") ...
>
>
>
---(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] join question - three tables, two with foreign keys to the first
> why do you expect nulls? probably because my sql is extremely rusty (o: > SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2; > > returns nulls for the b-columns in the select list, if you have a row > in t1 with a value t1.col1, that does not appear as col2 in any row of > t2. In your example, however, you select a single row from emp with > id = 1, and there are two rows in lv with employee_id = 1 and two rows > in pay with employee_id = 1. yes, as Stephan Szabo wrote: >> Both rows in employee_leave match and both rows >> in employee_pay match. They're not unrelated joins, >> you're asking to join employee with employee_leave >> and then join the results of that with employee_pay. that makes perfect sense. What I wanted is what you have given below (I think - I've only looked quickly so far). I suppose I want to do the left outer join on leave, and a left outer join on pay - I dont want to join the results of the first join with the second - as the pay and leave tables are unrelated - except for the fact that they both have a fk to emp. > And I doubt, you want to get the same row from lv more than once, only > because there are multiple matches in pay, and vice versa. Add lv.id > and pay.id to your SELECT to see what I mean. You may expect to get 4 > rows, but what you get is not 2+2, but 2*2. Add some more rows and > test again. Isn't there any relationship between lv and pay? no relationship. what I wanted is: - for each row in employee, select all matching records in pay - for each row in employee, select all matching records in leave - if no records match, select the matching record in employee alone. from memory, oracle would do this by sql somehting like: select emp.name, lv.from_date, lv.to_date, pay.amount from employee as emp, employee_leave as lv, employee_pay as pay where emp.id = (+) lv.employee_id and emp.id = (+) pay.employee_id where emp.id = 1 (although I can never remember the side that the + goes on) > I don't know if I understand your problem. Propably you want: > > SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount > FROM employee AS emp > LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id > WHERE emp.id = 1 > UNION ALL > SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount > FROM employee AS emp > LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id > WHERE emp.id = 1; yes, I think this is what I want which is effectively just the same as below yes? > or, if lv and pay are unrelated, why not two queries? I was wanting to only have one trip to the database. I've always been taught to avoid multiple db trips where possible. thanks for your help - much appreciated. cheers dim > > SELECT emp.name, lv.from_date, lv.to_date > FROM employee AS emp > LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id > WHERE emp.id = 1; > SELECT emp.name, pay.amount > FROM employee AS emp > LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id > WHERE emp.id = 1; > > HTH. > Servus > Manfred > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Please help me out on this insert error
6/14/2002 6:31:16 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote: > >On Thu, 13 Jun 2002, Vernon Wu wrote: > >> I, however, didn't use double quote mark when I created the table at all. > >If you used an interface to generate the table def, alot of them add the >quote marks behind your back when they do the creation. That must be what really happended without my knowledge. I used the pgAdmin to create the table. So, it is safer to create tables using the command line. In general, it's >safer to just use all lowercase names. :) > > That is a separated issue, is it? Thanks. v ---(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] simple recursive function in plpgsql fails
ians=# SELECT version();
version
---
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
My goal is to find the last occurance of a pattern in a string. As a
helper function, I wrote this:
DROP FUNCTION reverse(text);
CREATE FUNCTION reverse(text) RETURNS text AS
'DECLARE str ALIAS FOR $1;
BEGIN IF length(str) > 1 THEN
RETURN reverse(substr(str, 2)) || substr(str, 1, 1);
ELSE
RETURN str;
END IF;
END;' LANGUAGE 'plpgsql'
ians=# SELECT reverse('q');
reverse
-
q
(1 row)
ians=# SELECT reverse('qw');
reverse
-
wq
(1 row)
ians=# SELECT reverse('qwe');
reverse
-
ewq
(1 row)
ians=# SELECT reverse('qwer');
reverse
-
rerq
(1 row)
Ooops...
Andrew G. Hammond [EMAIL PROTECTED]
http://xyzzy.dhs.org/~drew/
Re: [SQL] join question - requesting for a simple C program where it can INSERT data into database as reference
My problem is, i am writing a simple retrieval program using C, to retrieve
a set of records from database. Then i'll make certain calculation based on
the data that i have retrieved and write it on a new database. I have follow
the sample program to do the retrieval and it works. But i don't seems to
have any samples to do an INSERT to the database? please anyone who have
such simple or working programs where it can INSERT to any database , please
reply to me as a reference.
here is the sample program that i used to do my retrieval:
#include
#include "libpq-fe.h"
void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
main()
{
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
int nFields;
int i,
j;
PGconn *conn;
PGresult *res;
pghost = NULL;
pgport = NULL;
pgoptions = NULL;
pgtty = NULL;
dbName = "template1";
conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "BEGIN");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from event");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in mycursor");
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH ALL command didn't return tuples
properly\n");
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
res = PQexec(conn, "CLOSE mycursor");
PQclear(res);
res = PQexec(conn, "COMMIT");
PQclear(res);
PQfinish(conn);
return 0;
}
Thanks,
joo
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] make a unique index for foreign keys?
> Chris/ Josh- > > OK-response to Chris below. Synopsis heresimply by creating a foreign > key will not create an index. On large tables I SHOULD put a non-unique > index on the foreign key (right?) For large tables, I guess you may as well. You can be more scientific about it if you you unhash this in your postgresql.conf: stats_command_string = true stats_row_level = true stats_block_level = true Then you can just use the pg_stat views to see how many sequential scans are being run over your tables and how expensive they are, etc. Chris ---(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] simple recursive function in plpgsql fails
Andrew Hammond <[EMAIL PROTECTED]> writes: > [ problems with a recursive plpgsql function ] Yeah, there are some known bugs there :-(. AFAICS this cannot be fixed properly until we get around to doing the querytree restructuring that I keep ranting about --- in particular, making a clean distinction between plan tree and execution state. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
