PostgreSQL: insert from another table

Question

I'm trying to insert data to a table from another table and the tables have only one column in common. The problem is, that the TABLE1 has columns that won't accept null values so I can't leave them empty and I can't get them from the TABLE2.

I have TABLE1: id, col_1 (not null), col_2(not null), col_3 (not null)

and TABLE2: id, col_a, col_b, col_c

so how could I insert id from TABLE2 to TABLE1 and fill the col_1-3 with hard coded strings like "data1", "data2", "data3"?

INSERT INTO TABLE1 (id) SELECT id FROM TABLE2 WHERE col_a = "something";

will result in:

ERROR: null value in column "col_1" violates not-null constraint

mu is too short · Accepted Answer · 2021-09-08 17:22:37Z

346

You can supply literal values in the SELECT:

INSERT INTO TABLE1 (id, col_1, col_2, col_3)
SELECT id, 'data1', 'data2', 'data3'
FROM TABLE2
WHERE col_a = 'something';

A select list can contain any value expression:

But the expressions in the select list do not have to reference any columns in the table expression of the FROM clause; they can be constant arithmetic expressions, for instance.

And a string literal is certainly a value expression.

edited Sep 8, 2021 at 17:22

answered Aug 1, 2011 at 13:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chomp Over a year ago

do you know if this way of doing inserts uses multi valued inserts under the hood?

mu is too short Over a year ago

@chomp The INSERT will be atomic either was insert into t (...) select ... or insert into t (...) values (...), (...), .... Have a look at Erwin's answer over here though.

hpaknia Over a year ago

Is "ON CONFLICT" supported?

mu is too short Over a year ago

@hpaknia Have you checked the documentation?

kris2k · Accepted Answer · 2020-05-05 18:43:58Z

19

For referential integtity :

insert into  main_tbl (col1, ref1, ref2, createdby)
values ('col1_val',
        (select ref1 from ref1_tbl where lookup_val = 'lookup1'),
        (select ref2 from ref2_tbl where lookup_val = 'lookup2'),
        'init-load'
       );

answered May 5, 2020 at 18:43

kris2k

3453 silver badges6 bronze badges

Comments

Tom Hood · Accepted Answer · 2025-03-05 21:37:17Z

11

Very late answer, but I think my answer is more straight forward for specific use cases where users want to simply insert (copy) data from table A into table B:

INSERT INTO table_b (col1, col2, col3, col4, col5, col6)
SELECT col1, 'str_val', 21, col4, col5, col6
FROM table_a

edited Mar 5 at 21:37

answered Jul 1, 2019 at 21:10

Tom Hood

5377 silver badges18 bronze badges

Comments

mu is too short · Accepted Answer · 2012-07-31 05:02:49Z

5

You could use coalesce:

insert into destination select coalesce(field1,'somedata'),... from source;

edited Jul 31, 2012 at 5:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Aug 1, 2011 at 20:50

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: insert from another table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related