patternsqlMinor

Achieving "sequence number", rather than dateTime, for updates; postgres specifically

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

achievingnumberpostgresspecificallythanrathersequenceforupdatesdatetime

Problem

So a postgres database (specifically, an AmazonRDS instance fwiw).

It's easy enough to arrange things to get the last updated (or created) time for any record.

That information would be a datetime.

What I want to achieve: imagine an integer, sequenceNumber, which is atomic and global to the database.

Simply, every table would have a sequenceNumber field.

Any time any record (ie, in any table) is updated (or created), it gets the next sequenceNumber.

You know how you have programmer friends who know nothing about databases. Wave. My idiot solution would be, in the entire codebase, every time you create or modify a record, be sure to remember to update sequenceNumber on it. (Of course, just have one global system that gives the next atomic sequenceNumber.)

What's the sound solution for this?

Indeed, is it completely available already in some way I don't know, or?

Solution

As a_horse_with_no_name originally suggested in question comments:

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

First we create a global sequence and add a column in each table:

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

Create trigger function (one) and UPDATE triggers (one in each table):

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS 
$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass); 
   RETURN NEW;
END;
$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW 
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW 
    EXECUTE PROCEDURE  update_global_sequence_id();

And profit:

-- example of use
INSERT INTO a (a_id, a_name)
VALUES 
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES 
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
1 a 1
2 b 2
3 c 3
4 d 4
5 e 5

And after some updates, to check that the triggers work:

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
2 b 2
4 d 4
5 e 5
1 aa 6 -- sequence updated to 6
3 cc 7 -- sequence updated to 7

Code Snippets

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL 
    DEFAULT nextval('global_sequence_number'::regclass) ;

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS 
$$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass); 
   RETURN NEW;
END;
$$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW 
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW 
    EXECUTE PROCEDURE  update_global_sequence_id();

-- example of use
INSERT INTO a (a_id, a_name)
VALUES 
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES 
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE 
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

Context

StackExchange Database Administrators Q#157501, answer score: 9

Revisions (0)

No revisions yet.