Postgres column reference ambiguous when qualified with table name

Question

This is a moderately complicated CTE that updates a number of tables depending on the content of a JSON argument. When executing this function, Postgres throws a column reference "explode" is ambiguous error. The line the error is referring to actually has a table name qualifying the column reference.

I'm pretty sure I'm right about which line is causing the problem as if I remove the line I've marked below, the error disappears.

I've been trying to figure this one out some time and I have no idea.

CREATE OR REPLACE FUNCTION prd.update_composition (json, OUT result json) AS
$$
BEGIN
  -- Update and return the new composition
  WITH composition AS (
    UPDATE prd.composition SET (
      explode
    ) = (
      payload_composition.explode
    )
    FROM json_to_record($1) AS payload_composition (
      id      integer,
      explode boolean
    )
    WHERE id IS NOT NULL
    RETURNING *
  ), payload_component AS (
    SELECT
      composition.composition_id,
      component."productId" AS product_id,
      component.quantity,
      component.removed
    FROM json_to_recordset($1->'components') AS component (
      "productId" integer,
      quantity    numeric(10,3),
      removed     boolean
    )
    CROSS JOIN composition
  ), updated_component AS (
    UPDATE prd.component existing SET (
      product_id,
      quantity
    ) = (
      component.product_id,
      component.quantity
    )
    FROM (
      SELECT
        composition_id,
        product_id,
        quantity
      FROM payload_component
      WHERE removed IS NOT TRUE
    ) component
    WHERE existing.composition_id = component.composition_id
    RETURNING *
  ), deleted AS (
    DELETE FROM prd.component existing
    USING (
      SELECT
        composition_id,
        product_id
      FROM payload_component
      WHERE removed IS TRUE
    ) payload_deleted
    WHERE existing.composition_id = payload_deleted.composition_id
      AND existing.product_id = payload_deleted.product_id
  )
  SELECT json_strip_nulls(to_json(r)) INTO result
  FROM (
    SELECT
      composition.composition_id,
      composition.explode -- HERE IS THE OFFENDING LINE --
    FROM composition
  ) r;
END
$$
LANGUAGE 'plpgsql' SECURITY DEFINER;

Why is there an ambiguous column reference error with this code?

Could you show us the full error, please, complete with line numbers? — Schwern
– Schwern, Commented Jun 30, 2018 at 3:12
The problem is to in the line you marked it is in your CTE, because there the explode column is not qualified with a table name. Here: UPDATE prd.composition SET ( --> explode <-- ) = ( payload_composition.explode ) FROM json_to_record($1) AS payload_composition ( id integer, explode boolean ) It does not know where to get this column from. — Jorge Campos
– Jorge Campos, Commented Jun 30, 2018 at 3:17
Does this answer your question? SQL column reference "id" is ambiguous — TylerH
– TylerH, Commented Oct 20, 2022 at 13:49

oorst · Accepted Answer · 2018-06-30 03:52:20Z

3

The problem is with the RETURNING clause in the composition CTE.

By using the * with RETURNING it returns all columns from all tables in the from_list.

The returning clause should specify which columns and from which tables to return, especially if there are multiple tables in the from_list.

In this case, an alias should be used in the INSERT to reference the required columns:

WITH composition AS (
    UPDATE prd.composition c SET ( -- See here
      explode
    ) = (
      payload_composition.explode
    )
    FROM json_to_record($1) AS payload_composition (
      id      integer,
      explode boolean
    )
    WHERE id IS NOT NULL
    RETURNING c.* -- and here
  )

answered Jun 30, 2018 at 3:52

oorst

9792 gold badges10 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres column reference ambiguous when qualified with table name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related