Bad error message with Prefer: missing=default on GENERATED COLUMNS

[steve-chavez]

Problem

Having:

create table foo (
  a text,
  b text GENERATED ALWAYS AS (
      case WHEN a = 'telegram' THEN 'im'
           WHEN a = 'proton' THEN 'email'
           WHEN a = 'infinity' THEN 'idea'
           ELSE 'bad idea'
      end) stored
);
-- example from https://stackoverflow.com/a/69569932/4692662

The request:

http POST 'localhost:3000/foo?columns=a,b' "Prefer: missing=default" <<JSON
[
  {"a": "val"},
  {"a": "val", "b": "val"}
]

Will give a bad error message:

{
    "code": "42703",
    "details": null,
    "hint": "There is a column named \"a\" in table \"foo\", but it cannot be referenced from this part of the query.",
    "message": "column \"a\" does not exist"
}

This is because the generated query is:

INSERT INTO "test"."foo"("a", "b") SELECT "pgrst_body"."a", "pgrst_body"."b"
FROM (SELECT '[ {"a": "Vier"}, {"a": "qewr", "b": "xxx"} ]'::jsonb AS json_data) pgrst_payload,
LATERAL (SELECT CASE WHEN jsonb_typeof(pgrst_payload.json_data) = 'array' THEN pgrst_payload.json_data ELSE jsonb_build_array(pgrst_payload.json_data) END AS val) pgrst_uniform_json, 
LATERAL (
  SELECT jsonb_agg(jsonb_build_object('b',
        CASE
            WHEN (a = 'telegram'::text) THEN 'im'::text
            WHEN (a = 'proton'::text) THEN 'email'::text
            WHEN (a = 'infinity'::text) THEN 'idea'::text
            ELSE 'bad idea'::text
        END) || elem) AS val
  FROM jsonb_array_elements(pgrst_uniform_json.val) elem) pgrst_json_defs,
LATERAL (SELECT * FROM jsonb_to_recordset(pgrst_json_defs.val) AS _("a" text, "b" text) ) pgrst_body
RETURNING 1;

Where a refers to the table column, for which there's no reference at that point in the query.

Solution

Don't apply defaults for GENERATED ALWAYS columns so the error can be:

{
    "code": "428C9",
    "details": "Column \"b\" is a generated column.",
    "hint": null,
    "message": "cannot insert a non-DEFAULT value into column \"b\""
}

Notes

• Considering this as minor because it will err anyway, the user shouldn't include non-writable columns in ?columns=..
• Generated columns can only be GENERATED ALWAYS, trying to change them to GENERATED BY DEFAULT will give an error(ERROR: for a generated column, GENERATED ALWAYS must be specified).

[steve-chavez]

Generated columns are actually a pg 12 feature: https://severalnines.com/blog/overview-generated-columns-postgresql/#:~:text=Generated%20Columns%20in%20PostgreSQL%2012,a%20result%20of%20an%20expression.

The pg_attribute.attgenerated can be used to check if the column is generated. Only available from pg >= 12: https://www.postgresql.org/docs/12/catalog-pg-attribute.html