Postgresql – Insert Into table ommitting columns that have default values

default valueinsertpostgresqlsubquery

I have a PostgreSQL database with lots of tables of the same structure, 36 columns in total:

CREATE TABLE some_schema.some_table    (
    id integer NOT NULL DEFAULT nextval('some_schema.id_seq'::regclass),
    col2,
    col3,
    col4,
    [...],
    col35,
    mi_prinx integer NOT NULL DEFAULT nextval('some_schema.mi_prinx_seq'::regclass),
    CONSTRAINT some_table_pkey PRIMARY KEY (mi_prinx)
)

In many instances I will have to insert records from another table with the same structure:

INSERT INTO some_schema.some_table (col2,col3...col35)
    SELECT col2,col3...col35
    FROM some_schema.another_table_with_same_structure;

Is there a way of doing this without having to list all columns that don't have a default value? I think I can use DEFAULT VALUES somehow but I can't get the syntax right based on the documentation.

Best Answer

Is there a way of doing this without having to list all columns that don't have a default value?

No you either insert all the columns using the * expansion,

INSERT INTO foo 
  SELECT * FROM bar;

Or, you insert just the listed columns

INSERT INTO foo (col2,col3)
  SELECT col2, col3
  FROM bar;

You can also use DEFAULT to be explicit, but no one does that. It's in the spec however, Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none. Being explicit looks like this.

INSERT INTO foo (col1,col2,col3)
  SELECT DEFAULT, col2, col3
  FROM bar;

DEFAULT COLUMNS sets all columns to their respective default. From the docs on DEFAULT COLUMNS,

DEFAULT VALUES, All columns will be filled with their default values.

CREATE TABLE foo (id serial, foo serial, bar serial);
INSERT INTO foo (id,foo,bar) VALUES (DEFAULT, DEFAULT, DEFAULT);
INSERT INTO foo DEFAULT VALUES;
INSERT INTO foo(id,foo,bar) VALUES (42,42,DEFAULT);

TABLE foo;
 id | foo | bar 
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
 42 |  42 |   3
(3 rows)
Related Question