SQL NOTIFY

What is the SQL NOTIFY command?

Sends an asynchronous notification on a named channel to all sessions currently listening for it.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NOTIFY: Supported: PostgreSQL, Amazon Redshift, TimescaleDB. Not supported: MySQL, SQL Server, Oracle, SQLite.

SQL NOTIFY Full Explanation

NOTIFY is a PostgreSQL-specific command used for lightweight, asynchronous inter-process communication inside the database. When issued, it queues a message on a specified channel. Any open sessions that previously executed LISTEN on the same channel will receive the notification after the surrounding transaction successfully commits. If the transaction rolls back, no notification is delivered. The optional payload lets you attach up to 8000 bytes of text, allowing consumers to react contextually. Notifications are delivered out of band, so they do not block the issuing transaction, and the consumer reads them with the LISTEN/pg_notification_queue. NOTIFY is ideal for event-driven architectures, cache invalidation, and job signaling. Caveats: payload size is limited, message order is not guaranteed across channels, and heavy use can saturate the notification queue.

SQL NOTIFY Syntax

NOTIFY channel_name [ , 'payload_text' ];

SQL NOTIFY Parameters

channel_name (identifier) - Name of the channel (must be a valid PostgreSQL identifier)
payload_text (text) - Optional message (single quotes required, max 8000 bytes)

Example Queries Using SQL NOTIFY

-- Session 1: listener
LISTEN new_order;
-- waits for notifications

-- Session 2: sender inside a transaction
BEGIN;
INSERT INTO orders (customer_id,total) VALUES (42,99.95);
NOTIFY new_order, '{"order_id": 1234}';
COMMIT;

Expected Output Using SQL NOTIFY

Session 2 commits successfully and returns COMMIT
After commit, Session 1 receives a notification on channel new_order with the payload {"order_id": 1234}

Use Cases with SQL NOTIFY

Triggering application workers to process newly inserted rows
Cache invalidation signals so web servers refresh stale data
Coordinating background jobs without polling tables
Real-time UI updates via a listener-bridge (e.g., WebSockets)

Common Mistakes with SQL NOTIFY

Expecting the notification before COMMIT; it only fires after a successful commit
Forgetting to LISTEN before waiting for messages
Using NOTIFY for large data transfers instead of a dedicated table
Quoting the channel name incorrectly; it must be an identifier, not a string

First Introduced In

PostgreSQL 6.4

Frequently Asked Questions

What privileges are required to use NOTIFY?

You need the CONNECT privilege on the database plus write permission on the channel. By default, all roles can NOTIFY any channel but you can restrict this with the pg_notify role attribute or a SECURITY LABEL.

Can I listen to multiple channels at once?

Yes. Execute a separate LISTEN statement for each channel. PostgreSQL will deliver notifications for any channel the session is listening to.

How do I receive notifications in application code?

Most PostgreSQL drivers expose an event or callback (e.g., `asyncpg.connection.add_listener`, `psycopg2.extensions.connection.notifies`) that fires when a notification arrives.

What happens if no session is listening?

The notification is still queued and discarded immediately after commit because there are no listeners. The sender does not receive an error.