All;
I've setup a logical replication slot in a 9.6 cluster.
I have pulled data with a query like this:
SELECT * FROM pg_logical_slot_get_changes('lr_cdc_slot', NULL, NULL,
'pretty-print', '1')
I've even staged the returned 'data' column in a table like this:
create table json_test as select data::jsonb from
pg_logical_slot_get_changes('lr_cdc_slot', null,
null,'include-timestamp','1');
I want to isolate the various "fields" and "values" of the output json
string (i.e. the table name, the operation, the columns, etc) However I
am not having much luck.
Here's some of the queries that do work but none of them give me a full
breakout of the fields:
select jsonb_each(data) from json_test ;
(change,"[{""kind"": ""insert"", ""table"": ""lr_test_tab"", ""
schema"": ""lr_test_schema"", ""columnnames"": [""id"", ""compan
y_name"", ""status"", ""active_date""], ""columntypes"": [""inte
ger"", ""character varying(100)"", ""character varying(10)"", ""
timestamp with time zone""], ""columnvalues"": [1, ""Acme CO"",
""B+"", ""2017-12-12 10:14:39.899462-05""]}]")
(timestamp,"""2019-12-12 10:14:39.901252-05""")
(2 rows)
select jsonb_object_keys(data::jsonb) from json_test ;
jsonb_object_keys
-------------------
change
timestamp
(2 rows)
How can I pull each field and it's value from this (i.e kind = insert,
table = lr_test_tab, columnnames = ...)?
Thanks in advance
