Why is Postgres double-quoting the first function identifier when I set a default function for a column?

Question

I have a legacy table with a default expression in a Rails project. This expression picks a random four-digit number and converts it to a string. During my tests I have been typing this exact statement into psql to set the default expression:

alter table owners alter column signing_code set default 
substring(to_char(((random() * ((10000 - 1))::double precision) + 
(1)::double precision), '0000'::text), '....$'::text);

Then when I do \d owners this is what Postgres has decided is my actual default expression:

default "substring"(to_char(((random() * ((10000 - 1))::double 
precision) + (1)::double precision), '0000'::text), '....$'::text)

Notice the double quotes around the first function identifier substring. This causes two problems with Rails schema dumps/loads:

1. When dumping the schema to db/schema.rb, invalid Ruby is produced because the double quotes are not escaped
2. Even if you correctly escape the quotes by hand, when loading the schema back into the database, Rails incorrectly sets the entire expression as a default string value, not an expression (i.e. it surrounds the expression with single quotes)

Is there a way to get Postgres to not double quote the first function in the nested function call in my case? That would be a good workaround, otherwise I'll submit a bug with the Rails project.

Answer 1

Why the substring function gets double quoted doesn't really matter, the two expressions are functionally equivalent. PostgreSQL will parse the default expression and then save the parsed version and during this process, the substring identifier gets double quoted. You can see similar things happen if you add a CHECK constraint like this:

check (c in (1, 2, 3))

Doing a \d on a table with that constraint will give you:

CHECK (c = ANY (ARRAY[1, 2, 3]))

PostgreSQL translates the in expression to an equivalent = ANY expression because that's what it wants to work with internally. The two expressions are functionally equivalent.

The real problem is that ActiveRecord doesn't know what to do with a default expression that is more complex than a single literal value. AR makes an invalid assumption about what defaults look like and then blindly plods along making a mess.

When you're dealing with anything beyond the simplest DDL SQL your best bet is to use db/structure.sql instead of db/schema.rb. structure.sql uses the database's native dump/restore tools so it will understand and preserve everything the database knows about.

Switching to structure.sql is pretty simple:

1. Update config/application.rb to use the right format:

   config.active_record.schema_format = :sql
   

2. Dump your structure.sql using the db:structure:dump rake task.

3. Remove db/schema.rb from your source tree and revision control.

4. Add db/structure.sql to revision control.

5. Retrain your fingers to use different rake tasks for dumping and restoring the schema:

   • db:structure:dump instead of db:schema:dump.
   • db:structure:load instead of db:schema:load.

I always start with structure.sql as schema.rb is too limited for how I want to work with my databases.