On 04/10/2016 09:24 AM, David G. Johnston wrote:
On Sun, Apr 10, 2016 at 8:39 AM, Michael Nolan <htfoot@xxxxxxxxx
<mailto:htfoot@xxxxxxxxx>>wrote:
Here's what I did:
\d gold1604_test
Table "uscf.gold1604_test"
Column | Type | Modifiers
--------+------+-----------
data | json |
Some sample data:
{"id":"10000001","name":"MISNER, J
NATHAN","st":"NY","exp":"2012-05-31","sts":
"A"} +
{"id":"10000002","name":"MISNER,
JUDY","st":"TN","exp":"2007-07-31","sts":"I"}
+
{"id":"10000003","name":"MISNER, J
AMSCHEL","st":"NY","exp":"2007-05-31","sts"
:"A"}+
(I think) PostgreSQL assumes that there is only a single top-level json
element, whether it be an array or an object. The first thing you'd
have to do is split on the newline and create a PostgreSQL text array.
But,
uscf=> insert into goldmast_test select * from
json_populate_record(NULL::"goldmast_test", (select * from
gold1604_test limit 2) )
uscf-> \g
ERROR: more than one row returned by a subquery used as an expression
Is there a way to get around the one row per subquery issue?
Yes, use LATERAL.
Something like the following should work (not tested):
INSERT INTO goldmast_test
SELECT jpr.*
FROM gold1604_test
LATERAL json_populate_record(null::goldmast_test", data) AS jpr
I can confirm this works after a little clean up:
test=> INSERT INTO goldmast_test
SELECT jpr.*
FROM gold1604_test,
LATERAL json_populate_record(null::"goldmast_test", data) AS jpr;
INSERT 0 3
test=> select * from goldmast_test ;
id | name | st | exp | sts | supp | rrtg |
qrtg | brtg | oqrtg | obrtg | fid
----------+-------------------+----+------------+-----+------+------+------+------+-------+-------+-----
10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | |
| | | |
10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | |
| | | |
10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | |
| | | |
(3 rows)
While trying to figure out how it works I discovered the LATERAL is not
necessary:
test=> INSERT INTO goldmast_test
SELECT jpr.*
FROM gold1604_test,
json_populate_record(null::"goldmast_test", data) AS jpr;
INSERT 0 3
test=> select * from goldmast_test ;
id | name | st | exp | sts | supp | rrtg |
qrtg | brtg | oqrtg | obrtg | fid
----------+-------------------+----+------------+-----+------+------+------+------+-------+-------+-----
10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | |
| | | |
10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | |
| | | |
10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | |
| | | |
10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | |
| | | |
10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | |
| | | |
10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | |
| | | |
(6 rows)
ideally you could just do (not tested):
INSERT INTO goldmast_test
SELECT jpr.*
FROM (SELECT t::json FROM unnest(string_to_array(?::text, E'\n') un
(t))) src j
LATERAL json_populate_record(null::goldmast_test", j) AS jpr
Where the "?::text" is placeholder for the textual JSON being handed to
the query thus avoiding the temporary "gold1604_test" table.
David J.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
