agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedinserting json content from a file into table column
6+ messages / 3 participants
[nested] [flat]
* inserting json content from a file into table column
@ 2016-02-10 15:10 Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 6+ messages in thread
From: Shashank Dutt Jha @ 2016-02-10 15:10 UTC (permalink / raw)
To: pgsql-sql
I have .json file.C:/ a.json
Table with column 'food' of type jsonb
how to insert the content of a.json into column 'food'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: inserting json content from a file into table column
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
@ 2016-02-10 15:22 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:01 ` Re: inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2016-02-10 15:22 UTC (permalink / raw)
To: Shashank Dutt Jha <shashank.dj@gmail.com>; pgsql-sql
On 02/10/2016 07:10 AM, Shashank Dutt Jha wrote:
> I have .json file.C:/ a.json
> Table with column 'food' of type jsonb
>
> how to insert the content of a.json into column 'food'
What are you using as your client, for example psql, Java program,
Python program, etc.?
What version of Postgres are you using? In this case it probably does
not matter that much, but json/jsonb has changed a good deal over recent
versions so it is nice to know what you are working with.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: inserting json content from a file into table column
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-02-10 16:01 ` Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 16:34 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 6+ messages in thread
From: Shashank Dutt Jha @ 2016-02-10 16:01 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
psql. PostgreSQL v9.5
On Wed, Feb 10, 2016 at 8:52 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 02/10/2016 07:10 AM, Shashank Dutt Jha wrote:
>
>> I have .json file.C:/ a.json
>> Table with column 'food' of type jsonb
>>
>> how to insert the content of a.json into column 'food'
>>
>
> What are you using as your client, for example psql, Java program, Python
> program, etc.?
>
> What version of Postgres are you using? In this case it probably does not
> matter that much, but json/jsonb has changed a good deal over recent
> versions so it is nice to know what you are working with.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: inserting json content from a file into table column
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:01 ` Re: inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
@ 2016-02-10 16:34 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:50 ` Re: inserting json content from a file into table column David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2016-02-10 16:34 UTC (permalink / raw)
To: Shashank Dutt Jha <shashank.dj@gmail.com>; +Cc: pgsql-sql
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"
}
}
}
}
}');
aklaver@test=> \x
Expanded display is on.
aklaver@test=> select * from json_test;
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
json_fld | {"glossary": {"title": "example glossary", "GlossDiv":
{"title": "S", "GlossList": {"GlossEntry": {"ID": "SGML", "Abbrev": "ISO
8879:1986", "SortAs": "SGML", "Acronym": "SGML", "GlossDef": {"para": "A
meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]}, "GlossSee": "markup", "GlossTerm":
"Standard Generalized Markup Language"}}}}}
Otherwise you will need to find a way to pass the file in from the
shell. You are using Windows it seems and I am not familiar enough with
it to offer any guidance on that topic.
>
> On Wed, Feb 10, 2016 at 8:52 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 02/10/2016 07:10 AM, Shashank Dutt Jha wrote:
>
> I have .json file.C:/ a.json
> Table with column 'food' of type jsonb
>
> how to insert the content of a.json into column 'food'
>
>
> What are you using as your client, for example psql, Java program,
> Python program, etc.?
>
> What version of Postgres are you using? In this case it probably
> does not matter that much, but json/jsonb has changed a good deal
> over recent versions so it is nice to know what you are working with.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: inserting json content from a file into table column
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:01 ` Re: inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 16:34 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-02-10 16:50 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-11 06:11 ` Re: inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2016-02-10 16:50 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Shashank Dutt Jha <shashank.dj@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: inserting json content from a file into table column
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:01 ` Re: inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 16:34 ` Re: inserting json content from a file into table column Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:50 ` Re: inserting json content from a file into table column David G. Johnston <david.g.johnston@gmail.com>
@ 2016-02-11 06:11 ` Shashank Dutt Jha <shashank.dj@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Shashank Dutt Jha @ 2016-02-11 06:11 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; 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.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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-02-11 06:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-10 15:10 inserting json content from a file into table column Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 15:22 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:01 ` Shashank Dutt Jha <shashank.dj@gmail.com>
2016-02-10 16:34 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-10 16:50 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-11 06:11 ` Shashank Dutt Jha <shashank.dj@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox