CODEWITHSUNDEEP
sqlpostgresql
Blair
Blair

Reputation: 3751

Query latest state based on audit events in Postgres

I have the following table with "auditing" events (add,remove, or update):

CREATE TABLE test (
  id INTEGER,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  action TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT
);

INSERT INTO test(id, action, key, value) VALUES (1, 'add',    'name', 'Stack Overflow');
INSERT INTO test(id, action, key, value) VALUES (1, 'add',    'website', 'google.com');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'google');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'Google');
INSERT INTO test(id, action, key, value) VALUES (3, 'add', 'name', 'Facebook');
INSERT INTO test(id, action, key, value) VALUES (2, 'add',    'name', 'Amazon'); // row 5
INSERT INTO test(id, action, key) VALUES (2, 'remove', 'name');
INSERT INTO test(id, action, key, value) VALUES (2, 'add',    'name', 'Oracle');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'Microsoft');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'website', 'microsoft.com');
INSERT INTO test(id, action, key) VALUES (3, 'remove', 'name');

Given a timestamp, I need to query the "state" of a config at any point in time.

i.e.,

If I queried the table using the timestamp of row 5, I should get:

id, key       , value
1 , 'name'    , 'Google'
1 , 'website' , 'google.com'
2 , 'name'    , 'Amazon'
3 , 'name'    , 'Facebook'

If I queried with the the current timestamp, I should get:

id, key       , value
1 , 'name'    , 'Microsoft'
1 , 'website' , 'microsoft.com'
2 , 'name'    , 'Oracle'

How can this be achieved in Postgres (using straight SQL preferably)?

Upvotes: 1

Views: 204

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would recommend distinct on:

select id, key, value
from (select distinct on (id, key) t.*
      from test t
      where timestamp < current_timestamp
      order by id, key, timestamp desc
     ) t
where action <> 'remove';

I don't know what "row 5" means in your context. SQL tables represent unordered sets. There is no "row 5" unless a column specifies the ordering or row number.

Upvotes: 1

Nicola Lepetit
Nicola Lepetit

Reputation: 795

Without any changes to your table, you can do like this:

select t.id,t.key,t.value from test t,
(select id,key,max(timestamp) ts from test
group by id,key) mx
where t.id = mx.id and t.key=mx.key and t.timestamp = mx.ts
and t.value != 'remove'

But I strongly suggest you to add an autoincremental primary key, so the comparison between tables will be much faster.

Upvotes: 1

Related Questions