| Query goal: | Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | Medium (Medium number of false-positive results) |
| Query license: | MIT License |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
WITH tsv_columns AS (SELECT table_schema, table_name, column_name, data_type, generation_expression, is_generated, ordinal_position FROM INFORMATION_SCHEMA.columns WHERE (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND data_type='tsvector' AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT table_schema, table_name, column_name, data_type, generation_expression, is_generated FROM tsv_columns WHERE EXISTS (SELECT * FROM tsv_columns WHERE is_generated='ALWAYS') AND EXISTS (SELECT * FROM tsv_columns WHERE is_generated='NEVER') ORDER BY table_schema, table_name, ordinal_position; |
| Collection name | Collection description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
| Category name | Category description |
|---|---|
| Full text search | Queries of this category provide information about full text search |
| Generated columns | Queries of this category provide information about generated stored base table columns. |
| Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |