Insert Data into PostgreSQL

You’ve designed your tables, wrestled with data types, and maybe even created a few rows along the way. But now comes the real test: getting your data into PostgreSQL reliably and efficiently. The INSERT statement is the doorway to your database, and mastering it means the difference between a smooth, performant application and a debugging nightmare. In this lesson, you’ll learn to insert data into PostgreSQL tables like a pro—from single rows to bulk inserts, with the tricks that save you time and headaches.

The problem this lesson solves

Every database application starts the same way: you have data, and you need to store it. The INSERT statement is how you get data into your PostgreSQL tables, and it’s one of the first commands you’ll use—yet many developers only scratch the surface. They copy a single-line example from a blog post, change the column names, and cross their fingers. Then reality hits:

• Your insert fails with a cryptic ERROR: null value in column message.
• You’re inserting 10,000 rows one at a time, and your app is crawling.
• You can’t figure out how to get the auto-generated ID back after an insert.
• You accidentally insert duplicate rows because you missed the ON CONFLICT clause.

These are not theoretical problems. They’re daily struggles for developers who don’t fully understand how INSERT works. By the end of this lesson, you’ll be able to insert data into PostgreSQL tables confidently, using the full power of the statement—from simple single-row inserts to efficient multi-row and conditional inserts. You’ll also know how to avoid the most common pitfalls that trip up beginners and even experienced users.

Core concept / mental model

Think of a PostgreSQL table as a well-organized spreadsheet with strict rules. Each column has a defined data type, and every row is a complete record. The INSERT statement is your way of adding new rows to that spreadsheet, one or many at a time. But unlike a casual spreadsheet, PostgreSQL enforces the rules: types must match, required columns must be filled, and constraints must be satisfied.

The mental model for INSERT is simple:

1. You name the target table – Where do you want the data to go?
2. You list the columns – Which columns are you providing values for? (You don’t have to include every column; omitted ones get defaults.)
3. You provide the values – For each listed column, you supply a value that matches its data type.
4. PostgreSQL validates and inserts – The database checks types, constraints, and defaults before adding the row.

Here’s the basic syntax, which you’ll get to know intimately:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

If you omit the column list, PostgreSQL assumes you’re providing values for all columns in the table’s defined order. That’s risky—if the table schema changes, your insert breaks. Always list columns explicitly; it’s a habit that will save you countless bugs.

For multi-row inserts, you chain value groups with commas:

INSERT INTO table_name (col1, col2) VALUES (v1a, v2a), (v1b, v2b), ...;

And for returning the generated values (like auto-increment IDs), add the RETURNING clause:

INSERT INTO ... VALUES ... RETURNING id;

That’s the core. Everything else—ON CONFLICT, DEFAULT VALUES, and data type nuance—builds on this foundation.

How it works step by step

Let’s say you have a simple users table created earlier in this track:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now()
);

Here’s what happens when you insert a row, step by step:

1. Parse the statement – PostgreSQL reads your INSERT and figures out the target table and columns.
2. Resolve defaults – For any column you didn’t mention, PostgreSQL uses its default value (e.g., created_at gets now()). If a column has no default and is NOT NULL, the insert fails.
3. Validate types – Each value is checked against the column’s type. A string like '123' might be coerced to an integer, but a malformed date will raise an error.
4. Check constraints – Unique, primary key, check, and foreign key constraints are enforced. A duplicate email, for example, causes a unique violation.
5. Write the row – The row is inserted into the table. In a transaction, you can roll back later; otherwise, it’s permanent.
6. Return results – If you used RETURNING, PostgreSQL returns the specified columns (like the new id) to the caller.

Role of the target table

The table name is the first part of the statement. You can also qualify it with a schema: INSERT INTO public.users. If the table is in your search_path, the schema is optional, but being explicit is good practice in multi-schema databases.

Column list vs. values

Always listing columns is more than a convention—it makes your insert self-documenting and resilient. Compare these two statements:

-- Fragile: relies on column order
INSERT INTO users VALUES (DEFAULT, 'alice', 'alice@example.com', DEFAULT);

-- Robust: explicit column mapping
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

The second version will keep working even if you add a new column with a default. The first one will break or, worse, silently insert wrong data if the schema changes.

How multi-row inserts work

PostgreSQL allows multiple VALUES groups in one statement. This is not just a convenience—it’s a performance win. A single multi-row insert is implemented as a single transaction and avoids the round-trip overhead of many separate INSERT statements.

INSERT INTO users (username, email)
VALUES
  ('bob', 'bob@example.com'),
  ('carol', 'carol@example.com'),
  ('dave', 'dave@example.com');

The RETURNING clause

When you insert a row with an auto-generated ID (like SERIAL), you often need that ID back for your application. RETURNING lets you fetch it in the same round-trip.

INSERT INTO users (username, email)
VALUES ('eve', 'eve@example.com')
RETURNING id, created_at;

This is especially useful when you need to reference the new row in subsequent operations (e.g., creating a related order).

Hands-on walkthrough

Time to get your hands dirty. We’ll use the psql command-line tool, but you can adapt these examples to any PostgreSQL client (Python, Node.js, etc.). First, ensure you’re connected to a database where the users table exists (or create it with the snippet above).

Example 1: Insert a single row

INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');

Expected output (psql):

INSERT 0 1

The 1 means one row was inserted. Notice we didn’t supply id or created_at; PostgreSQL generated them automatically.

Example 2: Insert multiple rows

INSERT INTO users (username, email)
VALUES
  ('bob', 'bob@example.com'),
  ('carol', 'carol@example.com'),
  ('dave', 'dave@example.com');

Output:

INSERT 0 3

All three rows are inserted in one transaction. If any row fails, the entire statement is rolled back—no partial inserts.

Example 3: Return the inserted values

INSERT INTO users (username, email)
VALUES ('eve', 'eve@example.com')
RETURNING id, username, created_at;

Output:

 id | username |          created_at          
----+----------+-------------------------------
  4 | eve      | 2025-03-14 10:23:45.678901+00
(1 row)

The RETURNING clause is your best friend when you need the generated ID for further operations.

Example 4: Using DEFAULT and ON CONFLICT (upsert)

INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com')
ON CONFLICT (email) DO UPDATE
SET username = EXCLUDED.username
RETURNING id, username;

If alice@example.com already exists, this updates her username instead of throwing an error. This is called an upsert—it combines insert and update into a single atomic operation.

Compare options / when to choose what

Now that you’ve seen the main ways to insert data into PostgreSQL tables, let’s compare them. Each approach has trade-offs in performance, usability, and feature set. The table below shows the key differences.

Performance considerations

• Multi-row insert is typically faster than many single-row inserts because it minimizes round trips and reuses the same execution plan. For batches up to a few thousand rows, it’s your best SQL-only option.
• COPY is the king for bulk data loads—it’s optimized for streaming rows and can be orders of magnitude faster than even multi-row inserts for huge datasets. Use it when you need to load a CSV file or export data.
• INSERT ... SELECT lets you combine data from another table, which is handy for migrations or building staging tables.

When to use ON CONFLICT

If your table has a unique constraint (like email), and you might insert a duplicate, ON CONFLICT prevents a hard error. You can choose to:

• DO NOTHING – Skip the duplicate silently.
• DO UPDATE – Update the existing row with new values (upsert).

This is perfect for syncing data from external sources where duplicates are possible.

Alternatives: COPY vs. INSERT

For huge data imports, COPY is your friend. Here’s a quick example:

COPY users (username, email) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER true);

But COPY is a separate command, not an INSERT. For most application logic, you’ll stick with INSERT. Know when to switch: if you’re loading more than, say, 50,000 rows, consider COPY.

Troubleshooting & edge cases

Even with a solid understanding, things go wrong. Here are the most common errors and edge cases when inserting data into PostgreSQL tables, with fixes.

ERROR: null value in column ... violates not-null constraint

You tried to insert a row without a value for a NOT NULL column. Solution: provide a value or make the column nullable.

-- Wrong (if username is NOT NULL)
INSERT INTO users (email) VALUES ('nouser@example.com');

-- Right
INSERT INTO users (username, email) VALUES ('nouser', 'nouser@example.com');

ERROR: duplicate key value violates unique constraint

You inserted a row with a duplicate value in a unique column (like email). Fix: use ON CONFLICT to handle it, or check before inserting.

INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com')
ON CONFLICT (email) DO NOTHING;

ERROR: invalid input syntax for type integer

You passed a string where a number is expected. PostgreSQL tries to coerce, but unparseable strings fail. Check your types and cast explicitly if needed.

-- Wrong
INSERT INTO products (price) VALUES ('abc');

-- Right
INSERT INTO products (price) VALUES (19.99);

INSERT 0 0 but no error

This is actually expected if you use ON CONFLICT DO NOTHING and the row was skipped. Don’t panic—check your RETURNING output or query the table to verify.

Edge case: mutable defaults

If you rely on defaults, be careful with functions like now()—they’re evaluated at insert time, not at statement parse time. That’s usually what you want, but it means the value is fixed once the row is written.

Pro tip: Always use RETURNING when you need the generated values (like IDs). It saves an extra SELECT and avoids race conditions in concurrent applications.

What you learned & what's next

Let’s recap what you’ve mastered in this lesson:

• You can now insert data into PostgreSQL tables using single-row, multi-row, and conditional inserts.
• You understand the core INSERT syntax and the importance of an explicit column list.
• You know how to return inserted values with the RETURNING clause to get auto-generated IDs.
• You can handle conflicts gracefully with ON CONFLICT to avoid duplicate-key errors.
• You’re aware of performance options like multi-row inserts and COPY for bulk loads.
• You can troubleshoot common insertion errors like null constraints, type mismatches, and duplicate keys.

You’ve hit every learning objective: you explained the core idea behind inserting data, and you completed a practical exercise that will stick with you.

Next in the track, you’ll learn how to query your inserted data with SELECT statements—turning raw rows into meaningful insights. You’ll also explore UPDATE and DELETE to keep your data current, and eventually tackle joins and aggregations. But for now, take a moment to practice inserting data into your own tables. The more you type INSERT, the more natural it becomes. Ready to query? Let’s move forward.