PostgreSQL ISNULL transform null to minimum value of datatype

Question

I have a PostgreSQL database and I have a complex query which is generated dynamically from C#. I need to use the ISNULL built-in function which in PostgreSQL is called COALESCE by replacing with minimum value of the given data type.

The outcome of this code is to set the minimum values on a table which keeps the transactional transfer status for each table. Setting to the minimum you are going to perform initial load in the first iteration.

For example, for integer:

SELECT COALESCE(int_value, 0) FROM my_transaction_log_table WHERE schema_name = '' AND table_name = ''

For example, for timestamp:

SELECT COALESCE(timestamp_value, to_timestamp(0)) FROM my_transaction_log_table WHERE schema_name = '' AND table_name = ''

I have implemented this in C# as the following code:

public static string FieldMinimumType(string field_type)
{
    string field_exp;
    switch (field_type)
    {
        case "timestamp without time zone":
            field_exp = string.Format("'1990-01-01 00:00:00'");
            break;
        case "timestamp with time zone":
            field_exp = string.Format("'1990-01-01 00:00:00'");
            break;
        case "smallint":
            field_exp = string.Format("0");
            break;
        case "integer":
            field_exp = string.Format("0");
            break;
        case "bigint":
            field_exp = string.Format("0");
            break;
        case "numeric":
            field_exp = string.Format("0");
            break;
        case "double precision":
            field_exp = string.Format("0");
            break;
        
        default:
            field_exp = string.Format("");
            break;
    };
    return field_exp;
}

Of course "minimum" is not very accurate based on the provided C# but -infinity for timestamp and lowest integer for integers will produce the same result.

Is something I can do regardless of the input column data type?

Answer 1

Actually I found out that -infinity and +infinity which were working for timestamp are also supported for float, double precision and numeric data types in newest PostgreSQL versions.

For numerics:

SELECT '-infinity'::numeric

For floats:

SELECT '-infinity'::float

For double precisions

SELECT '-infinity'::double precision

But still is not supported for smallint, int and big int

For the above problem, for now, a possible solution is to explicit convert input transactional status (numeric, smallint, integer, bigint or timestamp) to integer value and store in in the log table.

Then in the next iteration based on the column datatype this integer should be reverted back to original datatype.