The PostgreSQL CREATE TABLE command is used to define a new table and structure your data with specific column types and constraints. It’s one of the most fundamental operations when working with relational databases, used to store, organize, and enforce rules on your data.
This guide walks you through different ways to create PostgreSQL tables, including examples for developers, DBAs, and analysts. Whether you’re scripting manually, using the command line, or working with GUI tools like dbForge Studio for PostgreSQL, you’ll find practical instructions and syntax breakdowns for each approach.
- How to create tables in PostgreSQL
- PostgreSQL CREATE TABLE syntax explained
- Creating a new table from the command line
- Postgres table constraints
- How to use the PostgreSQL CREATE TABLE AS statement
- Why CREATE OR REPLACE TABLE does not work in PostgreSQL
- Create unlogged table PostgreSQL
- Create table with array column PostgreSQL
- Create partitioned table PostgreSQL
- Comparison of PostgreSQL table types
- Conclusion
- Frequently asked questions
How to create tables in PostgreSQL
To create a table in PostgreSQL, use the CREATE TABLE statement followed by column definitions. Each column should have a name, a data type, and optional constraints such as NOT NULL or PRIMARY KEY. Here are the steps to follow:
- Start with the
CREATE TABLEkeyword, followed by the name of your table. - Define each column inside parentheses, separating them with commas.
- Specify constraints if needed to enforce rules on the data.
- End the statement with a semicolon.
Example
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE
); This CREATE TABLE PostgreSQL example creates an employees table with four columns and applies constraints like primary key, not null, and unique where appropriate.
If you are new to PostgreSQL and looking for more insights, you may as well start with learning how to download and install PostgreSQL on Windows.
What is a table in Postgres?
A table in PostgreSQL is a data structure used to store records in rows and columns. Each column represents a specific data field (such as name, date, or ID), and each row holds a record with values for those fields.
Tables are the core of relational database design, they organize structured data and support operations like querying, filtering, updating, and joining with other tables.
Example
A simple users table might include the fields like in the code below.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
signup_date DATE
); This structure stores user data with a unique ID, email address, and the date the user signed up.
PostgreSQL CREATE TABLE syntax explained
The standard syntax for creating a table in PostgreSQL is as shown below.
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype(length) column_constraint,
column2 datatype(length) column_constraint,
...
table_constraints
); Breakdown of the syntax
| Element | Description |
|---|---|
| table_name | The name of the new table. |
| column1, column2 | Each column must have a name and a data type (e.g., VARCHAR, INTEGER, DATE ) |
| column_constraint | Optional rules applied to individual columns (e.g., NOT NULL, UNIQUE). |
| table_constraints | Optional rules that apply to the table as a whole (e.g., PRIMARY KEY, FOREIGN KEY). |
Example of using the syntax
CREATE TABLE accounts (
user_id SERIAL PRIMARY KEY, -- Table name: accounts
username VARCHAR(50) UNIQUE NOT NULL, -- Column: username with constraints
password VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
); This statement creates a table named accounts with six columns, enforces uniqueness on username and email, and prevents null values in several fields.
With the basics of PostgreSQL CREATE TABLE syntax covered, the next step is understanding how to use LIKE to create new tables based on existing ones.
How to copy table structure in PostgreSQL using LIKE
PostgreSQL’s LIKE clause allows you to create a new table with the same structure as an existing one, without copying the data. This includes column definitions, data types, constraints, and indexes (depending on the options used).
CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL); This command creates an empty table that mirrors the structure of old_table_name.
Tip: This method is especially useful when you want to clone a schema for staging, testing, or creating archive tables without duplicating the original data.
Another option worth noting is using CREATE TABLE AS SELECT when you want both the structure and data copied in one step. This approach lets you create a table from another table by selecting rows directly into the new table. It’s useful for scenarios like archiving data or building temporary analysis tables where you need more than just the schema.
Creating a temporary table
To create a PostgreSQL temporary table, use the CREATE TEMP TABLE or CREATE TEMPORARY TABLE statement. Temporary tables are session-specific — they exist only for the duration of the current database session and are automatically dropped when the session ends.
CREATE TEMP TABLE temp_sales (
id INT,
total NUMERIC
); Use case tip: The CREATE TEMPORARY TABLE PostgreSQL statement is ideal for staging intermediate data in ETL workflows, performing transformations, or running calculations that don’t need to be stored permanently.
You can also specify behavior at the end of a transaction using ON COMMIT options, like below.
CREATE TEMP TABLE temp_orders (
order_id INT,
status TEXT
) ON COMMIT DELETE ROWS; This ensures rows are cleared after each transaction, keeping the temp table ready for reuse.
See also: How to duplicate a table in PostgreSQL
Creating a new table from the command line
The psql CLI enables users to create tables quickly from the terminal or within automated scripts. This is especially useful in DevOps workflows, CI/CD pipelines, and database migration processes.
Example command
psql -U postgres -d mydb -c "CREATE TABLE employees (id SERIAL, name TEXT);" Explanation:
-U postgres: specifies the database user.-d mydb: connects to the target database.-c: executes the SQL command provided in quotes.
Use case tip: This method is ideal for scripting migrations, setting up test environments, or automating deployments where you need repeatable and reliable table creation via CLI.
You can also launch psql manually, connect to your database, and run SQL commands interactively as shown below.
psql -U postgres Then within the shell use the following code.
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
event TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
); In short, whether through scripts or interactively, using psql create table gives you a flexible and reliable way to manage schema creation directly from the command line.
Postgres table constraints
PostgreSQL provides various types of table constraints to enforce data integrity. Below are the PostgreSQL table constraints explained.
1. PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each row in a table. It does not allow NULL values and must contain unique values.
Example
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
); 2. FOREIGN KEY
A FOREIGN KEY enforces a relationship between columns in different tables. It ensures that values in one table match those in another.
Example
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
); 3. UNIQUE
The UNIQUE constraint ensures that all values in a column are distinct.
Example
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
sku TEXT UNIQUE
); 4. CHECK
The CHECK constraint validates that values in a column meet a specific condition.
Example
CREATE TABLE prices (
id SERIAL PRIMARY KEY,
amount NUMERIC CHECK (amount > 0)
); 5. NOT NULL
The NOT NULL constraint ensures that a column cannot contain NULL values.
Example
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
); How to use the PostgreSQL CREATE TABLE AS statement
The CREATE TABLE AS statement, often written as CREATE TABLE AS SELECT, creates a new table and populates it with the results of a query. It’s commonly used to generate reporting tables, backups, or filtered datasets for analysis.
Additionally, unlike the LIKE clause, which only copies the table structure, CREATE TABLE AS copies both the structure and the data returned by a query. In other words, it allows a PostgreSQL CREATE TABLE from another table in a single step.
Syntax
CREATE TABLE new_sales AS
SELECT * FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'; In this example, new_sales is created as a new table containing sales from the past 30 days.
Use case tip: Use CREATE TABLE AS when you need a working copy of filtered data, generate aggregated reports, or materialize a complex query result for reuse in downstream operations.
You can also add IF NOT EXISTS to avoid errors if the table already exists:
CREATE TABLE IF NOT EXISTS recent_sales AS
SELECT * FROM sales WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days'; Why CREATE OR REPLACE TABLE does not work in PostgreSQL
Unlike functions or views, PostgreSQL does not allow the use of CREATE OR REPLACE with the CREATE TABLE statement. Attempting to do so will result in a syntax error. This can be confusing for users familiar with other database systems like MariaDB, where CREATE OR REPLACE TABLE is supported.
Recommended alternatives
To avoid syntax errors and ensure full control over table creation, PostgreSQL offers two reliable patterns:
1. Create only if the table doesn’t exist: Use CREATE TABLE IF NOT EXISTS PostgreSQL syntax to safely create a table without overwriting or throwing an error if it already exists.
CREATE TABLE IF NOT EXISTS temp_table (
id SERIAL PRIMARY KEY,
name TEXT
); 2. Replace an existing table deliberately: If your goal is to overwrite a table, explicitly drop it first and then recreate it. This ensures clarity and prevents accidental data loss.
DROP TABLE IF EXISTS temp_table;
CREATE TABLE temp_table (
id SERIAL PRIMARY KEY,
name TEXT
); These approaches ensure predictable table creation behavior while staying aligned with the official PostgreSQL CREATE TABLE syntax, preventing errors and confusion.
Create unlogged table PostgreSQL
An unlogged table in PostgreSQL is a type of table that skips write-ahead logging (WAL), making it significantly faster for write-heavy operations, but at the cost of durability. Data in unlogged tables is not crash-safe and will be lost in the event of a server failure.
Use when:
- Temporary caching large data
- Performance benchmarking
- Staging bulk imports where persistence isn’t critical
Syntax
CREATE UNLOGGED TABLE fast_buffer (
id SERIAL PRIMARY KEY,
payload TEXT
); Tip: Avoid using unlogged tables for any critical data that must persist after crashes or restarts.
Create table with array column PostgreSQL
PostgreSQL allows you to define array columns, enabling a single column to store multiple values of the same type. This is ideal for use cases like tags, ratings, or metadata lists.
Example
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name TEXT,
tags TEXT[]
); To insert values follow the code below.
INSERT INTO projects (name, tags)
VALUES ('AI Toolkit', ARRAY['machine learning', 'NLP', 'automation']); Tip: You can query array contents using operators like @>, <@, and ANY.
Create partitioned table PostgreSQL
Partitioning in PostgreSQL allows you to divide a large table into smaller, more manageable pieces based on a key column, improving query performance and maintenance.
Step 1: Create the parent partitioned table
CREATE TABLE orders (
order_id INT,
order_date DATE,
amount NUMERIC
) PARTITION BY RANGE (order_date); Step 2: Create partitions
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); Tip: Use range, list, or hash partitioning depending on your data distribution and query patterns.
Comparison of PostgreSQL table types
PostgreSQL offers multiple ways to create tables, each designed for different use cases — from persistent storage to fast ETL staging. The table below compares the most common table types and methods:
| Table type | Description | When to use | Automatically dropped | Copies data |
|---|---|---|---|---|
| Regular | Default persistent table | For all standard storage needs | No | No |
| TEMP | Exists for session only | For temporary calculations/ETL | Yes | No |
| UNLOGGED | No WAL logging | Fast insert operations (non-critical) | No | No |
| LIKE | Copies structure (not data) | Clone schema for new table | No | No |
| AS SELECT | Copies structure and data | For derived datasets | No | Yes |
Tip: Choose your table type based on data durability, performance requirements, and whether you need to clone structure or data.
Conclusion
PostgreSQL offers multiple ways to create tables, whether through SQL statements, command line utilities like psql, or user-friendly GUI tools. From basic Postgres CREATE TABLE syntax to advanced features like partitioning, arrays, and unlogged tables, you have full flexibility to structure your data as needed.
But you can simplify this process using tools like dbForge Studio for PostgreSQL. With such a tool, you can build, modify, and manage tables visually, without writing complex SQL by hand.
Choose the method that suits your workflow, from CREATE TABLE syntax to visual editing with dbForge. Download a 30-day trial of dbForge Studio for PostgreSQL and check it yourself.
Frequently asked questions
What is the syntax for CREATE TABLE in PostgreSQL?
The official PostgreSQL CREATE TABLE syntax lets you define a new table with columns, data types, and constraints. For example:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE
); This structure ensures your schema is consistent and enforceable.
How do I copy a table structure in PostgreSQL without data?
You can use CREATE TABLE LIKE PostgreSQL syntax to clone the structure of an existing table. This copies columns, data types, and optionally constraints, but not the data itself.
Example
CREATE TABLE archive_employees (LIKE employees INCLUDING ALL); What’s the difference between TEMP and UNLOGGED tables in PostgreSQL?
A PostgreSQL temporary table exists only during the current session and is dropped automatically when the session ends. An UNLOGGED table (created with CREATE UNLOGGED TABLE PostgreSQL) persists beyond a session but does not write to the WAL (Write-Ahead Log), making it faster but less crash-safe.
Can I create a table from a SELECT query in PostgreSQL?
Yes. The CREATE TABLE AS SELECT command creates a new table and populates it with the result of a query. This is useful for reporting, backups, or filtered datasets.
How do constraints like CHECK and UNIQUE work in PostgreSQL tables?
PostgreSQL table constraints ensure data integrity. A CHECK constraint validates column values against a condition, while a UNIQUE constraint enforces that values in a column or set of columns remain distinct. Together, they help maintain clean and reliable datasets.
How to create a table if it doesn’t already exist in PostgreSQL?
Use CREATE TABLE IF NOT EXISTS PostgreSQL syntax to safely create a table. This prevents errors by skipping creation if the table already exists.
What’s the fastest way to create a table from the command line?
The quickest method is with psql CREATE TABLE, using the -c option to run commands directly.
psql -U postgres -d mydb -c "CREATE TABLE test(id SERIAL, name TEXT);" This is especially useful in scripts and automation.
How can I define default values for columns in PostgreSQL?
Use the PostgreSQL CREATE TABLE with default value syntax.
Example
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status TEXT DEFAULT 'pending'
); This ensures new rows automatically include defaults if no value is provided.
How do I define foreign key relationships when creating a table?
You can use CREATE TABLE WITH FOREIGN KEY PostgreSQL syntax to link tables together.
Example
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id)
); This enforces referential integrity between parent and child tables.
