Creating Tables in PostgreSQL: Full Guide (with Example)

Creating tables in PostgreSQL is foundational—but like any building block, it only works if it’s solid. Whether you’re using psql or pgAdmin, you need to understand data types, constraints, and schema design before you ever hit CREATE TABLE.

From altering structures to managing constraints, this guide walks you through every step, plus common pitfalls like duplicate keys and out-of-sync sequences.

And because who has the power to create or drop a table matters just as much as how it’s done, secure your PostgreSQL access with fine-grained permissions, full audit trails, and just-in-time access policies that eliminate over-privileged accounts and misconfigurations before they happen.

Prerequisites

Before you start using CREATE TABLE in PostgreSQL, there are several requirements you must meet. 

• First, you need to have PostgreSQL installed on your machine. If you don't, consult the installation guide.
• Next, you have to be familiar with psql or a graphical user interface (GUI) tool like pgAdmin.
• Lastly, you must understand the basics of SQL commands and database schemas to create a table in PostgreSQL. 

What Is a Table in PostgreSQL?

A table in PostgreSQL is a data structure that stores data in rows and columns. Each column has a name. While the number of columns is fixed in a table, the number of rows varies with how much data is contained in the table. 

Every column consists of a data type, which constrains the type of values it can hold. If a column is constrained to be a numerical type, it can’t accept text strings. PostgreSQL does, however, have built-in data types that you can use to organize your data. Examples of these data types include:

• Numeric types: These store numerical data and include smallint, bigint, real, integer, float, and serial.
• Character types: These store almost any kind of data that can’t be used mathematically. They include varchar(n), char(n), and text.  
• Date/time types: These data types handle temporal data and are vital for accurate time tracking, such as logging, scheduling, and historical data analysis. They include time, timestamp, date, and interval. 
• Boolean type: These can be true, false, or null values. 
• JSON and JSONB: These are useful for storing semi-structured data or flexible schemas. json stores JSON data in plain text, while jsonb stores JSON in a binary format for faster querying and indexing.
• UUID (universally unique identifier): Often used as a secure, non-sequential primary key, the uuid is a 128-bit unique identifier that’s highly useful in distributed systems or APIs.

Table constraints and their use

While data types limit the kind of data that goes into columns, constraints let you define narrower conditions in your table and columns. If you attempt to store data that violates a constraint, you will receive an error. 

There are several types of constraints, including:

• Unique constraints: These ensure the data in one or a group of columns is unique from all the rows in the table.
• Check constraints: These are the most generic type of constraint. They let you specify that values in columns must satisfy a Boolean expression (usually a truth value).
• Not-null constraints: These place the condition that the column cannot assume the null value. 
• Primary keys: These constraints show that a column is a unique identifier for rows in the table. They require the values to be null and unique. They implicitly enforce NOT NULL and UNIQUE.
• Foreign keys: These help maintain the referential integrity of two related tables because they specify that values in a column must match the values in a row of another table.

Creating Tables via SQL Interfaces

Now that you understand how tables work and their features, let’s discuss how to create them. 

Using psql

This method relies on using the basic create table in PostgreSQL syntax:

CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... );

Let's create a PostgreSQL table example so you can better understand how this command works. Say you want a table named employees. You want the first column to identify each employee, the second column to store the employees’ names, and the third column to store the date you hired each employee. You can use the following command in the terminal: 

CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), hire_date DATE );

Where: 

• SERIAL automatically generates a number (1, 2, 3, ...) when you add a new record for an employee.
• PRIMARY KEY is the main identifier for each row. It ensures that every value in the first column is unique and not null. 
• VARCHAR(100) indicates that the column with the employees’ names can hold text with up to 100 characters.

Using pgAdmin

To create a PostgreSQL table with pgAdmin, follow the steps below:

1. Launch pgAdmin and enter your password. 
2. Click on Databases and choose your preferred one. 
3. Under your selected database, click Schemas.
4. Under Schemas, right click on the Public option, then hover over the Create option. 
5. Click on Table, and a new window will appear for you to enter your table’s details. 
6. (Optional but recommended) You can also use the GUI tabs to configure constraints, such as primary keys, not-null rules, default values, and unique constraints here—without writing any SQL.
7. Click the Save button once you finish adding the details. 

Altering Existing Tables

PostgreSQL allows you to edit existing tables—they aren’t set in stone. You can make changes like adding or removing columns. 

For instance, say you want to make changes to the employees table we created with the initial CREATE TABLE syntax. If you want to add a new column to store each employee's city with up to 50 characters, use this syntax:

ALTER TABLE employees ADD COLUMN city VARCHAR(50);

If you want to remove the hire date column, this syntax can help you achieve that:

ALTER TABLE employees DROP COLUMN hire_date;

If you want to delete a whole table because you no longer need it, you can use:

DROP TABLE tablename;

or

DROP TABLE IF EXISTS tablename, which is the safer option. 

How do you replace a table in PostgreSQL?

To replace a table in PostgreSQL, you can either:

1. Use DROP TABLE IF EXISTS old_table; followed by CREATE TABLE new_table (...);, or
2. Use CREATE OR REPLACE VIEW if you're dealing with views, not actual tables.

Be cautious, DROP TABLE permanently deletes the table and its data unless you've backed it up.

Common Errors, Troubleshooting Tips, and Best Practices

You may experience several errors when creating and managing tables on PostgreSQL. Most have workarounds for correcting them:

• Relation already exists Error: This can happen if you’re trying to create a table with a name that already exists. To solve this, you can use the IF NOT EXISTS option when creating a table. Alternatively, you can use DROP TABLE IF EXISTS first to delete the existing table, then create your table with your preferred name. 
• Duplicate key value violates unique constraint Error: Seeing this error when the primary key column on your table uses auto-increment means the sequence is out of sync with the data. To solve this, it’s best to avoid manually inserting IDs and let PostgreSQL handle it. You can also fix the sequence using the following command: SELECT setval('employees_id_seq', (SELECT MAX(id) FROM employees));

When creating tables, use best practices to avoid errors. For instance, when naming your tables and columns, ensure you do so clearly and give them unique names to avoid future confusion. You can adopt a naming convention like snake_case (e.g., employee_id, created_at) to help improve readability and maintain consistency across your schema. It also helps to define all the constraints up front rather than after you’ve already created the table. 

StrongDM’s Approach to Secure PostgreSQL Table Creation and Management

Creating PostgreSQL tables is foundational, but in production environments, how you manage access to that power is just as important as writing the correct CREATE TABLE syntax. A misconfigured table or an over-privileged user can open the door to data leaks, broken applications, or compliance failures.

StrongDM takes the guesswork and risk out of PostgreSQL access by giving you centralized control over who can create, alter, or drop tables, along with full audit visibility into every session.

Here's how StrongDM helps secure and streamline PostgreSQL table operations:

Centralized Identity-Based Access

Forget managing native PostgreSQL users with inconsistent permissions. With StrongDM, database access is tied to your identity provider, so access is granted based on verified identity, not shared credentials.

• No more local users with superuser rights
• No more risk from stale accounts or forgotten access paths

Fine-Grained, Role-Aware Permissions

StrongDM enables just-in-time, least-privilege access for actions like CREATE, ALTER, or DROP TABLE.

• Grant temporary permissions to devs for schema changes
• Limit destructive commands to production database administrators only
• Automatically revoke access when projects end or roles change

Session Logging and Audit Trails

Every time someone connects to a PostgreSQL database through StrongDM, the entire session is logged—queries included.

• See who created or modified a table, and when
• Prove compliance with auditable logs
• Detect unusual or risky SQL activity in real time

Simplified Policy Management Across Environments

StrongDM works across all your environments—on-prem, cloud, multi-cloud—so you can enforce consistent access and security policies across dev, staging, and prod.

• Use the same access policy everywhere
• Eliminate drift between environments
• Simplify onboarding and offboarding across teams

StrongDM ensures that only the right people have the right access, at the right time, with the right visibility. So whether someone is creating a table via psql, modifying one in pgAdmin, or dropping tables via a script, you're always in control. No more over-privileged accounts. No more security guesswork. Just clean, auditable access to your PostgreSQL databases.

Ready to make secure table creation your default? Get a demo of StrongDM.