Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: insert from another table

People also ask

How do you insert data into a table in pgAdmin 4?

Expand your table properties by clicking on it in the pgAdmin4 legend. Right-click on 'Constraints', select 'Create' --> 'Primary Key'to define a primary key column. On the Data Output tab at the bottom of the table below the last row, there will be an empty row where you can enter new data in an excel-like manner.


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.


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

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

You could use coalesce:

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