From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Shashank Dutt Jha <shashank(dot)dj(at)gmail(dot)com>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: inserting json content from a file into table column |
Date: | 2016-02-10 16:50:19 |
Message-ID: | CAKFQuwYMnqeJtF7Abvk3ckK3f9bp7kxnT_TxHLX=9wh4XVUGPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, Feb 10, 2016 at 9:34 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 02/10/2016 08:01 AM, Shashank Dutt Jha wrote:
>
>> psql. PostgreSQL v9.5
>>
>
> A quick and dirty method:
>
> aklaver(at)test=> create table json_test(id integer, json_fld jsonb);
> CREATE TABLE
>
> aklaver(at)test=> \e sample.json
>
> \e opens an editor. Inside editor add INSERT statement to file:
>
> INSERT INTO json_test VALUES(1, '
> {
> "glossary": {
> "title": "example glossary",
> "GlossDiv": {
> "title": "S",
> "GlossList": {
> "GlossEntry": {
> "ID": "SGML",
> "SortAs": "SGML",
> "GlossTerm": "Standard Generalized Markup Language",
> "Acronym": "SGML",
> "Abbrev": "ISO 8879:1986",
> "GlossDef": {
> "para": "A meta-markup language, used to create
> markup languages such as DocBook.",
> "GlossSeeAlso": ["GML", "XML"]
> },
> "GlossSee": "markup"
> }
> }
> }
> }
> }');
>
>
I'd suggest dollar-quoting if going this route - the possibility of the
json containing single quotes is significantly large to warrant it.
[...] VALUES(1,$$json$
{...}
$json$);
In Linux we'd do:
(backticks used below)
\set json_var `cat json_file.txt`
INSERT INTO tbl (json_col) VALUES (:'json_var');
Not sure about Windows though.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Shashank Dutt Jha | 2016-02-11 06:11:13 | Re: inserting json content from a file into table column |
Previous Message | Adrian Klaver | 2016-02-10 16:34:46 | Re: inserting json content from a file into table column |