SQL ALSO

What is the SQL ALSO keyword used for?

ALSO tells PostgreSQL to execute a rule action in addition to the original statement, not in place of 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 ALSO: Supported: PostgreSQL and derivatives such as Amazon Redshift. Not supported: MySQL, SQL Server, Oracle, SQLite, MariaDB.

SQL ALSO Full Explanation

In PostgreSQL, the keyword ALSO appears only inside CREATE RULE statements. A rule can either replace the triggering command (using INSTEAD) or supplement it (using ALSO). When DO ALSO is specified, PostgreSQL runs the original user statement first, then executes the rule’s action list. If DO ALSO is omitted, PostgreSQL assumes DO ALSO by default, but stating it explicitly makes intent clear and improves readability. Rules fire at the statement level, so DO ALSO actions run once per statement, not once per row. Rules are processed before triggers, which can affect side-effect ordering. Because rules rewrite queries, pay attention to possible performance impacts and to the fact that rule actions execute with the same privileges as the original statement.

SQL ALSO Syntax

CREATE RULE rule_name AS
ON { SELECT | INSERT | UPDATE | DELETE } TO table_name
[ WHERE condition ]
DO ALSO action;

SQL ALSO Parameters

Example Queries Using SQL ALSO

-- Audit updates on the accounts table
CREATE TABLE account_log (
  id serial PRIMARY KEY,
  account_id int,
  op text,
  at timestamptz DEFAULT now()
);

CREATE RULE log_account_update AS
ON UPDATE TO accounts
DO ALSO
  INSERT INTO account_log(account_id, op)
  VALUES (NEW.id, 'update');

-- When this runs, both the UPDATE and the INSERT occur
UPDATE accounts SET balance = balance + 100 WHERE id = 42;

Expected Output Using SQL ALSO

The accounts row is updated
An extra row describing the operation is inserted into account_log
Both actions commit or roll back together

Use Cases with SQL ALSO

Auditing or logging table changes without modifying application code
Populating auxiliary tables (materialized views, summary tables) while keeping the original DML intact
Implementing soft business rules that add side effects but should never suppress the base operation

Common Mistakes with SQL ALSO

Using ALSO in databases other than PostgreSQL, where it is not recognized
Forgetting that rules fire once per statement, leading to missing per-row detail
Confusing DO ALSO with DO INSTEAD, accidentally suppressing the original statement
Overusing rules when a trigger would be clearer and safer

First Introduced In

PostgreSQL 7.0

Frequently Asked Questions

Why would I use DO ALSO instead of a trigger?

DO ALSO rules are evaluated during the query rewrite phase. They can add statements (like logging inserts) without invoking per-row overhead, making them efficient for statement-level side effects.

Does DO ALSO fire before or after the original statement?

The original statement runs first. PostgreSQL then executes the DO ALSO action list within the same transaction.

Can DO ALSO be combined with DO INSTEAD in the same rule?

No. A single rule can specify either INSTEAD or ALSO, not both. Create separate rules if you need different behaviors for different conditions.

How do I remove a rule that uses ALSO?

Run DROP RULE rule_name ON table_name; to remove the rule. This stops the associated DO ALSO actions from executing.