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
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.