Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

recomended datatype

Status
Not open for further replies.

aneev

Programmer
Jan 9, 2003
9
US
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).

 
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 (
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:
Code:
CREATE TABLE example_table (
example_column smallint(4) NOT NULL CHECK (example_column BETWEEN 1 and 999),
etc...
);
-------------------------------------------

PHP/Perl/SQL/Javascript --
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top