Re: automatic scan a table, report on data formats in columns

On Mon, Feb 21, 2022, 2:06 AM Shaozhong SHI <shishaozhong(at)gmail(dot)com> wrote:

> Is it possible to do the following?
>
> automatically scan a table of all text columns
> produce a report on data formats in columns as indicated in the following:
>
> Column A Column B Column C
> alphabetic words/phrases digits like xxxxx.xx alphanumeric
> identifiers
> City of London 5 digits followed by a iso12345
> decimal point and 2
> digits indicating precision
>
>
> It is a bit like detecting regular expression patterns automatically.
>
> Is automatically detecting something like regular expression patterns
> possible?
>
> Regards,
>
> David
>

Depending on your definition of automatic, I think this is very do-able.

First you find the table names (using system catalog or hard-coded values,
depending) you're interested in and then use the columns view (
https://www.postgresql.org/docs/current/infoschema-columns.html) to
enumerate over the fields in each table to find ones with data types you
want to analyze). From there you can query each record in each column using
regex or similar to classify each column as to its contents.

Of course you have to write all that code so it's not automatic as in
built-in. But it's automatic in the sense that once written it would work
against any set of tables and columns and can be run without any human
intervention or analysis in the moment.

Steve

>