Question 265848 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find unique data types in table. SQL

I have the task of finding all unique data types in all the tables.

This is how I wrote the SQL query:

WITH UniqueColumnTypes AS 
(
    SELECT table_name, COUNT(DISTINCT data_type) AS unique_types
    FROM information_schema.columns
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_name
)
SELECT table_name, unique_types
FROM UniqueColumnTypes;

For some reason, CHAR(13) and CHAR(12) are considered two different types.

I need them to be considered as the same data type.

I check the data types of the table with this code:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'Book' AND table_schema = 'Stud';

I can see, that there are only 4 unique data types in the Book table. But the answer that the first code is giving me is 6 unique data types.

This is how this table is being created:

CREATE TABLE Stud.Book 
(
    ISBN      CHAR(13)    NOT NULL,
    Title     VARCHAR(32) NOT NULL,
    Publisher CHAR(12)    NOT NULL,
    Year      SMALLINT    NOT NULL 
        CONSTRAINT LeidimoMetai CHECK (Year > 1900 AND Year < EXTRACT(YEAR FROM CURRENT_DATE) + 1),
    Pages     SMALLINT    NOT NULL 
        CONSTRAINT PagesCount CHECK (Pages > 0 AND Pages < 2000),
    Price     DECIMAL(5, 2) 
        CONSTRAINT PriceSize CHECK (Price > 0 AND Price < 1000),
    PRIMARY KEY (ISBN)
);

With this, I can see, that there are only 4 unique data types in that table.

Tags:

2

Answers


  1. 0

    It looks like that the issue that is happening is due to the inclusion of character codes (CHAR) with different lengths as distinct data types. In your case, CHAR(13) and CHAR(12) are being treated as separate data

    To handle the case where different character lengths are considered as distinct data types, you can modify your SQL query to eliminate the distinction based on length. Here’s an example:

    WITH UniqueColumnTypes AS ( SELECT table_name, CASE WHEN data_type LIKE 'CHAR%' THEN 'CHAR' ELSE data_type END AS unique_type FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY table_name, unique_type ) SELECT table_name, COUNT(DISTINCT unique_type) AS unique_types FROM UniqueColumnTypes GROUP BY table_name;

    in this modified query, the CASE expression is used to check if the data type starts with ‘CHAR’. If it does, it assigns the value ‘CHAR’ to the unique_type column. This ensures that all character data types are considered as one unique data type, regardless of their length.

    By grouping the UniqueColumnTypes subquery by both table_name and unique_type, and then performing the final grouping and counting in the outer query, you can achieve the desired result of finding the unique data types in each table.

    Login or Signup to reply.
  2. 0

    You need to add

    character_maximum_length

    As the size is not part of the datatype

    CREATE TABLE Book (
    ISBN           CHAR(13)    NOT NULL,
    Title          VARCHAR(32) NOT NULL,
    Publisher      CHAR(12)    NOT NULL,
    Year           SMALLINT    NOT NULL CONSTRAINT LeidimoMetai 
                                         CHECK(Year > 1900 AND Year < EXTRACT(YEAR FROM CURRENT_DATE) + 1),
    Pages          SMALLINT    NOT NULL CONSTRAINT PagesCount CHECK(Pages > 0 AND Pages < 2000),
    Price          DECIMAL(5,2) CONSTRAINT PriceSize CHECK(Price > 0 AND Price < 1000),
    PRIMARY KEY (ISBN)
);
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'Book'

    CREATE TABLE
    column_name data_type 
    SELECT 0

    SELECT *
FROM   information_schema.columns
WHERE    table_schema NOT IN ('pg_catalog', 'information_schema')
    table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
    postgres public book year 4 null NO smallint null null 16 2 0 null null null null null null null null null null null null postgres pg_catalog int2 null null null null 4 NO NO null null null null null NO NEVER null YES
    postgres public book pages 5 null NO smallint null null 16 2 0 null null null null null null null null null null null null postgres pg_catalog int2 null null null null 5 NO NO null null null null null NO NEVER null YES
    postgres public book price 6 null YES numeric null null 5 10 2 null null null null null null null null null null null null postgres pg_catalog numeric null null null null 6 NO NO null null null null null NO NEVER null YES
    postgres public book isbn 1 null NO character 13 52 null null null null null null null null null null null null null null null postgres pg_catalog bpchar null null null null 1 NO NO null null null null null NO NEVER null YES
    postgres public book title 2 null NO character varying 32 128 null null null null null null null null null null null null null null null postgres pg_catalog varchar null null null null 2 NO NO null null null null null NO NEVER null YES
    postgres public book publisher 3 null NO character 12 48 null null null null null null null null null null null null null null null postgres pg_catalog bpchar null null null null 3 NO NO null null null null null NO NEVER null YES 
    SELECT 6

    SELECT column_name,     data_type,  character_maximum_length
FROM   information_schema.columns
WHERE    table_schema NOT IN ('pg_catalog', 'information_schema')
    column_name data_type character_maximum_length
    year smallint null
    pages smallint null
    price numeric null
    isbn character 13
    title character varying 32
    publisher character 12 
    SELECT 6

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search