Postgresql cast int + Examples

In this Postgresql tutorial, we will learn about “Postgresql cast int” using different data types or functions and, cast or convert from int to another data type like boolean, float, timestamp, etc.

We are going to cover the following topic:

• PostgreSQL cast
• Postgresql cast int to string
• PostgreSQL cast int to float
• PostgreSQL cast int to boolean
• PostgreSQL cast int to timestamp
• PostgreSQL cast int to date
• PostgreSQL cast int to bit
• PostgreSQL cast int to character
• PostgreSQl cast int to bytea

Postgresql cast

When we have data stored in Postgresql database and we want to convert the data type of some value or column of the table into another data type like int.

We may have the data or column that has a text data type and we want to perform some calculation on that, but it is not possible with the text data type.

we might want to convert it into a numeric data type, for that we will use the cast function of Postgresql that helps in converting data from one type to another.

There are three ways to cast the value in Postgresql.

• Using the cast( ) function: This function accepts two things in its parenthesis, the first is a value that we want to convert, and the second is the data type like INTEGER, FLOAT, BOOLEAN.

SELECT CAST ( VALUE AS TYPE )

• Using the ( VALUE::TYPE ) notation: We also called it typecast operator in Postgresql.

Before the operator, write the value that we want to convert, and after the operator write the data type that we want to apply to the value.

SELECT VALUE::TYPE

• Using inbuilt functions: The last way is to use the inbuilt function like to_char, to_date, to_timestamp.

Read: Postgresql date_trunc function

PostgreSQL cast int to float

In Postgresql, to cast or convert from int to float, pass the first value as an integer and second value data type that is float.

Use the below command:

SELECT CAST( 5137 AS FLOAT ); --CAST TO FLOAT

In the above output, In the cast function, we have provided integer and float values then it converts the 5137 integer into float.

Read Postgresql generate_series

PostgreSQL cast int to string

In Postgresql, to cast or convert from int to string using the below command.

SELECT CAST(123 AS TEXT); --CAST TO STRING

In the above command, it converted 123 integer to string as we can see that we have provided 123 and data type as TEXT to cast function.

PostgreSQL cast int to boolean

In Postgresql, to cast or convert from int to boolean use the below command.

SELECT CAST( 1 AS boolean ); --CAST TO TRUE BOOLEAN VALUE

            OR

SELECT CAST( 0 AS boolean ); --CAST TO FALSE BOOLEAN VALUE

In the above output, it converts 1 ( integer ) to a true ( boolean ) value and when we change the value to 0 ( integer ), it will convert that value to a false ( boolean ) value.

Read: PostgreSQL TO_NUMBER() function

PostgreSQL cast int to timestamp

In Postgresql, to cast or convert from int to timestamp use the below command.

SELECT to_timestamp(20210921) --CAST TO TIMESTAMP

In the above output, 20210921 is cast to 1970-08-23 03:38:41+05:30 using the to_timestamp function that accepts the parameter as a double-precision value.

PostgreSQL cast int to date

In Postgresql, to cast or convert from int to date we need to convert int to text, then cast it from text to date.

Use the below command:

SELECT to_date(20210922::text, 'YYYYMMDD'); 
--CAST TO TEXT THEN CAST TO DATE

In the above output, first 20210922 is converted to text then it is cast to date using the to_date function that accepts two parameters.

1. Text to convert that we want to convert into date.
2. Date format like “YYYYMMDD” or “DDMMYYYY” etc.

Also Read: PostgreSQL TO_TIMESTAMP function

Postgrsql cast int to character

In Postgresql, to cast or convert from int to character use the below command.

SELECT CAST( 2021 AS varchar ); --CAST TO CHARACTER

In the above output, the 2021 integer changed by cast function to a character.

Read: Postgresql change column data type

Postgrsql cast int to bit

In Postgresql, to cast or convert from int to bit use the below command.

SELECT CAST( 44 AS bit(5) ); --CAST TO BIT

In the above output, the integer 44 is converted by cast function to 01100 bit, and Casting an integer to bit(n) copies the rightmost n bits.

Note that casting to just “bit” means casting to bit(1).

Read: How to find primary column name in Postgresql

Postgrsql cast int to bytea

In PostgreSQL, to cast or convert from int to bytea, there is no special function for that, so we are going to create our own int to bytea function that can convert from int to bytea.

Use the below command:

CREATE OR REPLACE FUNCTION integer2bytea(_int NUMERIC) RETURNS BYTEA AS $$
DECLARE
_bt BYTEA := '\x';
_v INTEGER;
BEGIN
WHILE _int > 0 LOOP
_v := _int % 256;
_bt := SET_BYTE(('\x00' || _bt),0,_v);
_int := (_int-_v)/256;
END LOOP;
RETURN _bt;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

Run the above command to create the integer2bytea function.

Note: When you run the above command in the pgAdmin application, it will show nothing in the output dialog, so run this command in the command-line or terminal.

Let’s run the function integer2bytea function to convert from int to bytea in the command-line.

As we can see in the above output, it converted integer 12345 to bytea \x3039.

You may also like reading the following articles.

• PostgreSQL TO_CHAR function
• Postgresql length of string
• How to backup PostgreSQL database
• PostgreSQL Export Table to CSV
• Postgresql row_number
• Postgresql group_concat

So, in this Postgresql tutorial, we have learned about “Postgresql cast int” using different inbuilt functions or operators of Postgresql. And we have also covered the following topics:

• PostgreSQL cast
• Postgresql cast int to string
• PostgreSQL cast int to float
• PostgreSQL cast int to boolean
• PostgreSQL cast int to timestamp
• PostgreSQL cast int to date
• PostgreSQL cast int to bit
• PostgreSQL cast int to character
• PostgreSQl cast int to bytea