Postgres Data Type Cast

Postgres data type cast

cast(varchar_col AS int)  -- SQL standard

or

varchar_col::int          -- Postgres syntax shorthand

Theses syntax variants are valid (almost) anywhere. The second may require nesting parentheses in special situations:

• PostgreSQL: Create index on length of all table fields

And the first may be required where only functional notation is allowed by syntax restrictions:

• PostgreSQL - CAST vs :: operator on LATERAL table function

There are two more variants:

int4(varchar_col)         -- only works for some type names
int '123'                 -- must be an untyped, quoted string literal

Note how I wrote int4(varchar_col). That's the internal type name and there is also a function defined for it. Wouldn't work as integer() or int().

Note also that the last form does not work for array types. int[] '{1,2,3}' has to be '{1,2,3}'::int[] or cast('{1,2,3}' AS int[]).

Details in the manual here and here.

To be valid for integer, the string must be comprised of an optional leading sign (+/-) followed by digits only. Leading / trailing white space is ignored.

typecast to int in postgres

You need parentheses. The cast operator :: has a higher precedence than the -> operator. So extras->>'end_date'::int4 is parsed as extras->> ('end_date'::int4) which is the reason for the error.

select * 
from subscription 
where (extras->>'end_date')::int4 > 1592424632

How can I know the function used to CAST data types in PostgreSQL database

The functions used to convert data types are referenced in system catalog pg_cast: this system catalog is referencing pg_type and pg_proc system catalogs.

PostgreSQL : Cast Data Type from Text to Bigint when using WHERE IN

Using strings for id list is wrong design. You can use a arrays in PostgreSQL.

For example

CREATE OR REPLACE FUNCTION foo(VARIADIC ids int[])
RETURNS SETOF table_a AS $$
SELECT * FROM table_a WHERE id = ANY($1)
$$ LANGUAGE sql;

SELECT foo(1,2,3);

But, usually wrapping simple SQL to functions looks like broken design. Procedures should not to replace views.

Cast a PostgreSQL column to stored type

regtype is a object identifier type and there is no reason to use it when you are not referring to system objects (types in this case).

You should cast the column to integer in the first query:

SELECT id::text 
FROM contacts 
ORDER BY id::integer;

You can use qualified column names in the order by clause. This will work with any sortable type of column.

SELECT id::text
FROM contacts 
ORDER BY contacts.id;

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.

---

Related Topics