PostgreSQL: insert from another table

user_360200 0
Viewed 196723

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

append insert postgresql sql
4 Answers

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'
       );
user_55978910

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', int_val, col4, col5, col6
FROM table_a
user_96104940
Related