0

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!

0

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.