Priority of use of type TEXT

PostgreSQL, unlike MySQL, stores data differently, including text. In general, MySQL has problems with fields like TEXT, and developers, switching to PostgreSQL, out of habit, write VARCHAR (100) everywhere, laying quite serious problems for the future.

1. Hard typing The fact is that in MySQL hard typing is turned off by default, and all errors of the dimension of numbers or field lengths are solved simply - it is truncated to the maximum value / length. Nobody is soaring that something was cut off there - they simply do not know about it. Well, to configure something there outside of PHP - this requires knowledge.

In PostgreSQL, everything is the other way around - already out of the box, it monitors all types and sizes of data, and any error leads to the exception and cancellation of the query. Moreover, comparing VARCHAR and TEXT is impossible without explicit type conversion, just like comparing INT and NUMERIC.

2. CHAR (N) in MySQL and PostgreSQL return different data. In MySQL, selecting a value from such a column will select characters up to \ n. That is, if you write 1111 \ n there, then the result will be returned string (4).

In PostgreSQL, it will be big news that this is not so - it will consider that the text length is always exactly 10 characters, and will add spaces to the desired length, as a result, sting (10) will return.

3. There is no difference between TEXT and VARCHAR As it was written at the very beginning, PostgreSQL works with the text in a different way. For example, in MySQL there are different types - TEXT, TINYTEXT, BIGTEXT, etc. This is done due to the principle of storing such data (different address space in the repository). Therefore, most programmers just write VARCHAR (255) or VARCHAR (1000) instead of TEXT.

In PostgreSQL there are no such differences between VARCHAR and TEXT - everything is TEXT there. The type VARCHAR (N) only limits the length of the text, exceeding which will throw an error.

4. Converting VARCHAR to TEXT You cannot just pick up and change a column from VARCHAR to text when this column is used in procedures or views. The thing again is that you can not compare VARCHAR and TEXT. When a project grows, changing types in production mode is difficult because of the time it takes to find and change all places of use, and because the table is locked.

Top