Converting mysql to Postgre query - could not find proper alternative for information_schema.STATISTICS

Question

I have a query in mysql, trying to convert to Postgre, I could not find 'information_schema.STATISTICS' equivalent in Postgre. Is there any way to achieve in Postgre.

Full Query:

SELECT t.TABLE_NAME as TName
FROM information_schema.TABLES t 
            JOIN information_schema.STATISTICS s on 
              s.TABLE_CATALOG = t.TABLE_CATALOG
            AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
            AND s.TABLE_NAME = t.TABLE_NAME
  AND s.INDEX_NAME  = 'PRIMARY'
             AND s.COLUMN_NAME = 'PK'
  AND s.SEQ_IN_INDEX  = 1
            JOIN information_schema.COLUMNS c on
            c.TABLE_CATALOG = t.TABLE_CATALOG
            AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
              AND c.TABLE_NAME = t.TABLE_NAME 
              AND c.COLUMN_NAME = 'VERSION'
            WHERE 1=1
              AND t.TABLE_NAME LIKE '%\\_%'
              AND t.TABLE_SCHEMA LIKE '%\\_%'
              AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME)```


In this query, we are trying to get tables which are having PRIMARY as index_name(that is the one index mysql creates automatically), and index_column is PK.
  And also the table should have a column called 'VERSION'.

Answer 1

There isn't a direct equivalent to MySQL's information_schema.STATISTICS table in POSTGRESQL so have to try with with PostgreSQL's system catalogs pg_class, pg_attribute, and pg_index to find tables with a primary key named 'PK' and a column named 'VERSION'.

SELECT
    t.relname AS "TName"
FROM
    pg_class t
    JOIN pg_namespace n ON n.oid = t.relnamespace
    JOIN pg_attribute a ON a.attrelid = t.oid
    JOIN pg_index i ON i.indrelid = t.oid
    JOIN pg_attribute ia ON ia.attrelid = i.indexrelid
WHERE
    t.relkind = 'r' -- regular table
    AND i.indisprimary = true -- is a primary index
    AND ia.attname = 'pk' -- primary key named 'PK'
    AND a.attname = 'version' -- has a column named 'VERSION'
    AND t.relname LIKE '%\_%' -- table name pattern
    AND n.nspname LIKE '%\_%' -- schema name pattern
    AND t.relname != LOWER(t.relname) -- binary check (case-sensitive)