CREATE TABLE AS in PostgreSQL: Crafting Tables from Queries

Introduction

Ever thought about generating a whole new table just from a query’s result? If yes, welcome to CREATE TABLE AS in PostgreSQL. This might not be the most famous command out there, but it’s powerful, especially when you’re dealing with temporary data or summary tables. So let’s dig in and see how to create tables on the fly based on query results.

Basic Syntax

The CREATE TABLE AS statement creates a new table and fills it with the result set of a query:

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE conditions;

Use Cases

1. Data Summarization: Suppose you want to create a summary table for better performance.
2. Temporary Data: When you want to stash some data temporarily without affecting the primary tables.

Examples with tv_series Table

Imagine you’re interested in all the drama TV series released after 2010. You could generate a new table for this as follows:

CREATE TABLE drama_series AS
SELECT id, name, genre, seasons, release_year
FROM tv_series
WHERE genre = 'Drama' AND release_year > 2010;

Now you have a new table named drama_series, holding only the drama TV series released after 2010.

Common Flags and Options

• UNLOGGED: Use this flag to create an unlogged table. Which means the table will not be added to write-ahead-log(WAL). This might make your table faster, but it provides weaker durability as data can be easily lost on crash.

  CREATE UNLOGGED TABLE new_table AS SELECT ...;

• WITH [NO] DATA: This option specifies whether the table should be filled with data.

  CREATE TABLE new_table AS SELECT ... WITH NO DATA;

Common Errors and Solutions

Error Message:

ERROR:  relation "new_table" already exists

Cause:
You’re attempting to create a table with a name that’s already in use.

Solution:
Choose a different name or use the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS new_table AS SELECT ...;

Pro Tips

• If you’re dealing with large datasets, you might want to opt for an unlogged table. They are faster because changes are not written to the write-ahead log.

• When using WITH NO DATA, it’s helpful when you want to create the table structure without filling in the data. You can insert or copy data into it later.

So, that’s the CREATE TABLE AS command for you. Though it’s not in everyday use, knowing about it gives you another trick up your sleeve when managing your PostgreSQL databases. As always, practice makes perfect. Happy querying!