Ecto migration - cannot define collation with pg_catalog."default"

Questions / Help
1

First of all I started learning Elixir about 2 months ago on my spare time (which I don’t have a lot of unfortunately), so my apologies if my question has an obvious answer.

I am having a hard time defining a column ‘pg_catalog.”default”’ collation in a migration file (for Postgres).

This sample code:

create table(:some_table, primary_key: false) do
  add :first_name, :text, null: false, collation: "pg_catalog.\"default\""
end

results in this error:

** (Postgrex.Error) ERROR 42601 (syntax_error) zero-length delimited identifier at or near """"

I think this is because the resulting “CREATE TABLE” query contains the following:

"first_name" text NOT NULL COLLATE "pg_catalog."default"",

I am able to work around this issue by removing the ‘collation’ definition and replacing with an ‘execute’ statement:

execute("ALTER TABLE some_table ALTER COLUMN first_name SET DATA TYPE text COLLATE  pg_catalog.\"default\""")

It is a bit annoying. Is there a way to get ‘collation’ to work in this case?

Thanks.

2

Interesting. The docs for add/3 explicitly mention that the type field is not automatically quoted because it can be an expression. Perhaps the collation field should receive the same treatment? I wonder if this would be a breaking change.

BTW this will not solve your problem, but if you’re not aware you can use the ~s sigil to avoid escaping the quotes in your strings. Sometimes it looks nicer.

~s(pg_catalog."default")
1 Like
3

Thanks for the feedback. I didn’t consider the possibility of a regression. I will take another look once I am done exploring changesets. I am aware of sigils and used them while trying to fix the collation issue (I thougth it would perhaps remove the quotes).