Hello,
--
I'm observing strange behaviour on comparing ::text field with string while quering replica.
Here is the table structure:
=# \dS+ raw.symbols_aggregates
Table "raw.symbols_aggregates"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------+---------+-----------+----------+------------------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('symbols_aggregates_id_seq'::regclass) | plain | |
symbol | text | | not null | | extended | |
id_type | bigint | | not null | | plain | |
id_aggregates_list | integer | | not null | | plain | |
id_regionals_list | integer | | not null | | plain | |
date_started | date | | not null | | plain | |
Indexes:
"symbols_aggregates_pkey" PRIMARY KEY, btree (id)
"symbols_aggregates_uniq" UNIQUE CONSTRAINT, btree (symbol, id_type, id_aggregates_list, id_regionals_list)
Foreign-key constraints:
"symbols_aggregates_id_aggregates_list_fkey" FOREIGN KEY (id_aggregates_list) REFERENCES aggregates_list(id)
"symbols_aggregates_id_regionals_list_fkey" FOREIGN KEY (id_regionals_list) REFERENCES regionals_list(id)
"symbols_aggregates_id_type_fkey" FOREIGN KEY (id_type) REFERENCES types_list(id)
Simple query on master works as expected:
=# select symbol from raw.symbols_aggregates where symbol='RUT';
symbol
--------
RUT
RUT
RUT
(3 rows)
The same query on replica works only after casting to varchar, using trim(), or something else:
(3 rows expected here)
=# select symbol from raw.symbols_aggregates where symbol='RUT';
symbol
--------
(0 rows)
with ::varchar(50) result looks correct:
=# select symbol from raw.symbols_aggregates where symbol::varchar(50)='RUT';
symbol
--------
RUT
RUT
RUT
There is no hidden characters, it looks identical with ::bytea
=# select symbol::bytea, 'RUT'::bytea, (symbol::varchar(50))::bytea from raw.symbols_aggregates where symbol::varchar(50)='RUT';
symbol | bytea | symbol
----------+----------+----------
\x525554 | \x525554 | \x525554
\x525554 | \x525554 | \x525554
\x525554 | \x525554 | \x525554
PostgreSQL server version is 10.3. It might be important, that master is running on Ubuntu 16.04.4 LTS and replica on Solaris 11.3.
PostgreSQL installed from binaries, not from sources. All locales are en_US.UTF-8
My suggestion it is a kind of collation issue, but I've no idea why :text is not working in this case.
Regards, Andrei Lizenko
