The Text Type... If That's Your Real Name.

tl;dr: Don’t give tables the same name as base PostgreSQL types, even though it will let you.

It’s interesting how synchronicity can occur. In my talk about custom PostgreSQL types in Python, I mentioned that any time you create a table in PostgreSQL, you’re also creating a type: the row type of the table.

While I was presenting the talk, a client sent me email wondering why a pg_restore of an expression index was failing, because the return type text was not the same as pg_catalog.text. OK, that’s strange!

What had happened is that the database has a table with the name text, which PostgreSQL will happily let you do:

1
postgres=# CREATE TABLE text (
2
postgres(#    i INTEGER
3
postgres(# );
4
CREATE TABLE

And both types appear in pg_type:

1
postgres=# SELECT typname, typnamespace FROM pg_type WHERE typname='text';
2
 typname | typnamespace 
3
---------+--------------
4
 text    |           11
5
 text    |         2200
6
(2 rows)

Needless to say, this isn’t a great idea, because although PostgreSQL seems to keep them straight most of the time, there are times (like a pg_restore processing an expression index) that it can get confused.

So, don’t do this.