How to use COPY command with jsonb datatype ?

Hi

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}

(1 row)

Is it possible to do that ? how ?

Thanks in advance

Best Regards
[cid:image001(dot)png(at)01D36392(dot)6B2E0730]
Didier ROS
DSP/CSP IT-DMA/Solutions Groupe EDF/Expertise Applicative
Expertise SGBD
32 Avenue Pablo Picasso
92000 NANTERRE
De : pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
Envoyé : mercredi 15 novembre 2017 09:49
À : queraltr(at)gmail(dot)com
Cc : pgsql-sql(at)postgresql(dot)org
Objet : Re: [SQL] Line number returned in Postgres Raise Exception sentence

Hi

2017-11-14 11:17 GMT+01:00 queralt <queraltr(at)gmail(dot)com<mailto:queraltr(at)gmail(dot)com>>:
We are migrating a system from Postgres 9.2 to Postgres 9.6.

The problem is that in migrating the Stored Procedures, where we use the
RAISE EXCEPTION sentence, a new line is added in the message returned when
there is a RAISE EXCEPTION. In our system the existence of this second part
of the message is a big problem.

"ERROR: Message of Error
SQL state: P0001
Context: function PL/pgSQL xxx() in line 4 of RAISE"

We are executing stored procedures from JDBC and obtain the message with
getMessage and from PgAdmin III or IV.

We would like to know if there is the possibility to configure Postgres 9.6
in order this part of the message does not appear. Or maybe if it is
possible to set some attribute during the database connection.

We have read about a solution changing in the file postgres.config the
attribute log_error_verbosity to terse. We did but it has no effect in the
result.

This filtering is client side task

https://www.postgresql.org/docs/current/static/libpq-control.html

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0426f349effb6bde2061f3398a71db7180c97dd9

So this should be changed by some JDBC setup if it is possible

Regards

Pavel

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-sql-f2142323.html

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org<mailto:pgsql-sql(at)postgresql(dot)org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les informations qui y figurent sont strictement confidentielles. Toute utilisation de ce Message non conforme à sa destination, toute diffusion ou toute publication totale ou partielle, est interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de votre système, ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support que ce soit. Nous vous remercions également d'en avertir immédiatement l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie électronique arrivent en temps utile, sont sécurisées ou dénuées de toute erreur ou virus.
____________________________________________________

This message and any attachments (the 'Message') are intended solely for the addressees. The information contained in this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or virus-free.