Re: How to use COPY command with jsonb datatype ?

I concur with briklen a number of different ways of doing this depending on your preference, for me postgres has such nice built in features that are quick and easy to use seems rude not to use them... the ability to handle CSV, regular expressions and json_build_object

..even a file_fdw

/**test.csv
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"
**/

create extension file_fdw;
create server pgcsv foreign data wrapper file_fdw;
drop foreign table intest;
create foreign table intest(
year int,
date text,
shares text,
trades text,
dollars text
)server pgcsv
options(filename '/DATA/dev/test.csv', format 'csv', header 'true');

drop table if exists factbookjsonb;
create table factbookjsonb as
select
year,
json_build_object(
'date',date,
'shares',regexp_replace(shares,',','','g')::bigint,
'trades',regexp_replace(trades,',','','g')::bigint,
'dollars',regexp_replace(dollars,'[\$,]','','g')::bigint
) as data
from intest;
select * from factbookjsonb;

________________________________
From: bricklen <bricklen(at)gmail(dot)com>
Sent: 22 November 2017 14:21:47
To: ROS Didier
Cc: pgsql-sql-owner(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
Subject: 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<mailto: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