Postgres Pro
Downloads
Home   >   mailing lists

Re: inserting json content from a file into table column - Mailing list pgsql-sql

From Shashank Dutt Jha
Subject Re: inserting json content from a file into table column
Date
Msg-id CAKkUp9-1hLZLb5Pi+xgYtEQiyUrnYg-gwMVtELSRB8aL-K-w4A@mail.gmail.com
Whole thread Raw
In response to Re: inserting json content from a file into table column  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
I am trying to insert directly into table using pgAdmin tool.

I came across something like this

create temporary table temp_json (values jsonb) on commit drop;
copy temp_json from 'C:\Users\\conceptmaps.json';

insert into jsontest ('food') from ---*
//
sp copy seeme to have copied the contents from conceptmaps.json ( from message displayed)
now how to store that content into column 'food' 

On Wed, Feb 10, 2016 at 10:20 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Feb 10, 2016 at 9:34 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/10/2016 08:01 AM, Shashank Dutt Jha wrote:
psql. PostgreSQL v9.5

A quick and dirty method:

aklaver@test=> create table json_test(id integer, json_fld jsonb);
CREATE TABLE

aklaver@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.


pgsql-sql by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: inserting json content from a file into table column
Next
From: "Venkatesan, Sekhar"
Date:
Subject: PostgreSQL: Inserting NULL values adds empty string.