title: POSTGRESQL :INSERT INTO ... SELECT with auto generated column
body: <p>I have two tables A(id, col1, col2) and B(col3, col4, col5, col6)</p>
  
  <p>the column "id" in table A is an auto generated, not null, primary key.</p>
  
  <p>To insert data from table B to table A, I am trying</p>
  
  <pre><code>INSERT INTO A(col1, col2)
  (SELECT col3, col4 from B)
  </code></pre>
  
  <p>This statement throws error </p>
  
  <pre><code>ERROR:  null value in column "id" violates not-null constraint
  DETAIL: Failing row contains (null, "abc", "def")
  </code></pre>
  
  <p>I also tried</p>
  
  <pre><code>INSERT INTO A(id, col1, col2)
  (SELECT DEFAULT, col3, col4 from B)
  </code></pre>
  
  <p>this throws error</p>
  
  <pre><code>ERROR:  syntax error at or near "DEFAULT"
  </code></pre>
  
  <p>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.</p>
  
  <p>EDIT:
  Table creation query</p>
  
  <pre><code>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></pre>