Table Aliases in PostgreSQL

A table alias assigns a short, temporary name to a table reference within a single SQL query. Aliases keep multi-table queries readable by reducing repetition and are a strict requirement when the same table appears more than once — most importantly in self-joins.

Syntax

-- With AS keyword (explicit)
FROM table_name AS alias_name

-- Without AS keyword (equally valid)
FROM table_name alias_name

Both forms are accepted by PostgreSQL. The AS keyword adds clarity; omitting it is common in concise queries. Once the alias is declared, use it as a prefix to qualify column names throughout the rest of the query:

SELECT alias_name.column_name
FROM table_name AS alias_name;

Practical Example

The examples below use an orders-and-shipments domain to illustrate alias patterns.

CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  product_code VARCHAR(50) NOT NULL,
  amount       NUMERIC(10, 2) NOT NULL,
  customer_id  INT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE shipments (
  shipment_id  SERIAL PRIMARY KEY,
  order_id     INT REFERENCES orders(order_id),
  carrier      VARCHAR(50),
  shipped_at   TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ
);

INSERT INTO orders (product_code, amount, customer_id)
VALUES
  ('SKU-101', 149.99, 10),
  ('SKU-202',  49.50, 11),
  ('SKU-303', 299.00, 10);

INSERT INTO shipments (order_id, carrier, shipped_at)
VALUES
  (1, 'FedEx', now() - interval '3 days'),
  (2, 'UPS',   now() - interval '1 day');

Basic alias usage

SELECT
  o.order_id,
  o.product_code,
  o.amount
FROM orders AS o
WHERE o.amount > 100.00
ORDER BY o.amount DESC;

Aliases in a multi-table JOIN

Without aliases, you would need to write the full table name every time you qualify a column:

-- With aliases — much more readable
SELECT
  o.order_id,
  o.product_code,
  s.carrier,
  s.shipped_at
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id
ORDER BY o.created_at DESC;

Verify the join is using an index on shipments.order_id:

EXPLAIN (FORMAT TEXT)
SELECT o.order_id, s.carrier
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id;

Aliases required for a self-join

When finding orders placed by the same customer, the orders table must appear twice with different aliases:

SELECT
  o1.order_id    AS order_a,
  o2.order_id    AS order_b,
  o1.customer_id
FROM orders o1
INNER JOIN orders o2
  ON  o1.customer_id = o2.customer_id
  AND o1.order_id    < o2.order_id   -- prevent duplicate pairs
ORDER BY o1.customer_id;

Without o1 and o2, any column reference like order_id would be ambiguous and cause an error.

Using Aliases with Subqueries

Subqueries in the FROM clause must always have an alias — they are a form of derived table and PostgreSQL requires a name for them:

SELECT recent.order_id, recent.amount
FROM (
  SELECT order_id, amount
  FROM orders
  WHERE created_at > now() - interval '7 days'
) AS recent
WHERE recent.amount > 50.00;

Testing with Vela

Refactoring large queries to introduce or clean up table aliases is low-risk but can expose latent column-ambiguity bugs. Vela’s database branching lets you test query rewrites against a production-data snapshot without touching the live database — run the refactored query on a branch, verify the result set matches the original, then apply the change confidently.

Production Tips

• Keep aliases short but meaningful: single letters work well for simple queries (o for orders, s for shipments); use two-letter abbreviations in complex queries with many tables to keep them distinguishable.
• Always qualify every column in multi-table queries with its table alias — this prevents ambiguity errors when a new column is added to one of the tables with a name that already exists in another.
• Aliases are required for self-joins and for subqueries used as derived tables in FROM.
• Aliases defined in the FROM clause are visible in WHERE, GROUP BY, HAVING, ORDER BY, and SELECT — but not in a SELECT expression that defines the alias itself.
• Avoid overly terse aliases like a, b, c across many tables; readers spend more time decoding them than the brevity saves.