Not able to search on zipcode column a table in postgresql Hello I have a postgresql question about searching on zip codes.  Basicly, if I do a: 

select * from my_table where zip_ = '90210';


I am returned 0 rows.  I know for a fact that there is thousands of rows with that zip code.  The table itself is over a million rows.  

    Column        |       Type        |                      Modifiers                      
---------------------+-------------------+-----------------------------------------------------
...
 zip_             | character varying | 


Here is my table and I am not able to search effectively on zip_ column.
  The table does return a search if I use the like keyword along with % char, but that is not what I need.  

my questions are:


why when i search on zip_ lets say '90210' it comes back with 0 rows when I know there is thousands of records with the zip code of '90210'?  
if i change the type to int would that make a difference?
do i need an index on the zip_ column?
Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);


And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();


Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. It was to expensive compared to the benefit (most postal services are quite good in finding out where misaddressed mail really belongs to anyway). But a "light version", for those countries it's easy for, might be reasonable.