PostgreSQL pg_constraint's confupdtype / confdeltype columns using undocumented letter (space). What does it mean?

346 views Asked by At

The doc at https://www.postgresql.org/docs/current/catalog-pg-constraint.html says:

Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

And does not mention a space at all. But it actually uses a space (ascii code 32) a lot:

ddevienne=> select '"'||confdeltype||'"', count(*) from pg_constraint group by confdeltype;
 ?column? | count
----------+-------
 "a"      |   242
 "c"      |   941
 " "      |  2210
 "n"      |   201
(4 rows)
Time: 3.968 ms

So what does that space mean? And why is it not documented?
I'm using PostgreSQL v12, but the doc is the same for v12 and v14 in that regard.

Update (given the accepted answer): Looking at Foreign Key constraints only (contype = 'f') does get rid of the spaces indeed.

ddevienne=> select '"'||confdeltype||'"', count(*) from pg_constraint where contype = 'f' group by confdeltype;
 ?column? | count
----------+-------
 "a"      |   242
 "c"      |   941
 "n"      |   201
(3 rows)
Time: 4.124 ms
ddevienne=> select '"'||confupdtype||'"', count(*) from pg_constraint where contype = 'f' group by confupdtype;
 ?column? | count
----------+-------
 "a"      |  1381
 "c"      |     2
 "n"      |     1
(3 rows)
Time: 3.361 ms
1

There are 1 answers

0
Laurenz Albe On BEST ANSWER

confdeltype is NOT NULL, so it is set to a blank for constraints other than foreign key constraints.