How to Insert Data into a Table in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Insert Data into a Table in PostgreSQL

Once you’ve got a table built, the next step is putting actual data into it. PostgreSQL’s INSERT statement is the command responsible for that, and while the basic form is simple, there’s a lot of useful depth to it — inserting multiple rows at once, handling conflicts, returning inserted values, and inserting data derived from other queries. This guide covers all of it with practical, real-world examples.

Basic INSERT Syntax

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

A concrete example, using a customers table:

INSERT INTO customers (name, email)
VALUES ('Sarah Johnson', 'sarah.johnson@example.com');

If the table has an auto-incrementing primary key (like SERIAL or BIGSERIAL), you don’t need to specify it — PostgreSQL generates it automatically.

Inserting Without Specifying Column Names

You can omit the column list entirely, but then you must provide values for every column, in the exact order they were defined in the table:

INSERT INTO customers
VALUES (DEFAULT, 'Sarah Johnson', 'sarah.johnson@example.com');

Here, DEFAULT tells PostgreSQL to use the column’s default value (in this case, the auto-generated ID) rather than a specific value. This approach works, but it’s fragile — if the table structure changes later, this statement can break or silently insert data into the wrong columns. Explicitly naming columns is almost always the safer habit.

Inserting Multiple Rows at Once

Rather than running separate INSERT statements for each row, you can insert several in a single statement:

INSERT INTO customers (name, email)
VALUES
    ('Sarah Johnson', 'sarah.johnson@example.com'),
    ('Michael Chen', 'michael.chen@example.com'),
    ('Priya Patel', 'priya.patel@example.com');

This is significantly faster than multiple individual INSERT statements when loading batches of data, since it reduces the number of round trips between your application and the database.

Using DEFAULT Values

If a column has a default defined (like created_at TIMESTAMPTZ DEFAULT NOW()), you can simply skip it in your insert, and PostgreSQL fills it in automatically:

INSERT INTO orders (customer_id, total_amount)
VALUES (1, 49.99);

Assuming status defaults to 'pending' and created_at defaults to NOW(), both get populated without needing to specify them explicitly.

Returning Inserted Data with RETURNING

One of PostgreSQL’s more convenient features is the RETURNING clause, which gives you back data from the row you just inserted — most commonly the auto-generated ID — without needing a separate SELECT query.

INSERT INTO customers (name, email)
VALUES ('Sarah Johnson', 'sarah.johnson@example.com')
RETURNING id;

Output:

 id
----
  1
(1 row)

You can return specific columns, all columns, or computed expressions:

INSERT INTO orders (customer_id, total_amount)
VALUES (1, 49.99)
RETURNING id, created_at;

Or return every column:

INSERT INTO customers (name, email)
VALUES ('Michael Chen', 'michael.chen@example.com')
RETURNING *;

This is especially useful in application code, where you often need the newly created record’s ID immediately to use in subsequent operations.

Inserting Data from Another Query

Instead of literal values, you can insert the results of a SELECT statement, which is useful for copying or transforming data between tables.

INSERT INTO customers_archive (name, email)
SELECT name, email FROM customers
WHERE created_at < '2023-01-01';

This copies every customer created before 2023 into an archive table in a single operation, no loop required.

Handling Conflicts with ON CONFLICT (Upserts)

A very common real-world need is inserting a row, but doing something different if a conflicting row (usually based on a unique constraint) already exists. PostgreSQL handles this with ON CONFLICT, commonly called an “upsert” (insert or update).

Do Nothing on Conflict

INSERT INTO customers (email, name)
VALUES ('sarah.johnson@example.com', 'Sarah Johnson')
ON CONFLICT (email) DO NOTHING;

If a customer with that email already exists, this statement simply does nothing instead of throwing a duplicate key error.

Update on Conflict

INSERT INTO customers (email, name)
VALUES ('sarah.johnson@example.com', 'Sarah J. Johnson')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

Here, EXCLUDED refers to the row that was proposed for insertion but conflicted — so this statement says “if a customer with this email already exists, update their name to the new value instead of failing.”

This pattern is extremely common for syncing data from external sources, where you don’t know in advance whether a record already exists.

Conditional Updates on Conflict

You can add a WHERE clause to only update under certain conditions:

INSERT INTO products (sku, price, updated_at)
VALUES ('SKU123', 29.99, NOW())
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price, updated_at = NOW()
WHERE products.price IS DISTINCT FROM EXCLUDED.price;

This only performs the update if the price has actually changed, avoiding unnecessary writes (and unnecessary trigger firing, if triggers are attached to the table).

Inserting JSON Data

If a column is typed as JSON or JSONB, you can insert JSON directly as a string literal:

INSERT INTO events (event_type, payload)
VALUES ('user_signup', '{"user_id": 42, "source": "referral"}');

PostgreSQL validates that the string is well-formed JSON at insert time and will reject malformed JSON with an error.

Inserting Array Data

For array-typed columns:

INSERT INTO products (name, tags)
VALUES ('Wireless Mouse', ARRAY['electronics', 'accessories', 'wireless']);

Or using the alternate curly-brace syntax:

INSERT INTO products (name, tags)
VALUES ('Wireless Mouse', '{electronics,accessories,wireless}');

Bulk Loading Large Amounts of Data with COPY

For loading genuinely large datasets — thousands or millions of rows — INSERT statements, even batched ones, become slow. PostgreSQL’s COPY command is dramatically faster for bulk loading:

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

From the command line, using psql‘s \copy (useful when the file lives on your local machine rather than the database server):

psql -U postgres -d mydatabase -c "\copy customers(name, email) FROM 'customers.csv' WITH (FORMAT csv, HEADER true)"

For anything beyond a few hundred rows, COPY is the right tool rather than looping INSERT statements in application code.

Practical Examples

Inserting a new order with a returned ID for further processing

INSERT INTO orders (customer_id, total_amount, status)
VALUES (5, 129.50, 'pending')
RETURNING id;

Bulk inserting seed data for a new application

INSERT INTO categories (name) VALUES
    ('Electronics'),
    ('Clothing'),
    ('Home & Garden'),
    ('Books');

Syncing a record from an external API (upsert pattern)

INSERT INTO users (external_id, email, name)
VALUES ('ext_9981', 'user@example.com', 'Alex Kim')
ON CONFLICT (external_id)
DO UPDATE SET email = EXCLUDED.email, name = EXCLUDED.name;

Copying filtered data into a reporting table

INSERT INTO monthly_sales_report (customer_id, total_amount, month)
SELECT customer_id, SUM(total_amount), DATE_TRUNC('month', created_at)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id, DATE_TRUNC('month', created_at);

Common Use Cases

Seeding a database with initial data. New applications typically need reference data (categories, roles, default settings) inserted right after the schema is created.

Application-driven inserts. Most inserts in a running application come from user actions — signing up, placing an order, submitting a form — usually one row at a time with RETURNING used to get the new ID back.

Data synchronization and ETL. Pulling data from external systems (APIs, other databases, spreadsheets) and inserting it, often with ON CONFLICT to handle records that may already exist.

Bulk data migration. Moving large volumes of data between systems, typically using COPY for performance rather than individual INSERT statements.

Troubleshooting Common Errors

ERROR: duplicate key value violates unique constraint. You’re trying to insert a value that conflicts with a UNIQUE or PRIMARY KEY constraint. Use ON CONFLICT if this is expected behavior you want to handle gracefully, or check your data for actual duplicates.

ERROR: null value in column "column_name" violates not-null constraint. You’re missing a required value. Either provide one, or check whether the column should actually have a default value or allow nulls.

ERROR: insert or update on table violates foreign key constraint. You’re trying to insert a value (like a customer_id) that doesn’t exist in the referenced table. Double-check that the referenced row actually exists before inserting.

ERROR: invalid input syntax for type integer. A type mismatch — you’re likely passing a string where a number is expected, or vice versa. Check your data types match the column definitions.

Slow performance on large batch inserts. If you’re inserting thousands of rows through individual INSERT statements, switch to either a single multi-row INSERT, or better, COPY for genuinely large datasets. Wrapping many inserts in a single transaction also helps significantly, since it avoids the overhead of committing after every single row.

Best Practices

Always specify column names explicitly rather than relying on positional ordering — it’s more readable and safer against future schema changes.
Use RETURNING instead of a follow-up SELECT when you need data back from an insert — it saves a round trip and guarantees you get exactly the row you just inserted.
Reach for ON CONFLICT instead of checking existence with a separate query first — it’s both faster and avoids race conditions in concurrent environments.
Use COPY for bulk loading rather than looping individual inserts, especially for anything beyond a few hundred rows.
Wrap multiple related inserts in a transaction (BEGIN / COMMIT) so they either all succeed or all fail together, keeping your data consistent.
Validate data at the application layer where practical, but don’t rely on it exclusively — database constraints are your last line of defense against bad data.

Inserting into Tables with Generated or Computed Columns

If a table has a generated column (as covered in the table creation guide), you cannot insert a value into it directly — PostgreSQL computes it automatically:

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

INSERT INTO order_items (quantity, unit_price)
VALUES (3, 9.99);

Trying to explicitly insert a value for total_price here would raise an error, since PostgreSQL owns that column’s value entirely.

Multi-Row Inserts with RETURNING

RETURNING isn’t limited to single-row inserts — it works just as well with multi-row inserts, returning one row of output for every row inserted:

INSERT INTO categories (name)
VALUES ('Electronics'), ('Clothing'), ('Books')
RETURNING id, name;

Output:

 id |    name
----+-------------
  1 | Electronics
  2 | Clothing
  3 | Books
(3 rows)

This is particularly handy when seeding data programmatically and needing to map the new IDs back to application-side references immediately.

Inserting with Explicit Type Casting

Sometimes PostgreSQL needs a nudge to interpret a literal value as the correct type, especially with more specific types like UUID, JSONB, or custom enum types:

INSERT INTO sessions (id, user_id, metadata)
VALUES (
    gen_random_uuid(),
    42,
    '{"device": "mobile", "ip": "203.0.113.5"}'::jsonb
);

Note that gen_random_uuid() requires the pgcrypto extension to be enabled (CREATE EXTENSION IF NOT EXISTS pgcrypto;), or you can use the built-in gen_random_uuid() function directly in PostgreSQL 13 and newer without any extension, since it was moved into core.

Combining INSERT with Common Table Expressions

For more complex insert logic, a CTE can prepare data before it’s inserted:

WITH new_customer AS (
    INSERT INTO customers (name, email)
    VALUES ('Jordan Lee', 'jordan.lee@example.com')
    RETURNING id
)
INSERT INTO customer_preferences (customer_id, newsletter_opt_in)
SELECT id, true FROM new_customer;

This inserts a new customer and immediately uses their freshly generated ID to insert a related row into a different table — all in a single atomic statement, guaranteeing both inserts succeed or fail together.

Performance Considerations for Bulk Inserts

Beyond just using multi-row INSERT statements or COPY, a few other techniques matter when loading larger volumes of data:

Wrap batches in a transaction. Each individual INSERT outside an explicit transaction is automatically wrapped in its own implicit transaction, which has commit overhead. Wrapping many inserts together reduces that overhead substantially:

BEGIN;
INSERT INTO logs (message) VALUES ('event 1');
INSERT INTO logs (message) VALUES ('event 2');
-- ... many more
COMMIT;

Temporarily disable indexes and constraints for very large bulk loads. For genuinely massive one-time imports (millions of rows), it can be faster to drop non-essential indexes, load the data, then recreate the indexes afterward, since maintaining indexes during each insert adds up. This isn’t necessary for everyday inserts, but it’s a real technique for large data migrations.

Consider UNLOGGED tables for temporary bulk loads. If you’re loading data into a staging table that doesn’t need crash-safety guarantees (because it can simply be reloaded if lost), an unlogged table skips write-ahead logging overhead:

CREATE UNLOGGED TABLE staging_import (
    raw_data JSONB
);

This trades durability for speed, so it’s only appropriate for genuinely disposable, intermediate data.

Practical Example: Full Application Signup Flow

Bringing several concepts together, here’s a realistic pattern for a user signup that touches two related tables atomically:

BEGIN;

WITH new_user AS (
    INSERT INTO users (email, password_hash)
    VALUES ('new.user@example.com', '$2b$12$examplehashvalue')
    RETURNING id
)
INSERT INTO user_profiles (user_id, display_name, created_at)
SELECT id, 'New User', NOW() FROM new_user
RETURNING user_id;

COMMIT;

Wrapping this in an explicit transaction ensures that if the second insert somehow fails (say, due to a constraint violation), the first insert is rolled back too, avoiding an orphaned user record with no associated profile.

Frequently Asked Questions

What happens if I insert a row that violates a CHECK constraint? PostgreSQL rejects the entire insert with an error describing which constraint failed, and no row is added. This applies per-statement — if you’re doing a multi-row insert and one row violates a constraint, by default the entire statement fails and none of the rows are inserted (unless you’re using ON CONFLICT for conflict-specific handling, which is a different kind of failure than a CHECK violation).

Can I insert data into a view? In some cases, yes — PostgreSQL supports inserting into simple, “updatable” views automatically, and more complex views can be made insertable using INSTEAD OF triggers. This is a more advanced pattern, generally used when you want to present a simplified interface over a more complex underlying table structure.

Is INSERT ... ON CONFLICT the same as MERGE? They overlap in purpose but aren’t identical. ON CONFLICT is specifically for handling a single insert that might collide with existing data. PostgreSQL (as of version 15) also supports a standard MERGE statement, which is more general-purpose and can handle inserting, updating, and deleting in a single statement based on a join condition against a source dataset — useful for more complex synchronization logic than a simple upsert.

Why does my multi-row insert with ON CONFLICT skip more rows than expected? Double-check that your ON CONFLICT target column actually has a UNIQUE or PRIMARY KEY constraint — ON CONFLICT only works against columns with an existing uniqueness guarantee, and you’ll get an error if you try to target a column without one.

Wrapping Up

INSERT is the command that actually puts data into your carefully designed tables, and PostgreSQL gives you a lot of flexibility around it — from simple single-row inserts to sophisticated upsert logic with ON CONFLICT, and from ordinary application inserts to genuinely high-performance bulk loading with COPY. Getting comfortable with RETURNING and ON CONFLICT in particular will save you a meaningful amount of extra application code down the line. From here, the next natural steps are querying that data back out with SELECT, and eventually updating and deleting it as your application evolves.