SELECT * FROM NEW TABLE equivalent in Postgres

Question

In DB2 I can do a command that looks like this to retrieve information from the inserted row:

SELECT *
FROM NEW TABLE (
    INSERT INTO phone_book
    VALUES ( 'Peter Doe','555-2323' )
) AS t

How do I do that in Postgres?

There are way to retrieve a sequence, but I need to retrieve arbitrary columns.

My desire to merge a select with the insert is for performance reasons. This way I only need to execute one statement to insert values and select values from the insert. The values that are inserted come from a subselect rather than a values clause. I only need to insert 1 row.

That sample code was lifted from Wikipedia Insert Article

don't understand what's the performance reason leading you try to select and insert in one command. In my opinion, you should submit commit for insert operation before select. — Paris Tao, Oct 27 '14 at 03:23
@ParisTao: A single command (returning the correct row including auto-generated content like a serial ID) is simplest and fastest and *safest*. One might also want to INSERT / UPDATE more rows (like child rows in other tables referencing the ID) *before* committing all of it (or nothing). — Erwin Brandstetter, Oct 27 '14 at 03:57

score 6 · Accepted Answer · answered Oct 27 '14 at 03:52

A plain INSERT ... RETURNING ... does the job and delivers best performance.
A CTE is not necessary.

INSERT INTO phone_book (name, number)
VALUES ( 'Peter Doe','555-2323' )
RETURNING *  -- or just phonebook_id, if that's all you need

Aside: In most cases it's advisable to add a target list.

The Wikipedia page you quoted already has the same advice:

Using an INSERT statement with RETURNING clause for PostgreSQL (since 8.2). The returned list is identical to the result of a SELECT.

score 2 · Answer 2 · answered Oct 27 '14 at 03:16

PostgreSQL supports this kind of behavior through a returning clause in a common table expression. You generally shouldn't assume that something like this will improve performance simply because you're executing one statement instead of two. Use EXPLAIN to measure performance.

create table test (
  test_id serial primary key,
  col1 integer
);

with inserted_rows as (
  insert into test (c1) values (3)
  returning *
)
select * from inserted_rows;

test_id  col1
--
1        3

Docs

SELECT * FROM NEW TABLE equivalent in Postgres

2 Answers2

Linked