On 04/10/2016 08:39 AM, Michael Nolan 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"}+
uscf-> \d goldmast_test
Table "uscf.goldmast_test"
Column | Type | Modifiers
--------+-----------------------+-----------
id | character varying(8) |
name | character varying(40) |
st | character varying(2) |
exp | date |
sts | character(1) |
supp | date |
rrtg | character varying(8) |
qrtg | character varying(8) |
brtg | character varying(8) |
oqrtg | character varying(8) |
obrtg | character varying(8) |
fid | character varying(12) |
insert into goldmast_test select * from
json_populate_record(NULL::"goldmast_test", (select * from gold1604_test
limit 1) )
produces:
uscf=> 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 | | | |
| | |
(1 row)
The fact that the null values were stripped out is not an issue here.
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?
Per Davids post:
http://www.postgresql.org/docs/9.5/interactive/functions-json.html
json_populate_recordset(base anyelement, from_json json)
Expands the outermost array of objects in from_json to a set of rows
whose columns match the record type defined by base (see note below).
select * from json_populate_recordset(null::myrowtype,
'[{"a":1,"b":2},{"a":3,"b":4}]')
--
Mike Nolan
--
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
