Check Possible Data Type of a string using pattern matching in PostgreSQL and Return Count.

PostgresCountDatatypeFromString.sql

SELECT COUNT(1) AS COUNT, 'Decimal' as DataType
FROM @TableName
WHERE @ColumnName ~ '^\d+\.\d+$'

UNION ALL
SELECT COUNT(1) AS COUNT, 'Integer' as DataType
FROM @TableName
WHERE @ColumnName ~ '^\+?(0|[1-9]\d*)$'

UNION ALL
SELECT COUNT(1) AS COUNT, 'Date' as DataType
FROM @TableName
WHERE @ColumnName ~ '^((0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20)?[0-9]{2})*$'

UNION ALL
SELECT COUNT(1) AS COUNT, 'Time' as DataType
FROM @TableName
WHERE @ColumnName ~ '^(?:1[0-2]|0?[0-9]):[0-5][0-9]:[0-5][0-9] (AM|PM|am|pm)$'

UNION ALL
SELECT COUNT(1) AS COUNT, 'DateTime' AS DataType
FROM @TableName
WHERE @ColumnName ~ '^((0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20)?[0-9]{2})\s(?:1[0-2]|0?[0-9]):[0-5][0-9]:[0-5][0-9] (AM|PM|am|pm)$'

Functions like IsNull and IsNumeric are not available in PostgreSQL and checking via parsing requires exception handling on parse failure as well as the need to create custom functions. Above is an efficient way to check for possible datatype of a string value in PostgreSQL using pattern matching.