PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 49 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
1 All CHECK constraints of domains that are not associated with any table Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
2 All domain default values Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values. General INFORMATION_SCHEMA only 2021-01-19 13:04 MIT License View
3 All domains Find all domains. General INFORMATION_SCHEMA only 2020-11-10 11:49 MIT License View
4 Base table column name is the same as its domain name Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
5 CHECKs are associated with a column instead of the domain of the column Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
6 Columns that have the same name as some domain/type Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-28 14:47 MIT License View
7 Database objects of the same type and case insensitive name in the same container Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:13 MIT License View
8 Default should be declared at the level of domain not at the level of base table columns Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
9 Domain based on another domain Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
10 Domain candidates Find column descriptions that are candidates for describing a domain. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
11 Domain CHECK constraint name contains table name Find names of domain CHECK constraints that contain the name of the base that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of defining different base tables. Thus, it is inappropriate to use the name of a particular table in the name of a domain constraint. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
12 Domain CHECK constraints with the same name Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
13 Domain declares the same default value for multiple independent foreign keys Find domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
14 Domain name and type name are the same Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
15 Domain name contains base table name Find names of domains that contain the name of the table that column the domain specifies. Domains are reusable artifacts that one should be able to use in case of different base tables. Thus, it is inappropriate to use the name of a particular table in the name of the domain. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
16 Domains that are associated with a sequence generator Domains are reusable artifacts. By associating a domain with a sequence generator, one essentially starts to share sequence generators between tables. It may cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently, i.e., it increases coupling between tables. Problem detection INFORMATION_SCHEMA only 2021-03-07 21:08 MIT License View
17 Domains with the same name in different schemas Domains are like words that can be used to construct generalized claims about the real world (table predicates). Better not to duplicate the words in the dictionary. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
18 Domain usage in base tables Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
19 Duplicate CHECK constraints that are connected to a domain The same domain should not have multiple CHECK constraints with exactly the same Boolean expression. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
20 Duplicate DEFAULT values of base table columns Find base table columns that have both default value determined through a domain and default value that is directly attached to the column. Do not duplicate specifications of default values to avoid confusion and surprises. If column and domain both have a default value, then in case of inserting data the default value that is associated directly with the column is used. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View