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?
  • Does where zip_ LIKE '%90210%' return rows? Commented Apr 28, 2018 at 19:30
  • yes that is the only way I can have rows returned. 'select * from my_table where zip_ LIKE '9021%`
    – Jeff WR
    Commented Apr 28, 2018 at 19:33
  • 1
    Wild guess: There are whitespaces in the data. Try select * from my_table where trim(zip_) = '90210';. Does that give you any rows? (And don't change the type to int at least if you want international addresses. Have a look at the British post code system. They have letters in them as well.)
    – sticky bit
    Commented Apr 28, 2018 at 19:34
  • that does work :), how do I remove white spaces?
    – Jeff WR
    Commented Apr 28, 2018 at 19:36
  • 1
    Normally I would agree, but @ErwinBrandstetter says that for any CHAR, VARCHAR or TEXT type, the underlying code is the same, but they are equivalent. A 2 GB postcode would be excessive :-), but that's not the point in this case!
    – Vérace
    Commented Apr 28, 2018 at 22:22

3 Answers 3


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.


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
      NEW.zip_ = trim(NEW.zip_);

    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.

  • This probably is the issue. If they have other characters - besides spaces - they'll need to modify the trim() parameter accordingly. Commented Apr 28, 2018 at 19:39

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.

  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.