Create Table in PostgreSQL – Syntax, Data Types, Constraints, and Examples | DbSchema

Table of Contents

1. What CREATE TABLE does
2. Version notes
3. Basic PostgreSQL CREATE TABLE syntax
4. Common PostgreSQL data types
5. Create a simple table
6. Create a table with constraints and defaults
7. Useful CREATE TABLE variants
8. Check the result
9. Create tables visually in DbSchema
10. FAQ
11. Conclusion

CREATE TABLE is one of the first PostgreSQL commands every developer learns. It defines the table name, columns, data types, defaults, and constraints that will shape how your application stores data.

You can create tables entirely in SQL, or design them visually in DbSchema and let the tool generate the PostgreSQL DDL for you. Both approaches are useful, especially when you also need diagrams, JDBC connectivity, or schema documentation.

What CREATE TABLE does

In PostgreSQL, CREATE TABLE creates a new table in a schema such as public, sales, or analytics. When you define a table, you usually decide:

• the table name and schema
• each column name and data type
• which columns are required with NOT NULL
• how rows are uniquely identified with a primary key
• whether values must be unique, checked, or related to another table

If you have not created the database yet, start with How to Create a Database in PostgreSQL.

Version notes

• CREATE TABLE, TEMP, UNLOGGED, and IF NOT EXISTS are available in modern PostgreSQL versions and are safe choices for current deployments.
• GENERATED ... AS IDENTITY is available in PostgreSQL 10+ and is the preferred replacement for SERIAL in new schemas.
• Stored generated columns are available in PostgreSQL 12+, so check your version before using them in shared scripts.
• SERIAL still works and appears in many examples, but identity columns are easier to manage in standards-oriented schemas.

Basic PostgreSQL CREATE TABLE syntax

CREATE TABLE [IF NOT EXISTS] schema_name.table_name (
    column_name data_type [column_constraint],
    column_name data_type [column_constraint],
    table_constraint
);

Use a schema-qualified name such as public.customers when you want scripts to be explicit and repeatable across environments.

Common PostgreSQL data types

Use numeric instead of floating-point types for prices, and consider timestamptz for application timestamps that may cross time zones.

Create a simple table

Connect with psql, then run:

CREATE TABLE public.students (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    age integer NOT NULL,
    grade char(2)
);

This creates a table named students with four columns and a primary key on id.

Create a table with constraints and defaults

Real-world tables usually need more than just column names and types:

CREATE TABLE public.orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    order_number varchar(30) NOT NULL UNIQUE,
    status varchar(20) NOT NULL DEFAULT 'new',
    total_amount numeric(12,2) NOT NULL CHECK (total_amount >= 0),
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT orders_customer_id_fkey
        FOREIGN KEY (customer_id)
        REFERENCES public.customers(customer_id)
);

This example shows several important PostgreSQL features:

• GENERATED ALWAYS AS IDENTITY for an auto-incrementing key
• UNIQUE to prevent duplicate order numbers
• DEFAULT for initial values
• CHECK to reject invalid totals
• FOREIGN KEY to connect orders to customers

For more on relationships, see Foreign Keys in PostgreSQL. If indexes are the next step, read Create Index in PostgreSQL.

Useful CREATE TABLE variants

Create the table only if it does not already exist

CREATE TABLE IF NOT EXISTS public.audit_log (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Create a temporary table

CREATE TEMP TABLE recent_orders (
    order_id bigint,
    created_at timestamptz
);

Temporary tables are session-scoped and are often useful for ETL steps or debugging.

Create an unlogged table for faster write-heavy workloads

CREATE UNLOGGED TABLE staging_import (
    id bigint,
    payload jsonb
);

Unlogged tables can be faster, but they are not crash-safe in the same way as regular tables.

Create a table from a query result

CREATE TABLE top_customers AS
SELECT customer_id,
       SUM(total_amount) AS revenue
FROM public.orders
GROUP BY customer_id;

Create a new table based on an existing one

CREATE TABLE archived_orders (
    LIKE public.orders INCLUDING ALL
);

This is a handy variant when you need the same structure, indexes, and defaults as an existing table.

Check the result

After running CREATE TABLE, verify it:

\dt public.*

Then inspect the full definition:

\d+ public.orders

You can also use SQL queries from Show Tables in PostgreSQL and Describe Table in PostgreSQL.

Create tables visually in DbSchema

DbSchema is useful when you want to design tables visually, preview PostgreSQL SQL, and keep the model synchronized with the live database.

Typical workflow:

1. connect through the PostgreSQL JDBC driver
2. use the diagram editor to add a table and define columns
3. mark primary keys, foreign keys, defaults, and indexes from the table editor
4. preview the generated SQL before execution
5. deploy changes with schema synchronization

DbSchema is especially helpful when you want the SQL and the schema diagram to stay aligned. The connect to database, diagram, and schema documentation pages explain the workflow in more detail.

Add columns and constraints

After the table exists in the model, add columns, choose PostgreSQL data types, and define keys or constraints from the same editor.

FAQ

How do I create a table only if it does not already exist?

Use CREATE TABLE IF NOT EXISTS ....

Should I use SERIAL or IDENTITY in PostgreSQL?

For new tables, prefer identity columns in PostgreSQL 10+ because they are standards-friendly and easier to manage explicitly. SERIAL is still widely used in older schemas.

How do I create a table with a foreign key?

Add a FOREIGN KEY constraint in the CREATE TABLE statement, or add it later with ALTER TABLE. See Foreign Keys in PostgreSQL.

How do I create a table from an existing table?

Use CREATE TABLE new_table (LIKE existing_table INCLUDING ALL) for the structure, or CREATE TABLE AS SELECT ... if you also want copied query results.

How do I see the table after creating it?

Use \dt or \dt schema.* in psql, query information_schema.tables, or refresh the model in DbSchema.

Conclusion

To create a table in PostgreSQL, define the right data types, keys, defaults, and constraints up front. Start with simple syntax, then add identity columns, checks, foreign keys, or IF NOT EXISTS variants as the schema becomes more realistic.

If you want a visual design workflow instead of writing every statement by hand, DbSchema can model the table, generate the PostgreSQL DDL, connect over JDBC, and keep your schema documentation in sync.