Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading