What Does INSERT 0 1 Actually Tell You?

If you’ve ever run an insert statement in a terminal or an IDE, you’ve seen it: the cryptic insert 0 1 message. While it looks like a bit of ancient binary, it’s actually a precise status report from the database engine.

The Anatomy of a Command Tag 🔗

In PostgreSQL, every successful command returns a “Command Tag.” For an insertion, the format is: INSERT [oid] [rows]

• The “0” (oid¹): Historically, Postgres could assign an internal Object ID to every row. Since version 12, this feature has been completely removed for user tables, which is why you always see a 0 today.
• The “1” (rows): This is the actual number of rows processed by your query.

Under the Hood 🔗

If you are curious about the source code, this message is built across a few files in the PostgreSQL backend. The heart of it lives in src/backend/tcop/cmdtag.c, in a function called BuildQueryCompletionString.

First, each command tag is declared in src/include/tcop/cmdtaglist.h:

PG_CMDTAG(CMDTAG_INSERT, "INSERT", false, false, true)

That last true means “display the row count in the completion string”.

Then, BuildQueryCompletionString assembles the final string. Here’s the relevant extract from src/backend/tcop/cmdtag.c:

/*
 * In PostgreSQL versions 11 and earlier, it was possible to create a
 * table WITH OIDS.  When inserting into such a table, INSERT used to
 * include the Oid of the inserted record in the completion tag.  To
 * maintain compatibility in the wire protocol, we now write a "0" (for
 * InvalidOid) in the location where we once wrote the new record's Oid.
 */
if (command_tag_display_rowcount(tag) && !nameonly)
{
    if (tag == CMDTAG_INSERT)
    {
        *bufp++ = ' ';
        *bufp++ = '0';       /* legacy OID field, always 0 now */
    }
    *bufp++ = ' ';
    bufp += pg_ulltoa_n(qc->nprocessed, bufp);  /* the row count */
}

Have you seen how the PostgreSQL project documents such a changing of behaviour between Postgres 12 and earlier versions, which in its turn explains why we keep the value 0 ?

So, how does it work? It starts with the tag name INSERT, appends 0 (a hardcoded zero for the legacy OID, kept for wire protocol compatibility with PostgreSQL 11 and earlier), then appends a space and the number of rows processed. That gives us our INSERT 0 1.

Once the string is built, EndCommand in src/backend/tcop/dest.c sends it to the client as a CommandComplete protocol message:

len = BuildQueryCompletionString(completionTag, qc, force_undecorated_output);
pq_putmessage(PqMsg_CommandComplete, completionTag, len + 1);

On the client side, psql picks it up via PQcmdStatus() and prints it to your terminal in PrintQueryStatus (src/bin/psql/common.c).

Real-World Examples 🔗

To test this, let’s create a table users.

create table users (
    id int generated always as identity primary key,
    name text not null,
    email text unique,
    created_at timestamptz default now()
);

A Simple Insert 🔗

insert into users (name, email) 
values ('alice', 'alice@example.com');
-- output: INSERT 0 1

Result: 1 new row was created.

Inserting Multiple Rows 🔗

insert into users (name, email)
values
  ('bob', 'bob@example.com'),
  ('charlie', 'charlie@example.com');
-- output: INSERT 0 2

Result: 2 new rows were created.

The “Upsert” (on conflict) 🔗

This is a common source of confusion. When you use on conflict do update, Postgres still returns an insert tag even if an update occurred.

insert into users (name, email)
values ('alice', 'alice@example.com')
on conflict (email)
do update set name = excluded.name;
-- output: INSERT 0 1

Result: Even though no new row was created (it was an update), the total count of processed rows is 1.

Conclusion 🔗

insert 0 1 is simply Postgres-speak for “Mission accomplished: 1 row handled.”