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

Postgresql – column reference 'updated_at' is ambiguous in postgres

I’m trying to upsert a post:

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;

But I get the error:

{
  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'
}

I have used aliases everywhere I know possible. How can I get this working without renaming my upsert_post parameters with a prefix i_ or something similar?

J

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    My issue here was the input parameter conflicting with the ON CONFLICT parameter.

    I'm astounded at how people just nonchalantly downvote your post for no reason. There are three real answers I finally found.

    1. #variable_conflict

    Postgres has three variables to use for this use case:

    #variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

    But I got it working thanks to the Variable Substitution Docs provided above:

    RETURNS SETOF posts
AS $$
#variable_conflict use_column
DECLARE
    post_id uuid;
BEGIN

    2. ON CONFLICT ON CONSTRAINT

    The problem with my code above was the ON CONFLICT not allowing you to use aliases with it. If you know a way, please let me know. Otherwise, I simply just used the name of the compound constraint with ON CONSTRAINT instead.

     ON CONFLICT ON CONSTRAINT id_updated_at_pk DO UPDATE

    Of course I actually had to name my constraint, which I hadn't before, in the schema:

    CREATE TABLE posts (
  ...
  CONSTRAINT id_updated_at_pk PRIMARY KEY (id, updated_at)
  ...
);

    3. Create a sub function

    Basically you could just create a function _upsert_post() which does take prefixed parameters. Then just pass your non prefixed parameters into this function:

    upsert_post

    RETURN QUERY SELECT * FROM _upsert_post(post_short_id, 'draft');

    To say that you have to use prefixes is simply not true. In my case, the user would have to change the frontend code to use different parameters than the backend code. This can be important for teams.

    Alternatively you can qualify ambiguous references to make them clear.

    This was my goal of this post, and I feel like it was very clear. You don't need my full schema to understand the problem. Once someone downvotes a post, other people assume it is not a valid question. Please be more mindful.

    Thanks,

    J


  2. 0

    i needed to adept the function, as user typ and function are supplied and create table would also help much

    In short you should never name your variables like column names, so that postgres has no more problem differentiate them

    the idea behind short_id i can only guess. but i think you should also enter it, when inserting a new row

    CREATE TABLe posts (id text PRIMARY KEY,    
    status text DEFAULT 'draft',
  title text,
  content text,
    short_id text DEFAULT NULL,
    image text DEFAULT NULL,
    
    updated_at timestamptz DEFAULT NULL,
    published_at timestamptz DEFAULT NULL,
    created_at timestamptz DEFAULT NULL,
  UNIQUE (id, updated_at)
)

    CREATE TABLE

    CREATE OR REPLACE FUNCTION upsert_post(
    f_title text,
    f_content text,
    f_short_id text DEFAULT NULL,
    f_image text DEFAULT NULL,
    f_status text DEFAULT 'draft',
    f_updated_at timestamptz DEFAULT NULL,
    f_published_at timestamptz DEFAULT NULL,
    f_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 = f_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, gen_random_uuid ()),
        f_status,
        f_title, 
        f_content,
        f_image,
        COALESCE(f_updated_at, now()),
        COALESCE(f_published_at, now()),
        COALESCE(f_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;


    CREATE FUNCTION

    SELECT
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
AND
    routine_schema = 'public';
    routine_name
    upsert_post 
    SELECT 1

    SELECT upsert_post('A'::TEXT,'b'::TEXT,'C'::TEXT,'D'::TEXT,'draft'::TEXT,NOW(),NOW(), NOW())
    upsert_post
    (f9bb1bd4-62f7-4380-b088-5185db0fca40,draft,A,b,,D,"2023-06-25 18:50:17.61884+01","2023-06-25 18:50:17.61884+01","2023-06-25 18:50:17.61884+01") 
    SELECT 1

    SELECT * FROM posts
    id status title content short_id image updated_at published_at created_at
    f9bb1bd4-62f7-4380-b088-5185db0fca40 draft A b null D 2023-06-25 18:50:17.61884+01 2023-06-25 18:50:17.61884+01 2023-06-25 18:50:17.61884+01 
    SELECT 1

    fiddle

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

Back To Top
Search