Goal Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).
Notes For each base type PostgreSQL automatically provides support for arrays of that type. The array type typically has the same name as the base type with the underscore character (_) prepended. The query excludes the array types (more precisely, array type generators).

Explanation of the toasting strategy.


  • p (plain): Values must always be stored plain (non-varlena types always use this value).
  • e (external): Values can be stored in a secondary “TOAST” relation (if relation has one, see pg_class.reltoastrelid).
  • m (main): Values can be compressed and stored inline.
  • x (extended): Values can be compressed and/or moved to a secondary relation.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source system catalog only
SQL Query 
SELECT 
pt.typname AS type_name,
CASE WHEN pt.typstorage='p' THEN 'plain'
WHEN pt.typstorage='e' THEN 'external'
WHEN pt.typstorage='m' THEN 'main'
WHEN pt.typstorage='x' THEN 'extended'
END AS toasting_strategy,
CASE WHEN pt.typtype='b' THEN 'BASE TYPE'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS type_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname IN ('pg_catalog') OR a.rolname<>'postgres')
AND pt.typtype IN ('b','e','r')
AND pt.typlen = -1
AND left(pt.typname,1)<>'_'
ORDER BY type_name, type_type;

Categories

This query is classified under the following categories:

Data at the database physical level

Queries of this category provide information about the disk usage.

Data types

Queries of this category provide information about the data types and their usage.

TOAST

Queries of this category provide information about The Oversized-Attribute Storage Technique.

NameDescription
Data at the database physical levelQueries of this category provide information about the disk usage.
Data typesQueries of this category provide information about the data types and their usage.
TOASTQueries of this category provide information about The Oversized-Attribute Storage Technique.