CODEWITHSUNDEEP
postgresql
Pikaling
Pikaling

Reputation: 8312

Select from insert

Is it possible to select from an insert statement? For example:

SELECT id FROM (INSERT INTO table (col1, col2) VALUES (val1, val2));

Where id is an autoincrementing primary key.

Upvotes: 2

Views: 734

Answers (2)

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37904

It's not possible in such way, because INSERT doesn't return virtual table for SELECT. However you could get id's actual value using currval(regclass) sequence function as:

SELECT currval('yourTableName_id_seq'::regclass);
 currval 
---------
       1
(1 row)

EDIT:

Use RETURNING clause (available since PostgreSQL 8.2):

INSERT INTO yourTableName (col1, col2) VALUES ('aaa', 'bbb') RETURNING id;
 id 
----
  2
(1 row)

Upvotes: 2

Nausik
Nausik

Reputation: 745

"SELECT id FROM mytable WHERE id IS NULL;

"id" has to be an auto_increment column to make it work. It will return the last_insert_id just as last_insert_id() is expected to do.

example: mysql> INSERT INTO orders (customer_cust_id, orderdatetime, message, taxrate, shippingprice) -> SELECT '1', NOW(), null, taxrate, shippingprice FROM customer -> WHERE cust_id='1';"

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Upvotes: 0

Related Questions