CREATE TABLE AS in PostgreSQL

CREATE TABLE AS is the SQL-standard way to create a new table and populate it from a query in one statement. It is preferred over SELECT INTO because it works inside PL/pgSQL functions, supports additional modifiers like UNLOGGED and IF NOT EXISTS, and makes the intent explicit.

Syntax

CREATE [TEMP | UNLOGGED] TABLE [IF NOT EXISTS] new_table_name
  [(column_name_list)]
AS query;

Key modifiers:

• TEMP / TEMPORARY — table is dropped automatically at session end.
• UNLOGGED — skips WAL logging for faster writes; the table is truncated on crash recovery.
• IF NOT EXISTS — silently skips creation if the table already exists.
• (column_name_list) — optional custom column names; count must match the query’s output.

Practical Example

Create a snapshot of high-value orders by joining two tables:

CREATE TABLE orders (
  order_id   SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  placed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100) NOT NULL
);

-- Create a permanent summary snapshot
CREATE TABLE high_value_orders AS
SELECT
  o.order_id,
  p.product_name,
  p.category,
  o.quantity * o.unit_price AS line_total,
  o.placed_at
FROM orders o
JOIN products p USING (product_id)
WHERE o.quantity * o.unit_price > 500
ORDER BY line_total DESC;

Verify the result:

SELECT * FROM high_value_orders LIMIT 3;

Create a summary table with explicit column names:

CREATE TABLE IF NOT EXISTS category_revenue (category, total_revenue) AS
SELECT category, SUM(quantity * unit_price)
FROM orders
JOIN products USING (product_id)
GROUP BY category;

Inspect the structure — note no indexes are copied:

\d high_value_orders

UNLOGGED Tables for Staging

CREATE UNLOGGED TABLE staging_import AS
SELECT * FROM orders WITH NO DATA;

-- Fast bulk load — no WAL overhead
INSERT INTO staging_import SELECT * FROM orders WHERE placed_at >= '2026-01-01';

-- Validate, then move to permanent table
INSERT INTO orders_archive SELECT * FROM staging_import;
DROP TABLE staging_import;

Testing with Vela

When building ETL pipelines or reporting tables with CREATE TABLE AS, Vela database branching lets you test the full transformation against a real copy of your production data before committing the pipeline to your live environment. Create a branch, run the CREATE TABLE AS query, validate row counts and data quality, then merge to production with confidence.

Production Tips

• Explicitly name output columns when the SELECT list contains expressions or aggregates — auto-generated names like ?column? are unusable.
• Add indexes and constraints after creation: ALTER TABLE new_table ADD PRIMARY KEY (id).
• Use UNLOGGED for high-throughput staging tables where durability is not required, then copy to a logged table after processing.
• Prefer CREATE TABLE AS over SELECT INTO in all new code — it is SQL standard and works without restriction in PL/pgSQL.
• Use WITH NO DATA to create empty structural clones for schema testing: CREATE TABLE new_table AS TABLE existing_table WITH NO DATA.
• Wrap the create + index steps in a transaction to avoid leaving a partially built table on failure.