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),
    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.

1 Answer 1


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,

  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 (42,42,DEFAULT);

TABLE foo;
 id | foo | bar 
  1 |   1 |   1
  2 |   2 |   2
 42 |  42 |   3
(3 rows)
  • So if you were to use an explicit SELECT DEFAULT, col2, col3 statement, would this mean 2 separate calls to the implicit nextval function, once when you select, again when you insert?
    – Alex
    Commented Aug 22, 2017 at 16:37
  • No, just the single call. It just looks weird and no one uses that syntax. Commented Aug 22, 2017 at 16:50
  • 3
    I'm getting "DEFAULT is not allowed in this context" when trying to use DEFAULT in a SELECT clause. Has this changed?
    – Bergi
    Commented Mar 28, 2021 at 0:42
  • @Bergi what database and version? Commented Mar 28, 2021 at 2:19
  • @EvanCarroll PostgreSQL 12.2. Same on Postgres 13: online demo
    – Bergi
    Commented Mar 28, 2021 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.