Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Overview of this book

Table of Contents (19 chapters)
PostgreSQL 9 Administration Cookbook Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Enforcing the same name and definition for columns


Sensibly designed databases have smooth, easy-to-understand definitions. This allows all users to understand the meaning of data in each table. It is an important way of removing data quality issues.

Getting ready

If you want to run the queries in this recipe as a test, then use the following examples. Alternatively, you can just check for problems in your own database:

CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.X
(col1 smalliER
,col2 TEXT);
CREATE TABLE s2.X
(col1 smallint
,col3 NUMERIC);

How to do it…

First, we will show you how to identify columns that are defined in different ways in different tables, using a query against the catalog. We use an Information Schema query, as follows:

SELECT
 table_schema
,table_name
,column_name
,data_type
  ||coalesce(' ' || text(character_maximum_length), '')
  ||coalesce(' ' || text(numeric_precision), '')
  ||coalesce(',' || text(numeric_scale), '')
  as data_type
FROM information_schema.columns...