Postgresql Insert into table values with multiple records

Question

I need to insert into a table (groupL) two rows (custId, aName). custId is constant while aName is multiple records of variable size based on a nested subquery.

That subquery is:

SELECT aName 
FROM artwork 
WHERE artwork.title IN (SELECT title 
                        FROM classify 
                        WHERE NEW.g_name = classify.g_name);

I've tried a lot of SQL but I really don't get it. I'm looking for something that if we have two rows in aName (name1, name2) it'll do something like

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name1 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name2 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name2);

How do I do this? Any guidance would be appreciated.

Where does `NEW.g_name` come from? I realize you said this is from a subquery but I think that's going to be relevant info. — shawnt00, Apr 28 '23 at 17:17
It's based on a trigger when a record is inserted. It's not relevant for the iteration thing I'm trying to figure out — ellivron, Apr 28 '23 at 17:22

score 0 · Answer 1 · answered Apr 28 '23 at 16:53

You can do in one Go but you need to have the ids before hand, if you want to compare and of course a FROM clause

CREATE tABLe groupL(custId int , aName TEXT)

CREATE TABLE

CREATE TABLe aName (name1 TEXT, name2 TEXT)

CREATE TABLE

INSERT INTO aName VALUEs ('A','B')

INSERT 0 1

DO $$
DECLARE  
   custIdConstant integer := 10;  
BEGIN  
INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);

END $$;

DO

SELECT * FROM groupL

custid	aname
10	A
10	B

SELECT 2

DO $$
DECLARE  
   custIdConstant integer := 10;  
BEGIN  
INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);

END $$;

DO

SELECT * FROM groupL

custid	aname
10	A
10	B

SELECT 2

fiddle

Thanks but the issue is moreso that the subquery can be a variable list. It can range from 0 to N I just used two as an example. Something to iterate over this list ya know? — ellivron, Apr 28 '23 at 17:01
you would get all column names https://dba.stackexchange.com/questions/22362/list-all-columns-for-a-specified-table , iterate trough the result https://stackoverflow.com/questions/32668201/how-to-iterate-over-results-of-query and build a dynamic sql query https://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres and execute it — nbk, Apr 28 '23 at 17:05

shawnt00 · Answer 2 · 2023-04-28T17:45:53.220

0

I think you just want to check for the existence of those rows via a second subquery:

INSERT INTO groupL (custId, aName)
SELECT <custId>, a.aName 
FROM artwork as a
WHERE a.title IN (SELECT c.title 
                  FROM classify as c INNER JOIN NEW ON NEW.g_name = c.g_name)
  AND NOT EXISTS (SELECT 1
                  FROM groupL as g
                  WHERE g.custId = <custId> AND g.aName = a.aName);

You might want to check into merge as well.

edited Apr 28 '23 at 17:45

answered Apr 28 '23 at 17:21

shawnt00

16,443
3
17
22

no the first insert was only a example where subqueries work, he wants the rest of the question, it seems thqat it is a part of a trigger function# – nbk Apr 28 '23 at 17:26
@nbk: Yeah, I've never worked with Postgres triggers so I may be missing something. With a "variable" number of rows though I don't see why you'd break this up into multiple inserts though. – shawnt00 Apr 28 '23 at 17:47

Postgresql Insert into table values with multiple records

2 Answers2