On Monday, February 29, 2016 at 2:01:07 PM UTC-8, Tom Close wrote: > > I am trying to implement a uniqueness constraint over a set of columns > that can include a null values, using the technique described in this > stackoverflow answer <http://stackoverflow.com/a/8289327/452769>: > > Sequel.migration do > change do > create_table :test_table do > primary_key :id > > String :user_id > String :menu_id > String :recipe_id > > unique [:user_id, Sequel.function(:coalesce, :menu_id, "000-000"), > :recipe_id] > end > end > > When I run this it executes the following sql: > > CREATE TABLE "test_table" ("id" serial PRIMARY KEY, "user_id" text, > "menu_id" text, "recipe_id" text, UNIQUE ("user_id", coalesce("menu_id", > '000-000'), "recipe_id")) > > and gives the following error: > > Sequel::DatabaseError: PG::SyntaxError: ERROR: syntax error at or near "(" > LINE 1: ...ext, "recipe_id" text, UNIQUE ("user_id", coalesce("menu_id"... > > Is there something obvious I'm doing wrong here? >
PostgreSQL's documentation says UNIQUE takes columns, not expressions: http://www.postgresql.org/docs/current/static/sql-createtable.html Basically, it appears that PostgreSQL doesn't support what you want to do. You probably should use a unique index instead of a constraint: create_table :test_table do primary_key :id String :user_id String :menu_id String :recipe_id index [:user_id, Sequel.function(:coalesce, :menu_id, "000-000"), :recipe_id], :unique=>true, :name=>:unique_menu_recipe_idx end Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
