CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Ahmed
Ahmed

Reputation: 55

Any bad affect if I use TEXT data-type to store a number

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.

Upvotes: 2

Views: 2122

Answers (4)

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656844

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.

Upvotes: 1

user330315
user330315

Reputation:

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....

Upvotes: 5

Marc
Marc

Reputation: 16512

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.

Upvotes: 1

Related Questions