I have a table species_info in Postgres SQL that is defined like this:
| Column | Type | Collation | Nullable | Default |
|---|---|---|---|---|
| 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?
>Solution :
Try this:
SELECT * FROM new_species_codes
WHERE TRIM(common_name) = 'Machete';