BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
The following bug has been logged on the website:
Bug reference: 19417
Logged by: yuanchao zhang
Email address: zhangyc0706@gmail.com
PostgreSQL version: 18.2
Operating system: windows 10
Description:
Hi,
I created a domain object named `numeric` using the following SQL:
`CREATE DOMAIN numeric AS NUMERIC(12,2) DEFAULT 0 CHECK (VALUE >= 0);`
After executing this SQL, it indicated that the domain was created
successfully.
However, when I executed `\dD` in psql, I found that the domain I just
created could not be displayed.
Therefore, I queried `pg_type` using the following statement:
`SELECT
n.nspname AS schema_name,
t.typname AS domain_name,
pg_catalog.format_type(t.typbasetype, t.typtypmod) AS base_type,
t.typnotnull AS not_null,
t.typdefault AS default_value,
t.typtype
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'd'
ORDER BY schema_name, domain_name;`
The execution result of this SQL statement shows the domain object I just
created.
I then created a domain using a different name, as shown in the following
SQL:
`CREATE DOMAIN numeric_t1 AS NUMERIC(12,2) DEFAULT 0 CHECK (VALUE >= 0);`
After executing the SQL, I found that this domain could be shown both in
`pg_type` and through a `\dD` query.
Of course, I know that using the name numeric as the domain name isn't
particularly reasonable,
but I didn't receive any prompts during the creation process.
Therefore, I think that since the creation was successful, it should be
displayed via `\dD`.
Because I checked the documentation regarding the use of '\dD', and there
was no explanation for this special case.
Thanks.
On Thu, Feb 26, 2026 at 8:43 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 19417
Logged by: yuanchao zhang
Email address: zhangyc0706@gmail.com
PostgreSQL version: 18.2
Operating system: windows 10
Description:Because I checked the documentation regarding the use of '\dD', and there
was no explanation for this special case.
Because it is not a special case of \dD specifically but a behavior of how
all the \d meta-commands function. This behavior is documented under
Patterns (here, the absence of specifying any pattern):
''""
Whenever the pattern parameter is omitted completely, the \d commands
display all objects that are visible in the current schema search path —
this is equivalent to using * as the pattern. (An object is said to be
visible if its containing schema is in the search path and no object of the
same kind and name appears earlier in the search path. This is equivalent
to the statement that the object can be referenced by name without explicit
schema qualification.)
"""
Your new domain is not visible since the plain type numeric in pg_catalog
is implicitly first in the default search_path.
https://www.postgresql.org/docs/current/app-psql.html
https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH
David J.
PG Bug reporting form <noreply@postgresql.org> writes:
I created a domain object named `numeric` using the following SQL:
`CREATE DOMAIN numeric AS NUMERIC(12,2) DEFAULT 0 CHECK (VALUE >= 0);`
After executing this SQL, it indicated that the domain was created
successfully.
However, when I executed `\dD` in psql, I found that the domain I just
created could not be displayed.
This is expected, because that domain will be behind the built-in
"numeric" type: pg_catalog.numeric comes ahead of public.numeric
in the default search_path. As the psql documentation explains:
Whenever the pattern parameter is omitted completely, the \d
commands display all objects that are visible in the current
schema search path — this is equivalent to using * as the
pattern. (An object is said to be visible if its containing schema
is in the search path and no object of the same kind and name
appears earlier in the search path. This is equivalent to the
statement that the object can be referenced by name without
explicit schema qualification.) To see all objects in the database
regardless of visibility, use *.* as the pattern.
So you'd see the domain if you wrote
\dD *.*
or
\dD public.*
regards, tom lane
Thank you for your reply.
Okay, I understand the reason you mentioned. However, as a user (especially
a novice user), when they create a domain, they can't directly display
their object. Perhaps the user doesn't understand this internal mechanism
of PostgreSQL. The '\dD' manual states, "By default, only user-created
objects are shown." I think this means that user-created objects will be
displayed. Therefore, when a built-in object appears in the search path
before a user-created object, the user-created object should be displayed.
This is because the user-created object is a domain object, which doesn't
belong to the same type as built-in objects in pg_type (domain type is 'd',
while built-in types use 'b'). Otherwise, it might give the user the
illusion that the object wasn't created successfully. This also applies to
other '\d' commands. Alternatively, we could explicitly explain the object
display mechanism in the command's help documentation, or there are other
ways to avoid this.
Thank you.
Best regards，
yuanchao zhang
David G. Johnston <david.g.johnston@gmail.com> 于2026年2月27日周五 00:13写道：
Show quoted text
On Thu, Feb 26, 2026 at 8:43 AM PG Bug reporting form <
noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 19417
Logged by: yuanchao zhang
Email address: zhangyc0706@gmail.com
PostgreSQL version: 18.2
Operating system: windows 10
Description:Because I checked the documentation regarding the use of '\dD', and there
was no explanation for this special case.Because it is not a special case of \dD specifically but a behavior of how
all the \d meta-commands function. This behavior is documented under
Patterns (here, the absence of specifying any pattern):''""
Whenever the pattern parameter is omitted completely, the \d commands
display all objects that are visible in the current schema search path —
this is equivalent to using * as the pattern. (An object is said to be
visible if its containing schema is in the search path and no object of the
same kind and name appears earlier in the search path. This is equivalent
to the statement that the object can be referenced by name without explicit
schema qualification.)
"""Your new domain is not visible since the plain type numeric in pg_catalog
is implicitly first in the default search_path.https://www.postgresql.org/docs/current/app-psql.html
https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH
David J.
On Thu, Feb 26, 2026 at 6:17 PM yuanchao zhang <zhangyc0706@gmail.com>
wrote:
Alternatively, we could explicitly explain the object display mechanism in
the command's help documentation, or there are other ways to avoid this.
There is no bug here. Discussions on how to improve the documentation do
not belong on this mailing list.
If you have a concrete improvement to propose we have a -docs list for such
things. But the system is complex and sometimes requires reading multiple
areas to understand how something works.
David J.