PG::Error: ERROR: zero-length delimited identifier at or near """"

Question

I just did a dump of my staging databse, than did pg_restore on my local postgres databse to get them to line up. All the data went through.

Now when I try to query in the Rails console, I get errors.

Artist.count
   (0.5ms)  SELECT COUNT(*) FROM "artists" 
=> 668

**

Artist.last
  Artist Load (1.4ms)  SELECT "artists".* FROM "artists" ORDER BY "artists"."" DESC LIMIT 1
PG::Error: ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...T  "artists".* FROM "artists"  ORDER BY "artists"."" DESC LI...
                                                             ^
: SELECT  "artists".* FROM "artists"  ORDER BY "artists"."" DESC LIMIT 1
ActiveRecord::StatementInvalid: PG::Error: ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...T  "artists".* FROM "artists"  ORDER BY "artists"."" DESC LI...
                                                             ^
: SELECT  "artists".* FROM "artists"  ORDER BY "artists"."" DESC LIMIT 1
from /Users/ryanrebo/.rvm/gems/ruby-2.0.0-p481/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec'

I get the same error on all other Models. I also cannot use find(id).

Any ideas?

Answer 1

The error occurs because "artists" table does not have a primary key which is used for the order by:

Artist Load (1.4ms) SELECT "artists".* FROM "artists" ORDER BY "artists"."" DESC LIMIT 1

Add a primary key to fix by adding the following code block to a rails migration or run the alter table command in the database:

execute <<-SQL ALTER TABLE assets ADD PRIMARY KEY (id); SQL

where id is the primary key of "artists".

Answer 2

I fixed this by dropping and then re-creating the local database, re-running the migrations, and passing --data-only as an option on pg_restore. Thanks for the help y'all. That was a strange error.