Question 88825 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql Insert into table values with multiple records

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search