7

Rails 3 + postgresql

I want to have a sha of a random string for a default value of a column.

So, in my migration I have:

t.string :uniqueid, default: md5(random()::text)

However i can not get this to actually produce anything, I've used backticks, quotes,etc. From examples that I've seen it seems like that pg function only works in a SELECT statement.

Is that accurate? Any ideas on how I could achieve this?

Thanks

mu is too short
  • 426,620
  • 70
  • 833
  • 800
cbrulak
  • 15,436
  • 20
  • 61
  • 101
  • You'll need to look at the generated SQL, because the md5(...) call is perfectly valid as a DEFAULT clause in a CREATE/ALTER TABLE. – Richard Huxton Mar 01 '13 at 09:39

2 Answers2

7

You can also do

t.string :token, default: -> { "md5((random())::text)" }, null: false

Edit : Even if this generates a random string with 32 digits, it does not mean it's unique. I thought so but I just had a unique index error.

brcebn
  • 1,571
  • 1
  • 23
  • 46
6

Note: You probably want to look at brcebn's answer if you're using a more recent version of Rails.

Rails will try to interpret this:

t.string :uniqueid, default: md5(random()::text)

as Ruby code and :default => md5(...) doesn't mean anything in Ruby. If you quote it, then Rails will think it is a string and make the default value for uniqueid the string 'md5(random()::text)' and that's not going to help.

If you want to use a function call in a column default, you can do the alter table by hand:

connection.execute(%q{
    alter table your_table alter column uniqueid set default md5(random()::text)
})

That will get you the default you want in the database but you might notice that there's no mention of the new default in your schema.rb. If you want a usable schema then you'll have to use an SQL schema instead by putting this in your application.rb:

config.active_record.schema_format = :sql

Then delete your schema.rb and use structure.sql instead. Note that SQL schema dumps were broken until 3.2 and there are schema loading problems in various Rails versions (but you can always psql < structure.sql your way around that). On the upside, SQL schema dumps will keep track of fancy things real foreign keys, check constraints, triggers, ...

BTW, if you really want SHA then you'll want to look at the digest function from pgcrypto.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • the connection.execute(...) is that something you would do in the pg console? – cbrulak Mar 01 '13 at 19:48
  • @cbrulak: No, you can do that right inside your migration's `def up` or `def down` methods. You just need to bypass all the Rails stuff and talk straight to the database, that's what `connection.execute` does. – mu is too short Mar 01 '13 at 19:51
  • Okay, that worked. However, when new rows are created the default is still null. Any pointes on that issue? – cbrulak Mar 03 '13 at 06:21
  • @cbrulak: *swearing* ActiveRecord. ActiveRecord doesn't understand the default value it sees in the database and decides to throw a NULL in there instead, see my updated answer for further musings on this. Basically you're stuck with a `before_create` hook to compute the column default in Ruby. – mu is too short Mar 03 '13 at 07:10
  • okay, thanks a lot for you help. I ended up doing that but with the secure random library. – cbrulak Mar 04 '13 at 15:49
  • 1
    @cbrulak: There's a more thorough overview of the default value handling [over here](http://stackoverflow.com/a/15239014/479863) if you're interested. – mu is too short Mar 06 '13 at 04:30