Here I'm using postgres 9.6 And PFA is my table schema for postgres
id username
----------
1 ashesh
2 123456
While doing a query on a username with '123456' it will not gives the response but when doing a query with 'ashesh' it will give a response. My query is
select * from customer where username = '123456';
Gives me a blank response but while querying with 'ashesh'
select * from customer where username = 'ashesh';
it will give the response.
Perhaps, sometimes you keep a blank in the username value(it seems for 123456),
trim may be used in the where condition for username column :
create table customer( ID int, username varchar(125));
insert into customer values(1,'ashesh');
insert into customer values(2,'123456 ');
create table customer( ID int, username varchar(125));
insert into customer values(1,'ashesh');
id username
1 ashesh
select * from customer where trim(username) = 'ashesh';
1 ashesh
select * from customer where username = '123456';
--no results return
select * from customer where trim(username) = '123456';
id username
2 123456
Your problem would seem to be hidden characters -- either in the where constant or in the column itself. If the first, then simply re-typing the code should fix the problem.
The second requires more investigation. I would start with:
where username like '%123456%'
to see if any usernames contain that sequence of characters. If this finds the row, then start looking for hidden characters. If not, then try:
where username like '%1%2%3%4%5%6%'
This will look for hidden characters between the digits. Once you have determined what the issue is, you can figure out how to deal with the problem.
If you have a fixed set of characters that are allowed, you can also test:
where regexp_replace(username, '[^a-zA-Z0-9]', '', 'g') = '123456'
If this is the problem, you can remove the hidden characters with:
update customer
set username = regexp_replace(username, '[^a-zA-Z0-9]', '', 'g')
where username ~ '[^a-zA-Z0-9]';
where username like '123456%'work? Do you have an unique index on that column? What is your exact Postgres version (select version()will tell you).