missing FROM-clause entry for table when trying to update / insert

Question

Try to update a table with a new set of values,

if the values already exist then it should update the fields, if it doesn't then it should insert the fields.

WITH f AS (
SELECT 1 as MarketId,  
                            'dros@test.com' as userName, 
                            28 as age, 
                            1 as isPremiumMember, 
                            1 as isSubscribed, 
                            '2021-03-12T17:07:30' as LastModifiedOn
    from members
    ) 


    
    INSERT INTO members
(age,isPremiumMember,isSubscribed, lastModifiedOn) 
    VALUES (f.age,f.isPremiumMember,f.isSubscribed,f.lastModifiedOn)
    ON CONFLICT (age,isPremiumMember,isSubscribed,lastModifiedOn)
DO UPDATE SET age= EXCLUDED.age,isPremiumMember = EXCLUDED.isPremiumMember,isSubscribed= EXCLUDED.isSubscribed,lastModifiedOn= EXCLUDED.lastModifiedOn;

however when I run the query I get this error:

missing FROM-clause entry for table "f"

trying to write this in plain SQL

INSERT INTO members (age,isPremiumMember,isSubscribed, lastModifiedOn) select age,isPremiumMember,isSubscribed,lastModifiedOn from f ON CONFLICT (age,isPremiumMember,isSubscribed,lastModifiedOn) DO UPDATE SET age= EXCLUDED.age,isPremiumMember = EXCLUDED.isPremiumMember,isSubscribed= EXCLUDED.isSubscribed,lastModifiedOn= EXCLUDED.lastModifiedOn;

tried adding the unique constraint as select as recommend below. this is my current statement.

Does this help? https://stackoverflow.com/questions/36359440/postgresql-insert-on-conflict-update-upsert-use-all-excluded-values — Abra, Jun 24 '21 at 08:57

score 1 · Answer 1 · answered Jun 24 '21 at 08:55

1

Try using select instead of f.

INSERT INTO members
(age,isPremiumMember,isSubscribed, lastModifiedOn) 
    select age,isPremiumMember,isSubscribed,lastModifiedOn from f
    ON CONFLICT (age,isPremiumMember,isSubscribed,lastModifiedOn)
DO UPDATE SET age= EXCLUDED.age,isPremiumMember = EXCLUDED.isPremiumMember,isSubscribed= EXCLUDED.isSubscribed,lastModifiedOn= EXCLUDED.lastModifiedOn;

answered Jun 24 '21 at 08:55

Emanuele

723
4
15

tried this but have this: error there is no unique or exclusion constraint matching the ON CONFLICT specification – dros Jun 24 '21 at 09:07
https://stackoverflow.com/questions/42022362/no-unique-or-exclusion-constraint-matching-the-on-conflict – Emanuele Jun 24 '21 at 09:09
1

You need to place a unique key if you want to use the conflict function – Emanuele Jun 24 '21 at 09:09
so the solution is to create a new table? is there not another way? – dros Jun 24 '21 at 09:13
No no, you can use this `ALTER TABLE members ADD UNIQUE (age,isPremiumMember,isSubscribed,lastModifiedOn);` – Emanuele Jun 24 '21 at 09:30
I've altered the columns but still getting the there is no unique or exclusion constraint matching the ON CONFLICT specification. pasting my current statement for clarity – dros Jun 24 '21 at 09:37
Can you add that as primary key as the sample? – Emanuele Jun 24 '21 at 09:41

score 0 · Answer 2 · answered Jun 24 '21 at 10:37

Simplified example for an upsert query (avoiding the CTE):

\i tmp.sql

create table members (
        member_id integer primary key
        , name text
        , eyes text
        , hair text
        );

INSERT INTO members(member_id, name, eyes, hair) VALUES
 (1, 'Lisa', 'brown', 'brown'),
 (2, 'Bob', 'brown', 'brown');

\echo Initial
SELECT * FROM members order by 1;

INSERT INTO members(member_id,name, eyes, hair)
VALUES(1, 'Alice', 'blue', 'blonde' )
ON conflict (member_id) DO
UPDATE SET name = EXCLUDED.name, eyes = EXCLUDED.eyes, hair = EXCLUDED.hair
        ;

\echo After the update
SELECT * FROM members order by 1;

Results:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 2
Initial
 member_id | name | eyes  | hair  
-----------+------+-------+-------
         1 | Lisa | brown | brown
         2 | Bob  | brown | brown
(2 rows)

INSERT 0 1
After the update
 member_id | name  | eyes  |  hair  
-----------+-------+-------+--------
         1 | Alice | blue  | blonde
         2 | Bob   | brown | brown
(2 rows)

missing FROM-clause entry for table when trying to update / insert

2 Answers2