Ever since Nathan had been partnered with the Siberian application development team, he couldn't help wondering if they'd been sent there as punishment. He supposed the long winters got boring, and coming up with "clever" solutions was the only thing keeping the developers sane, so Nathan was used to cutting the far-flung team some slack. Usually.

One day a simple task crossed Nathan's desk: there were two systems on two servers, let's call them Aardvark and Barracuda, that had similar databases. Nathan was asked to compare the subscriber lists stored in each. Since the servers were isolated from each other, he couldn't join across them. Instead, he planned to pull a simple plaintext dump of each and diff them. On Aardvark, this was easy:

psql -Upsql -t -c "select ID, template from subscription;" > file

But Barracuda balked at the same command. Nathan tried again from the Postgres console itself:

psql=>select ID, template from subscription;
ERROR: relation "subscription" does not exist

Nathan was confused: while the databases weren't identical between the two systems, he was quite sure the subscriber list was. He pulled a list of tables:

psql=>\dt
          List of relations
Schema |     Name     | Type  | Owner
-------+--------------+-------+------
public | Subscription | table | psql

The capital-S shouldn't have made a difference... unless, Nathan learned after some Googling, the relation had been created with a quoted name—Postgres treats quoted identifiers as case-sensitive. Odd, Nathan thought, but easy enough to handle:

psql=>select ID, template from "Subscription";
ERROR: column "ID" does not exist

It didn't take a wild leap of logic to guess that the table name wasn't the only thing created with quotes. Nathan's table-dump command became:

psql -Upsql -t -c "select \"ID\", \"Template\" from \"Subscription\";" > file

When the diff was done, Nathan found himself sufficiently annoyed at having to escape all those quotation marks that he emailed Vitaly on the Siberian Team to ask what was up with all the quoted identifiers. Vitaly replied that the Barracuda database had been converted from MySQL to Postgres, and, not wanting to change all the table names from uppercase, they'd simply quoted every identifier on import. Nathan wrote back, praising the solution as "very clever, but didn't you have to go back through all the code that references the tables and add the quotes?" Vitaly elected not to reply to that one.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!