Insert rows into a (postgres) table where the first col is a static value and the second col is the result of a SELECT DISTINCT from another table

Question

I haven't had to use SQL in years and I'm clearly rusty. I have a situation where I'm trying to insert multiple rows into table A. There are two columns of data to be inserted, the first is a "static" value (it is derived but will be the same for all new rows) and the second is the result of a SELECT DISTINCT on table B. There are no relevant constraints on either of the tables that should come into play here.

FWIW, this is a sql script in Retool going against a postgres db.

Apologies for the formatting. Table markdown doesn't seem to be working for me

Table A should look like this at the end:

id | source_file

foo | apple.csv

foo | banana.csv

foo | orange.csv

Based on these values in Table B

source_file

apple.csv

apple.csv

apple.csv

banana.csv

banana.csv

orange.csv

orange.csv

I've tried a few variations all along the lines of:

INSERT INTO table_A (id, source_file)
     {{id}}, SELECT DISTINCT source_file 
     FROM table_B WHERE source_file IS NOT NULL AND source_file <> ''

Which throws syntax error at or near "$1"

And

INSERT INTO table_A (id, source_file)
     SELECT DISTINCT {{id}},  source_file 
     FROM table_B WHERE source_file IS NOT NULL AND source_file <> ''

Which throws

insert or update on table "dg_client_data_sources" violates 
 foreign key constraint "fk_data_grid"

That second error has me wondering if I need to loop through (gasp!) the results of the SELECT DISTINCT to insert the multiple rows? I may be overthinking that bit....

I can provide other context/examples if needed. Any direction is greatly appreciated!

Answer 1

INSERT takes a set of rows as input. You can use VALUES to create a set of rows (possibly only one):

INSERT INTO ... VALUES ({{id}}, (SELECT ...))

This, however requires that your select results in at most one row. A better solution is to add your scalar to the select as:

INSERT INTO A (id, source_file)
SELECT DISTINCT 'foo',  source_file 
FROM source_file 
WHERE source_file IS NOT NULL AND source_file <> '';

Here the constant 'foo' is added to each row. It is somewhat similar to the cross product of the select and a table with one column and one row:

SELECT DISTINCT t.x,  sf.source_file 
FROM ( VALUES ('foo') ) t(x)
CROSS JOIN source_file sf
WHERE source_file IS NOT NULL AND source_file <> '';

I added a Fiddle that demonstrates the above