pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
5+ messages / 4 participants
[nested] [flat]
* BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
@ 2026-02-26 07:21 PG Bug reporting form <noreply@postgresql.org>
2026-02-26 16:12 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') David G. Johnston <david.g.johnston@gmail.com>
2026-02-26 16:18 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2026-02-26 07:21 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zhangyc0706@gmail.com
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
2026-02-26 07:21 BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') PG Bug reporting form <noreply@postgresql.org>
@ 2026-02-26 16:12 ` David G. Johnston <david.g.johnston@gmail.com>
2026-02-27 01:16 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') yuanchao zhang <zhangyc0706@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2026-02-26 16:12 UTC (permalink / raw)
To: zhangyc0706@gmail.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
2026-02-26 07:21 BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') PG Bug reporting form <noreply@postgresql.org>
2026-02-26 16:12 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') David G. Johnston <david.g.johnston@gmail.com>
@ 2026-02-27 01:16 ` yuanchao zhang <zhangyc0706@gmail.com>
2026-02-27 01:20 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: yuanchao zhang @ 2026-02-27 01:16 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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写道：
> 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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
2026-02-26 07:21 BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') PG Bug reporting form <noreply@postgresql.org>
2026-02-26 16:12 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') David G. Johnston <david.g.johnston@gmail.com>
2026-02-27 01:16 ` Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') yuanchao zhang <zhangyc0706@gmail.com>
@ 2026-02-27 01:20 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2026-02-27 01:20 UTC (permalink / raw)
To: yuanchao zhang <zhangyc0706@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric')
2026-02-26 07:21 BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') PG Bug reporting form <noreply@postgresql.org>
@ 2026-02-26 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2026-02-26 16:18 UTC (permalink / raw)
To: zhangyc0706@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-02-27 01:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-26 07:21 BUG #19417: '\dD' fails to list user-defined domains that shadow built-in type names (e.g., 'numeric') PG Bug reporting form <noreply@postgresql.org>
2026-02-26 16:12 ` David G. Johnston <david.g.johnston@gmail.com>
2026-02-27 01:16 ` yuanchao zhang <zhangyc0706@gmail.com>
2026-02-27 01:20 ` David G. Johnston <david.g.johnston@gmail.com>
2026-02-26 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox