PostgreSQL: insert from another table

sql postgresql insert append
Seerumi picture Seerumi · Aug 1, 2011 · Viewed 134k times · Source

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

Answer

mu is too short picture mu is too short · Aug 1, 2011

Just 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.

Related questions

postgresql: INSERT INTO ... (SELECT * ...)

I'm not sure if its standard SQL: INSERT INTO tblA (SELECT id, time FROM tblB WHERE time > 1000) What I'm looking for is: what if tblA and tblB are in different DB Servers. Does PostgreSql gives any utility or has …

Read More
Bulk/batch update/upsert in PostgreSQL

I'm writing a Django-ORM enchancement that attempts to cache models and postpone model saving until the end of the transaction. It's all almost done, however I came across an unexpected difficulty in SQL syntax. I'm not much of a DBA, …

Read More
PostgreSql INSERT FROM SELECT RETURNING ID

In PostgreSql 9.2.4 I have two tables: user (id, login, password, name) and dealer (id, user_id). And I want to insert into both tables returning id of created dealer. Currently I'm doing it with two queries: WITH rows AS ( INSERT …

Read More