2

Is there any bad affect if I use TEXT data-type to store an ID number in a database? I do something like:

CREATE TABLE GenData ( EmpName TEXT NOT NULL, ID TEXT PRIMARY KEY);

And actually, if I want to store a date value I usually use TEXT data-type. If this is a wrong way, what is its disadvantage?

I am using PostgreSQL.

Ahmed
  • 55
  • 1
  • 6

4 Answers4

5

Storing numbers in a text column is a very bad idea. You lose a lot of advantages when you do that:

  • you can't prevent storing invalid numbers (e.g. 'foo')
  • Sorting will not work the way you want to ('10' is "smaller" than '2')
  • it confuses everybody looking at your data model.

I want to store a date value I usually use TEXT

That is another very bad idea. Mainly because of the same reasons you shouldn't be storing a number in a text column. In addition to completely wrong dates ('foo') you can't prevent "invalid" dates either (e.g. February, 31st). And then there is the sorting thing, and the comparison with > and <, and the date arithmetic....

  • 4
    All of this, and your data tables and indexes are going to be bigger. This will exhaust your RAM faster, leading to more disk accesses and lower overall performance. – 9000 Nov 20 '12 at 17:30
  • other significant issue - probably any estimation will be out – Pavel Stehule Nov 21 '12 at 07:32
1

I really don't recommend using text for dates.

Look at all the functions you are missing with text

If you want to use them, you have to cast and it's only problems if by accident the dates stored are not valid cause with text there's no validation.

Community
  • 1
  • 1
Marc
  • 16,170
  • 20
  • 76
  • 119
1

In addition to what the other answers already provided:

text is also subject to COLLATION and encoding, which may complicate portability and data interchange between platforms. It also slows down sorting operations.

Concerning storage size of text: an integer occupies 4 byte (and is subject to padding for data alignment). text or varchar occupy 1 byte plus the actual string, which is 1 byte for ASCII character in UTF-8 or more for special characters. Most likely, text will be much bigger.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

It depends on what operations you are going to do on the data.

If you are going to be doing a lot of arithmetic on numeric data, it makes more sense to store it as some kind of numeric data type. Also, if you plan on sorting the data in numerical order, it really helps if the data is stored as a number.

When stored as text, "11" comes ahead of "9" because "1" comes ahead of "9". If this isn't what you want, don't use text.

On the other hand, it often makes sense to store strings of digits, such as zipcodes or social security number or phone numbers as text.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58