SQL ONLY

What is the SQL ONLY keyword?

ONLY restricts a query or DML statement to the named table or type, ignoring inherited tables or sub-types.

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 ONLY: Supported: PostgreSQL, Greenplum, YugabyteDB, Oracle (object tables). Not supported: MySQL, SQL Server, SQLite, MariaDB.

SQL ONLY Full Explanation

In databases that support table inheritance (PostgreSQL) or object sub-types (Oracle), statements issued against a parent table normally include rows from all descendant tables. Prepending the keyword ONLY tells the optimizer to operate solely on the specified table or object type. This applies to SELECT, UPDATE, DELETE, and ALTER TABLE. In PostgreSQL, ONLY can appear before a table name in the FROM clause or after ALTER TABLE. In Oracle, ONLY is used with object tables in the FROM clause. Because it bypasses child tables, queries can run faster and avoid accidental updates to underlying partitions, but you must reference each child explicitly if they are still required.

SQL ONLY Syntax

SELECT columns
FROM ONLY table_name
WHERE condition;

UPDATE ONLY table_name
SET column = value
WHERE condition;

DELETE FROM ONLY table_name
WHERE condition;

ALTER TABLE ONLY table_name
ADD CONSTRAINT ...;

SQL ONLY Parameters

table_name (identifier) - The parent table to restrict the statement to.

Example Queries Using SQL ONLY

-- Return rows only from the parent orders table
SELECT *
FROM ONLY orders
WHERE status = 'pending';

-- Update the parent table but leave child tables untouched
UPDATE ONLY orders
SET status = 'archived'
WHERE created_at < CURRENT_DATE - INTERVAL '1 year';

-- Add a check constraint that does not cascade to children
ALTER TABLE ONLY orders
ADD CONSTRAINT chk_total_positive CHECK (total_amount > 0);

Expected Output Using SQL ONLY

PostgreSQL scans only the orders table
Child tables like orders_2023 or orders_2024 are skipped
Updates and constraints affect the parent table alone

Use Cases with SQL ONLY

Querying a parent table for fast counts without scanning partitions
Applying a constraint that should not inherit to children
Safely updating or deleting rows in the parent while preserving descendants
Debugging data anomalies in the base table only

Common Mistakes with SQL ONLY

Forgetting that child tables are excluded and assuming complete results
Using ONLY in databases that do not support inheritance, leading to syntax errors
Omitting ONLY in ALTER TABLE when you intend the change to stay local

First Introduced In

PostgreSQL 7.1

Frequently Asked Questions

What is the purpose of ONLY?

ONLY limits a SELECT or DML statement to the named table so inherited tables or subtypes are skipped.

Where can I place ONLY in a statement?

Put ONLY directly before the table name in SELECT, UPDATE, or DELETE, or after ALTER TABLE when adding or changing constraints.

Is ONLY required for partitioned tables?

No, but it is helpful when you need to target just the parent partition for maintenance or sampling.

What happens if I use ONLY on a table without children?

The statement runs normally. The presence of ONLY has no effect beyond the regular table scan.