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
demandé il y a 4 ans359 vues
2 réponses
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
répondu il y a 4 ans
0

Thanks Kurt, much appreciated.

larryq
répondu il y a 4 ans

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions