recomended datatype

[aneev]

Hi,
I have 2 questions.

1. I need to define a column in one of my postgresql table. This column can have integer values from 1-999. Which is the best datatype, varchar or smallint?Considering the disk space occupied.
2. Is it possible to have custom built value for a column of type SERIAL. For ex: can I have a customerid as x61,x62 ?(the interger part should increment automatically).

 

[rycamor]

It's always best to use the datatype that best fits your target. If you use a VARCHAR, then that allows users (or client applications) to enter non-numeric values, spaces, etc... So then, you would be forced to create a CHECK constraint to prevent that, etc... Also, if you want to perform any math operations on the column, an INT type is better. I would recommend smallint, since the values will be much smaller than the max size for small int (

http://www.postgresql.org/idocs/index.php?datatype.html#DATATYPE-NUMERIC).

Also, though, if you know for sure the values will be between 1-999, then you can add an extra CHECK to prevent any incorrect values from being entered:

CREATE TABLE example_table (
example_column smallint(4) NOT NULL CHECK (example_column BETWEEN 1 and 999),
etc...
);

-------------------------------------------

PHP/Perl/SQL/Javascript --

http://brainscraps.com/resume/