Re: Storing number '001' ?
От | Josh Berkus |
---|---|
Тема | Re: Storing number '001' ? |
Дата | |
Msg-id | web-521797@davinci.ethosmedia.com обсуждение исходный текст |
Ответ на | Storing number '001' ? (Charles Hauser <chauser@acpub.duke.edu>) |
Список | pgsql-novice |
Chuck, First off, I'd like to congratulate you on having the foresight to break down the value into seperate, atomic, components. Far too many novice DBA's would have stored the whole ID as one VARCHAR, and then written a bunch of custom functions to parse it. The latter approach has disastrous consequences for data integrity, so I'm thrilled that you have made the right choice. > project: 894 > plate: 001 > plate row: A > plate column: 01 > read: x > ver: 1 > Unfortunately, storing these numbers as integers converts 001 ->1, > which I can't use. > > How does one store a number like '001'? That depends on the answer to this question: Is the Plate number always a zero-filled 3-digit integer? If the answer is Yes, then you can simply use the to_char function to format the integer plate number as you wish to see it: SELECT to_char(plate, '000'); (warning: to_char has a bug in ver. 7.1.x that causes it to sometimes insert a leading space, e.g. " 001" instead of "001". To fix this, use the Trim function) The database will still store the integer, making for smaller disk space, faster searching, and automatic matching if you forget to zero-fill. If the answer is No (i.e. the plate number is sometimes more or less than three digits) then you'll have to store the plate number as a VARCHAR. In this case, you will want to create a Constraint that prevents entry of non-numerical characters into the Plate field, and adapt your user interface so that it zero-fills user input automatically before saving. E-mail me back if you need to do this. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
В списке pgsql-novice по дате отправления: