INSERT | PostgreSQL Tutorial

The INSERT statement is how you add data to a PostgreSQL table. You name the table, list the columns you want to populate, and supply corresponding values. PostgreSQL inserts exactly one row per VALUES list and returns a command tag (INSERT 0 1) confirming success.

Basic INSERT syntax

The minimal form specifies the table, a column list, and a value list in matching order:

INSERT INTO links (url, name)
VALUES ('https://neon.com/postgresql', 'PostgreSQL Tutorial');

Output:

INSERT 0 1

Columns omitted from the list receive their default value — NULL if no default is defined. Columns declared NOT NULL without a default must appear in the column list or the statement will error. SERIAL columns generate their value automatically, so you skip them entirely.

To insert a string containing a single quote, escape it by doubling the quote character:

INSERT INTO links (url, name)
VALUES ('http://www.oreilly.com', 'O''Reilly Media');

To insert a date value, use the 'YYYY-MM-DD' format:

INSERT INTO links (url, name, last_update)
VALUES ('https://www.google.com', 'Google', '2013-06-01');

Using the RETURNING clause

PostgreSQL extends standard SQL with a RETURNING clause that returns column values from the newly inserted row — useful for retrieving auto-generated primary keys without a separate query:

INSERT INTO links (url, name)
VALUES ('https://www.postgresql.org', 'PostgreSQL')
RETURNING id;

Output:

 id
----
  4
(1 row)

You can return any column, an expression, or every column with RETURNING *. Use AS to rename the returned value:

INSERT INTO links (url, name)
VALUES ('https://example.com', 'Example')
RETURNING id AS new_id, name AS inserted_name;

Practical tips

Always specify an explicit column list. Relying on column order makes the statement fragile against future schema changes.
Wrap related inserts in a transaction so either all succeed or none do, preventing partial state.
Use RETURNING id instead of a follow-up SELECT to retrieve the new primary key — it is atomic and avoids a round-trip.
For bulk data loading, consider COPY instead of many individual INSERT statements — it is significantly faster for large datasets.
If you need to insert or update based on a conflict, look at INSERT ... ON CONFLICT (upsert) rather than separate INSERT/UPDATE logic.

Reference: PostgreSQL documentation — INSERT.

Frequently Asked Questions

What does INSERT 0 1 mean in PostgreSQL?

The command tag INSERT oid count is returned after every INSERT. The first number is the OID (object identifier), which is always 0 for regular tables. The second number is the count of rows inserted. So INSERT 0 1 means one row was inserted successfully.

How do I get the ID of a row I just inserted?

Use the RETURNING clause: INSERT INTO table (col) VALUES (val) RETURNING id; This returns the generated id in the same statement without a second query.

What happens if I omit a NOT NULL column in an INSERT?

PostgreSQL raises an error: "null value in column violates not-null constraint". You must supply a value for every NOT NULL column that does not have a DEFAULT defined.

How do I insert a string that contains a single quote?

Escape the single quote by doubling it. For example, to insert O'Reilly write the value as 'O''Reilly'. Alternatively, use dollar-quoting: $$O'Reilly$$.

Can I insert a row and immediately use it in another statement?

Yes. Use a data-modifying CTE: WITH inserted AS (INSERT INTO t (col) VALUES (val) RETURNING id) INSERT INTO audit (ref_id) SELECT id FROM inserted; This chains the INSERT results directly into the next operation within a single transaction.