Re: How to use COPY command with jsonb datatype ?

Поиск
Список
Период
Сортировка
От Hector Vass
Тема Re: How to use COPY command with jsonb datatype ?
Дата
Msg-id AM4PR06MB1874A7888C7C4C4F506F83B9BC210@AM4PR06MB1874.eurprd06.prod.outlook.com
обсуждение исходный текст
Ответ на Re: How to use COPY command with jsonb datatype ?  (bricklen <bricklen@gmail.com>)
Список pgsql-sql
Дерево обсуждения

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@gmail.com>
Sent: 22 November 2017 14:21:47
To: ROS Didier
Cc: pgsql-sql-owner@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: How to use COPY command with jsonb datatype ?
 


On Wed, Nov 22, 2017 at 4:04 AM, ROS Didier <didier.ros@edf.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

В списке pgsql-sql по дате отправления: