Column reference is ambiguous in Postgres

Question

Trying to simplify this INSERT INTO and keep getting this ambiguous error below. What am I doing wrong here, do I need an alias somewhere that I'm missing?

<internal.PGError>: {
                m: {
                    82: "scanRTEForColumn",
                    83: "ERROR",
                    86: "ERROR",
                    67: "42702",
                    77: "column reference \"created_at\" is ambiguous",
                    80: "3082",
                    70: "parse_relation.c",
                    76: "694",
                },
            }

Here is the SQL statement I'm using:

INSERT INTO delivery_areas
    SELECT
      r.drn_id AS restaurant_drn_id,
      'initial'::algorithm_name AS algorithm_name,
      z.city_drn_id AS city_drn_id,
      ?::geometry AS delivery_area,
      gen_random_uuid() AS drn_id,
      ?::timestamp AS created_at,
      ?::timestamp AS updated_at,
      'custom'::delivery_area_type AS delivery_area_type
    FROM restaurants r
    JOIN neighborhood_zones nz ON (nz.hood_drn_id = r.hood_drn_id)
    JOIN zones z ON (z.drn_id = nz.zone_drn_id)
    WHERE r.drn_id = ?
    GROUP BY restaurant_drn_id, algorithm_name, city_drn_id, created_at, updated_at, delivery_area_type
    ON CONFLICT ON CONSTRAINT delivery_areas_pkey DO UPDATE
    SET
      delivery_area = EXCLUDED.delivery_area,
      delivery_area_type = EXCLUDED.delivery_area_type,
      updated_at = EXCLUDED.updated_at
    RETURNING *

Create table statements for the delivery_areas and restaurants:

CREATE TYPE algorithm_name as ENUM ('initial');

CREATE TABLE delivery_areas (
  restaurant_drn_id uuid NOT NULL,
  algorithm_name algorithm_name NOT NULL DEFAULT 'initial',
  city_drn_id uuid NOT NULL,
  delivery_area geometry(MultiPolygon,4326) NOT NULL,
  drn_id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  PRIMARY KEY (restaurant_drn_id, algorithm_name)
);

CREATE INDEX delivery_areas_algorithm_city_idx on delivery_areas (algorithm_name, city_drn_id);
CREATE INDEX delivery_areas_delivery_area_idx on delivery_areas USING gist(delivery_area);

ALTER TABLE delivery_areas ADD FOREIGN KEY (restaurant_drn_id) REFERENCES restaurants(drn_id);

---

CREATE TABLE restaurants (
  drn_id uuid PRIMARY KEY,
  hood_drn_id uuid NOT NULL,
  delivery_range_delta_m int4 NOT NULL,
  geo_lat double precision NOT NULL,
  geo_long double precision NOT NULL,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

Answer 1

Notice in your GROUP BY clause, you have a reference to created_at:

 GROUP BY restaurant_drn_id, algorithm_name, city_drn_id, created_at, updated_at, delivery_area_type

But that column is found in several of your tables.

Prefix that column reference with the correct table, like this:

 GROUP BY restaurant_drn_id, algorithm_name, city_drn_id, delivery_areas.created_at, updated_at, delivery_area_type

I only guessed which table you wanted to reference. This will resolve that error, but it might not be the date/timestamp you wanted to group by.

Here's an example of the problem and the solution, and the kind of detail that should be provided when asking this kind of question:

CREATE TABLE delivery_areas (
      id          int
    , created_at  timestamp
);

CREATE TABLE restaurants (
      drn_id      int
    , created_at  timestamp
);

CREATE TABLE othertbl (
      id          int
    , created_at  timestamp
);

-- The following generates an error:

INSERT INTO delivery_areas
    SELECT r.drn_id AS restaurant_drn_id
         , current_timestamp AS created_at
      FROM restaurants r
      JOIN othertbl    o
        ON o.id = r.drn_id
     GROUP BY restaurant_drn_id, created_at
;

-- ERROR:  column reference "created_at" is ambiguous
-- LINE 7:      GROUP BY restaurant_drn_id, created_at

-- The following is one way to resolve the error:

INSERT INTO delivery_areas
    SELECT r.drn_id AS restaurant_drn_id
         , current_timestamp AS created_at
      FROM restaurants r
      JOIN othertbl    o
        ON o.id = r.drn_id
     GROUP BY restaurant_drn_id, r.created_at
;

Notice the r.created_at. r is the qualifier that resolve the ambiguity.

Here's a link to the test case:

Full working test case