ERROR: relation table does not exist during PostgreSQL maintenance tasks (VACUUM, REINDEX)

Written by Claudio Kuenzler - 0 comments

Published on January 25th 2023 - Listed in Databases PostgreSQL

---

While running a  maintenance script (doing VACUUM FULL ANALYZE and REINDEX on all tables of a database), the script spat out a lot of errors like these (here with the maintenance tasks manually launched):

postgres@pgserver:~$ psql mydb -c 'VACUUM FULL ANALYZE TB_78C957_LABELS'
ERROR:  relation "tb_78c957_labels" does not exist

postgres@pgserver:~$ psql mydb -c 'REINDEX TABLE TB_78C957_LABELS'
ERROR:  relation "tb_78c957_labels" does not exist

Interestingly the maintenance tasks worked on all lowercase tables while the "relation 'table' does not exist" error showed up on all the tables with uppercase names.

I was unable to find a reason of this error showing up related to VACUUM or REINDEX, but could find related errors when using capital or mixed-letter tables names:

the table is defined with a mixed-case spelling, and you're trying to query it with all lower-case

Although this is not the case here, it gives an important hint: The spelling matters! PostgreSQL uses lower-case naming by default. From the PostgreSQL Gotchas:

All unquoted identifiers are assumed by PostgreSQL to be lower case by default.

That means: The uppercase (and mixed case) table names need to be quoted:

postgres@pgserver:~$ psql mydb -c 'VACUUM FULL ANALYZE "TB_78C957_LABELS"'
VACUUM

postgres@pgserver:~$ psql mydb -c 'REINDEX TABLE "TB_78C957_LABELS"'
REINDEX

Voilà, no error anymore.

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.