From: | Shashank Dutt Jha <shashank(dot)dj(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(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-11 06:11:13 |
Message-ID: | CAKkUp9-1hLZLb5Pi+xgYtEQiyUrnYg-gwMVtELSRB8aL-K-w4A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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 | Venkatesan, Sekhar | 2016-02-14 13:13:13 | PostgreSQL: Inserting NULL values adds empty string. |
Previous Message | David G. Johnston | 2016-02-10 16:50:19 | Re: inserting json content from a file into table column |