{"id":322,"date":"2021-12-07T10:43:33","date_gmt":"2021-12-07T18:43:33","guid":{"rendered":"http:\/\/aklaver.org\/wordpress\/?p=322"},"modified":"2021-12-07T10:43:33","modified_gmt":"2021-12-07T18:43:33","slug":"postgres-and-json","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2021\/12\/07\/postgres-and-json\/","title":{"rendered":"Postgres and JSON"},"content":{"rendered":"\n

Time for a renovation on an old post that dealt with Postgres and<\/a> <\/a>hstore<\/a>. This post will move from the hstore data type to json(b). At the time the original post was written introduction of the json type was about three months away and jsonb two and half years out. It also took several years for the operator and function choices to be fleshed out. Now that the support has been mature for some time it seems a good time to make the change. To recap, the original post dealt with using hstore to capture deleted records into an audit table without having to modify the audit table when the source table structure changed. On to doing this with JSON instead.<\/p>\n\n\n\n

Setup section<\/strong>.<\/p>\n\n\n\n

This is being done using Postgres version 14.1. In the previous post a table from an actual application was used. Unfortunately, that relied on information not available to the reader. This post uses a test set up that allows for running the code locally should you desire to.<\/p>\n\n\n\n

The setup DDL<\/em>:<\/p>\n\n\n

\nDROP TABLE IF EXISTS archive_test;\n\nCREATE TABLE archive_test (\n    line_id smallint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,\n    varchar_fld varchar(10) NOT NULL,\n    integer_fld integer,\n    boolean_fld boolean NOT NULL DEFAULT 'f',\n    ts_fld  timestamptz NOT NULL DEFAULT now()\n);\n\nDROP TABLE IF EXISTS archive_test_delete;\n\nCREATE TABLE archive_test_delete (\n    del_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,\n    record_fld jsonb,\n    del_ts timestamp with time zone DEFAULT now(),\n    del_user character varying\n);\n\nDROP FUNCTION IF EXISTS archive_record();\n\nCREATE OR REPLACE FUNCTION archive_record()\nRETURNS trigger\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $BODY$\nDECLARE\n    tbl_name text := TG_TABLE_NAME || '_delete' ;\n    archive_row jsonb := row_to_json(OLD.*);\n    usr_name text := session_user;\nBEGIN\n    EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name)\n    ||'(record_fld,del_ts,del_user)' ||\n    ' VALUES('||quote_literal(archive_row)||',now(),'\n    ||quote_literal(usr_name)||')';\nRETURN OLD;\nEND;\n$BODY$;\n \nCREATE TRIGGER \n    p1_delete \nAFTER DELETE ON \n    archive_test \nFOR EACH ROW EXECUTE PROCEDURE archive_record();\n<\/pre><\/div>\n\n\n
The archive_record() trigger function uses the row_to_json() function from here JSON Functions<\/a> to convert the OLD record that represents the deleted values in the function to a JSON object. This is then combined with now()<\/em> and session_user<\/em> to be inserted into the audit table. The audit table name to be inserted into is derived from the source table name with the addition of the suffix _delete<\/em>.<\/p>\n\n\n\n
Initial data values:<\/em><\/p>\n\n\n
\nINSERT INTO \n    archive_test \nVALUES \n    (DEFAULT, 'test', 1, 't', '12\/06\/2021 10:12:30'),\n    (DEFAULT, 'cat', 2, 'f', '12\/06\/2021 11:00:15'),\n    (DEFAULT, 'dog', 3, 't', '12\/06\/2021 11:15:32');\n\nSELECT * FROM archive_test;\n\n-[ RECORD 1 ]-----------------------\nline_id     | 1\nvarchar_fld | test\ninteger_fld | 1\nboolean_fld | t\nts_fld      | 2021-12-06 10:12:30-08\n-[ RECORD 2 ]-----------------------\nline_id     | 2\nvarchar_fld | cat\ninteger_fld | 2\nboolean_fld | f\nts_fld      | 2021-12-06 11:00:15-08\n-[ RECORD 3 ]-----------------------\nline_id     | 3\nvarchar_fld | dog\ninteger_fld | 3\nboolean_fld | t\nts_fld      | 2021-12-06 11:15:32-08\n\n<\/pre><\/div>\n\n\n
What happens when you DELETE a record:<\/em><\/p>\n\n\n
\nDELETE FROM archive_test WHERE line_id = 2;\n\nSELECT * FROM archive_test_delete;\n-[ RECORD 1 ]----------------------------------------------------\ndel_id     | 1\nrecord_fld | {"ts_fld": "2021-12-06T11:00:15-08:00", "line_id": 2, "boolean_fld": false, "integer_fld": 2, "varchar_fld": "cat"}\ndel_ts     | 2021-12-06 13:52:29.266929-08\ndel_user   | postgres\n\n\n<\/pre><\/div>\n\n\n
Per the trigger function the deleted row is added as JSON object to the audit table along with the time of deletion as well as the user that deleted it.<\/p>\n\n\n\n
Change table by adding a column:<\/em><\/p>\n\n\n
\nALTER TABLE \n    archive_test\nADD COLUMN\n    json_fld json;\n    \n--INSERT values into new form of table:\n\nINSERT INTO \n    archive_test \nVALUES \n    (DEFAULT, 'fish', 4, 't', '11\/26\/2021 10:12:30', '{"one": 1, "two": 2}'),\n    (DEFAULT, 'rabbit', 5, 'f', '11\/26\/2021 11:00:15', '{"three": 3, "four":4}'),\n    (DEFAULT, 'cow', 6, 't', '11\/26\/2021 11:15:32', '{"five": 5, "six": 6}');\n    \n--DELETE from changed table:\n    \nDELETE FROM archive_test WHERE line_id = 6;\n\nSELECT * FROM archive_test_delete WHERE del_id = 2;\n\n-[ RECORD 1 ]----------------------------------------------------\ndel_id     | 2\nrecord_fld | {"ts_fld": "2021-11-26T11:15:32-08:00", "line_id": 6, "json_fld": {"six": 6, "five": 5}, "boolean_fld": true, "integer_fld": 6, "varchar_fld": "cow"}\ndel_ts     | 2021-12-06 13:56:22.47116-08\ndel_user   | postgres\n\n<\/pre><\/div>\n\n\n
The function picks up the new column and includes it in the delete audit table .<\/p>\n\n\n\n
Retrieving data from the audit table:<\/em><\/p>\n\n\n
\nSELECT \n    t.* \nFROM \n    archive_test_delete, \nLATERAL \n    jsonb_populate_record(null::archive_test, record_fld) t;\n    \n-[ RECORD 1 ]-----------------------\nline_id     | 2\nvarchar_fld | cat\ninteger_fld | 2\nboolean_fld | f\nts_fld      | 2021-12-06 11:00:15-08\njson_fld    | \n-[ RECORD 2 ]-----------------------\nline_id     | 6\nvarchar_fld | cow\ninteger_fld | 6\nboolean_fld | t\nts_fld      | 2021-11-26 11:15:32-08\njson_fld    | {"six": 6, "five": 5}\n\n<\/pre><\/div>\n\n\n
Again using a function from JSON Functions<\/a> pull the data out of the audit table and expand the JSON object into a record. json_populate_record<\/em> takes as it’s first argument a composite type, in this case archive_test<\/em>. In Postgres every table has a composite type defined for its structure so using archive_test<\/em> means that the second argument record_fld<\/em>(the JSON record) will use the archive_test table to match its field names to the column names and types for the table. The null:<\/em>: just says use NULL for any columns in the archive_test<\/em> type\/table that do not have corresponding fields in the JSON object. The LATERAL<\/em> comes from SELECT<\/a> and in this particular case is really not needed. It is there to emphasize that the json_populate_record<\/em> is working against each row in the archive_test_delete<\/em> table.<\/p>\n\n\n\n
What happens if you drop a column:<\/em><\/p>\n\n\n
\nBEGIN;\n\nALTER TABLE archive_test DROP COLUMN json_fld ;\n\nSELECT \n    t.* \nFROM \n    archive_test_delete, LATERAL jsonb_populate_record(null::archive_test, record_fld) t;\n\n\n-[ RECORD 1 ]-----------------------\nline_id     | 2\nvarchar_fld | cat\ninteger_fld | 2\nboolean_fld | f\nts_fld      | 2021-12-06 11:00:15-08\n-[ RECORD 2 ]-----------------------\nline_id     | 6\nvarchar_fld | cow\ninteger_fld | 6\nboolean_fld | t\nts_fld      | 2021-11-26 11:15:32-08\n\nROLLBACK;\n<\/pre><\/div>\n\n\n
Because jsonb_populate_record<\/em> is using the archive_test<\/em> composite type and the json_fld<\/em> no longer exists it also ‘disappears’ from the query.  This also applies if the column is renamed. The upshot is that if you are only always adding columns to a table and not renaming them then this system works without to much fuss on the retrieval end. Otherwise you will just have to pull out the record_fld<\/em> as a JSON object and view the data from there. <\/p>\n","protected":false},"excerpt":{"rendered":"
Time for a renovation on an old post that dealt with Postgres and hstore. This post will move from the hstore data type to json(b). At the time the original post was written introduction of the json type was about … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-322","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=322"}],"version-history":[{"count":41,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/322\/revisions"}],"predecessor-version":[{"id":365,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/322\/revisions\/365"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}