Postgres serial values insertion

Question

From the Postgres documentation on INSERT, default keyword should auto increment columns declared as serial.But when I combine it with select statement, it throws me an error

syntax error at or near "DEFAULT"

Here is the insert statement

insert into abc (id,date,serialnumber) (DEFAULT,select (data.date,data.serialnumber) from data)

Are you really still using the outdated 8.2 version?

user330315
– user330315

2013-08-29 21:47:27 +00:00
Commented Aug 29, 2013 at 21:47 — user330315
– user330315, Commented Aug 29, 2013 at 21:47

score 3 · Accepted Answer · 2013-08-29 21:55:51Z

3

DEFAULT can only be as a "literal" for an INSERT statement in the VALUES clause. I cannot be used inside the column list of a SELECT statement even if that is used for an INSERT.

To apply the default value, simply leave out the column:

insert into abc (date,serialnumber) 
select date, serialnumber 
from data

For an example see here: http://sqlfiddle.com/#!12/d291a/1

Also: do not put a column list into parantheses. (a,b) is something different than a,b in Postgres. The first is a single record with two attributes, the second are two different columns.

See this SQLFiddle demo here: http://sqlfiddle.com/#!12/3a890/1 and note the difference between the two results.

edited Aug 29, 2013 at 21:55

answered Aug 29, 2013 at 21:49

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

learningtocode Over a year ago

Thanks for replying! Thats giving me "ERROR: INSERT has more target columns than expressions" which why I tried explicitly giving default.

user330315 Over a year ago

@learningtocode Did you really run exactly the statement I wrote? Sounds like you didn't remove the id column from the columnlist in the insert part or you didn't remove the parentheses around the column names (which makes the result have only a single column not two).

learningtocode Over a year ago

I did remove the id field but not the parantheses, I misunderstood your explanation, I should have removed the parantheses in select statement and not in the insert statement. Thanks so much!!! Still not sure what is single record with two attributes..

Collectives™ on Stack Overflow

Postgres serial values insertion

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related