PostgreSQL中'updated_at'列引用歧义问题解决求助

尝试实现帖子的Upsert操作，编写了如下PL/pgSQL函数：

DROP FUNCTION IF EXISTS upsert_post;
CREATE OR REPLACE FUNCTION upsert_post(
    title text,
    content text,
    short_id text DEFAULT NULL,
    image text DEFAULT NULL,
    status post_status DEFAULT 'draft',
    updated_at timestamptz DEFAULT NULL,
    published_at timestamptz DEFAULT NULL,
    created_at timestamptz DEFAULT NULL
)
RETURNS SETOF posts
AS $$
DECLARE
    post_id uuid;
BEGIN
    -- Get the id based on the provided short_id
    SELECT id INTO post_id FROM posts as p 
    WHERE p.short_id = upsert_post.short_id;

    -- Upsert the post and return the changed row
    RETURN QUERY
    INSERT INTO posts as i (
        id,
        status,
        title,
        content,
        image,
        updated_at,
        published_at,
        created_at
    )
    VALUES (
        COALESCE(post_id, uuid_generate_v4()),
        upsert_post.status,
        upsert_post.title, 
        upsert_post.content,
        upsert_post.image,
        COALESCE(upsert_post.updated_at, now()),
        COALESCE(upsert_post.published_at, now()),
        COALESCE(upsert_post.created_at, now())
    )
    ON CONFLICT (id, updated_at) DO UPDATE
    SET
        title = EXCLUDED.title,
        content = EXCLUDED.content,
        image = COALESCE(EXCLUDED.image, i.image),
        updated_at = COALESCE(EXCLUDED.updated_at, i.updated_at),
        published_at = COALESCE(EXCLUDED.published_at, i.published_at),
        created_at = COALESCE(EXCLUDED.created_at, i.created_at)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

执行时出现以下错误：

{
  code: '42702',
  details: 'It could refer to either a PL/pgSQL variable or a table column.',
  hint: null,
  message: 'column reference "updated_at" is ambiguous'
}

已在可用位置使用了别名，请问如何在不为upsert_post参数添加i_这类前缀的情况下解决该问题？

错误根源是ON CONFLICT (id, updated_at)中的updated_at同时匹配函数参数和表列，导致引用歧义。只需给冲突列明确指定表别名即可解决，无需修改参数命名。

修改后的完整函数代码：

DROP FUNCTION IF EXISTS upsert_post;
CREATE OR REPLACE FUNCTION upsert_post(
    title text,
    content text,
    short_id text DEFAULT NULL,
    image text DEFAULT NULL,
    status post_status DEFAULT 'draft',
    updated_at timestamptz DEFAULT NULL,
    published_at timestamptz DEFAULT NULL,
    created_at timestamptz DEFAULT NULL
)
RETURNS SETOF posts
AS $$
DECLARE
    post_id uuid;
BEGIN
    -- Get the id based on the provided short_id
    SELECT id INTO post_id FROM posts as p 
    WHERE p.short_id = upsert_post.short_id;

    -- Upsert the post and return the changed row
    RETURN QUERY
    INSERT INTO posts as i (
        id,
        status,
        title,
        content,
        image,
        updated_at,
        published_at,
        created_at
    )
    VALUES (
        COALESCE(post_id, uuid_generate_v4()),
        upsert_post.status,
        upsert_post.title, 
        upsert_post.content,
        upsert_post.image,
        COALESCE(upsert_post.updated_at, now()),
        COALESCE(upsert_post.published_at, now()),
        COALESCE(upsert_post.created_at, now())
    )
    -- 给冲突列添加表别名，明确指向posts表的列
    ON CONFLICT (i.id, i.updated_at) DO UPDATE
    SET
        title = EXCLUDED.title,
        content = EXCLUDED.content,
        image = COALESCE(EXCLUDED.image, i.image),
        updated_at = COALESCE(EXCLUDED.updated_at, i.updated_at),
        published_at = COALESCE(EXCLUDED.published_at, i.published_at),
        created_at = COALESCE(EXCLUDED.created_at, i.created_at)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

核心修改点：

• 将ON CONFLICT (id, updated_at)改为ON CONFLICT (i.id, i.updated_at)，通过表别名i明确指定冲突列属于posts表，消除与函数参数的歧义。

内容的提问来源于stack exchange，提问作者Jonathan