Not able to search on zipcode column a table in postgresql

Question

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?

Answer 1

if i change the type to int would that make a difference?

Yes, it would make a difference, but it is NOT a good idea. Zip codes in the extreme northeast have leading zeroes (e.g., every zip code in Boston, MA is of the form 02###). While all of the characters in a US zip code are numerals, the code itself is not numeric -- it is 5 characters, including the leading zero on many. In reports and in many programs that consume the data you will be expected to display/provide the leading zeroes.

Note that if you were to decide to add the zip+4 characters into the same column, you would also need "-" (non-integer). If you went with the more usual additional "plus4" column it also has the leading zero issue.

Better by far to trim your incoming data of spaces or post-process it to trim it out. You might even consider a varchar(5) data type to ensure proper form if you are not dealing with non-US zip codes.

Answer 2

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.

Answer 3

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'?

Because you may have characters inside the zip code that you're not expecting, like spaces, non-breaking spaces, or some other fun unicode thing.

if i change the type to int would that make a difference?

If you're only storing US Zip Codes (and only the 5-digit model at that) you can store zip codes in an int.

ALTER TABLE foo ALTER zip
  SET DATA TYPE int
  USING (trim(zip)::int);

That's smaller/faster.

do i need an index on the zip_ column?

Yes, if it's in a where clause there is a good chance the database can make use of an index.

Better idea

Alternatively, you could geocode, resolve to WGS84 addresses and store the lat/long in in a database using PostGIS's geography(POINT). Then you can calculate distances and finding the nearest point on the index.