PostgreSQL multi-column multi-line writing not working

Question

I have a strange situation in PostgreSQL 9.4. I have a table:

id      integer                 NOT NULL DEFAULT nextval('users_userpropmeta_id_seq'::regclass)
name    character varying(255)  NOT NULL
cls     character varying(4)    NOT NULL
app_id  integer                 NOT NULL

And a unique constraint: UNIQUE (app_id, name)

Now I am querying the table:

 SELECT COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

And we get:

 count | app_id |          name
-------+--------+-------------------------
     2 |   6019 |   
     2 |   6019 |   
(2 rows)

So unique doesn't work? I went further:

SELECT * FROM users_userpropmeta WHERE app_id=6019 AND name in ('  ', '  ');
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 7308 |       | str |   6019
 7309 |    | str |   6019
(2 rows)

Only 2 lines. Some kind of magic happens here. Let's find the lines with the hack:

SELECT MAX(id), MIN(id), COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

 max  | min  | count | app_id |          name
------+------+-------+--------+-------------------------
 7308 | 4633 |     2 |   6019 |   
 7309 | 4636 |     2 |   6019 |   
(2 rows)

Here are the lines:

SELECT * FROM users_userpropmeta WHERE id IN (7308, 7309, 4633, 4636);
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 4633 |       | str |   6019
 4636 |    | str |   6019
 7308 |       | str |   6019
 7309 |    | str |   6019
(4 rows)

The string comparison is correct one by one, they are equal:

SELECT a.id, b.id, a.name, b.name, a.name = b.name FROM users_userpropmeta AS a CROSS JOIN users_userpropmeta AS b WHERE a.id IN (7308, 7309, 4633, 4636) AND b.id IN (7308, 7309, 4633, 4636);
  id  |  id  |          name           |          name           | ?column?
------+------+-------------------------+-------------------------+----------
 4633 | 4633 |       |       | t
 4633 | 4636 |       |    | f
 4633 | 7308 |       |       | t
 4633 | 7309 |       |    | f
 4636 | 4633 |    |       | f
 4636 | 4636 |    |    | t
 4636 | 7308 |    |       | f
 4636 | 7309 |    |    | t
 7308 | 4633 |       |       | t
 7308 | 4636 |       |    | f
 7308 | 7308 |       |       | t
 7308 | 7309 |       |    | f
 7309 | 4633 |    |       | f
 7309 | 4636 |    |    | t
 7309 | 7308 |    |       | f
 7309 | 7309 |    |    | t
(16 rows)

Can anyone explain why the unique constraint inserted both rows without exception? Why can't the IN operator find them (I was thinking about some non-printable characters?), But does the equality test show that they are equal?

. s. PostgreSQL 9.6 pglogical, postgres . , =).

Answer 1

.

enable_seqscan = off , .

, ctid .