By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table foo (
id serial primary key,
data jsonb not null default '{}',
constraint data_must_be_object check (jsonb_typeof(data) = 'object')
)
CREATE TABLE
--fails as expected
insert into foo (data)
values ('"primitive"'::jsonb);
ERROR: new row for relation "foo" violates check constraint "data_must_be_object" DETAIL: Failing row contains (1, "primitive").
--fails as expected
insert into foo (data)
values ('["array"]'::jsonb);
ERROR: new row for relation "foo" violates check constraint "data_must_be_object" DETAIL: Failing row contains (2, ["array"]).
--works as expected
insert into foo (data)
values ('{"key":"value"}'::jsonb);
INSERT 0 1
-- I can see the check constraint, but not its definition
select * from information_schema.table_constraints
where table_name = 'foo' and constraint_name = 'data_must_be_object'
| constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred | enforced | nulls_distinct |
|---|---|---|---|---|---|---|---|---|---|---|
| postgres | public | data_must_be_object | postgres | public | foo | CHECK | NO | NO | YES | null |
SELECT 1
-- I really want the shape of my data to be described here.
-- this is where the type and nullability is described already.
select *
from information_schema.columns
where table_name = 'foo'
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| postgres | public | foo | id | 1 | nextval('foo_id_seq'::regclass) | NO | integer | null | null | 32 | 2 | 0 | null | null | null | null | null | null | null | null | null | null | null | null | postgres | pg_catalog | int4 | null | null | null | null | 1 | NO | NO | null | null | null | null | null | NO | NEVER | null | YES |
| postgres | public | foo | data | 2 | '{}'::jsonb | NO | jsonb | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | postgres | pg_catalog | jsonb | null | null | null | null | 2 | NO | NO | null | null | null | null | null | NO | NEVER | null | YES |
SELECT 2
--
select * from information_schema.check_constraints
where constraint_name = 'data_must_be_object'
| constraint_catalog | constraint_schema | constraint_name | check_clause |
|---|---|---|---|
| postgres | public | data_must_be_object | ((jsonb_typeof(data) = 'object'::text)) |
SELECT 1
select * from pg_catalog.pg_constraint where conname =
'data_must_be_object'
| oid | conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | confdelsetcols | conexclop | conbin |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 16390 | data_must_be_object | 2200 | c | f | f | t | 16385 | 0 | 0 | 0 | 0 | t | 0 | f | {2} | null | null | null | null | null | null | {OPEXPR :opno 98 :opfuncid 67 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args ({FUNCEXPR :funcid 3210 :funcresulttype 25 :funcretset false :funcvariadic false :funcformat 0 :funccollid 100 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 3802 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 133}) :location 120} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 141 :constvalue 10 [ 40 0 0 0 111 98 106 101 99 116 ]}) :location 139} |
SELECT 1