> This page location: Managing Tables > Create Table As
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL CREATE TABLE AS

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `CREATE TABLE AS` statement to create a new table from the result set of a query.

## Introduction to the PostgreSQL CREATE TABLE statement

The `CREATE TABLE AS` statement [creates a new table](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table) and fills it with the data returned by a query. The following shows the syntax of the `CREATE TABLE AS` statement:

```sql
CREATE TABLE new_table_name
AS query;
```

In this syntax:

1. First, specify the new table name after the `CREATE TABLE` clause.
2. Second, provide a query whose result set is added to the new table after the `AS` keyword.

The `TEMPORARY` or `TEMP` keyword allows you to create a [temporary table](https://neon.com/postgresql/postgresql-tutorial/postgresql-temporary-table):

```sql
CREATE TEMP TABLE new_table_name
AS query;
```

The `UNLOGGED` keyword allows the new table to be created as an unlogged table:

```sql
CREATE UNLOGGED TABLE new_table_name
AS query;
```

The columns of the new table will have the names and data types associated with the output columns of the `SELECT` clause.

If you want the table columns to have different names, you can specify the new table columns after the new table name:

```sql
CREATE TABLE new_table_name ( column_name_list)
AS query;
```

If you want to avoid an error by creating a new table that already exists, you can use the `IF NOT EXISTS` option as follows:

```sql
CREATE TABLE IF NOT EXISTS new_table_name
AS query;
```

## PostgreSQL CREATE TABLE AS statement examples

We will use the `film` and `film_category` table from the [sample database](../postgresql-getting-started/postgresql-sample-database) for the demonstration.

![film\_and\_film\_category\_tables](https://neon.com/postgresqltutorial/film_and_film_category_tables.png)The following example uses the CREATE TABLE AS statement to create a new table that contains the films whose category is 1:

```sql
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;
```

To verify the table creation, you can query data from the `action_film` table:

```sql
SELECT * FROM action_film
ORDER BY title;
```

![PostgreSQL CREATE TABLE AS data verification](https://neon.com/postgresqltutorial/PostgreSQL-CREATE-TABLE-AS-data-verification.png)
To check the structure of the `action_film`, you can use the following command in the psql tool:

```text
\d action_film;
```

It returns the following output:

![PostgreSQL CREATE TABLE AS example](https://neon.com/postgresqltutorial/PostgreSQL-CREATE-TABLE-AS-example.png)
As clearly shown in the output, the names and data types of the `action_film` table are derived from the columns of the `SELECT` clause.

If the `SELECT` clause contains expressions, it is a good practice to override the columns, for example:

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

This example statement created a new table `film_rating` and filled it with the summary data from the `film` table. It explicitly specified the column names for the new table instead of using the column names from the `SELECT` clause.

To check the structure of the `film_rating` table, you use the following command in psql tool:

```text
\d film_rating
```

The following is the output:

![PostgreSQL CREATE TABLE AS with explicit column names](https://neon.com/postgresqltutorial/PostgreSQL-CREATE-TABLE-AS-with-explicit-column-names.png)
Note that the `CREATE TABLE AS` statement is similar to the [`SELECT INTO`](https://neon.com/postgresql/postgresql-tutorial/postgresql-select-into) statement, but the `CREATE TABLE AS` statement is preferred because it is not confused with other uses of the `SELECT INTO` syntax in [PL/pgSQL](../postgresql-plpgsql). In addition, the `CREATE TABLE AS` statement provides a superset of the functionality offered by the `SELECT INTO` statement.

## Summary

- Use the PostgreSQL `CREATE TABLE AS` statement to create a new table from the result of a query.

---

## Related docs (Managing Tables)

- [PostgreSQL Data Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-data-types)
- [Create Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table)
- [Select Into](https://neon.com/postgresql/postgresql-tutorial/postgresql-select-into)
- [SERIAL](https://neon.com/postgresql/postgresql-tutorial/postgresql-serial)
- [Sequences](https://neon.com/postgresql/postgresql-tutorial/postgresql-sequences)
- [Identity Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-identity-column)
- [Generated Columns](https://neon.com/postgresql/postgresql-tutorial/postgresql-generated-columns)
- [Alter Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-alter-table)
- [Rename Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-rename-table)
- [Add Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-add-column)
- [Drop Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-drop-column)
- [Change Column's Data Type](https://neon.com/postgresql/postgresql-tutorial/postgresql-change-column-type)
- [Rename Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-rename-column)
- [Drop Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-drop-table)
- [Temporary Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-temporary-table)
- [Truncate Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-truncate-table)
- [Copy Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-copy-table)