Postgres: Define a Default Value for Cast Failures

Postgres: define a default value for CAST failures?

There is no default value for a CAST:

A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:

CAST ( expression AS type )
expression::type

There is no room in the syntax for anything other than the expression to be casted and the desired target type.

However, you can do it by hand with a simple function:

create or replace function cast_to_int(text, integer) returns integer as $$
begin
    return cast($1 as integer);
exception
    when invalid_text_representation then
        return $2;
end;
$$ language plpgsql immutable;

Then you can say things like cast_to_int('pancakes', 0) and get 0.

PostgreSQL also lets you create your own casts so you could do things like this:

create or replace function cast_to_int(text) returns integer as $$
begin
    -- Note the double casting to avoid infinite recursion.
    return cast($1::varchar as integer);
exception
    when invalid_text_representation then
        return 0;
end;
$$ language plpgsql immutable;

create cast (text as integer) with function cast_to_int(text);

Then you could say

select cast('pancakes'::text as integer)

and get 0 or you could say

select cast(some_text_column as integer) from t

and get 0 for the some_text_column values that aren't valid integers. If you wanted to cast varchars using this auto-defaulting cast then you'd have to double cast:

select cast(some_varchar::text as integer) from t

Just because you can do this doesn't make it a good idea. I don't think replacing the standard text to integer cast is the best idea ever. The above approach also requires you to leave the standard varchar to integer cast alone, you could get around that if you wanted to do the whole conversion yourself rather than lazily punting to the built in casting.

NULL handling is left as an (easy) exercise for the reader.

How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

I was just wrestling with a similar problem myself, but didn't want the overhead of a function. I came up with the following query:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres shortcuts its conditionals, so you shouldn't get any non-integers hitting your ::integer cast. It also handles NULL values (they won't match the regexp).

If you want zeros instead of not selecting, then a CASE statement should work:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;

Postgres: Default for column (string) cannot be cast automatically to type enum

You need to remove the default value from the column prior to the change as the default is set to a value that is valid for the old column type but incompatible with the new type.

alter table schema.site_applications alter status drop default

Then you can change the column type. Finally once the new column type is applied, you can add a new default against the table.

alter table schema.site_applications alter status set default 'pending'::status_options

Postgres: How to convert column type from string to integer with default value?

As I understood, you need cast "character varying" column to INTEGER.

It seems that you have default value for this "character varying" column and postgres can't "attach" that value for INTEGER type.

If so, you can first drop default value, convert column to INTEGER and add default value appropriate for INTEGER type. like this:

alter table leads alter customer_id drop default;

alter table leads alter column customer_id type INTEGER using CAST("customer_id" as INTEGER);

alter table leads alter customer_id set default <YOUR DEFAULT VALUE HERE>;

When using cast - error: invalid input syntax for type numeric: (postgreSQL)

The reason it's not working is because your duration column contains values which cannot be cast to a numeric type. The specific value throwing the error is an empty string. Also, you shouldn't be comparing a numeric type to an empty string.

Also, if you're comparing a varchar column to a character value in your CASE statement, why are you trying to cast it to a numeric type at all?

For what you're doing here, I would just write it as CASE WHEN duration <> '' THEN 1 ELSE 0 END

And if you do need to cast it to a numeric type at some point, the way to do that would be something like CASE WHEN duration = '' THEN NULL ELSE cast(duration AS DECIMAL) END (asuming that empty strings are the only values in your column which cannot be cast to decimal)

PostgreSQL - Auto Cast for types?

SQL is a typed language, and PostgreSQL takes that more seriously than other relational databases. Unfortunately that means extra effort when porting an application with sloppy coding.

It is tempting to add implicit casts, but the documentation warns you from creating casts between built-in data types:

Additional casts can be added by the user with the CREATE CAST command. (This is usually done in conjunction with defining new data types. The set of casts between built-in types has been carefully crafted and is best not altered.)

This is not an idle warning, because function resolution and other things may suddenly fail or misbehave if you create new casts between existing types.

I think that if you really don't want to clean up the code (which would make it more portable for the future), you have no choice but to add more versions of your functions.

Fortunately PostgreSQL has function overloading which makes that possible.

You can make the job easier by using one argument with a polymorphic type in your function definition, like this:

CREATE OR REPLACE FUNCTION f_concat3 (
   s1 text, s2 integer, s3 anyelement
) RETURNS text
   LANGUAGE sql IMMUTABLE LEAKPROOF AS
'SELECT f_concat3(s1, s2::text, s3::text)';

You cannot use more than one anyelement argument though, because that will only work if all such parameters are of the same type.

If you use function overloading, be careful that you don't create ambiguities that would make function resolution fail.

Postgres return null values on function error/failure when casting

This can be done by trapping an exception in a plpgsql function.

create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
    begin
        return arg::timestamp;
    exception when others then
        return null;
    end;
end $$;

select id, c1, my_to_timestamp(c1) as c2 from a;

---

Trying to define a generic function.

Assume that you defined a function set_null_on_error(anyelement). Calling

select set_null_on_error('foo'::timestamp);

raises error before the function is executed.

You can try something like this:

create or replace function set_null_on_error(kind text, args anyarray)
returns anyelement language plpgsql
as $$
begin
    begin
        if kind = 'timestamp' then
            return args[1]::timestamp;
        elseif kind = 'number' then
            return to_number(args[1], args[2]);
        end if;
    exception when others then 
        return null;
    end;
end; $$;

select set_null_on_error('timestamp', array['2014-01-01']);
select set_null_on_error('number', array['1.22444', '9999D99']);

In my opinion such a solution is too complicated, quite inconvenient to use and generally might turn out to generate problems hard to debug.

---

Related Topics