Re: How to use COPY command with jsonb datatype ?

On Wed, Nov 22, 2017 at 4:04 AM, ROS Didier <didier(dot)ros(at)edf(dot)fr> wrote:

>
>
> I have a .csv file containing data like this :
>
>
>
> year date shares
> trades dollars
>
> 2010 01/04/2010 1,425,504,460 4,628,115
> $38,495,460,645
>
> 2010 01/05/2010 1,754,011,750 5,394,016
> $43,932,043,406
>
>
>
> I would like to insert the content of the.csv file into this table, with
> the COPY command :
>
>
>
> create table factbookjsonb
>
> (
>
> year int,
>
> data jsonb
>
> );
>
>
>
> NB : furthermore I want to replace ‘,’ (comma) by empty space in the .csv
> file.
>
> For instance the date in the table could be :
>
>
>
> factbook=> select * from factbookjsonb ;
>
>
>
> year | data
>
> ------+-----------------------------------------------------
> ------------------------------------
>
> 2017 | {"date": "10/31/2017", "shares": 1206770409, "trades": 4485293,
> "dollars": 48582276227}
>
>
>

​You can't do all that data massaging in a single step with COPY, but you
have many options for that ETL process. Some examples:
* http://pgloader.io/ is a good tool for data manipulation and fast
loading. Whether that works with JSONB or not, I cannot say, but it should
be straightforward to find out.
* COPY your data into a staging table, and load your production table with
an INSERT ... AS SELECT ... command, where the SELECT is doing conversion
to JSONB.
* External tools and languages, eg. Python, or using Python's odo package,
http://odo.pydata.org/en/latest/index.html​