21:14

The Text Type… If That’s Your Real Name.

13 March 2013

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:

postgres=# CREATE TABLE text (
postgres(#    i INTEGER
postgres(# );
CREATE TABLE

And both types appear in pg_type:

postgres=# SELECT typname, typnamespace FROM pg_type WHERE typname='text';
 typname | typnamespace 
---------+--------------
 text    |           11
 text    |         2200
(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.

There are 5 comments.