36

In my LiveCode Server app I'm getting a dberr returned on insertion but no explicit error code.

I went on a terminal and did the insertion by hand as user Postgres.

%My_Dbase=# INSERT INTO new-table (first_name, last_name, anonymous) VALUES ('batman', 'Moonboy', TRUE);

The psql process returns:

INSERT 0 1

What does this line mean? Besides the primary table I also have a sequence to increment the primary key ID (int) of the main table.

If I check the data, the data is inserted, the primary key is increment by one and everything seems fine, I'm not sure why my app is returning an error (could be a bug in the app or my code).

But if I knew what INSERT 0 1 meant, that would help me assure myself that:

  1. Yes, the insertion was done without errors, or
  2. No, the 0 1 indicates an error of some sort.

If anyone has a link to the PostgreSQL doc which tells what these server response params are, I will study it... I have looked everywhere.

tshepang
  • 12,111
  • 21
  • 91
  • 136
katir
  • 361
  • 1
  • 3
  • 3

2 Answers2

49

Excerpt from the relevant page in the manual:

Outputs

On successful completion, an INSERT command returns a command tag of the form

INSERT oid count

The count is the number of rows inserted. If count is exactly one, and the target table has OIDs, then oid is the OID assigned to the inserted row. Otherwise oid is zero.

Community
  • 1
  • 1
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 7
    As an addendum to this answer, assuming you have a field called "id" in your table that is auto-assigned via sequence, if you run your insert in the form "INSERT INTO new-table (first_name, last_name, anonymous) VALUES ('batman', 'Moonboy', TRUE) RETURNING id", the message "INSERT 0 1" would be replaced by the new value of id in the just-inserted record. – Matthew Wood Oct 01 '10 at 14:54
  • Do you guys know what would `INSERT 0 0` mean in this context? I don't get any error, just that. – jayarjo May 28 '20 at 10:17
  • 1
    @jayarjo - it means that your insert failed (the second 0) – Ray Jennings Sep 20 '21 at 15:14
12

EDIT: I've been pointed at the documentation for this, https://www.postgresql.org/docs/14/protocol-message-formats.html

The format is COMMAND OID ROWS.

As OIDs system columns are not supported anymore, OID is always zero.

So what you are seeing is that you've done an insert, the OID was zero, and one row was inserted.

So in other words, your command is completing successfully!

SamStephens
  • 5,721
  • 6
  • 36
  • 44
  • outstanding, thank you, I subsequently determined that. I mean if I see the data in phpPGsql admin... I had to assume it was working. @matthew: that tip on returning the primary id key is very useful... I do have one, though it is name "donation_id" and is auto incremented via an associated sequence. – katir Oct 02 '10 at 18:56
  • What would `INSERT 0 0` mean then? Like - `successfully inserted nothing`? Get these a lot recently. – jayarjo May 28 '20 at 10:15
  • 1
    @jayarjo it could be worth asking a new stackoverflow question. However searching Google for "INSERT 0 0" in quotes finds me https://pgdash.io/blog/postgres-insert.html , which talks how ON CONFLICT [...] DO NOTHING will return "INSERT 0 0" for a conflict. So it does indeed look like it means the insert operation suceeded, and no rows were inserted. I don't know if there are cases other than ON CONFLICT when this can happen, I haven't used postgres for a long time. – SamStephens May 30 '20 at 03:26
  • 1
    I know this is an old post, but I've applied strikethrough to the incorrect inference that the first number in `INSERT 0 1` is a status code. This is a CommandComplete string, as mentioned in the other answer and documented: https://www.postgresql.org/docs/14/protocol-message-formats.html This first number may be an OID for the inserted row, but is not a status code. – bitoffdev Oct 20 '21 at 17:47
  • 1
    Thanks @bitoffdev . It could have been worth keeping the rows bit though, because that's still true, and was the useful bit of that incorrect inference. I'll fix it up now. – SamStephens Oct 21 '21 at 19:53
  • 1
    Appreciate the quick response; thanks @SamStephens! :) – bitoffdev Oct 21 '21 at 20:33