Non-null domain types that have a default value are coerced to null

[imageck]

Environment

• PostgreSQL version: PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
• PostgREST version: Supabase doesn't disclose it anywhere (Server header reads cloudflare)
• Operating system: Linux

Description of issue

Domain constraints should be respected but they aren't. json_to_recordset() fills remaining columns with nulls, thus violating the non-null constraint.

https://stackblitz.com/edit/stackblitz-starters-6sexes?file=pages%2Findex.js

[imageck]

Note that:

1. Supabase doesn't disclose their version of PostgREST. The Server header is overridden by their cloud provider.
2. It is not possible to extract the error logs through the API.

The final query looks like this:

WITH pgrst_source AS (WITH pgrst_payload AS (SELECT $1 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 \"public\".\"people\"(\"name\") SELECT \"name\" FROM json_populate_recordset (null::\"public\".\"people\", (SELECT val FROM pgrst_body)) _  RETURNING \"public\".\"people\".*) SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, coalesce((json_agg(_postgrest_t)->0)::text, 'null') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status FROM (SELECT \"people\".* FROM \"pgrst_source\" AS \"people\"    ) _postgrest_t

[steve-chavez]

CREATE DOMAIN uint AS int
  DEFAULT 0
  NOT NULL
  CHECK (VALUE >= 0);

CREATE TABLE people (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text,
  count uint
);

Using https://postgrest.org/en/stable/references/api/tables_views.html#bulk-insert-with-default-values.

curl 'localhost:3000/people?columns=name,count' -H "Content-Type:application/json" \
-H "Prefer: missing=default, return=representation" \
-d '{"name": "x"}'

{"code":"23502","details":null,"hint":null,"message":"domain uint does not allow null values"}

So I guess you'd expect the 0 to be inserted instead of the null right?

---

https://stackblitz.com/edit/stackblitz-starters-6sexes?file=pages%2Findex.js

Please try to isolate the problem to SQL plus http(curl) calls for next time 🙏. Otherwise it's hard to reproduce the issue.

[steve-chavez]

As a workaround, you can set the DEFAULT in the table instead:

CREATE DOMAIN uint AS int
  NOT NULL
  CHECK (VALUE >= 0);

CREATE TABLE people (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text,
  count uint DEFAULT 0
);

curl 'localhost:3000/people?columns=name,count' -H "Content-Type:application/json" \
-H "Prefer: missing=default, return=representation" \
-d '{"name": "x"}'

[{"id":1,"name":"x","count":0}]

[steve-chavez]

https://stackblitz.com/edit/stackblitz-starters-6sexes?file=pages%2Findex.js

Btw, what error did you got there? It should work as you're not doing a bulk insert.

[imageck]

Hi, @steve-chavez. Thanks for taking your time and looking at the issue closely.

So I guess you'd expect the 0 to be inserted instead of the null right?

Right. Whatever the default value is set to.

Btw, what error did you got there? It should work as you're not doing a bulk insert.

Same as you:
{"code":"23502","details":null,"hint":null,"message":"domain uint does not allow null values"}

The missing=default parameter doesn't seem to make a difference, huh?

[steve-chavez]

Supabase doesn't disclose their version of PostgREST. The Server header is overridden by their cloud provider.

FYI, this one will also get fixed on #2740.

[imageck]

Thanks a lot, @steve-chavez!