What does bank return INSERT 0 1 mean?

Question

What does bank return INSERT 0 1 mean?

Navigation

#1 by (6 votes)
#2 by (0 votes)

7

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

Doubt

What does 0 mean 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 I have already noticed is that they are rows effectively inserted.

Addendum

The same does not exist when it is UPDATE

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

postgresql sql-insert

asked by anonymous 28.06.2017 / 15:53

2 answers

0

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

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

28.06.2017 / 16:06

How to detect that the headset has been plugged in? LINQ query with sum

score 6 · Accepted Answer

Excerpted from the page in the manual :

Exits

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

INSERT [oid] [count]

Count [count] is the number of rows entered. If the [count] count is exactly UMA 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 several system tables. It would be about the same as Oracle's ROWID .

Exemplifying:

OID will only appear if you INSERT a LINE. Because the return is unique (single return), so it is shown 0 for insertion of 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, 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