CODEWITHSUNDEEP
postgresqltypes
Martin Burch
Martin Burch

Reputation: 2911

Can PostgreSQL recommend optimal field types?

MySQL offers PROCEDURE ANALYSE. Given a query like

SELECT `field` FROM `table` PROCEDURE ANALYSE();

The result offers a suggested field type 

Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL

Does PostgreSQL offer a similar functionality? I've looked at pg_statistic/pg_stats. Seems like I can use this information to infer what data type might be appropriate, but it would be handy if Postgres could recommend an actual data type itself.

Upvotes: 3

Views: 332

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

There is no equivalent in Postgres for MySQL's PROCEDURE ANALYSE that I know of. (And I think I would know.)

For the given example, obviously about an integer-type column, I would:

SELECT min(field), max(field) FROM tbl;

And check against the range of possible numeric data types in the manual.

Or for the distribution of distinct values:

SELECT field, count(*) AS ct FROM tbl GROUP BY 1 ORDER BY 2 DESC;

Actually, any DBA with a minimum of experience should know the essential characteristics of basic data types. The best type for each use case depends on a lot more than the current range and distribution of values. MySQL's PROCEDURE ANALYSE() would basically be assistance to newcomers - who would easily misinterpret results.

Upvotes: 4

Related Questions