PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal ▲ Type Data source Last update License
921 ROW-level BEFORE and INSTEAD OF triggers with RETURN NULL Such triggers effectively cancel data modification. It might be correct but could also be a mistake. "Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). " (PostgreSQL documentation) "INSTEAD OF triggers (which are always row-level triggers, and may only be used on views) can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT/UPDATE/DELETE). " (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
922 Table columns that are associated with a sequence generator 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. General INFORMATION_SCHEMA only 2021-03-07 21:06 MIT License View
923 Base table columns with the type VARCHAR(1) The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string. Problem detection INFORMATION_SCHEMA only 2024-12-14 13:41 MIT License View
924 Columns with exact/floating numeric types have textual default values The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
925 Too short table constraint names The names should be expressive. Find names of constraints, which are associated directly to a table, that are shorter than the length of the name of the table + two characters. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
926 Names of triggers and rules that do contain the table name The names should contain table name in order to make the names better understandable. General INFORMATION_SCHEMA+system catalog base tables 2023-01-13 19:23 MIT License View
927 Names of triggers and rules that do not contain the table name The names should contain table name in order to make the names better understandable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-13 19:18 MIT License View
928 Different prefixes of a candidate key column and a referencing foreign key column The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different prefixes. Thus, for instance, one cannot use USING syntax for joining the tables. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
929 Different suffixes of a candidate key column and a referencing foreign key column The naming must be consistent. Find foreign key constraints where the candidate key column and foreign key column names have different suffixes. Thus, for instance, one cannot use USING syntax for joining the tables. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
930 Names of columns with the type BOOLEAN The naming of BOOLEAN columns must be consistent. For the better readability the names of such columns could have prefix "is_" (in English) or "on_" (in Estonian) General INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:41 MIT License View
931 Columns with BOOLEAN type that do not have a good name The phrase "is_" or "has_" or "can_" (in English) or "on_" (in Estonian) should be used in the name - preferably in the beginning. Worse: agreed, kinnitatud. Better: contract_is_agreed, leping_on_kinnitatud. Perhaps the best: is_contract_agreed or is_agreement, on_leping_kinnitatud, on_kinnitatud. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:56 MIT License View
932 Cannot accommodate all the fractional seconds in case of table columns The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
933 Columns with BOOLEAN type that do have a good name The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: agreed, kinnitatud. Better: is_agreement, on_kinnitatud. General INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:56 MIT License View
934 Routines with BOOLEAN return type that do not have a good name The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: check_rights. Better: has_rights. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-27 11:12 MIT License View
935 Insufficient number of user-defined non-trigger routines There must be at least n (four in this case) user-defined non-trigger routines in the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
936 Insufficient number of user-defined views There must be at least n (four in this case) user-defined views in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
937 Insufficient number of user-defined domains There must be at least n (one in this case) user-defined domains in the database each of that must be used in case of at least two columns of base tables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
938 Insufficient number of user-defined base tables There must be at least n (seven in this case) user-defined base tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
939 Insufficient number of user-defined foreign tables There must be at least n (two in this case) user-defined foreign tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
940 Insufficient number of user-defined triggers+rules (based on number of tasks) There must be user-defined triggers and/or rules for at least n (three in this case) tasks in the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-04 11:46 MIT License View