2

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.

3

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)
  • 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 Aug 22 '17 at 16:37
  • No, just the single call. It just looks weird and no one uses that syntax. – Evan Carroll Aug 22 '17 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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