3 ms·

Coming from other database systems I found this intriguing, and did some googling. Turns out there are historical reasons that PG has functionally overlapping t

Coming from other database systems I found this intriguing, and did some googling. Turns out there are historical reasons that PG has functionally overlapping text and varchar(n) datatypes, where text was the original still the best datatype as detailed in this thread, whereas varchar was added for ANSI compatibility, and does not enjoy the same system support (changing length is a pain compared to other engines). PG is going to be the engine of choice for my next project, so I'm happy to learn about this quirk compared to other engines. Which doesn't mean I'm all that happy about it. I'm really in agreement with this post from 2010 (still current AFAICT) that wishes the support for varchar(n) would be better, for readability and cross-engine compatibility nothing else [0] [0] https://www.postgresonline.com/journal/archives/154-In-Defense-of-varcharx.html https://www.postgresonline.com/journal/archives/154-In-Defen...

tracker1 8y ago
For contrast, MS SQL Server has `NVARCHAR(MAX)`. It's generally bugged me with MS-SQL that they added `NVARCHAR(MAX)` rather than just changing the behavior of `TEXT`
- da_chicken 8y ago
  It's because text and ntext are extremely old LOB types, and they support SQL statements like WRITETEXT and UPDATETEXT that other statements don't. They also can't be indexed, don't support functions like LEFT(), REPLACE(), etc. Text and ntext behave wildly differently than char and varchar while varchar(max) and nvarchar(max) are almost the same. Changing that functionality would cause problems. Further, neither text nor ntext are ISO standard data types. It's the same reason that SQL Server has datetime2 and Oracle has varchar2 (the latter of which is only now being merged into varchar). That's why float, real, and money still exist even though numeric/decimal was created. They behave differently and in ways that are not exactly backwards compatible. In order to prevent your customers from having to rewrite their application just because you updated your data types, they chose to add new data types.

tracker1 8y ago

For contrast, MS SQL Server has `NVARCHAR(MAX)`. It's generally bugged me with MS-SQL that they added `NVARCHAR(MAX)` rather than just changing the behavior of `TEXT`

da_chicken 8y ago
It's because text and ntext are extremely old LOB types, and they support SQL statements like WRITETEXT and UPDATETEXT that other statements don't. They also can't be indexed, don't support functions like LEFT(), REPLACE(), etc. Text and ntext behave wildly differently than char and varchar while varchar(max) and nvarchar(max) are almost the same. Changing that functionality would cause problems. Further, neither text nor ntext are ISO standard data types. It's the same reason that SQL Server has datetime2 and Oracle has varchar2 (the latter of which is only now being merged into varchar). That's why float, real, and money still exist even though numeric/decimal was created. They behave differently and in ways that are not exactly backwards compatible. In order to prevent your customers from having to rewrite their application just because you updated your data types, they chose to add new data types.