Run rake db:seed and got table “does not exist” error

By | 8th March 2016

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/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.