SELECT WHERE clause not working in Postgres (psql) when searching in a varchar column-CodePudding

I have a table species_info in Postgres SQL that is defined like this:

Column	Type	Nullable
spccode	integer	not null
itis_tsn	character varying(10)
common_name	character varying(50)
scientific_name	character varying(100)
taxon_rank	character varying(25)

And has values like this:

spccode	itis_tsn	common_name	scientific_name	taxon_rank
1		Unidentified	None	None
2		Disintegrated fish larvae	None	None
3	161109	Tenpounders	Elopidae	Family
4	161112	Machete	Elops affinis	Species
5	161119	Bonefishes	Albulidae	Family
7	161120		Albula	Genus
9	161694		Clupeiformes	Order
12	161743	Round herring	Etrumeus teres	Species

I do the following in psql:

select * 
from new_species_codes 
where common_name = 'Bonefishes';

and it works fine, returning the row with that value.

But when I try:

select * 
from new_species_codes 
where common_name = 'Machete';

it returns zero rows.

I've tried it with multiple values, and some of them work while others don't. What gives?

CodePudding user response：

Try this:

SELECT * FROM new_species_codes 
WHERE TRIM(common_name) = 'Machete';

CodePudding user response：

This is a straightforward query and I tried as well, It is working as expected. Can you please confirm there is no blank space in between the common name start or trail?

Also, I would like to suggest using the LIKE operator when you searching for something using wildcard where common_name like %Machete%