- Rails version: 4.2.5
- Database: PostgreSQL 9.5
On chapter 11 of Rails Tutorial, user table was created directly via pgAdmin III – not by Rails database migration – I prefer to create database tables etc via SQL script so I have full control over all things at database level. Also, that is probably necessary for integration with existing / “legacy” database.
CREATE SEQUENCE users_id_seq; CREATE TABLE public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'), name character varying, email character varying, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL, password_digest character varying, remember_digest character varying, admin boolean DEFAULT false, activation_digest character varying, activated_at timestamp without time zone, activated boolean DEFAULT false, reset_digest character varying, reset_sent_at timestamp without time zone, CONSTRAINT users_pkey PRIMARY KEY (id) );
Here is the content of db/seed.rb file
User.create!(name: "Example User", email: "[email protected]", password: "foobar", password_confirmation: "foobar", admin: true, activated: true, activated_at: Time.zone.now)
Run rake db:seed and got undefined table referencing users
> bundle exec rake db:seed rake aborted! ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "users" does not exist LINE 5: WHERE a.attrelid = '"users"'::regclass ^ : 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 = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum /var/lib/gems/2.1.0/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `async_exec' /var/lib/gems/2.1.0/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `block in exec_no_cache' x...
- Notice the error reference the where clause “WHERE a.attrelid = ‘”users”‘::regclass”.
- Extract the select query, execute the query in Pgadmin3 and got the error “relation “users” does not exist”.
- Look up pg_attribute in Postgresql and attrelid is of type “oid” with description “The table this column belongs to”.
- Check the documentation for OID (Object Identifier) –
“Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created” – this suggest no OID is assigned for the users table when table is created.
- Check the documentation for CREATE TABLE and found OID can be added using “WITH (OID=TRUE)” option so the CREATE TABLE statement looks like
... CONSTRAINT wlr_users_pkey PRIMARY KEY (id) ) WITH (OIDS=TRUE);
- Rerun rake db:seed and no further error.