尝试更新/插入时，表的子句条目中缺少

Question

尝试用一组新的值更新表，

如果值已经存在，那么它应该更新字段，如果不存在，那么它应该插入字段。

WITH f AS (
SELECT 1 as MarketId,  
                            '[e-mail]' 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;

但是，当我运行查询时，会出现以下错误：

missing FROM-clause entry for table "f"

试图用纯SQL编写这个

插入成员（年龄、isPremiumMember、isSubscribed、lastModifiedOn）从f中选择年龄、isPremiumMember、isSubscribed、lastModifiedOn（年龄、isPremiumMember、isSubscribed、lastModifiedOn）进行更新设置年龄=排除。年龄，ispremiummber=不包括在内。ispremiummber，isSubscribed=已排除。isSubscribed，lastModifiedOn=已排除。拉斯顿；

尝试将唯一约束添加为下面的“按建议选择”。这是我目前的声明。

Answer 1

简化的示例，用于upsert查询（避免使用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;

结果：

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)

Answer 2

尝试使用select而不是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;