Write JSON field to postgres Database

stuckeyam

I'm working on a project to duplicate some of our production Postgres tables in a new BI environment so that reporting isn't interfering with our app. I thought this would be a pretty simple task, but I've run into some issues with JSON(B) fields. I don't like that there are JSON(B) fields in the production database, but that's not my call to make.

That being said, I need to be able to pull the tables from production and load them to 1:1 with the exact same structure in the new environment. The table structure is similar to this:

create table table1
(
   id integer not null,
   field1 text not null,
   field2 jsonb,
   field3 text
);

Alteryx is reading the JSON field2 input as string. How do I force the load into field2? Right now I'm getting: ERROR: invalid input syntax for type json;

Thanks for the help in advance!

stuckeyam

So, this is an annoying work around, but here's what I came up with. Originally we wanted Alteryx to create a temp table in the Pre SQL statement, but we I got an error that it couldn't create temp tables. Instead I created a new schema called temp, where a duplicate table is created, but with text fields instead of JSONB.

create table temp.table1
(
   id integer not null,
   field1 text not null,
   field2 text,
   field3 text
);

Then we truncate and load to that temp table, using the Post SQL Statement field to load to the actual table.

INSERT INTO public.table1
(
   id,
   field1,
   field2,
   field3
)
SELECT id,
  field1,
  field2::jsonb,
  field3
FROM temp.table1
;

 All of this was very frustrating because if you have a long JSONB object, it could get chopped at 255 characters without you realizing. Here's how to change that setting if needed.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-tool-json-field-truncated-how-to-solve/td-p/109514