CODEWITHSUNDEEP
sqldatabasepostgresql
Aditya Anand
Aditya Anand

Reputation: 896

Postgres SQL command to figure out what all columns can be enums but aren't?

The context is that I'm building a generic tool that can take in different customer databases and connect it with an internal dashboard. So in my use case, I'm frequently running into dozens of varchar/int columns that are honestly just enums.

I'm looking for a way so that I don't have to manually sift through each column and see if it can be an enum.

Is there a command that can automatically detect enum-able columns in a table

SQL: Select <column_name> FROM <table> WHERE <column_name> has maximum 10 distinct values

Or something else like the above. Any ideas?

Upvotes: 0

Views: 111

Answers (1)

pifor
pifor

Reputation: 7882

You can use pg_stats statistics system catalog to have an estimation of distinct values for a given column in a given table:

select schemaname, tablename, attname, n_distinct
from pg_stats 
where n_distinct > 0 and n_distinct <= 10;

But it is only an estimation.

Note that for n_distinct column:

If greater than zero, the estimated number of distinct values in the column. If less than zero, the negative of the number of distinct values divided by the number of rows. (The negated form is used when ANALYZE believes that the number of distinct values is likely to increase as the table grows; the positive form is used when the column seems to have a fixed number of possible values.) For example, -1 indicates a unique column in which the number of distinct values is the same as the number of rows.

Upvotes: 1

Related Questions