Error when selecting nested resource with POST

[tad-lispy]

Environment

• PostgreSQL version: Docker, postgres:12.4
• PostgREST version: Docker, postgrest/postgrest:v7.0.1
• Operating system: OS X 10.14.6

Description of issue

I have a schema like this:

Create table if not exists collections (
    collection_id uuid primary key default uuid_generate_v4(),
    title text not null,
);

Create table if not exists items (
    item_id uuid primary key default uuid_generate_v4(),
    collection uuid not null references collections(collection_id),
    name citext not null,
);

The following request:

http POST :3000/collections \
	"Accept: application/vnd.pgrst.object+json" \
	"Prefer: return=representation" \
	"Authorization: Bearer [...] \
	"name=demos" \
	"select==items(item_id,name),*" \

Results in the following response:

HTTP/1.1 400 Bad Request
Content-Encoding: gzip
Content-Type: application/json; charset=utf-8
Date: Mon, 24 Aug 2020 17:27:12 GMT
Server: postgrest/7.0.1 (UNKNOWN)
Transfer-Encoding: chunked

{
    "code": "42702",
    "details": null,
    "hint": null,
    "message": "column reference \"collection_id\" is ambiguous"
}

The error from the PostgreSQL is:

2020-08-24 17:34:20.306 UTC [389] ERROR:  column reference "collection_id" is ambiguous at character 1332
2020-08-24 17:34:20.306 UTC [389] STATEMENT:
	      WITH
	      pgrst_source AS (WITH pgrst_payload AS (SELECT $1::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) INSERT INTO  "collections" ("name", "title") SELECT "name", "title" FROM json_populate_recordset (null:: "collections" , (SELECT val FROM pgrst_body)) _   RETURNING "collections".*, "collections"."collection_id")
	      SELECT
	        '' AS total_result_set,
	        pg_catalog.count(_postgrest_t) AS page_total,
	        CASE WHEN pg_catalog.count(_postgrest_t) = 1 THEN coalesce((
	  WITH data AS (SELECT row_to_json(_) AS row FROM pgrst_source AS _ LIMIT 1)
	  SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null'))
	  FROM data CROSS JOIN json_each_text(data.row) AS json_data
	  WHERE json_data.key IN ('collection_id')
	), array[]::text[]) ELSE array[]::text[] END AS header,
	        coalesce(string_agg(row_to_json(_postgrest_t)::text, ','), '')::character varying  AS body,
	        coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
	      FROM (SELECT "pgrst_source".*, COALESCE ((SELECT json_agg("pgrst_source".*) FROM (SELECT "items"."item_id", "items"."name" FROM "items"  WHERE "pgrst_source"."collection_id" = "items"."collection"  ) "pgrst_source"), '[]') AS "items" FROM "pgrst_source"    ) _postgrest_t

I've carefully handcrafted the above snippets from a more complex application. If it proves hard to reproduce I'll take more effort and prepare an SSCCE.

[wolfgangwalther]

The problem is here: RETURNING "collections".*, "collections"."collection_id"

That selects collection_id twice and will lead to the error later.

This should already be fixed as part of #1475:

postgrest/src/PostgREST/DbRequestBuilder.hs

Lines 328 to 331 in bd2160d

	returningCols rr@(Node _ forest) pkCols
	-- if * is part of the select, we must not add pk or fk columns manually - otherwise those would be selected and output twice
	| "*" `elem` fldNames = ["*"]
	| otherwise = returnings

However that's not released yet.

In the meantime you'll have to make it a select=items(item_id,name),collection_id,title (so replace the * with the column names).

[tad-lispy]

Thanks for a very quick response. The suggested workaround works. I suppose the issue should remain open until it's fixed but feel free to close it.

[wolfgangwalther]

I think we can close this, because we have released it as a nightly already.