Background

• 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...

Resolutions

• 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.

References

• http://www.postgresql.org/docs/
• http://www.postgresql.org/docs/manuals/