ERROR: zero-length delimited identifier at or near """" LINE 1: DELETE FROM "regions" WHERE "regions"."" = $1

Question

I'm using Rails 4.1 and Postgresql (with PG gem) as my database. I have a very stand many to many association from companies to provinces with a join table called regions. Now obviously the regions table has no primary key cause I used { :id => false }. But when I try to use depending destroy or just simply calling destroy on the region object it self I get this error:

 ERROR:  zero-length delimited identifier at or near """"
 LINE 1: DELETE FROM "regions" WHERE "regions"."" = $1

I know the problem is caused due to the lack of a primary key for the regions table. And oddly if I add the primary key back to the table destroy works fine and no error. However, if I remove the primary key from the table the error comes back. I know this has something to do with the postgres but I've no idea how to solve this without having to add a primary key column to my regions table.

Here is the actual query

[DEBUG] [AdminUser Load (0.4ms)  SELECT  "admin_users".* FROM "admin_users"  WHERE "admin_users"."id" = 1  ORDER BY "admin_users"."id" ASC LIMIT 1] (pid:29655)
[DEBUG] [Province Load (0.2ms)  SELECT  "provinces".* FROM "provinces"  WHERE "provinces"."id" = $1 LIMIT 1  [["id", 5]]] (pid:29655)
[DEBUG] [ (0.1ms)  BEGIN] (pid:29655)
[DEBUG] [Region Load (0.3ms)  SELECT "regions".* FROM "regions"  WHERE "regions"."province_id" = $1  [["province_id", 5]]] (pid:29655)
[ERROR] [PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"
LINE 1: DELETE FROM "regions" WHERE "regions"."" = $1

Answer 1

You want single quotes not double quotes around empty strings, double quotes make delimited identifiers, and "" isn't a meaningful identifier.

try:

WHERE 'regions'.'' = $1

or at least:

WHERE "regions".'' = $1

Answer 2

Try setting dependent: :delete_all instead.

Example (Not entirely sure how you have your many-to-many relationships setup).

# models/region.rb
...
has_many :provinces_regions, dependent: :delete_all
has_many :provinces, through: :provinces_regions
...

:destroy/:destroy_all will remove associated objects by calling their destroy method, and thus callbacks (:before_destroy, :after_destroy, etc.)

:delete/:delete_all will remove associated objects without calling their destroy method.

Answer 3

Based on Alex Carol's answer, but completely different solution.

If someone did:

# models/region.rb
...
has_many :provinces_regions, dependent: :destroy_all
has_many :provinces, through: :provinces_regions
...

and are getting this issue, then I would guess you probably added id: false like this:

create_table :provinces_regions, id: false do |t|
  t.belongs_to :regions, index: true
  t.belongs_to :provinces, index: true
end

If the above is true, then Rails is trying to use the ids to run callbacks before destroying. So, give it an id:

create_table :provinces_regions do |t|
  t.belongs_to :regions, index: true
  t.belongs_to :provinces, index: true
end

And thus you can continue to use dependent: :destroy_all and use callbacks and other features that require the id.

At this stage, you need to make a new migration to correct it. So, as Hussein refers, you should make the migration:

add_column :provinces_regions, :id, :primary_key

See here for more details.

Answer 4

As none of the answers here worked for me, I thought I'd include my custom fix. Not sure what the translation for your use case is, but I hope the gist of my strategy is clear.

I, too, am using a custom primary key (uuid). In order to delete the record on my join table, I skipped active record for the actual delete call and used SQL instead.

uid = BucketListPhoto.find_by(bucket_list_id: 12, photo_id: 4).uid

deleted_uids = ActiveRecord::Base.connection.execute(
    "DELETE FROM bucket_list_photos WHERE uid='#{uid}' RETURNING uid"
).to_a.map { |record| record['uid'] }

I overwrote BucketListPhoto#destroy to use this.

I tried to overwrite BuckerListPhoto::ActiveRecord::Query#destroy_all as well but wasn't able to successfully pass an array of uids (to delete many with a single query).