Table columns that are associated with a sequence generator

Query goal:

Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys.

Notes about the query:

The query takes into account both external and internal (are created as the result of declaring a column as the identity column) sequence generators. The query takes into account a possibility that a column may be associated with a sequence generator through a domain.

Query type:

General (Overview of some aspect of the database.)

Query license:

MIT License

Data source:

INFORMATION_SCHEMA only

SQL query:

Click on query to copy it

SELECT A.table_schema, T.table_type, A.table_name , A.column_name, A.data_type, CASE WHEN column_default LIKE '%nextval%' THEN 'EXTERNAL' ELSE 'INTERNAL' END AS sequence_generator_type
FROM information_schema.columns A LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE (coalesce (A.column_default, d.domain_default) ILIKE '%nextval%' OR is_identity='YES')
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY T.table_type, A.table_schema, A.table_name, A.column_name;

Collections where the query belongs to

Collection name	Collection description
Find problems by overview	Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Collection name

Collection description

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category name	Category description
Comfortability of data management	Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Default value	Queries of this catergory provide information about the use of default values.
Sequence generators	Queries of this category provide information about sequence generators and their usage.

Category name

Category description

Comfortability of data management

Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.

Default value

Queries of this catergory provide information about the use of default values.

Sequence generators

Queries of this category provide information about sequence generators and their usage.