1

I have created these two tables:

CREATE TABLE Purchase(
purchaseID SERIAL,
custName VARCHAR(30) NOT null,
PRIMARY KEY (purchaseID));

CREATE TABLE PurchasedItem(
purchaseID INT,
itemNo INT NOT NULL,
PRIMARY KEY (purchaseID, itemNo),
FOREIGN KEY (purchaseID) REFERENCES Purchase(purchaseID));

Next I wish to insert data into both tables, with the purchaseID foreign key of purchased item having the same value as the purchaseID Serial from Purchase table.

I am using a PostgreSQL client called PSequel. I tried setting AUTOCOMMIT to off first in the client so I could have the two INSERT statement in the same transaction, however the client didn't recognise "autocommit", so I tried it in the terminal and I think it worked... anyway, these are the two INSERT statements I tried.

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) VALUES (DEFAULT, 111);
commit;

However I get an error: 

ERROR: null value in column purchaseID violates not-null constraint.

this is referring to the PurchasedItem's purchaseID as in running the first INSERT statement by itself it works. How do I solve this problem?

Ivan Lendl
  • 87
  • 1
  • 2
  • 8

2 Answers2

2

DEFAULT will work for SERIAL as it sets default value for column. So 

INSERT INTO Purchase VALUES (DEFAULT,'Lendl');

should work. But PurchasedItem.purchaseID has no default value set, so it it tries to insert NULL (and null is not in referenced column yet), so it fails.

try:

INSERT INTO Purchase(custName) VALUES ('Lendl') RETURNING purchaseID;

you will see the value of inserted purchaseID, use it in next query:

INSERT INTO PurchasedItem(purchaseID, itemNo) VALUES (_the_value_above_, 111);
commit;

If you want it to be used without interactivity, use DO block with returning purchaseID into _value

update:

or cte, smth like 

WITH i AS (
  INSERT INTO Purchase(custName, orderedDate) 
  VALUES ('Lendl', '2016-09-28') 
  RETURNING purchaseID
)
insert into PurchasedItem
select i.purchaseID,'smth',3
from i
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • how do you access _the_value_above_ as purchaseID? I am running this in a postgres client by the way: psequel. unsure how to use that returning purchaseid in the next statement. – Ivan Lendl Aug 29 '17 at 06:09
  • I also don't understand the last part, with the DO block. How does that work? – Ivan Lendl Aug 29 '17 at 06:28
  • returning purchaseID will show the value as if you were selecting, so just copy paste it – Vao Tsun Aug 29 '17 at 06:33
1

You can use lastval()

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) VALUES (lastval(), 111);
commit;

Alternatively query the underlying sequence directly:

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) 
VALUES (currval('purchase_purchaseid_seq'), 111);
commit;

Or if you don't want to rely on the automatic naming of the sequence, use pg_get_serial_sequence to get the sequence associated with the column:

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) 
VALUES (currval(pg_get_serial_sequence('purchase', 'purchaseid')), 111);
commit;

For more details see the manual: https://www.postgresql.org/docs/current/static/functions-sequence.html

  • These all work perfectly :) is there any advantage to using one or the other? – Ivan Lendl Aug 29 '17 at 06:16
  • @IvanLendl `lastval()` will only work if nothing else happens in your transaction between the two inserts. Otherwise all three of them work the same - it's a matter of personal taste –  Aug 29 '17 at 06:17
  • what if i wanted to put in an attribute that wasn't a sequence? then how would i get that value from one table to the other? – Ivan Lendl Aug 29 '17 at 09:07
  • @IvanLendl: see [here](https://stackoverflow.com/q/33414552/330315) or [here](https://stackoverflow.com/q/7391090/330315) or [here](https://stackoverflow.com/q/42941006/330315) or [here](https://stackoverflow.com/q/21386772/330315) –  Aug 29 '17 at 09:18
  • So are CTE's the only watch to achieve this? – Ivan Lendl Aug 30 '17 at 03:25
  • do these solutions, apart form lastval(), retrieve the autogenerated key in the same transaction? is it possible that it could get mixed up with another transactions id that's happening at the same time? – Ivan Lendl Aug 31 '17 at 03:43
  • @IvanLendl: [Quote from the manual](https://www.postgresql.org/docs/current/static/functions-sequence.html) "*currval: Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did*" –  Aug 31 '17 at 05:23