CODEWITHSUNDEEP
jsonpostgresqlrecursive-query
28Smiles
28Smiles

Reputation: 104

Postgres JSON operation error in Recursive Common Table Expression

Using this Cte:

WITH RECURSIVE "_.catalogIds" AS (
    SELECT "catalog"."name", "catalog"."parent_id", "catalog"."owner_id", "catalog"."image_id", "catalog"."id", "catalog"."created", "catalog"."updated", "catalog"."icon_class" 
    FROM "catalog" 
    WHERE "catalog"."id" = 1
    UNION 
    SELECT "catalog"."name", "catalog"."parent_id", "catalog"."owner_id", "catalog"."image_id", "catalog"."id", "catalog"."created", "catalog"."updated", "catalog"."icon_class" 
    FROM "_.catalogIds"
    INNER JOIN "catalog" ON "catalog"."id" = "_.catalogIds".parent_id
)

On this Table:

CREATE TABLE catalog (
  id         BIGSERIAL,
  created    timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated    timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  name       JSON      NOT NULL,
  parent_id  BIGINT,
  owner_id   BIGINT    NOT NULL,
  image_id   BIGINT,
  icon_class VARCHAR(255)       DEFAULT 'fa fa-book',

  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES catalog (id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES "user" (id) ON DELETE CASCADE,
  FOREIGN KEY (image_id) REFERENCES media_file (id) ON DELETE SET NULL
);

Results in an error of postgres saying, it can not find an equals operator for type json. Even more wierd, it says the error is at position 42, which is in the select part of the statement. Also if I shorten the query the error moves backwards, as it stays at a specific place of the query. I wrote a lot of recursive querys but this one seems very wierd. It looks like postgres bug to me, but i need confirment and an workaround/working example.

Upvotes: 1

Views: 34

Answers (1)

klin
klin

Reputation: 121564

UNION eliminates duplicates, so the query compares resulting rows of component queries. Unfortunately, the type JSON has no equality operator, so the rows containg a column of the type cannot be compared.

You can cast the column to JSONB in both queries:

    SELECT "catalog"."name"::jsonb, ...

or use UNION ALL if you do not care of duplicates.

Upvotes: 1

Related Questions