Inserts now set unset columns to null


#1

Hi,

I upgraded to Ruby 1.8.4 and whatever the latest Rails is as of March 29
2006.

I am using PostgreSQL 7.4.

With the earlier version of rails, I had insert queries that looked like
this:
Topic Create (0.001189) INSERT INTO topics (“inlibrary”,
“irx_display_title”, “irx_title”, “title”, “creation_stamp”,
“last_update_stamp”, “url”, “mime_type”, “irx_doc_path”, “dr_title”,
“irx_machine_id”, “source”, “keywords”) VALUES(‘t’, NULL, 'd gdssg sd5
gd5g ', NULL, ‘2006-03-29 10:49:23’, ‘2006-03-29 10:49:23’, NULL, NULL,
‘/irx/xml/DGdssgSd5Gd5g.xml’, ‘dgbtev sgdg d’, ‘DGdssgSd5Gd5g’, NULL,
NULL)

Note that creation_stamp and last_update_stamp (which are both defined
as timestamp without time zone | not null default
(‘now’::text)::timestamp(6) with time zone) are populated with the
current time. My code does not explicitly do that, so I’m not sure where
that came from.

With the new version of rails, the same query fails:

Topic Create (0.000000) PGError: ERROR: null value in column
“creation_stamp” violates not-null constraint
: INSERT INTO topics (“inlibrary”, “irx_display_title”, “irx_title”,
“title”, “creation_stamp”, “last_update_stamp”, “url”, “mime_type”,
“irx_doc_path”, “dr_title”, “irx_machine_id”, “source”, “keywords”)
VALUES(‘t’, NULL, 'd gdssg sd5 gd5g ', NULL, NULL, NULL, NULL, NULL,
‘/irx/xml/DGdssgSd5Gd5g.xml’, ‘dgbtev sgdg d’, ‘DGdssgSd5Gd5g’, NULL,
NULL)

Here the timestamp columns are inserted as null. I would prefer that the
insert not try to insert values for those columns, as the default will
put in the appropriate value. (For updating, I have a trigger that
accomplishes the same thing).

How can I fix this?


#2

Dan T. wrote:

Hi,

I upgraded to Ruby 1.8.4 and whatever the latest Rails is as of March 29
2006.

I am using PostgreSQL 7.4.

With the earlier version of rails, I had insert queries that looked like
this:
Topic Create (0.001189) INSERT INTO topics (“inlibrary”,
“irx_display_title”, “irx_title”, “title”, “creation_stamp”,
“last_update_stamp”, “url”, “mime_type”, “irx_doc_path”, “dr_title”,
“irx_machine_id”, “source”, “keywords”) VALUES(‘t’, NULL, 'd gdssg sd5
gd5g ', NULL, ‘2006-03-29 10:49:23’, ‘2006-03-29 10:49:23’, NULL, NULL,
‘/irx/xml/DGdssgSd5Gd5g.xml’, ‘dgbtev sgdg d’, ‘DGdssgSd5Gd5g’, NULL,
NULL)

BTW, I forgot to mention that this query is generated by the save!
instance method of the ActiveRecord model object.


#3

Dan T. wrote:

Dan T. wrote:

Hi,

I upgraded to Ruby 1.8.4 and whatever the latest Rails is as of March 29
2006.

I am using PostgreSQL 7.4.

With the earlier version of rails, I had insert queries that looked like
this:
Topic Create (0.001189) INSERT INTO topics (“inlibrary”,
“irx_display_title”, “irx_title”, “title”, “creation_stamp”,
“last_update_stamp”, “url”, “mime_type”, “irx_doc_path”, “dr_title”,
“irx_machine_id”, “source”, “keywords”) VALUES(‘t’, NULL, 'd gdssg sd5
gd5g ', NULL, ‘2006-03-29 10:49:23’, ‘2006-03-29 10:49:23’, NULL, NULL,
‘/irx/xml/DGdssgSd5Gd5g.xml’, ‘dgbtev sgdg d’, ‘DGdssgSd5Gd5g’, NULL,
NULL)

BTW, I forgot to mention that this query is generated by the save!
instance method of the ActiveRecord model object.

I think this is a more interesting issue.

If you have default values ( a sequence - or serial field definition in
postgres ) you want it to NOT use NULL as that obscures the
auto-increment ( or calculation eg now() ) value that you WANT to go
into that field.

I worked on that problem last night and came up with a solution that
solves the problem in a very Rails kind of way.

Here it is :

module ActiveRecord
module Defaults #:nodoc:
def self.append_features(base)
super
base.extend(ClassMethods)
end
module ClassMethods
def default_on_null(options = {})
#print “default_on_null options: #{options.inspect}\n”
configuration = { }
configuration.update(options) if options.is_a?(Hash)

			class_eval <<-EOV
				include ActiveRecord::Defaults::InstanceMethods
					def default_on_null
						:#{options}
					end

					def defaults_class
						::#{self.name}
					end
			EOV
		end
	end
	module InstanceMethods
		private
		public
		def attributes(options=nil)
			attributes=super(options)
			#print "atts: #{attributes.inspect}\ndefault_on_null: 

#{default_on_null.inspect}\n"
if d=default_on_null
attributes.delete(d.to_s)
end
attributes
end
end
end
end

I call this Defaults because you want to be able to switch off actually
inserting a NULL if no value is present.

To use it simply put a ‘default_on_null :my_autocalc_field’ at the top
of your model definition.

I put this code in the ActiveRecord libray location and modified
active_record.rb to ‘require active_record/defaults’ and added an
‘Include ActiveRecord::Defaults’ in the class_eval block.

I’m sure it can be made a lot better by checking if there IS a value
ready to be inserted … and then allowing that to happen.

I have not fully tested this code so it may need some more work.

Cheers