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

Rascal

You'll need to show the actual SQL query that Rails generated; you can get it from PostgreSQL's log files or from the Rails logs. – Anthurium

This: WHERE "regions"."" = $1 is invalid SQL and that is not Postgres' fault - it's Rails which creates the invalid SQL. My guess is that Rails generates the where condition based on the columns that make up the primary key. As there are not such columns, it generated invalid SQL (essentially using an empty column name) – Interlocutory

Yes You're right. self.primary_key = :province_id solves the problem but it's not the cleanest.... – Rascal

I'm experiencing the same thing but my join table does have a primary key so I don't think that is affecting the outcome – Stan

your model might have custom primary key. add self.primary_key = :custom_key in model file. – Safeconduct

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

Depolymerize

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.

Britannic

with a has_many [...] through definition this works. Did not find reference ot this in the rails guides... – Rosaliarosalie

Before resorting to this, please try my answer below. I feel not running callbacks only circumvents the problem. – Fishworm

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.

Fishworm

This fixed my problem. I had to add_column :table_name, :id, :primary_key on the joint table to be able to use destroy_all. More details here: github.com/rails/rails/issues/25347 – Variegation

Thanks, I feel stupid. I initially created a table for habtm and decided to go with has many through in the process thus forgetting to add an id – Goliard

Answer 4

Add to model file:

self.primary_key = :id

Shoran

Answer 5

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

Penny

Answer 6

Had this one randomly after restoring from a db backup where the data had duplicate primary keys (ids) in sidekiq_jobs and thus when creating the unique pkey index, it broke with this error. Presumably, it was reading the column name from the pkey index.

Fortunately it was an easy solve - just kept deleting the IDs that were duplicated until it was able to create the index.

*** => delete from sidekiq_jobs where id in (1,2,3,4);

*** => ALTER TABLE ONLY public.sidekiq_jobs
    ADD CONSTRAINT sidekiq_jobs_pkey PRIMARY KEY (id);
ALTER TABLE

Instructive