Geek Answers Handbook

Column type for ZipCode in PostgreSQL database?

What is the correct column type for storing values ZipCodein a database PostgreSQL?

+5
source share
3 answers

This is something like xxxxx-xxxx, so it is recommended varchar(10).

If you want to check the syntax of the values ​​in the database, you can create a type domainfor zip codes.

CREATE DOMAIN zipcode varchar(10) 
    CONSTRAINT valid_zipcode 
    CHECK (VALUE ~ '[A-Z0-9-]+'); -- or a better regular expression

You can look at this site that offers this regular expression:

(^\d{5}(-\d{4})?$)|(^[ABCEGHJKLMNPRSTVXY]{1}\d{1}[A-Z]{1} *\d{1}[A-Z]{1}\d{1}$)

But you have to check if this works for regex syntax PostgreSQL.

+7
source

I strongly disagree with the advice presented here.

  • , .
  • Zip-, .
  • , , , , , , -
  • , , , , (, , )
  • 11 (13 , ) - zip, zip + 4 zip + 6 ( zip + 4 + 2); , ..
  • , ( , , , zip 00203 didn 't zip 203, , )
  • , , . "".

:

  • , .
  • , . ( unix TZ, , .)
  • , , , , . , . . , , 6- , .
+10

, zip . , 5- , int .

however, if you need to make 5 + 4 extended, it is best to use a 10-digit character field. I personally suggest that since this will make it easier to work in the future if you need to store international postal codes, 10 digits cover almost all possible postal code formats that I have encountered.

0
source

More articles:


All Articles