Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error editing row with JSON column #769

Closed
mets11rap opened this issue Oct 10, 2019 · 12 comments
Labels
Milestone

Comments

@mets11rap
Copy link

@mets11rap mets11rap commented Oct 10, 2019

Steps to reproduce this issue

  1. Step 1: Create a table where a column has a JSON type
  2. Step 2: Insert a row with the JSON column filled out with valid JSON
  3. Step 3: Edit any column of that row
  4. Error:
    Error

Current behavior

It's giving me an error no matter what I do or edit. It doesn't have to be the JSON column being edited.

Expected behavior

The row updating correctly.

Possible solution

I have no clue
EDIT: This can be fixed with a primary key column, however, this should still not error.

Environment

  • HeidiSQL version:
    10.2.0.5712
  • Database system and version:
    PostgreSQL v11.4
  • Operating system:
    Windows 10
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 11, 2019

Problem seems to be the WHERE clause on a JSON column, which is included on UPDATEs if the underlying table has no primary key:

 WHERE  "json-column"='[1,2]'

I suppose there is again some specialty for comparing JSON values in PostgreSQL.

@mets11rap Do you perhaps know how to manually update your row?

@mets11rap

This comment has been minimized.

Copy link
Author

@mets11rap mets11rap commented Oct 11, 2019

Do you perhaps know how to manually update your row?

What do you mean by this?

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 11, 2019

If you know how to update your row by hand, with an UPDATE ... query, in a query tab?

@mets11rap

This comment has been minimized.

Copy link
Author

@mets11rap mets11rap commented Oct 11, 2019

Yes, I do. The problem is resolved for me anyway because I actually forgot to specify the ID column as a PKey, but this may be a problem for others.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 11, 2019

It would be nice if you share that UPDATE here, so I can probably fix this in HeidiSQL for others.

@mets11rap

This comment has been minimized.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 11, 2019

Well that explains how to query inner structure of a json field. I could not yet find a way to circumvent that "Operator does not exist: json = unknown"

@mets11rap

This comment has been minimized.

Copy link
Author

@mets11rap mets11rap commented Oct 11, 2019

Hm, this might be it.
https://www.postgresql.org/docs/10/datatype-json.html
img

Other than that, I don't know how to check for the full JSON value.

You could try looking through this, but that's pretty much it. I can't seem to find anything.
https://www.postgresql.org/docs/9.5/functions-json.html

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 20, 2019

I tried several approaches, which did not work (j is a JSON field in a table):

  • j = '[1,2]'
  • j @> '[1,2]'
  • j @> '[1,2]'::json

The only one which works for me on PG 10.3 is this one:

  • j::text = '[1,2]'

Please do a similar test on your server, and share your results here.

ansgarbecker added a commit that referenced this issue Oct 20, 2019
…auses. Fixes "Operator does not exist" errors
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Oct 24, 2019

@mets11rap please update HeidiSQL to the latest build to get this fix, and please report back if that works for you, like it does for me.

@mets11rap

This comment has been minimized.

Copy link
Author

@mets11rap mets11rap commented Oct 24, 2019

Sorry I didn't get to test your request! I've been recently unavailable. I'll try out your update when I get home today and let you know! Thank you

@mets11rap

This comment has been minimized.

Copy link
Author

@mets11rap mets11rap commented Oct 25, 2019

@ansgarbecker Yeah, it works great! Thanks!

@mets11rap mets11rap closed this Oct 25, 2019
@ansgarbecker ansgarbecker added this to the v10.3 milestone Oct 27, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.