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
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.
Type
General (Overview of some aspect of the database.)
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
This query belongs to the following collections:
Name
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
This query is classified under the following categories:
Name
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.