How to import Postgres data that has JSON columns?

Question

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.

klarson · Answer

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

larryq · Answer

Thanks Kurt, much appreciated.

How to import Postgres data that has JSON columns?

Relevant content