Announcement

Collapse
No announcement yet.

Error editing data in JSON format (grid), copy as update sql

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error editing data in JSON format (grid), copy as update sql

    Hi, I have table defined as:

    CREATE TABLE "files" (
    "id" int8 NOT NULL DEFAULT nextval('files_id_seq'::regclass),
    "data" json,
    "lang" varchar(3) COLLATE "pg_catalog"."default" DEFAULT 'A'::character varying,
    "lastmodifieddate" int8 DEFAULT transaction_timestamp_linux_int(),
    "state" int2 DEFAULT 1
    );

    there is no primary index, but unique index id + lang.

    Now when I try to edit in grid or "Copy as update statement" I get following error:

    ERROR: operator does not exist: json = unknown
    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

    Problem is, Navicat not knowing primary index generates

    UPDATE "files" SET "id" = 32915, "data" = '{"no":101,"name":"2016","filename":"TAA8676A6"} ', "lang" = 'A', "lastmodifieddate" = 14796262832059, "state" = 1 WHERE "id" = 32915 AND "data" = '{"no":101,"name":"2016","filename":"TAA8676A6"} ' AND "lang" = 'A' AND "lastmodifieddate" = 14796262832059 AND "state" = 1;

    simple type cast in where clausule when json (jsonb suppose too) data type detected would solve the problem:

    UPDATE "files" SET "id" = 32915, "data" = '{"no":101,"name":"2016","filename":"TAA8676A6"} ', "lang" = 'A', "lastmodifieddate" = 14796262832059, "state" = 1 WHERE "id" = 32915 AND "data" ::text = '{"no":101,"name":"2016","filename":"TAA8676A6"} ' AND "lang" = 'A' AND "lastmodifieddate" = 14796262832059 AND "state" = 1;

  • #2
    Hi mapes,

    ​​​​​​​We are able to identify the problem and our development team is trying to fix it. We will contact you after the bug is fixed.

    Comment


    • #3
      Hi mapes,

      We are glad to tell you that Navicat version 12.0.19 has been released and fixed your reported problem. Please upgrade to this latest version - submit your registered email address on the Customer Center at:

      https://customer.navicat.com

      Or, you can simply choose Help -> Check for Updates from the menu bar.

      Comment


      • mapes
        mapes commented
        Editing a comment
        Hi,
        Thank you.
        I can confirm v 12.0.19 works well.

        But now I have found similar problem: when sorting by column with json data (click on header) following error occures:
        could not identify an ordering operator for type json.

        Again can be solved by SELECT * FROM "files" order by data ::varchar.

        So If you can include this in next version....

    • #4
      Hi mapes,

      We are able to identify the problem and our development team is trying to fix it. We will contact you after the bug is fixed.

      Comment


      • #5
        Hi mapes,

        We are glad to tell you that Navicat version 12.0.20 has been released and fixed your reported problem. Please upgrade to this latest version - submit your registered email address on the Customer Center at:

        https://customer.navicat.com

        Or, you can simply choose Help -> Check for Updates from the menu bar.

        Comment

        Working...
        X