PostgreSQL TEXT Data Type - Features, Examples and Equivalents

TEXT data type stores variable-length character data.

Versions: PostgreSQL 9.x and 8.x

Related data types for TEXT in PostgreSQL:

The only difference between TEXT and VARCHAR(n) is that you can limit the maximum length of a VARCHAR column, for example, VARCHAR(255) does not allow inserting a string more than 255 characters long.

Both TEXT and VARCHAR have the upper limit at 1 Gb, and there is no performance difference among them (according to the PostgreSQL documentation).

In a value inserted to a TEXT column has trailing blanks, PostgreSQL does not truncate them, and they are significant in comparisons:

  CREATE TABLE t_text1 (c1 TEXT);
 
  INSERT INTO t_text1 VALUES ('A   ');
 
  SELECT '''' || c1 || '''' FROM t_text1;
  -- Result: 'A   '
 
  SELECT COUNT(c1) FROM t_text1 WHERE c1 = 'A';
  -- Result: 0
 
  SELECT COUNT(c1) FROM t_text1 WHERE c1 = 'A   ';
  -- Result: 1

Similar data types in other databases:

Oracle:

SQL Server:

MySQL:

Convertion of TEXT data type:

PostgreSQL:

   CREATE TABLE t_text2
   (
     c1 TEXT
   );

Oracle:

Although Oracle CLOB data type can store up to 4 Gb, converting TEXT to CLOB may be not appropriate in some cases due to CLOB restrictions in Oracle:

• In PostgreSQL, you can actually store relatively short strings in TEXT columns and then use them in GROUP BY and ORDER BY
• In Oracle, you can use CLOB in string functions, but you cannot use them in GROUP BY and ORDER BY

TEXT to CLOB:

   CREATE TABLE t_text2
   (
     c1 CLOB
   );
 
   INSERT INTO t_text2 VALUES('ABC');
 
   -- Using CLOB in string functions
   SELECT SUBSTR(c1, 1, 1), LENGTH(c1) FROM t_text2;
   -- Result: A    3
 
   SELECT * FROM t_text2 ORDER BY c1;
   -- ERROR at line 1:
   -- ORA-00932: inconsistent datatypes: expected - got CLOB
 
   SELECT * FROM t_text2 GROUP BY c1;
   -- ERROR at line 1:
   -- ORA-00932: inconsistent datatypes: expected - got CLOB

If a TEXT column is used to store short strings and used in GROUP BY and ORDER BY, you can convert it to VARCHAR(4000) in Oracle:

   CREATE TABLE t_text2
   (
     c1 VARCHAR(4000)
   );

SQL Server:

You can convert TEXT to VARCHAR(max) in SQL Server that can store up to 2 Gb characters:

   CREATE TABLE t_text2
   (
     c1 VARCHAR(max)
   );

MySQL:

You can convert TEXT to LONGTEXT in MySQL that can store up to 4 Gb characters:

   CREATE TABLE t_text2
   (
     c1 LONGTEXT
   );

• PostgreSQL Reference
• http://www.postgresql.org/docs/9.1/static/datatype-character.html