How to make Rails use default DB values on INSERT?

Rails
1

When creating a new record, rails does not use the default DB values
that are functions. For example, ``col1 timestamp with time zone not
null default NOW().‘’ Instead, Rails sets col1 to “NULL” on an INSERT
and my Postgresql DB complains and fails.

Using magical column names, such as created_at is not an option. I want
the DB to set the timestamps, not the Rails application servers.

If Rails would just leave these untouched/unset/nil columns out of the
INSERT statement, everything would be fine because the DB would set the
values using its defaults. I have tried removing the instance variables
using the method remove_instance_variables and have tried removing
elements in the attributes hash using attr_accessible, that correspond
to the nil columns; nothing works. Also, I don’t want to resort to
custom SQL queries.

Many people have mentioned this issue on the web, but I cannot find a
resolution. Also, at least three tickets, #4540, #2257, #3913, on
dev.rubyonrails.org/ticket/ have mentioned this same thing. It seems
this issue has been in limbo between fixed and broken.

I have searched through
active_record/connection_adapters/postgresql_adapter.rb in my Rails
Edge for parsings of “now” (DB function), but came up empty. It seems
that the code that fixed this in the tickets I mentioned above was for
Rails 1.1. Unfortunately, it looks like this patch was removed
recently.

Suggestions and insights will be greatly appreciated.

-pachl

2

This is either a PostGreSQL issue, or a Rails + PostGreSQL issue, as it
does not happen with MySQL.

I just updated a MySQL table in one of my temporary apps with:

ALTER TABLE brands ADD TESTR VARCHAR( 5 ) NOT NULL DEFAULT ‘XXX’;
ALTER TABLE brands ADD TESTF TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP;

I changed nothing else in my application model/view/controller. I then
addeda new ‘brand’ (where the view form did not include these two new
fields) and the fields were populated with the appropriate/expected
defaults.

c.

clintpachl wrote:

When creating a new record, rails does not use the default DB values
that are functions. For example, ``col1 timestamp with time zone not
null default NOW().‘’ Instead, Rails sets col1 to “NULL” on an INSERT
and my Postgresql DB complains and fails.

Using magical column names, such as created_at is not an option. I want
the DB to set the timestamps, not the Rails application servers.

If Rails would just leave these untouched/unset/nil columns out of the
INSERT statement, everything would be fine because the DB would set the
values using its defaults. I have tried removing the instance variables
using the method remove_instance_variables and have tried removing
elements in the attributes hash using attr_accessible, that correspond
to the nil columns; nothing works. Also, I don’t want to resort to
custom SQL queries.

Many people have mentioned this issue on the web, but I cannot find a
resolution. Also, at least three tickets, #4540, #2257, #3913, on
dev.rubyonrails.org/ticket/ have mentioned this same thing. It seems
this issue has been in limbo between fixed and broken.

I have searched through
active_record/connection_adapters/postgresql_adapter.rb in my Rails
Edge for parsings of “now” (DB function), but came up empty. It seems
that the code that fixed this in the tickets I mentioned above was for
Rails 1.1. Unfortunately, it looks like this patch was removed
recently.

Suggestions and insights will be greatly appreciated.

-pachl

3

Hi,

AR needs all the fields values for validating. Leaving default values at
the db level is then not possible.

Jean-Etienne

4

Looked at my log, more information…

Here’s the insert statement:

INSERT INTO brands (name, TESTF, TESTR, description)
VALUES(‘TESTRUBY’, NULL, ‘XXX’, ‘TESTRUBY’)

So, in the case of the timestamp, yes Rails is inserting NULL as you
said, and apparently MySQL deals well with this but PostGreSQL does not.

Interestingly (to me at least) is that Rails pulled the default value
for the character field from the database dictionary and used that in
the INSERT statement.

So you are correct, Rails is not leaving these fields ‘alone’ and
defaulting to the database. Sorry - talking/posting as I think through
it, it’s an interesting issue.

c.

5

The first thing that comes to mind is removing the null constraint and
using a trigger to update col1. I’m sure there is a better way to
handle this, but the following should work.

CREATE FUNCTION touch() RETURNS “trigger”
AS $$
begin
NEW.col1 = now();
return NEW;
end;
$$
LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER t_tablename
AFTER INSERT OR UPDATE ON tablename
FOR EACH ROW
EXECUTE PROCEDURE touch();

6

Cayce B. wrote:

Looked at my log, more information…

Here’s the insert statement:

INSERT INTO brands (name, TESTF, TESTR, description)
VALUES(‘TESTRUBY’, NULL, ‘XXX’, ‘TESTRUBY’)

So, in the case of the timestamp, yes Rails is inserting NULL as you
said, and apparently MySQL deals well with this but PostGreSQL does not.

Actually, I think Postgresql is doing the right thing. What if you
wanted TESTF=NULL?

I found the reason for NULL being inserted for the timestamp column in
an embedded comment in the file postgresql_adapter.rb. This snippit is
at the end of the method ``default_values’’.

Anything else is blank, some user type, or some function

and we can’t know the value of that, so return nil.

return nil

I believe the reason for not accepting user-defined data and DB
functions is that they would not pass the SQL validation process. For
example, to use the DB’s NOW() function, it would need to be passed as
a non-quoted string. This would violate the validation of a timestamp
column. The way Rails is setup currently, it just doesn’t worry about
non-conforming data types and just uses nil/NULL.

ALTER TABLE brands ADD TESTR VARCHAR( 5 ) NOT NULL DEFAULT ‘XXX’;
ALTER TABLE brands ADD TESTF TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP;

Interestingly (to me at least) is that Rails pulled the default value
for the character field from the database dictionary and used that in
the INSERT statement.

Yes, that’s because the TESTR column is a VARCHAR and the default value
is a string (‘XXX’). This conforms to Rails’s rules. I verified this
behavior in the ActiveRecord source. So, Rails treats MySQL and
PostgreSQL the same (i.e. builds the same INSERT query), but the two
DBs handle incoming requests differently. I have used MySQL in the past
and IIRC, a NULL value for a timestamp field would result in using the
default value. This works, but sometimes you may want a NULL. I think
Postgresql is doing the right thing according to SQL standards.

So you are correct, Rails is not leaving these fields ‘alone’ and
defaulting to the database. Sorry - talking/posting as I think through
it, it’s an interesting issue.

I just wish that Rails would leave the nil key/value pairs out of the
SQL INSERT queries. This would be more efficient and would let the DB
make the final decision on what gets stored in the DB, which is how it
should be. Rails is not the only application using the DB. I wonder if
this behavior would break Rails in some way.

-pachl

7

values.

Can someone please tell me if I am blatantly breaking something?
Otherwise, I will continue to use this hack and monitor its side
effects.

My first thought is that if you can supply the values you need in
rails, then do that instead of using default values in the database.
That seems to be the most coherent and logic way unless you run into
cases where it won’t work for whatever reason. I do think that there
should be something added to AR to deal with this so people don’t end
up having to create their own hacks. If you don’t want a column
included in the insert, there should be a supported way to handle that
scenario.

Maybe the only other thing I might add is something to restrict which
columns can be deleted from the insert.

@attributes = @attributes.delete_if {|k,v| v.nil? and
[‘col1’,‘col2’].include?(k)}

8

Jean-Etienne D. wrote:

Hi,

AR needs all the fields values for validating. Leaving default values at
the db level is then not possible.

I wonder then if it is possible to some how remove the attributes
before an insert so they are not included in the query? I have done
this and it works great when inserting a record, but I’m not sure if it
breaks something of which I am unaware.

Summary of my hack:
I remove all key/value pairs from <model_obj>.attributes that are nil
before creating a record. After the record has been inserted, I reload
the object attributes from the DB.

class User < ActiveRecord::Base
after_create :reload

def before_create
@attributes = @attributes.delete_if {|k,v| v.nil?}
end
end

After setting only the “application required” fields from the
controller, I call save on the model object. Below is the simple and
elegant SQL query constructed by ActiveRecord, which is sent to
Postgresql:
INSERT INTO users (“pw”, “usr”, “email”) VALUES(‘XXXX’, ‘username’,
‘[email protected]’)

**The users table contains about ten additional columns. The non-null
columns are then set by the DB using default values. The “null-able”
columns are then set by the DB to NULL or thier default values.

Can someone please tell me if I am blatantly breaking something?
Otherwise, I will continue to use this hack and monitor its side
effects.

-pachl

9

Why not simply use a before_save callback on your model to populate the
fields.

class Person
def before_save
self.time_column ||= Time.now
end
end

-Jonathan.

10

Good points all. On this though…

clintpachl wrote:

Actually, I think Postgresql is doing the right thing. What if you
wanted TESTF=NULL?

default value. This works, but sometimes you may want a NULL. I think
Postgresql is doing the right thing according to SQL standards.

If you want to be able to set a field value to “NULL” when you create
the record, then you wouldn’t set a default value in the database. By
it’s very nature, a default value says “never let this field be anything
else on creation of a new record”.

c.

11

Wow, read that back and it doesn’t sound right at all. Wish I could
delete posts here.

:slight_smile:

I’m gonna stop talking now and go back to coding.

Go about your business, the crazy man on the sidewalk should be ignored.

c.

Cayce B. wrote:

Good points all. On this though…

clintpachl wrote:

Actually, I think Postgresql is doing the right thing. What if you
wanted TESTF=NULL?

default value. This works, but sometimes you may want a NULL. I think
Postgresql is doing the right thing according to SQL standards.

If you want to be able to set a field value to “NULL” when you create
the record, then you wouldn’t set a default value in the database. By
it’s very nature, a default value says “never let this field be anything
else on creation of a new record”.

c.

12

On 10/12/06, Cayce B. [email protected] wrote:

If you want to be able to set a field value to “NULL” when you create
the record, then you wouldn’t set a default value in the database.

That’s simply not true. “NULL” in many cases can be used as a
reasonable value. The fact a column is set to null might mean
something to some application. (I’m not defending that type of design
mind you, only that it is valid.)

By it’s very nature, a default value says “never let this field be anything
else on creation of a new record”.

To you, maybe, but not universally. Defaults mean exactly that; use
this value if I didn’t specify anything else. “Anything else” here
can include null.

–
Rule Of Robustness: Robustness is the child of transparency and
simplicity.

13

I just wish that Rails would leave the nil key/value pairs out of the
SQL INSERT queries.

That breaks things. SQL cannot determine then if you WANT a null vs. a
default.

This would be more efficient and would let the DB
make the final decision on what gets stored in the DB, which is how it
should be.

But standard SQL does not work like this. If you have a column that
IS nullable, but has a default, you have to, from the application’s
side, determine if you want a null there, or to use the default.

SQL’s behavior is very well defined here; you insert a NULL, you get a
null, you don’t insert the value at all, then and only then, the
default is used.

RAILS should have some way of saying “don’t insert this column
value”. But it should not simply always use null, NOR should it
always leave the nulls out. Either way breaks things.

14

By it’s very nature, a default value says “never let this field be anything
else on creation of a new record”.

To you, maybe, but not universally. Defaults mean exactly that; use
this value if I didn’t specify anything else. “Anything else” here
can include null.

I think a lot of the confusion is that some people think NULL stands
for nothing, or no value at all, instead of an ‘unknown value’.

RAILS should have some way of saying “don’t insert this column
value”. But it should not simply always use null, NOR should it
always leave the nulls out. Either way breaks things.

Yes, well said.

Chris

15

IIRC, SQL standard allows :
INSERT INTO TableNameVALUES (DEFAULT, DEFAULT …)

So, what about some crazy idea?
Lets define nil constant, that isn’t just NilClass:
module DefaultClass; end
DefaultValue = nil
DefaultValue.extend(DefaultClass)
DefaultClass === DefaultValue # => true
NilClass === DefaultValue # => true

Next, initialize fresh model instance attributes with not ‘nil’ but
such a DefaultValue, and add proper quoting:

module ActiveRecord
module ConnectionAdapters # :nodoc:
module Quoting
# Quotes the column value
def quote(value, column = nil)
# records are quoted as their primary key
return value.quoted_id if value.respond_to?(:quoted_id)

    case value
      when String

[snip]
# add handling defaults:
when DefaultClass then “DEFAULT”
when NilClass then “NULL”

Or, even simpler way, add in ActiveRecord::Base
DefaultValue = nil
def DefaultValue.quoted_id; “DEFAULT”; end
def attributes_from_column_definition
self.class.columns.inject({}) do |attributes, column|
attributes[column.name] = (column.default || DefaultValue)
unless column.name == self.class.primary_key
attributes
end
end

2006/10/12, Michael C. [email protected]:

16

On Oct 12, 6:48 am, “Michael C.” [email protected]
wrote:

null, you don’t insert the value at all, then and only then, the
default is used.

RAILS should have some way of saying “don’t insert this column
value”. But it should not simply always use null, NOR should it
always leave the nulls out. Either way breaks things.

Yes, this is right on. It would be excellent if Rails had this kind of
flexibility.

17

On Oct 12, 2:54 am, “Jonathan V.” [email protected] wrote:

Why not simply use a before_save callback on your model to populate the
fields.

class Person
def before_save
self.time_column ||= Time.now
end
end

Because this uses the application server’s time, not the DB’s time.
What if time accuracy was a priority and there were multiple front end
application servers, some in different time zones, and all with
unsyncronized time?

More importantly, it’s not a matter of accepting the current time
(ruby’s Time.now or DB’s NOW()). What if I needed to set a
timestamp/date column to a DB function that did some crazy calculation
and must be hidden from the app? To do this, one would have to set the
column attribute equal to a String describing the function, however, a
String object will be discarded while being parsed in
ActiveRecord::ConnectionAdapters::Column.string_to_time. See
active_record/connection_adapters/abstract/schema_definitions.rb for
details.

-pachl

18

On Oct 12, 7:24 am, “Lugovoi N.” [email protected] wrote:

def DefaultValue.quoted_id; "DEFAULT"; end
def attributes_from_column_definition
    self.class.columns.inject({}) do |attributes, column|
      attributes[column.name] = (column.default || DefaultValue)

unless column.name == self.class.primary_key
attributes
end
end

This is an excellent idea. After looking at the ActiveRecord source
code, I can see how this may be implemented, but it may take some work.
One thing that AR does is quote everything. As a test, I just tried
inserting a record in my Postgresql 8.1 DB using quoted and unquoted
DEFAULT values. Quoting the keyword DEFAULT simply does not work. For
example, if the column is a string type, the literal string “DEFAULT”
gets stored and if the column is a timestamp, the DB complains of
invalid syntax.

So, AR would have to discern the object types and avoid using quotes
when inserting the DEFAULT keyword.

-pachl