What is a "clob" or Character Large Object?

by Evan Carroll   Last Updated August 16, 2018 21:06 PM

I see in this post on listagg — Rows to Delimited Strings talking about SQL's LISTAGG() clause ON OVERFLOW

The return type of listagg is either varchar or clob with 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 text type?



Answers 1


Colloquially

The 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.

"Character Large Object" - SQL Spec

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.

Evan Carroll
Evan Carroll
August 16, 2018 20:36 PM

Related Questions



What does "ISO standard equivalent" actually mean?

Updated November 29, 2016 08:02 AM

What is table bloating in databases?

Updated October 02, 2018 20:06 PM


Difference between UPSERT and MERGE?

Updated April 07, 2018 10:06 AM