Обсуждение: 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.

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