PG undefinedtable error relation users does not exist

Question

I saw this question up before, but only for rspec. I haven't created test yet because it's too advanced for me but one day soon i will! :P

I get this error when I try to sign-up/login into my app. I used devise to create user and also omniauth2 to sign-in with google.

this is the error

ActiveRecord::StatementInvalid at /users/auth/google_oauth2/callback
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

I tried rake db:migrate, but it already is created: in schema table users exist. Has anyone got this error before?

database.yml

config=/opt/local/lib/postgresql84/bin/pg_config

development:
  adapter: postgresql
  encoding: unicode
  database: tt_intraweb_development
  pool: 5
  username: my_username
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: tt_intraweb_test
  pool: 5
  username: my_username
  password:

production:
  adapter: postgresql
  encoding: unicode
  database: tt_intraweb_production
  pool: 5
  username: my_username
  password:

Not for me, having the same error and none of the answers have been helpful. — Gustavo Semião-Lobo, Mar 27 '14 at 15:49
Actually, I was wrong, sorry for that. `RAILS_ENV=test rake db:migrate` worked for me. — Gustavo Semião-Lobo, Apr 22 '14 at 14:28

Малъ Скрылевъ · Answer 1 · 2018-08-23T14:42:36.360

At first, you shall detach all connections out of database. By default you use the development environment. Then try to reset database with the following:

rake db:reset

The rake db:reset task will drop the database and set it up again. This is functionally equivalent to rake db:drop db:setup.

This is not the same as running all the migrations. It will only use the contents of the current schema.rb file. If a migration can't be rolled back, rake db:reset may not help you. To find out more about dumping the schema see Schema Dumping and You section. Rails Docs

If the trick doesn't help, drop the database, then re-create it again, migrate data, and if you have seeds, sow the database:

rake db:drop db:create db:migrate db:seed

or in short way (since 3.2):

rake db:migrate:reset db:seed

Since db:migrate:reset implies drop, create and migrate the db. Because the default environment for rake is development, in case if you see the exception in spec tests, you should re-create db for the test environment as follows:

RAILS_ENV=test rake db:drop db:create db:migrate

or with just loading the migrated scheme:

RAILS_ENV=test rake db:drop db:create db:schema:load

In most cases the test database is being sowed during the test procedures, so db:seed task action isn't required to be passed. Otherwise, you shall to prepare the database (this is deprecated in Rails 4):

rake db:test:prepare

and then (if it is actually required):

RAILS_ENV=test rake db:seed

On newer versions of Rails the error ActiveRecord::NoEnvironmentInSchemaError may be risen, so just prepend the tasks with a database environment set task: db:environment:set:

RAILS_ENV=test rake db:environment:set db:drop db:create db:migrate

for me - RAILS_ENV=test rake db:drop db:create db:schema:load works, thanks for answer. Great — AMIC MING, Apr 27 '16 at 18:22
Thank you Mr. you just save my life. I try to drop, create, migrate, setup but not any of those commands works until I try you command `rake db:reset`. The magic happens man! — Travis Le, Jan 29 '19 at 07:49
I accidentally dropped my PG tables and got this error when attempting to migrate. changing/altering migration and schema files have always been an anxiety for me. For me, I deleted a table and for some reason, PG still recognized it in the schema. I ran `rails db:reset` just to drop and create everything locally and it realigned everything. — Kelvin, Nov 29 '21 at 18:58
For me, I am using Rails version 7, and ```rails db:reset``` worked! — ejoka, Mar 14 '23 at 15:12

score 23 · Answer 2 · edited Dec 02 '19 at 14:21

23

I encountered this error, and upon my research, found out that one of the reasons for PG undefinedtable error relation users does not exist error is:

This error is a migration error. You may have created new model with some database attributes. After creating model you have to migrate attributes to your rails app schema.

If you are using local machine, for development, you can use command

rake db:migrate

If you're using heroku

heroku run rake db:migrate

edited Dec 02 '19 at 14:21

ARK

772
7
21

answered Aug 15 '16 at 00:39

supritshah1289

839
8
15

4

Excellent! For rails 5 you must use 'heroku run rails db:migrate' – Raphael Onofre Feb 01 '17 at 18:35
1

This worked for me. I needed to run `rake db:migrate`. Thanks. – Promise Preston Mar 12 '20 at 23:57
I also faced this when I made a change to my Table name from `article` to `blog_article` instead of changing the model class name to `BlogArticle` I still left the name of the model class as `Article` – Promise Preston Feb 20 '21 at 17:26

score 16 · Answer 3 · answered Oct 11 '13 at 09:59

Your test database is not ready for rspec.

Prepare your test database for rspec to fix this error

RAILS_ENV=test rake test:prepare

It will drop, create and add migrations to your test database

In case rake task is aborted with message like 'PG::Error: ERROR: database "[your_db_test]" is being accessed by other users' execute this one

RAILS_ENV=test rake db:migrate

score 14 · Answer 4 · answered Apr 01 '14 at 22:25

I was getting this error as well when running rspec:

 Failure/Error: it { expect(subject.priority_list).to eq [nil] * 9 }
 ActiveRecord::StatementInvalid:
   PG::UndefinedTable: ERROR:  relation "priorities" does not exist
   LINE 5:              WHERE a.attrelid = '"priorities"'::regclass
 ...

It was resolved for me after I ran

rake db:test:prepare
rake db:test:load

score 13 · Answer 5 · answered Jan 21 '15 at 01:49

I had a similar error. The root of my error was that I had a reference to a Rails model in my factories.rb file. So it caused a load error issue. The fix was to wrap the reference in a block or {} so that it delays running it.

Here was the BROKEN code:

FactoryGirl.define do
  factory :user do
    guid User.new.send(:new_token)
  end
end

And it was erroring because User was not defined when factories.rb was being loaded. I wrapped the User.new call in a block and it solved the issue:

Fixed code:

FactoryGirl.define do
  factory :user do
    guid { User.new.send(:new_token) }
  end
end

Note: probably not best practice to need to call your model like this, but it was a solution to DRY up my code.

Can anyone please explain why it's "probably not best practice to need to call your model"? I agree with the DRY benefits...what are the drawbacks? Thanks! — theUtherSide, Feb 18 '16 at 18:36

Arcolye · Answer 6 · 2015-11-05T03:28:31.093

12

This is often caused by a bug in ActiveAdmin. Here's how to get around the bug:

If you're using ActiveAdmin, whichever table PG says doesn't exist, comment out the contents of that ActiveAdmin rb file.

For example, for this case PGError: ERROR: relation "users" does not exist, comment out the entire contents of app/admin/users.rb, then uncomment after you've done your migrations.

edited Nov 05 '15 at 03:28

answered Dec 02 '13 at 07:02

Arcolye

6,968
4
34
28

you can also comment all admin areas at routes: # ActiveAdmin.routes(self) – super_p Aug 30 '21 at 11:15

igmnlt · Answer 7 · 2016-10-20T23:19:55.057

That issue for me was being caused by Factory Girl rails. I would recommend for those using it to rename the specs/factories folder to specs/temp and attempting

RAILS_ENV=your_environment bundle exec rake db:migrate --trace

If it passes, then you just found what was causing it. A quick dig through the Factory Girl Rails gem github repo helped me identify the issue.

The factories were failing because I was trying to instantiate a Model that didn't exist upon running! Code sample below:

FactoryGirl.define do
  factory :billing_product, class: 'Billing::Product' do
    name            Faker::Cat.name
    product_type    'fuel'
    active          true
    payment_options [Billing::PaymentOption.new(term: 1, payment_term: 1)]
  end
end

Encapsulating the Array in a block (adding {}) did the fix for me. Note that payment_options can take more than one payment option in the example...

payment_options {[Billing::PaymentOption.new(term: 1, payment_term: 1)]}

Refer to the Dynamic Attributes part of the Factory Girl Rails docs for more info.

Don't forget to rename your factories folder back!

score 4 · Answer 8 · answered Sep 06 '18 at 08:38

I was facing the same problem and then I discovered the following solution.

Make sure You have entered all of the following credentials in the database.yml file and they are correct:

development:
 adapter: postgresql
 encoding: unicode
 database: my_database
 host: localhost
 port: 5432
 pool: 5
 username: postgres
 password: xyz

test:
 adapter: postgresql
 encoding: unicode
 database: my_test_database
 host: localhost
 port: 5432
 pool: 5
 username: postgres
 password: xyz

score 3 · Answer 9 · answered Jan 20 '16 at 10:53

3

I had this problem after I deleted the users table. solutions was changing

change_table(:users)

to

create_table(:users)

answered Jan 20 '16 at 10:53

KnuturO

1,565
15
19

score 3 · Answer 10 · answered May 24 '17 at 07:20

::Migration[5.0] was missing in migrations. instead of throwing syntax error it throws

PG::UndefinedTable: ERROR: relation roles does not exists

after wasting hours I finally figured out that migration is missing ::Migration[5.0].

Erroneous Migration:

class CreateRoles < ActiveRecord # <---- Pay attention
  def change
    create_table :roles do |t|
      t.string :name
      t.integer :code, limit: 2
      t.boolean :is_active, default: true

      t.timestamps
    end
  end
end

Fixed and Correct Migration

class CreateRoles < ActiveRecord::Migration[5.0]
  def change
    create_table :roles do |t|
      t.string :name
      t.integer :code, limit: 2
      t.boolean :is_active, default: true

      t.timestamps
    end
  end
end

This could be a bug with rails and might help someone, instead of struggling and wondering.

yes!!! I don't think it's a bug though it is a common issue for rails 5. I think rails purposely did this [Directly inheriting from ActiveRecord.](https://github.com/RolifyCommunity/rolify/issues/444) — Community, Feb 14 '19 at 09:51

score 2 · Answer 11 · answered Sep 30 '13 at 15:37

2

The most probable cause is that your rake is using different environment from database.yml than your webserver.

answered Sep 30 '13 at 15:37

techvineet

5,041
2
30
28

Hi, how is this something I can be sure of? is there command I can put in the terminal? – Naomi K Sep 30 '13 at 16:02
and how you are starting your webserver? – techvineet Sep 30 '13 at 16:27
I am just running locally right now using rails s in the terminal – Naomi K Sep 30 '13 at 19:53

score 2 · Answer 12 · answered Oct 31 '14 at 21:09

I was getting a similar error while trying to run tests using rspec.

I followed Малъ Скрылевъ's steps but still ended up short. The final step I needed to do was load my schema into my test database using:

RAILS_ENV=test rake db:schema:load

After that the problem went away and I could move on to the next bug. Hopefully that gives you some insight.

score 2 · Answer 13 · answered Mar 22 '16 at 03:42

2

Remove the Admin folder and run rake again.

answered Mar 22 '16 at 03:42

Roberto Capelo

79
2
4

MCB · Answer 14 · 2016-05-13T21:10:10.177

(I know this is old, but for future googlers)

Are you using devise? I know specifically omniauthable is a problem, but maybe others as well. It doesn't have to be devise though. Generically the solution is to comment out the offending model, class, whatever, and un-comment any sections the errors ask for.

For me, what was happening is that devise is reading the User model to see what you have as arguments for devise (the class method i.e. devise :database_authenticatable, :registerable #etc)

But, it will read the whole file and if this isn't a new project it might get tripped up by other class methods relying on other things (in my case it was the friendly_id gem, and then an alias_method

The answer was to comment out the User model except for the devise lines(s) * and rake db:schema:load should run fine.

otherwise I got this error:

ArgumentError: Mapping omniauth_callbacks on a resource that is not omniauthable Please add devise :omniauthable to the User model

score 2 · Answer 15 · answered Feb 02 '19 at 12:50

2

If you get this error while migrating, make sure your model name is plural

e,g.

add_column :images, :url, :string

answered Feb 02 '19 at 12:50

stevec

41,291
27
223
311

score 1 · Answer 16 · edited May 23 '17 at 11:54

I had this problem and it turned out to be caused by Grape API. I noticed in the stack trace that the routes file was being read during the migration.

In routes.rb the Grape api is mounted

mount API::Base => '/'

And in the API were references to the missing model. So, thanks to this answer I put it in a block that detects whether its being run by the server or during the migration.

unless ( File.basename($0) == "rake" && ARGV.include?("db:migrate") )
    mount API::Base => '/'
end

And it worked.

score 1 · Answer 17 · answered Feb 16 '17 at 01:24

I was having the following error and doing a lookup into all my application code for type_zones I was unable to find it. I also looked at the db and it was updated.

Turns out it was a file under fixtures /test/fixtures/type_zones.yml that was causing the trouble.

ERROR["test_should_get_new", UsersControllerTest, 0.47265757399145514]
test_should_get_new#UsersControllerTest (0.47s)
ActiveRecord::StatementInvalid:  ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "type_zones" does not exist
    LINE 1: DELETE FROM "type_zones"
                        ^
    : DELETE FROM "type_zones"

score 1 · Answer 18 · edited Dec 02 '19 at 16:56

1

For anyone who is still having this problem, in my case, it was my factory in FactoryGirl that was triggering this error.

I was trying to add reference via '.new' or '.create'.

edited Dec 02 '19 at 16:56

Tsahi Levent-Levi

2,351
2
18
26

answered Jun 21 '17 at 21:00

Mateus Luiz

756
7
20

score 0 · Answer 19 · edited May 23 '17 at 12:34

In my case, I had to comment out 2 ActiveAdmin files. Here were my steps:

Initial error/stacktrace (note we're using Solr on this project): ⇒ rkdbm java version "1.7.0_25" Java(TM) SE Runtime Environment (build 1.7.0_25-b15) Java HotSpot(TM) 64-Bit Server VM (build 23.25-b01, mixed mode) => Solr is already running rake aborted! PG::UndefinedTable: ERROR: relation "discussions" does not exist LINE 5: WHERE a.attrelid = '"discussions"'::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 = '"discussions"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum /Users/matthewcampbell/Sites/code/stack-builders/AchieveX/app/admin/users.rb:25:in block in <top (required)>' /Users/matthewcampbell/Sites/code/stack-builders/AchieveX/app/admin/users.rb:1:in' /Users/matthewcampbell/Sites/code/stack-builders/AchieveX/config/routes.rb:3:in block in <top (required)>' /Users/matthewcampbell/Sites/code/stack-builders/AchieveX/config/routes.rb:1:in' /Users/matthewcampbell/Sites/code/stack-builders/AchieveX/config/environment.rb:5:in `' Tasks: TOP => db:migrate => environment (See full trace by running task with --trace)

I commented out the app/admin/discussions.rb file per Arcolye's answer above and tried to migrate my database again.

Same error.

I looked at the stacktrace a bit more closely, and noticed that in fact app/admin/users.rb:25 was throwing the exception - and sure enough, that file has a dependency on my discussions table (via executing Discussion.all).

Finally, commenting out the contents of users.rb allowed me to finally migrate my database successfully.

FYI: there's a discussion here in ActiveAdmin about whether that gem should load the database when required.

score 0 · Answer 20 · answered Feb 25 '16 at 00:05

0

So having the same problem just now. Remember to have only one model in each migration. That solved it for me.

I came across the answer here.

answered Feb 25 '16 at 00:05

Rex

238
2
13

score 0 · Answer 21 · edited Jul 20 '18 at 01:05

0

I was catching the Error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "users" does not exist
LINE 8:                WHERE a.attrelid = '"users"'::regclass

It turned out to be a super easy fix. I had copied files over from an older version of the project and forgot to nest them inside of a "migrate" folder. When I did that it solved the problem for me.

edited Jul 20 '18 at 01:05

Stephen Rauch

47,830
31
106
135

answered Jul 20 '18 at 00:46

John Chriest

1
2

milaziggy · Answer 22 · 2019-12-18T21:09:58.923

it usually can happen when you use wrong type of association between models, check for dependency destroy and has_many associations, for example:

wrong way that can cause this trouble:

article.rb

 has_many :subcategories, through: :categories, dependent: :destroy

subcategory.rb

has_and_belongs_to_many :articles

right way:

article.rb

 has_many :subcategories, through: :categories, dependent: :destroy

subcategory.rb

declare association with categories here not articles (belongs_to / has_many_and_belongs_to)

score 0 · Answer 23 · answered Nov 11 '20 at 16:38

0

Forgetting the migration file in your commit can cause this issue. When pushed on heroku for e.g. rails db:migrate will obviously not work. Be sure that the migration file defining the undefined table have been committed.

answered Nov 11 '20 at 16:38

Paul Volet

1
1
1

score 0 · Answer 24 · answered Dec 16 '21 at 16:50

My case was also related to FactoryGirl/FactoryBot and I just had to change the definition. Replacing the constant by a string.

My env:

Rails 5.2.6
factory_bot 4.8.2

From this:

FactoryBot.define do
  factory :user, class: Admin::User do
    ...
  end
end

To this:

FactoryBot.define do
  factory :user, class: 'Admin::User' do
    ...
  end
end

Which is something recommended by Getting Started - Specifying the class explicitly.

score 0 · Answer 25 · answered Jan 17 '23 at 14:11

0

I was trying to run test.rake script and encountered the same issue. Spent a lot of time trying to understand what's going on. Eventually, it get fixed after I renamed the file.

answered Jan 17 '23 at 14:11

Ahmed Gasanov

139
1
6

PG undefinedtable error relation users does not exist

25 Answers25

Linked