Clojurians Log v2
Clojure programming
Channels
Apps
sql
sheluchin 2023-05-22T19:03:38.212399Z
I have a Postgres JSON field that's giving me some trouble. I'm following the steps explained in https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md#working-with-json-and-jsonb. The issue is that some of the content I'm storing in this field contains the string \u0000. When I go to retrieve this data, I get an error like:
Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2676).
ERROR: unsupported Unicode escape sequence
Detail: \u0000 cannot be converted to text.
Where: JSON data, line 1: ..."[type s & [fill]]"],"varargs-min-arity":2,"doc":...\u0000 with \\u0000 before inserting it, but perhaps there's a more comprehensive solution that covers this?
slipset 2023-05-23T07:04:26.273879Z
I stumbled upon this when migrating from mongo to postgres. Luckily the strings which had \u0000 were remnants of pen testing, so I could just delete them.
❤️ 1
2023-05-22T21:16:52.993309Z
You might enjoy this essay: https://www.commandprompt.com/blog/null-characters-workarounds-arent-good-enough/
AFAIK this is a Postgres gotcha; you either need to replace it, or use something like a bytea column
sheluchin 2023-05-22T21:40:32.297559Z
@stopachka great read and a clear explanation. Thanks! Now I'm just wondering if anyone here has a snippet to share for how they deal with it. Seems like it should be a common enough thing that I wouldn't need to reinvent the wheel 🤞