Unable to match same value in field.

From: Condor <condor(at)stz-bg(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to match same value in field.
Date: 2016-03-10 09:09:00
Message-ID: 799b81c38f852f929234fc6081cc2ad6@stz-bg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,

I using postgresql 9.5.1 and I have problem to match value in one field.
Both tables are text:

=# \d list_cards_tbl;

Column | Type | Modifiers
-----------+---------+--------------------------------------------------------------
recid | integer | not null default
nextval('list_cards_tbl_recid_seq'::regclass)
imsi | text |
Indexes:
"imsi_list_cards_tbl" btree (imsi)

=# \d list_newcard_tbl;
Column | Type | Modifiers
------------+---------+---------------------------------------------------------------
recid | integer | not null default
nextval('list_newcard_tbl_recid_seq'::regclass)
serial | text |
imsi | text |
Indexes:
"list_newcard_tbl_pkey" PRIMARY KEY, btree (recid)
"list_newcard_ser_idx" btree (serial)

=# select imsi, md5(imsi), bit_length(imsi) from list_newcards_tbl where
imsi = '284110000123315';
imsi | md5 | bit_length
-----------------+----------------------------------+------------
284110000123315 | b438e984c97483bb942eaaed5c0147f3 | 120
(1 row)

So far so good, value of table list_newcard_tbl is fine, problem is in
table list_cards_tbl

=# select imsi from list_cards_tbl where imsi = '284110000123315';
imsi
------
(0 rows)

No value, lets change to LIKE

=# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
imsi like '284110000123315%';
imsi | md5 | bit_length
-----------------+----------------------------------+------------
284110000123315 | b438e984c97483bb942eaaed5c0147f3 | 120
(1 row)

Both have the same MD5 sum, also bit length.

With EXPLAIN:

=# explain analyse select imsi from list_cards_tbl where imsi =
'284110000123315';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using imsi_list_card_tbl on list_cards_tbl
(cost=0.28..4.30 rows=1 width=16) (actual time=0.021..0.021 rows=0
loops=1)
Index Cond: (imsi = '284110000123315'::text)
Heap Fetches: 0
Planning time: 0.080 ms
Execution time: 0.045 ms
(5 rows)

I see only index scan, so I do:

=# reindex table list_cards_tbl;
REINDEX
=# vacuum list_cards_tbl;
VACUUM
=# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
imsi = '284110000123315';
imsi | md5 | bit_length
------+-----+------------
(0 rows)

Still cant find value.

Some settings:

enable_bitmapscan | on
| Enables the planner's use of bitmap-scan plans.
enable_hashagg | on
| Enables the planner's use of hashed aggregation
plans.
enable_hashjoin | on
| Enables the planner's use of hash join plans.
enable_indexonlyscan | on
| Enables the planner's use of index-only-scan
plans.
enable_indexscan | on
| Enables the planner's use of index-scan plans.
enable_material | on
| Enables the planner's use of materialization.
enable_mergejoin | on
| Enables the planner's use of merge join plans.
enable_nestloop | on
| Enables the planner's use of nested-loop join
plans.
enable_seqscan | on
| Enables the planner's use of sequential-scan
plans.
enable_sort | on
| Enables the planner's use of explicit sort steps.
enable_tidscan | on
| Enables the planner's use of TID scan plans.
client_encoding | UTF8
| Sets the client's character set encoding.
lc_collate | bg_BG.utf8
| Shows the collation order locale.
lc_ctype | bg_BG.utf8
| Shows the character classification and case
conversion locale.
lc_messages | bg_BG.utf8
| Sets the language in which messages are
displayed.
lc_monetary | bg_BG.utf8
| Sets the locale for formatting monetary amounts.
lc_numeric | bg_BG.utf8
| Sets the locale for formatting numbers.
lc_time | bg_BG.utf8
| Sets the locale for formatting date and time
values.
server_encoding | UTF8
| Sets the server (database) character set
encoding.
server_version | 9.5.1
| Shows the server version.
server_version_num | 90501
| Shows the server version as an integer.

Can some one point me what can be the problem with this value and how to
resolve it ? I think probably index problem but I reindex that table and
problem is not resolved or some broken utf8 char but md5 and bit_length
should not to be equal.

Regards,
HS

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2016-03-10 09:51:34 Re: Unable to match same value in field.
Previous Message David G. Johnston 2016-03-10 06:40:30 Re: [GENERAL] Request - repeat value of \pset title during \watch interations