The Build

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.

Tom Lane at 21:50, 13 March 2013:

While *you* might get confused, pg_restore shouldn’t. Could we have a bug report with a reproducible example?

Andres Freund at 03:26, 14 March 2013:

Hi!

it would be cool if you could report a bug, because that surely isn’t supposed to be a problem for pg_restore or such…

Andres

Ian Barwick at 03:51, 14 March 2013:

Interesting to know, thanks. I’ve been trying to reproduce this without success; do you have the definition of the offending index?

I’d always kind of assumed datatypes were reserved words but I see it’s possible to take things to extremes a la CREATE TABLE text.text (text text)

Ingo at 06:14, 14 March 2013:

Thanks for this post. I also ran into this once. Is this rather a bug or a feature? Why doesn’t posgres issue a warning?

Darren Duncan at 17:22, 15 March 2013:

Definitely, if pg_restore is having a problem here, that is a bug that should get fixed. Saying you shouldn’t name one of your own entities something is bad. That’s why we have namespaces and delimited identifiers, so users can name things whatever they want and not have that conflict with built-ins.