How can I access a Postgres column default value using ActiveRecord?

Question

I'm trying to access a column's default value in my Postgres 9.2 database. By using raw SQL, I can verify that the column default is "users_next_id()":

> db = ActiveRecord::Base.connection
> db.execute("SELECT table_name,column_name,column_default 
FROM information_schema.columns 
WHERE table_name = 'users' and column_name ='id'").first

=> {"table_name"=>"users",
 "column_name"=>"id",
 "column_default"=>"users_next_id()"}

But when I use AR's 'columns' method, the default value appears to be nil:

[26] pry(main)> db.columns('users')[0]=> #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x007feb397ba6e8
 @coder=nil,
 @default=nil,
 @limit=8,
 @name="id",
 @null=false,
 @precision=nil,
 @primary=nil,
 @scale=nil,
 @sql_type="bigint",
 @type=:integer>

This isn't causing any problems (other than confusing me), but is this expected behavior? Am I making an incorrect assumption about the 'columns' method?

Answer 1

When ActiveRecord needs to know about a table it does a query similar to your information_schema query but AR will go through the PostgreSQL-specific system tables instead:

  SELECT a.attname, format_type(a.atttypid, a.atttypmod),
         pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
    FROM pg_attribute a LEFT JOIN pg_attrdef d
      ON a.attrelid = d.adrelid AND a.attnum = d.adnum
   WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass
     AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

Search the PostgreSQL adapter source for "regclass" and you'll see some other queries that AR will use to figure out the table's structure.

The pg_get_expr call in the above query is where the column's default value comes from.

The results of that query go, more or less, straight into PostgreSQLColumn.new:

def columns(table_name, name = nil)
  # Limit, precision, and scale are all handled by the superclass.
  column_definitions(table_name).collect do |column_name, type, default, notnull|
    PostgreSQLColumn.new(column_name, default, type, notnull == 'f')
  end
end

The PostgreSQLColumn constructor will use extract_value_from_default to Ruby-ify the default; the end of the switch in extract_value_from_default is interesting here:

else
  # Anything else is blank, some user type, or some function
  # and we can't know the value of that, so return nil.
  nil

So if the default value is bound to a sequence (which an id column in PostgreSQL will be), then the default will come out of the database as a function call similar to this:

nextval('models_id_seq'::regclass)

That will end up in the above else branch and column.default.nil? will be true.

For an id column this isn't a problem, AR expects the database to supply the values for id columns so it doesn't care what the default value is.

This is a big problem if the column's default is something that AR doesn't understand, say a function call such as md5(random()::text). The problem is that AR will initialize all the attributes to their default values – as Model.columns sees them, not as the database sees them – when you say Model.new. For example, in the console you'll see things like this:

 > Model.new
=> #<Model id: nil, def_is_function: nil, def_is_zero: 0>

So if def_is_function actually uses a function call as its default value, AR will ignore that and try to insert a NULL as that column's value. That NULL will prevent the default value from being used and you'll end up with a confusing mess. Defaults that AR can understand (such as strings and numbers) work just fine though.

The result is that you can't really use non-trivial default column values with ActiveRecord, if you want a non-trivial value then you have to do in Ruby through one of the ActiveRecord callbacks (such as before_create).

IMO it would be much better if AR left the default values up to the database if it didn't understand them: leaving them out of the INSERT or using DEFAULT in the VALUES would produce much better results; AR would, of course, have to reload newly created objects from the database in order to get all the proper defaults but you'd only need the reload if there were defaults that AR didn't understand. If the else in extract_value_from_default used a special "I don't know what this means" flag instead of nil then the "I need to reload this object after the first save" condition would be trivial to detect and you'd only reload when necessary.

---

The above is PostgreSQL-specific but the process should be similar for other databases; however, I make no guarantees.