Create Table As

CREATE TABLE AS is the SQL-standard way to create a new table and populate it with data from a query in a single 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 clearer. It is commonly used to create snapshot tables, denormalized reporting tables, and staging tables.

CREATE TABLE AS syntax

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

• TEMP / TEMPORARY — creates a table that 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 list of custom column names; must match the number of output columns in the query.

CREATE TABLE AS examples

Example 1 — create a table of action films from a join:

CREATE TABLE action_film AS
SELECT film_id, title, release_year, length, rating
FROM film
INNER JOIN film_category USING (film_id)
WHERE category_id = 1;

Verify the result:

SELECT * FROM action_film ORDER BY title LIMIT 3;

Example 2 — create a summary table with explicit column names:

CREATE TABLE IF NOT EXISTS film_rating (rating, film_count) AS
SELECT rating, COUNT(film_id)
FROM film
GROUP BY rating;

Here the output columns from the aggregate query (rating and COUNT(film_id)) are renamed to rating and film_count using the explicit column list. Inspect the structure:

d film_rating

        Table "public.film_rating"
   Column   |  Type   | Nullable
------------+---------+----------
 rating     | mpaa_rating |
 film_count | bigint  |

CREATE TABLE AS tips

• Explicitly name columns when the SELECT list contains expressions or aggregates — auto-generated names like ?column? are hard to work with.
• Add primary keys, indexes, and constraints after creation with ALTER TABLE and CREATE INDEX; they are not inherited from the source.
• Use UNLOGGED for high-throughput staging tables where durability is not required, then copy to a regular table after processing.
• Prefer CREATE TABLE AS over SELECT INTO in all new code — it is the SQL standard and works without restriction in procedural code.

Reference: PostgreSQL documentation — CREATE TABLE AS.