Striim 3.9.7 documentation

PostgreSQLReader example output

PostgreSQLReader's output type is WAEvent. See WAEvent contents for change data and PostgreSQLReader WAEvent fields for more information.

The following are examples of WAEvents emitted by PostgreSQLReader for various operation types. They all use the following table:

CREATE TABLE posauthorizations (
  business_name varchar(30),
  merchant_id character varying(35) PRIMARY KEY,
  primary_account bigint,
  pos bigint,
  code character varying(20),
  exp character(4),
  currency_code character(3),
  auth_amount numeric(10,3),
  terminal_id bigint,
  zip bigint,
  city character varying(20));
INSERT

If you performed the following INSERT on the table:

INSERT INTO posauthorizations VALUES(
  'COMPANY 1',
  'D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu',
  6705362103919221351,
  0,
  '20130309113025',
  '0916',
  'USD',
  2.20,
  5150279519809946,
  41363,
  'Quicksand');

The WAEvent for that INSERT would be similar to:

data: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TableName":"public.posauthorizations","TxnID":556,"OperationName":"INSERT",
"LSN":"0/152CD58","NEXT_LSN":"0/152D1C8","Sequence":1,"Timestamp":"2019-01-11 16:29:54.628403-08"}
UPDATE

If you performed the following UPDATE on the table:

UPDATE posauthorizations SET BUSINESS_NAME = 'COMPANY 5A' where pos=0;

The WAEvent for that UPDATE would be similar to:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TableName":"public.posauthorizations","TxnID":557,"OperationName":"UPDATE",
"LSN":"0/152D2E0","NEXT_LSN":"0/152D6F8","Sequence":1,"Timestamp":"2019-01-11 16:31:54.271525-08"}
before: [null,"D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",null,null,null,null,null,null,null,null,null]

When an UPDATE changes the primary key, you may retrieve the old primary key value from the before array.

DELETE

If you performed the following DELETE on the table:

DELETE from posauthorizations where pos=0;

The WAEvent for that DELETE would be similar to:

data: [null,"D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",null,null,null,null,null,null,null,null,null]
metadata: {"TableName":"public.posauthorizations","TxnID":558,"OperationName":"DELETE",
"LSN":"0/152D730","NEXT_LSN":"0/152D7C8","Sequence":1,"Timestamp":"2019-01-11 16:33:09.065951-08"}

Only the primary key value is included.