How to import Postgres data that has JSON columns?

0

Hi everyone,

We're interested in copying data from our Postgres database into a Redshift cluster, however we use a couple of redshift-unsupported column types in a few of our tables.

One is a bigserial (how are the rest of you handling that one?) and the really tough one to swallow is one of our columns is a JSON type.

That JSON column has a lot of important data for us. May I ask how others here are dealing with the challenge of getting that data into redshift, any recommended approaches? Thanks much.

larryq
asked 2 years ago71 views
2 Answers
0

Hi larryq,

I'll refer you to the doc. page that has most of what you need here: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

If the PostgreSQL DB is the true source for this data then I recommend the following:

  1. BIGSERIAL -> BIGINT as they are both INT8. If you source the rows from PostgreSQL you don't need the auto generating value creation of BIGSERIAL in Redshift. Otherwise Redshift has the IDENTITY column attribute to generate values.
  2. JSON -> VARCHAR. Of course you'll have to use Redshift's JSON function to do anything with the JSON data.

Regards,
-Kurt

klarson
answered 2 years ago
0

Thanks Kurt, much appreciated.

larryq
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions