What does the return of the INSERT 0 1 bank mean?

Asked

Viewed 758 times

7

When I perform commands in the bank I have returns to and execution of the same.

Doubt

  • What does the 0 in INSERT 0 1?

Example

CREATE TABLE test(
    id SERIAL,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

input : INSERT INTO test (name) VALUES ('Guilherme');
output : INSERT 0 1

input : INSERT INTO test (name) VALUES ('Guilherme'), ('Felipe'), ('Carlos');
output : INSERT 0 3

The second parameter has already noticed that it is rows effectively inserted.

Addendum

The same does not exist when it comes to UPDATE

input : UPDATE test SET name = 'guilherme' WHERE name = 'Guilherme'
output : UPDATE 1

postgresql sql-insert

  • I found an explanation here on forum of Postgre that can be useful @Guilherme! In translation it says the following: Newer & #Xa; Postgresql versions by default have tables created without OIDs on the lines, then you just get a 0 returned instead.

  • @Not Everson, I even researched about it, but I didn’t go into it to say that I know.

  • If you send a duplicate value in this Index, the output is: 1068, 0?

  • @rray no, it’s still INSERT 0 1;

2 answers

6


Extracted from the page in the manual :

Exits

On successful completion of a command INSERT it returns a form command tag:

INSERT [oid] [Count]

The [Count] count is the number of rows entered. If the [Count] count is exactly A and the target table has Oids enabled, the OID is assigned to the inserted row. Otherwise OID is zero.

Oids are used internally by Postgresql as primary keys in various system tables. It would be almost the same thing as ROWID oracle.

Exemplifying:

OID will appear only if you INSERT A LINE. Because the return is singular (single Return), so it is shown 0 for inserting many records, but behind it is generated the OID for each record inserted in the table. (unless WITHOUT OIDS is specified in the table creation, or the default_with_oids configuration variable is set to false.)

Running some INSERT’s:

INSERT INTO t VALUES ('x', 'x', 'x');
INSERT INTO t VALUES ('x', 'x', 'y');
INSERT INTO t VALUES ('x', 'y', 'x');
INSERT INTO t VALUES ('x', 'x', 'y');
INSERT INTO t VALUES ('x', 'x', 'y');
INSERT INTO t VALUES ('x', 'y', 'y');
INSERT INTO t VALUES ('y', 'y', 'y'); 
INSERT INTO t VALUES ('y', 'y', 'y');

SELECT oid, t.* FROM t;

  oid  | c1 | c2 | c3
-------+----+----+----
 17839 | x  | x  | x
 17840 | x  | x  | y
 17841 | x  | y  | x
 17842 | x  | x  | y
 17843 | x  | x  | y
 17844 | x  | y  | y
 17845 | y  | y  | y
 17846 | y  | y  | y
(8 linhas)

Each record is assigned a OID different, because internally each record (line) is a different object.

DELETE FROM t WHERE oid NOT IN
(SELECT min(oid) FROM t GROUP BY c1, c2, c3);
DELETE 3

SELECT oid, t.* FROM t;

  oid  | c1 | c2 | c3
-------+----+----+----
 17839 | x  | x  | x
 17840 | x  | x  | y
 17841 | x  | y  | x
 17844 | x  | y  | y
 17845 | y  | y  | y
  • 1

    +1, but I still have some doubts, This OID will always be 0? in what situation does it change? if something comes into it, so it can be used?

  • @Guilhermelautert check if it is more understandable

0

The first number means that the table was created without Oids (Postgresql default configuration). The second number means the number of records added.

Oids basically provide an internal, globally unique ID for each row contained in a system column.

Browser other questions tagged postgresql sql-insert

You are not signed in. Login or sign up in order to post.