Shadowing type names because I am not smart
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/sql-syntax-lexical.html
Description:
Hello, Postgres!
When playing around, I accidentally created a composite type called "text",
and this caused all kinds of trouble that took a while for me to sort out.
(Some GUI clients really don't behave nicely in this situation.) Even after
I realized what I'd done, I found that it behaved in strange ways that
differed from user type names which shadow other built-in type names.
This is all on Linux (Debian stable), using Postgres 17.9.
(1) New type with unique name: works as expected, of course.
typetest=> create type t as (x smallint);
CREATE TYPE
typetest=> \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access
privileges | Description
--------+------+---------------+-------+----------+-------+-------------------+-------------
public | t | t | tuple | | ken |
|
(1 row)
typetest=> drop type t;
DROP TYPE
(2) New type which shadows an existing name: works as long as you "quote" it
(but I'm not sure this is correct: see below).
typetest=> create type integer as (x smallint);
CREATE TYPE
typetest=> \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access
privileges | Description
--------+-----------+---------------+-------+----------+-------+-------------------+-------------
public | "integer" | integer | tuple | | ken |
|
(1 row)
typetest=> drop type integer;
ERROR: 42501: must be owner of type integer
LOCATION: aclcheck_error, aclchk.c:2981
typetest=> drop type "integer";
DROP TYPE
(3) New type which shadows the specific name "text": can't view it (with
psql \dT+), and can't drop it (in the same way as before).
typetest=> create type text as (x smallint);
CREATE TYPE
typetest=> \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges
| Description
--------+------+---------------+------+----------+-------+-------------------+-------------
(0 rows)
typetest=> drop type text;
ERROR: 42501: must be owner of type text
LOCATION: aclcheck_error, aclchk.c:2981
typetest=> drop type "text";
ERROR: 42501: must be owner of type text
LOCATION: aclcheck_error, aclchk.c:2981
But it definitely exists:
typetest=> select user_defined_type_catalog, user_defined_type_schema,
user_defined_type_name from information_schema.user_defined_types;
user_defined_type_catalog | user_defined_type_schema |
user_defined_type_name
---------------------------+--------------------------+------------------------
typetest | public | text
(1 row)
The documentation for CREATE TYPE says: "The type name must be distinct from
the name of any existing type or domain in the same schema." The built-in
types are in "pg_catalog", and I'm definitely not creating any new types
there. It seems wrong for it to create an un-listable/un-droppable type.
I would expect that CREATE TYPE would give an error message, rather than
create a type which it can't deal with.
I would also expect that if "integer" works, then "text" should also work
the same -- they're both built-in types.
Eventually I discovered that I could drop the type by schema-qualifying it:
typetest=> drop type public.text;
DROP TYPE
I also discovered that names like "int2", "int4", "int8" act more like
"text": you can't list them, or drop them by unqualified name. But even
those are slightly nicer than "text" because they give you a hint when you
try to drop them. Compare:
typetest=> drop type "text";
ERROR: must be owner of type text
typetest=> drop type "int8";
ERROR: must be owner of type bigint
It's like "int8" is an alias for "bigint", so if you try to delete (your
own) "int8", it complains about "bigint". But if you try to delete (your
own) "text", it complains about "text", leaving the poor user confused
because they thought that's what they just said.
Upon further reading, 5.10.5 says that "pg_catalog" is effectively first in
the search path, if not explicitly placed later, so I'm not sure why DROP
TYPE "integer"; works. Even quoted, shouldn't that "integer" get resolved
to "pg_catalog"."integer", and therefore report an error?
I know the answer to all of this is "So don't do that!", but I'm confused
anyway.
Thanks for the nice database!
- Ken
On Wed, 2026-07-29 at 22:19 +0000, PG Doc comments form wrote:
When playing around, I accidentally created a composite type called "text",
and this caused all kinds of trouble that took a while for me to sort out.
(Some GUI clients really don't behave nicely in this situation.) Even after
I realized what I'd done, I found that it behaved in strange ways that
differed from user type names which shadow other built-in type names.
I'd say that's a problem of the schema search path: the system schema
pg_catalog is *always* on "search_path", and by default at the beginning.
To access your custom created type, you'd have to schema-qualify it:
DROP TYPE public.text;
Yours,
Laurenz Albe
On Wednesday, July 29, 2026, PG Doc comments form <noreply@postgresql.org>
wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/sql-syntax-lexical.html
Description:This is all on Linux (Debian stable), using Postgres 17.9.
In this case reading the v18 docs and experimenting on v17 is problematic
since the defaults have changed.
Public schema is in the search_path in v17. The implied pg_catalog entry
only exists when searching the search_path during resolution, not when
trying to find a schema into which to create an object.
So yes, creating a type shadowed by a system type behaves as shown. It can
be created without schema qualification but cannot be found that way with a
safe search_path.
David J.
Hi again,
First, sorry about the URL. I composed my email in my text editor, and
then came back to the form later by clicking on the first "please use this
form" link on a Postgres documentation page I had open. The URL isn't
visible on the form, and I didn't realize it would be part of the email
until after I'd submitted it.
My message admittedly wandered a bit, but if I had to narrow it down to one
point of confusion for me, it'd be:
Why does shadowing the name of a built-in type behave differently, for
different built-in types?
Sometimes they appear in \dT, and sometimes they don't.
Sometimes you can refer to them by "quoting" them, and sometimes you need
to schema.qualify them.
Sometimes error messages refer to the type as you wrote it, and sometimes
by an "extension" name for that type.
and so on
Any mental model that I hypothesized, based on one type, failed when I
tried to use it to explain what I saw with a different type. I'm unable to
find anything in the documentation which explains why some built-in types
would act differently than others in any of these ways.
Anyway, thanks for listening! My lesson from this is: this is one of those
areas which you should really just avoid.
- Ken
Ken Harris <kengruven@gmail.com> writes:
My message admittedly wandered a bit, but if I had to narrow it down to one
point of confusion for me, it'd be:
Why does shadowing the name of a built-in type behave differently, for
different built-in types?
The short answer here is that some "built-in" types just have names
that are in the pg_type catalog, while others have names that are
recognized by the grammar and translated to pg_type names. For
example, "double precision" is not a type name per the basic rules,
but the SQL standard demands that we recognize it. So the grammar
has a production that translates that to "pg_catalog.float8" ---
not just float8 --- and that means that a user-defined type can't
override the meaning of "double precision" no matter what the
search path is. Another example is that "integer" is the name
called out by the SQL spec for the type that is entered in
pg_type as "int4". So "integer" is translated to "pg_catalog.int4"
and you can't override that, but you could override plain "int4"
depending on search_path. Conversely, some error messages translate
type OIDs back to the SQL-standard names, but I suspect that not all
do; there may be places that just report the pg_type name.
It doesn't look like we have this situation documented terribly
well, short of looking into gram.y for typename-related productions.
The table in
https://www.postgresql.org/docs/current/datatype.html
leaves the impression that the SQL type names are ground truth
and the other names are aliases, which is basically backwards
from implementation reality. For Postgres, the names in pg_type
are ground truth and the other ones are aliases.
regards, tom lane
On Fri, Jul 31, 2026 at 10:58:41PM -0400, Tom Lane wrote:
Ken Harris <kengruven@gmail.com> writes:
My message admittedly wandered a bit, but if I had to narrow it down to one
point of confusion for me, it'd be:Why does shadowing the name of a built-in type behave differently, for
different built-in types?The short answer here is that some "built-in" types just have names
that are in the pg_type catalog, while others have names that are
recognized by the grammar and translated to pg_type names. For
example, "double precision" is not a type name per the basic rules,
but the SQL standard demands that we recognize it. So the grammar
has a production that translates that to "pg_catalog.float8" ---
not just float8 --- and that means that a user-defined type can't
override the meaning of "double precision" no matter what the
search path is. Another example is that "integer" is the name
called out by the SQL spec for the type that is entered in
pg_type as "int4". So "integer" is translated to "pg_catalog.int4"
and you can't override that, but you could override plain "int4"
depending on search_path. Conversely, some error messages translate
type OIDs back to the SQL-standard names, but I suspect that not all
do; there may be places that just report the pg_type name.
Should we prohibit users from creating types that are hard-coded into
the grammar?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.