the phantom record

#1

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----

Quote:

70 | 121
70 | 111
60 | 120
70 | 117
70 | 107
50 | 110
175 | 112
175 | 114
125 | 112
80 | 101
50 | 105
60 | 124
185 | 101
175 | 108
70 | 115
70 | 119
175 | 104
170 | 102
50 | 106
70 | 109
70 | 120
20 | 101
40 | 103
115 | 101
50 | 111
60 | 122
50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----

Quote:

70 | 121
70 | 111
60 | 120
70 | 117
70 | 107
50 | 110
175 | 112
175 | 114
125 | 112
80 | 101
50 | 105
60 | 124
185 | 101
175 | 108
70 | 115
70 | 119
175 | 104
170 | 102
50 | 106
70 | 109
200 | 49
70 | 120
20 | 101
40 | 103
115 | 101
50 | 111
60 | 122
50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----

Quote:

20 | 101
40 | 103
50 | 105
50 | 106
50 | 107
50 | 110
50 | 111
60 | 120
60 | 122
60 | 124
70 | 107
70 | 109
70 | 111
70 | 115
70 | 117
70 | 119
70 | 120
70 | 121
80 | 101
115 | 101
125 | 112
170 | 102
175 | 104
175 | 108
175 | 112
175 | 114
185 | 101
200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----

Quote:

70 | 121
70 | 111
60 | 120
70 | 117
70 | 107
50 | 110
175 | 112
175 | 114
125 | 112
80 | 101
50 | 105
60 | 124
185 | 101
175 | 108
70 | 115
70 | 119
175 | 104
170 | 102
50 | 106
70 | 109
200 | 49
70 | 120
20 | 101
40 | 103
115 | 101
50 | 111
60 | 122
50 | 107
(28 rows)

myPGdb=#

#2

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#3

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#4

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#5

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#6

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#7

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#8

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

#9

Nevermind, I found the reason is a duplicate value in the triplet (td,
vp, vc) on tableB. Sorry for rush out.

Coniglio Sgabbiato ha scritto:

Quote:

Hi, I have two real tables in a database, I am comparing some values and
can notice that queries that are supposed to return the same values
differs for one record. When I use the /not in/ clause I lose one record
and get 27 instead of 28 records. So let's see the queries, the data and
queries I put here are real, I changed only the name of tables and
database for privacy purposes:

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB);
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(27 rows)

myPGdb=# select td, vp, vc from tmp.tableA where (td, vp, vc) not in
(select td, vp, vc from tableB) or td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=# select td, vp, vc from tmp.tableA except select td, vp, vc from
tableB;
td | vp | vc
----+-----+-----
| 20 | 101
| 40 | 103
| 50 | 105
| 50 | 106
| 50 | 107
| 50 | 110
| 50 | 111
| 60 | 120
| 60 | 122
| 60 | 124
| 70 | 107
| 70 | 109
| 70 | 111
| 70 | 115
| 70 | 117
| 70 | 119
| 70 | 120
| 70 | 121
| 80 | 101
| 115 | 101
| 125 | 112
| 170 | 102
| 175 | 104
| 175 | 108
| 175 | 112
| 175 | 114
| 185 | 101
| 200 | 49
(28 rows)

myPGdb=# select td, vp, vc from tableB where td is null;
td | vp | vc
----+----+----
(0 rows)

myPGdb=# select td, vp, vc from tmp.tableA where td is null;
td | vp | vc
----+-----+-----
| 70 | 121
| 70 | 111
| 60 | 120
| 70 | 117
| 70 | 107
| 50 | 110
| 175 | 112
| 175 | 114
| 125 | 112
| 80 | 101
| 50 | 105
| 60 | 124
| 185 | 101
| 175 | 108
| 70 | 115
| 70 | 119
| 175 | 104
| 170 | 102
| 50 | 106
| 70 | 109
| 200 | 49
| 70 | 120
| 20 | 101
| 40 | 103
| 115 | 101
| 50 | 111
| 60 | 122
| 50 | 107
(28 rows)

myPGdb=#

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

the phantom record

comp.databases.postgresql comp.databases.postgresql

the phantom record - 05-12-2008 , 08:35 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM

Re: the phantom record - 05-12-2008 , 09:04 AM