[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] inserting json content from a file into table column
From:       Shashank Dutt Jha <shashank.dj () gmail ! com>
Date:       2016-02-11 6:23:13
Message-ID: CAKkUp9-1hLZLb5Pi+xgYtEQiyUrnYg-gwMVtELSRB8aL-K-w4A () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="ltr">I am trying to insert directly into table using pgAdmin \
tool.<div><br></div><div>I came across something like \
this</div><div><br></div><div><div>create temporary table temp_json (values jsonb) on \
commit drop;</div><div>copy temp_json from \
&#39;C:\Users\\conceptmaps.json&#39;;</div><div><br></div><div>insert into jsontest \
(&#39;food&#39;) from ---*</div></div><div>//</div><div>sp copy seeme to have copied \
the contents from conceptmaps.json ( from message displayed)</div><div>now how to \
store that content into column &#39;food&#39;  </div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Wed, Feb 10, 2016 at 10:20 PM, \
David G. Johnston <span dir="ltr">&lt;<a href="mailto:david.g.johnston@gmail.com" \
target="_blank">david.g.johnston@gmail.com</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><div class="h5"><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif">On Wed, Feb 10, 2016 at 9:34 AM, Adrian Klaver \
</span><span dir="ltr" style="font-family:arial,sans-serif">&lt;<a \
href="mailto:adrian.klaver@aklaver.com" \
target="_blank">adrian.klaver@aklaver.com</a>&gt;</span><span \
style="font-family:arial,sans-serif"> wrote:</span><br></div></div></div><div \
class="gmail_extra"><div class="gmail_quote"><div><div class="h5"><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">On 02/10/2016 08:01 AM, Shashank Dutt Jha wrote:<br> \
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> psql. PostgreSQL v9.5<br>
</blockquote>
<br>
A quick and dirty method:<br>
<br>
aklaver@test=&gt; create table json_test(id integer, json_fld jsonb);<br>
CREATE TABLE<br>
<br>
aklaver@test=&gt; \e sample.json<br>
<br>
\e opens an editor. Inside editor add INSERT statement to file:<br>
<br>
  INSERT INTO json_test VALUES(1, &#39;<br>
{<br>
      &quot;glossary&quot;: {<br>
            &quot;title&quot;: &quot;example glossary&quot;,<br>
            &quot;GlossDiv&quot;: {<br>
                  &quot;title&quot;: &quot;S&quot;,<br>
                  &quot;GlossList&quot;: {<br>
                        &quot;GlossEntry&quot;: {<br>
                              &quot;ID&quot;: &quot;SGML&quot;,<br>
                              &quot;SortAs&quot;: &quot;SGML&quot;,<br>
                              &quot;GlossTerm&quot;: &quot;Standard Generalized \
Markup Language&quot;,<br>  &quot;Acronym&quot;: &quot;SGML&quot;,<br>
                              &quot;Abbrev&quot;: &quot;ISO 8879:1986&quot;,<br>
                              &quot;GlossDef&quot;: {<br>
                                    &quot;para&quot;: &quot;A meta-markup language, \
                used to create markup languages such as DocBook.&quot;,<br>
                                    &quot;GlossSeeAlso&quot;: [&quot;GML&quot;, \
&quot;XML&quot;]<br>  },<br>
                              &quot;GlossSee&quot;: &quot;markup&quot;<br>
                        }<br>
                  }<br>
            }<br>
      }<br>
}&#39;);<br><div><div><br></div></div></blockquote><div><br></div></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">​I&#39;d suggest \
dollar-quoting if going this route - the possibility of the json containing single \
quotes is significantly large to warrant it.</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">[...] \
VALUES(1,$$json$</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">{...}</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">$json$);</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">In Linux we&#39;d \
do:</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">(backticks used \
below)</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">\set json_var `cat \
json_file.txt`</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">INSERT INTO tbl \
(json_col) VALUES (:&#39;json_var&#39;);</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">​Not sure \
about Windows though​.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">David \
J.</div><br></div></div></div></div> </blockquote></div><br></div>



[prev in list] [next in list] [prev in thread] [next in thread]