CODEWITHSUNDEEP
sqlpostgresqldatabase-designsql-insert
user187676
user187676

Reputation:

Use RETURNING without explicitly naming the columns

I have a table with more than a few columns and want to return the id from the INSERT query using RETURNING. Unfortunately it seems that in this case I have to specify ALL columns

INSERT INTO "user" ( "id", ... ) VALUES ( DEFAULT, ... ) RETURNING "id"

instead of just doing

INSERT INTO "user" VALUES ( DEFAULT, ... ) RETURNING "id"

wich makes the query extremely long and rather unreadable. Can I return the ID somehow without specifying every column?

Upvotes: 1

Views: 43

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

Sure you can. Your second query should just work - provided that id has actually the first position in the table - which I doubt.

Verify with:

SELECT attnum
FROM   pg_attribute
WHERE  attrelid = '"user"'::regclass
AND    attname = 'id';

Doesn't mean you should do it, though. Except for ad-hoc calls there is only few occasions where you should not append a column list to INSERT commands.

Either way, you shouldn't be using a reserved word like user as table name. That's a very bad idea. A classical case of a loaded foot-gun.

Upvotes: 2

jszobody
jszobody

Reputation: 28941

I'm unable to reproduce the issue. I'm running PostgreSQL version 9.1, created a simple users table with columns id and name.

I then ran this query:

INSERT INTO users VALUES(DEFAULT,'jszobody') RETURNING id

I got the id back without specifying the columns in the INSERT query. It appears to have just worked.

What error are you getting? What version of PostgreSQL? Have you tried reproducing in a very simple test case like this, ruling out any other issues with the query, default values, etc?

Upvotes: 0

Related Questions