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

Casting sqlite jsonb to postgres jsonb error #1607

Open
irismessage opened this issue Aug 29, 2024 · 1 comment
Open

Casting sqlite jsonb to postgres jsonb error #1607

irismessage opened this issue Aug 29, 2024 · 1 comment

Comments

@irismessage
Copy link

Pgloader version:

pgloader version "3.6.7~devel"
compiled with SBCL 2.2.9.debian

I'm trying to load an sqlite column with the sqlite jsonb type (https://sqlite.org/draft/jsonb.html) into a postgres column with the type jsonb. However the types have incompatible binary formats, when I try to run it I get this:

2024-08-29T21:28:28.314987Z ERROR Database error 22P02: invalid input syntax for type json
DETAIL: Token "#" is invalid.
CONTEXT: JSON data, line 1: #...
COPY profile, line 1, column markings: "#(91 34 76 105 122 97 114 100 70 114 105 108 108 115 72 111 111 100 64 35 70 70
  53 55 56 65 70 70 ..."

In sqlite the data will render like this, you can see that what it's trying to load in corresponds to ascii:

["LizardFrillsHood@#FF578AFF,#FF578AFF","LizardHornsCurled@#FF578AFF","LizardSnoutSharp@#FF578AFF","LizardChestUnderbelly@#FFFFF5FF"]

I'm running it with:

include no drop, create no tables,
create no indexes, no foreign keys,
truncate,

I have tried with and without casting directives and I can't see a corresponding transformation function

@irismessage
Copy link
Author

irismessage commented Sep 2, 2024

I had success doing this on the source sqlite database:
UPDATE profile SET markings = CAST(markings AS TEXT);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant