snippetModeratepending

Postgres LISTEN/NOTIFY — real-time database change notifications

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

LISTENNOTIFYpg_notifytriggerreal-timechange notification

linux

Problem

Need to react to database changes in real-time without polling. A row is inserted or updated and the application needs to know immediately.

Solution

PostgreSQL LISTEN/NOTIFY sends real-time notifications over existing connections. Combine with triggers for automatic notification on data changes.

Code Snippets

LISTEN/NOTIFY with trigger for real-time change notifications

-- 1. Create a trigger function that notifies on changes
CREATE OR REPLACE FUNCTION notify_changes()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'table_changes',
    json_build_object(
      'table', TG_TABLE_NAME,
      'op', TG_OP,
      'id', COALESCE(NEW.id, OLD.id),
      'data', CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)
                   ELSE row_to_json(NEW) END
    )::text
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 2. Attach trigger to table
CREATE TRIGGER orders_notify
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_changes();

-- Node.js listener (using 'pg' package)
-- const client = new pg.Client(connectionString);
-- await client.connect();
-- await client.query('LISTEN table_changes');
-- client.on('notification', (msg) => {
--   const payload = JSON.parse(msg.payload);
--   console.log(payload.op, payload.table, payload.id);
-- });

Revisions (0)

No revisions yet.