I’m using the postgresql adapter with ActiveRecord. When I execute
this script, I get an error about the id sequence not existing even
though i created the table without the id.
ActiveRecord::Schema.define(:version => 0) do
create_table :articles, :id => false do |t|
t.column :title, :string
end
end
art = Article.create( :title => “Green Eggs and Ham” )
Executing this script gives this error:
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract_adapter.rb:120:in
`log’: RuntimeError: ERROR C42P01 Mrelation “articles_id_seq” does
not exist Fnamespace.c L200 RRangeVarGetRelid: SELECT
currval(‘articles_id_seq’) (ActiveRecord::StatementInvalid)
Since I created the table with “:is => false”, there is no
articles_id_seq and so AR should not be looking for one. Am I missing
something or is this a bug?
Answer:
You need to set your primary key field and create it as a
autoincrementing column in the database.
create_table :articles, :id=>false, :primary_key=>‘pkey_field’ do
t.column ‘pkey_field’, :serial
t.column :title, :string
end
Explanation:
When ActiveRecord defines the default primary key “id” on a table, it
sets it’s type as “serial”, so PostgreSQL automatically creates a
primary key for it. You can use “bigserial” if you need more then
2,147,483,647 ids.
class ArticleWithId < ActiveRecord::Base
set_table_name ‘articles_with_id’
end
def setup
ActiveRecord::Schema.create_table :articles_without_id, :id=>false,
:primary_key=>‘pkey’ do |t|
t.column :pkey, :serial, :null=>false
t.column :title, :string
end
ActiveRecord::Schema.create_table :articles_with_id do |t|
t.column :title, :string
end
end
def teardown
ActiveRecord::Schema.drop_table( :articles_without_id )
ActiveRecord::Schema.drop_table( :articles_with_id )
end
def test_create
assert ArticleWithId.create( :title => “Green Eggs and Ham” )
assert ArticleWithoutId.create( :title => “Green Eggs and Ham 2” )
end
end
class ArticleWithId < ActiveRecord::Base
set_table_name ‘articles_with_id’
end
def setup
ActiveRecord::Schema.create_table :articles_without_id, :id=>false,
:primary_key=>‘pkey’ do |t|
t.column :pkey, :serial, :null=>false
t.column :title, :string
end
ActiveRecord::Schema.create_table :articles_with_id do |t|
t.column :title, :string
end
end
def teardown
ActiveRecord::Schema.drop_table( :articles_without_id )
ActiveRecord::Schema.drop_table( :articles_with_id )
end
def test_create
assert ArticleWithId.create( :title => “Green Eggs and Ham” )
assert ArticleWithoutId.create( :title => “Green Eggs and Ham 2” )
end
end
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
Even though what I posted to you works there may be a problem with
ActiveRecord, by supplying the “primary_key” option it should have
created a primary key in the database table for you, but the PostgreSQL
adapter does not do this. It just creates an indexed and sequenced
column.
Zach
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
My copy / paste cut off half of the test case. Below is the full test
case…
equire ‘abstract_unit’
class PostgreSQLTest < Test::Unit::TestCase
class ArticleWithoutId < ActiveRecord::Base
set_table_name ‘articles_without_id’
set_primary_key ‘pkey’
end
class ArticleWithId < ActiveRecord::Base
set_table_name ‘articles_with_id’
end
def setup
ActiveRecord::Schema.create_table :articles_without_id, :id=>false,
:primary_key=>‘pkey’ do |t|
t.column :pkey, :serial, :null=>false
t.column :title, :string
end
ActiveRecord::Schema.create_table :articles_with_id do |t|
t.column :title, :string
end
end
def teardown
ActiveRecord::Schema.drop_table( :articles_without_id )
ActiveRecord::Schema.drop_table( :articles_with_id )
end
def test_create
assert ArticleWithId.create( :title => “Green Eggs and Ham” )
assert ArticleWithoutId.create( :title => “Green Eggs and Ham 2” )
end
end
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
Even though what I posted to you works there may be a problem with
ActiveRecord, by supplying the “primary_key” option it should have
created a primary key in the database table for you, but the PostgreSQL
adapter does not do this. It just creates an indexed and sequenced
column.
It does for me… at least with rails 1.1.6, postgres 8.1.5, and the
postgres 0.7.1 gem…
I just checked one of my tables and it reports a primary key…
otf_dev=> \d photos;
Table “public.photos”
Column | Type |
Modifiers
-------------±----------------------------±----------------------------------------------------
id | integer | not null default
nextval(‘photos_id_seq’::regclass)
name | character varying(255) |
sku | character varying(255) |
description | text |
is_featured | boolean | default false
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
“photos_pkey” PRIMARY KEY, btree (id)
It does for me… at least with rails 1.1.6, postgres 8.1.5, and the
postgres 0.7.1 gem…
I just checked one of my tables and it reports a primary key…
You have a “default” id generated primary key column. I am talking in
reference to the OPs scenarios, where you have a different field then
“id” be the primary key.
I agree it does work when using the default “id” primary key field.
Ah… I’d missed that part of the thread… I thought I’d read somewhere
that if you specify your own primary key you’re on your own as far as
setting up the sequence/next id stuff regardless of DB since Rails
assumed
if you’re doing that you had a good reason… but I coudl be wrong. And
not sure it’s even relavant to what you two were talking about
You have a “default” id generated primary key column. I am talking in
reference to the OPs scenarios, where you have a different field then
“id” be the primary key.
I agree it does work when using the default “id” primary key field.
Zach
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
“photos_pkey” PRIMARY KEY, btree (id)
-philip
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
