Query for identifying unique columns and not null columns across in schema

Question

Can we able to get the queries for identifying unique column and not null column in schema.

Please refer below queries in oracle.

SELECT Table_name, index_name, num_rows, distinct_keys FROM dba_indexes WHERE table_owner = 'ownername' and uniqueness = 'NONUNIQUE' AND num_rows > 0 AND 100* ( num_rows - ( num_rows - distinct_keys ) ) / num_rows > 99 ;

SELECT t.table_name, c.column_name, t.num_rows, t.null_values FROM dba_tab_columns c, tab_tables t WHERE t.owner = 'ownername'  and t.table_name=c.table_name and t.owner = c.owner and c.nullable='Y' and c.num_nulls=0;

Can we get same kind of queries in postgres?

Thanks

score 1 · Answer 1 · edited May 01 '18 at 05:24

1

friend I had never needed what you need before but I found this and I hope you can use the reference

Equivalent of "describe table" in PgAdmin3

 psql -d "$db_name" -c '
    SELECT 
    ordinal_position , table_name , column_name , data_type , is_nullable
    FROM information_schema.columns
    WHERE 1=1
    AND table_name = '\''my_table'\''
    ;'

# or just the col names
psql -d "$my_db" -t -c \
"SELECT column_name FROM information_schema.columns 
WHERE 1=1 AND table_name='my_table'"

PostgreSQL "DESCRIBE TABLE"

https://www.postgresql.org/docs/9.3/static/information-schema.html

Excuse me for not doing the query

edited May 01 '18 at 05:24

Kaushik Nayak

30,772
5
32
45

answered May 01 '18 at 05:14

nelson E

11
2

Thanks for update, but my question is. How to find the columns which is having unique data but no unique index How to find the columns which is not having null values and no not null constraint in schema – Ram May 01 '18 at 05:47

score 0 · Answer 2 · edited Aug 07 '21 at 13:06

Below the queries will give result:

Finding columns which have unique data but no unique key index on those columns.

SELECT DISTINCT a.schemaname, a.tablename, attname, indexdef
FROM pg_stats a, pg_indexes b
WHERE a.tablename = b.tablename
    AND a.schemaname ILIKE 'pegadata'
    AND n_distinct = -1
    AND indexdef NOT ILIKE '%UNIQUE%';

Finding columns which are never null but no constraint.

SELECT DISTINCT table_schema, table_name, column_name
FROM information_schema.columns a, pg_stats b
WHERE a.table_name = b.tablename
    AND a.TABLE_SCHEMA = 'pegadata'
    AND a.IS_NULLABLE = 'YES'
    AND b.null_frac = 0;

Query for identifying unique columns and not null columns across in schema

2 Answers2