I see in this post on
listagg — Rows to Delimited Strings talking about SQL's
The return type of
clobwith an implementation defined length limit.
I've never previously seen the term
clob before. Looking it up, it's apparently a term "character large object" that comes from the spec?
What exactly is a "Character Large Object" and how does it relate to the PostgreSQL
clob in reference here is NOT in reference to the spec, which is confusing. This is term
clob here is from Oracle/DB2/Informix parlance
So yes, in this case the PostgreSQL equivalent is
text, which is confirmed by this post on the PostgreSQL mailing list. This is perhaps why PostgreSQL doesn't use
clob as a term anywhere.
Bun in the SQL Spec, there is some confusion. The SQL:2011 Part 1: Framework (SQL/Framework) defines two types for character string types.
A "character string type" is either of fixed length, or of variable length up to some implementation-defined maximum.
- A value of character large object type is a string of characters from some character repertoire and is always associated with exactly one character set.
- A large object character string is of variable length, up to some implementation-defined maximum that is probably greater than that of other character strings.
So both of the lengths are undefined by the spec, and left to the implementation. But only the "Large Object Character String" is variable width, the character large object is not. The spec also defines a "binary string type" but I don't believe that's what is being referenced here.
In summary in the spec a Character Large Object (CLOB) is fixed-width, but in implementation and RDBMS parlance a CLOB is variable-width like any Large Object Character String, with the maximum width supported by the implementation.
I would not use the term
clob unless the database supported a
clob type that didn't otherwise have a different (more standardized like
VARCHAR(max) or less confusing
text) name to refer to the same type.