On Sun, Apr 10, 2016 at 7:49 AM, Michael Nolan <htf...@gmail.com> wrote:
> > > On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan <htf...@gmail.com> wrote: >> >>> >>> 2nd Followup: It turns out that loading a table from a JSON string is >>> more complicated than going from a table to JSON, perhaps for good reason. >>> There does not appear to be a direct inverse to the row_to_json() function, >>> but it wasn't difficult for me to write a PHP program that takes the JSON >>> file I created the other day and converts it back to a series of inserts, >>> recreating the original table. >>> >>> Of course this simple program does NO validation (not that this file >>> needed any), so if the JSON string is not well-formed for any of a number >>> of reasons, or if it is not properly mapped to the table into which the >>> inserts are made, an insert could fail or result in incorrect data. >>> -- >>> Mike Nolan >>> >> >> See: http://www.postgresql.org/docs/9.5/interactive/functions-json.html >> >> json_populate_record(base anyelement, from_json json) >> json_populate_recordset(base anyelement, from_json json) >> >> Exists in 9.3 too...though if you are going heavy json I'd suggest doing >> whatever you can to keep up with the recent releases. >> >> David J. >> >> > If there's a way to use the json_populate_record() or > json_populate_recordset() functions to load a table from a JSON file (eg, > using copy), it would be nice if it was better documented. I did find a > tool that loads a JSON file into a table (pgfutter), and even loaded one > row from that table into another table using json_populate_record(), but > the 'subquery returned multiple rows' issue wouldn't let me do the entire > table. > > But that still doesn't deal with validating individual fields or checking > that the JSON is complete and consistent with the table to be loaded. > > It isn't that involved once you've learned generally how to call normal record functions and also set-returning functions (that later must be in the FROM clause of the query). If you provide what you attempted its becomes easier to explain away your mis-understanding. It doesn't work with COPY. You have to write an explicit INSERT+SELECT query where the text of the JSON is a parameter. Your client library should let you do this. If you are using "psql", which doesn't support parameters, you up having to store the json in a psql variable and reference that in the function. INSERT INTO %I SELECT * FROM json_populate_recordset(null::%I, $1) The function ensures that column order is consistent so "INSERT INTO %I" is all you need to write. Data validation is why we invented CHECK constraints - if you need more functionality than the simple mechanical conversion from a json object to a table row you will need to write code somewhere to do the additional work. All json_populate_record(set) promises is that the above command will work. I suppose the way you'd write your attempt that failed would be similar to: INSERT INTO %I SELECT rec.* FROM src_table_with_json LATERAL json_populate_record(null::%I, src_table_with_json.json_column); Again, seeing what you actually did would be helpful - I'm having trouble imaging what you did to provoke that particular error. David J.
