Thread: 'text' is gone?
Looks like microsoft is going to drop the data type "text" in the near future. VarChar(max) will be taking it's place. Does Postgres have some like varChar(max)? -- John Fabiani
johnf wrote: > Looks like microsoft is going to drop the data type "text" in the near future. > VarChar(max) will be taking it's place. Does Postgres have some like > varChar(max)? You can either specify a max length (varchar(X)) or leave the length off to get a text like field: varchar http://www.postgresql.org/docs/current/static/datatype-character.html -- Postgresql & php tutorials http://www.designmagick.com/
On Tuesday 10 February 2009 6:39:27 pm johnf wrote: > Looks like microsoft is going to drop the data type "text" in the near > future. VarChar(max) will be taking it's place. Does Postgres have some > like varChar(max)? > -- > John Fabiani http://www.postgresql.org/docs/8.3/interactive/datatype-character.html varchar() -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes: > On Tuesday 10 February 2009 6:39:27 pm johnf wrote: >> Looks like microsoft is going to drop the data type "text" in the near >> future. VarChar(max) will be taking it's place. Does Postgres have some >> like varChar(max)? > http://www.postgresql.org/docs/8.3/interactive/datatype-character.html > varchar() I don't think I believe the OP's premise anyway. The concept that every string column has to have a specific hard-coded maximum length is an evil hangover from the days of punched cards. It seems very unlikely that M$, who are not exactly known for slavish adherence to standards to begin with, would suddenly drop a more flexible datatype and make all their customers use a less flexible one. I'd be *much* less surprised to see 'text' or some moral equivalent of it (such as our varchar-without-length) show up in a future SQL spec than to see any major implementation remove it. regards, tom lane
On Tuesday 10 February 2009 07:22:45 pm Tom Lane wrote: > I don't think I believe the OP's premise anyway. The concept that every > string column has to have a specific hard-coded maximum length is an > evil hangover from the days of punched cards. It seems very unlikely > that M$, who are not exactly known for slavish adherence to standards to > begin with, would suddenly drop a more flexible datatype and make all > their customers use a less flexible one. > > I'd be *much* less surprised to see 'text' or some moral equivalent of > it (such as our varchar-without-length) show up in a future SQL spec > than to see any major implementation remove it. > > regards, tom lane I have been reading in several emails, blog, and other posts that data type 'text' has been deprecated. The premise may be wrong but that's what I have been reading. "deprecated" normally mean dropped in the future. From the answers I received it looks like they are following Postgres varchar(). Please see the link and the description of varchar(max). http://www.sitecrafting.com/blog/ms-sql-server/ Please check the title - MS SQL Server 2005 text and ntext dated Oct 2008. Which is just one I have read. Below is a discription of the replacement. Understanding VARCHAR(MAX) in SQL Server 2005In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk. To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs. To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions. Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server. -- John Fabiani
On Tue, Feb 10, 2009 at 10:49 PM, johnf <jfabiani@yolo.com> wrote: > To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), > and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold > the same amount of data BLOBs can hold (2 GB) and they are stored in the same > type of data pages used for other data types. When data in a MAX data type > exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically > assigns an over-flow indicator to the page and knows how to manipulate data > rows the same way it manipulates other data types. You can declare variables > of MAX data types inside a stored procedure or function and even pass them as > variables. You can also use them inside string functions. > > Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. > In fact, BLOBs are being deprecated in future releases of SQL Server. So, they did exactly what pgsql crew did with TOAST, but instead of sticking it under an existing datatype that everyone already had, they made another new type to keep track of. I can't think of a reason to not just update the text type to be just like the leader's, Postgresql's, but I'm sure they have their reasons. I'd love to hear them though.
Scott Marlowe wrote: > So, they did exactly what pgsql crew did with TOAST, but instead of > sticking it under an existing datatype that everyone already had, they > made another new type to keep track of. I can't think of a reason to > not just update the text type to be just like the leader's, > Postgresql's, but I'm sure they have their reasons. I'd love to hear > them though. Their text type was accessed through BLOB interfaces at the application/SQL level, so just substituting it would break a lot of things. That said, you'd think they could provide wrappers to allow code used to using the BLOB interfaces to operate on the new no-longer-a-blob type. Perhaps there are performance issues there (say, it being more expensive to repeatedly re-write a tuple with a VARCHAR(MAX) field than to re-write a TEXT field) that meant they preferred to separate it out. Whatever the SQL server folks are, they're not stupid, and I can't imagine they'd do this without at least a half-decent reason. -- Craig Ringer