POSTGRESQL :INSERT INTO ... SELECT with auto generated column

Question

I have two tables A(id, col1, col2) and B(col3, col4, col5, col6)

the column "id" in table A is an auto generated, not null, primary key.

To insert data from table B to table A, I am trying

INSERT INTO A(col1, col2)
(SELECT col3, col4 from B)

This statement throws error

ERROR:  null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, "abc", "def")

I also tried

INSERT INTO A(id, col1, col2)
(SELECT DEFAULT, col3, col4 from B)

this throws error

ERROR:  syntax error at or near "DEFAULT"

Why is postgres not auto generating value for column "id" ? The query works if I provide "id" from table B, or if I insert single row (without select) and providing "DEFAULT" keyword for auto generated column.

EDIT: Table creation query

CREATE TABLE A 
(
id bigint NOT NULL, 
col1 character varying(10), 
col2 character varying(10), 
CONSTRAINT A_pk PRIMARY KEY (id) 
); 
ALTER TABLE A OWNER TO user1; 

CREATE SEQUENCE A_id_seq 
START WITH 1 
INCREMENT BY 1 
NO MINVALUE 
NO MAXVALUE 
CACHE 1; 

ALTER TABLE A_id_seq OWNER TO user1; 

ALTER SEQUENCE A_id_seq OWNED BY A.id;

@Code-Monk CREATE TABLE A ( id bigint NOT NULL, col1 character varying(10), col2 character varying(10), CONSTRAINT A_pk PRIMARY KEY (id) ); ALTER TABLE A OWNER TO user1; CREATE SEQUENCE A_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE A_id_seq OWNER TO user1; ALTER SEQUENCE A_id_seq OWNED BY A.id; — Somil Bhandari, Apr 09 '16 at 19:37

score 3 · Answer 1 · answered Apr 09 '16 at 20:10

Don't use an explicit sequence. Just define the column as serial or bigserial:

CREATE TABLE A (
    id bigserial NOT NULL, 
    col1 character varying(10), 
    col2 character varying(10), 
    CONSTRAINT A_pk PRIMARY KEY (id) 
);

Then the insert will be fine. Here is a SQL Fiddle.

score 3 · Answer 2 · answered Apr 09 '16 at 20:38

INSERT INTO A(col1, col2)
(SELECT col3, col4 from B)

Fails because the default value hasn't been set for id. The default value should be to take the nextval from the sequence.

CREATE SEQUENCE A_id_seq 
  START WITH 1 
  INCREMENT BY 1 
  NO MINVALUE 
  NO MAXVALUE 
  CACHE 1; 

CREATE TABLE A 
(
  id bigint NOT NULL DEFAULT nextval('A_id_seq'::regclass), 
  col1 character varying(10), 
  col2 character varying(10), 
  CONSTRAINT A_pk PRIMARY KEY (id) 
);

Or you can use bigserial instead of bigint in your create table to have the sequence created and the default set automatically for you. The end result is the same.

POSTGRESQL :INSERT INTO ... SELECT with auto generated column

2 Answers2