PostgreSQL Cast Int

In this PostgreSQL tutorial, we will learn about “PostgreSQL cast int” using different data types or functions and casting or converting from int to another data type, such as boolean, float, timestamp, etc.

PostgreSQL Cast Int

When data is stored in a PostgreSQL database, we want to convert the data type of a value or column in the table into another data type, like int.

We may have data or a column with 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 PostgreSQL’s cast function, which helps convert 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 parentheses: a value that we want to convert and a data type like INTEGER, FLOAT, or BOOLEAN.

SELECT CAST ( VALUE AS TYPE )

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

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

SELECT VALUE::TYPE

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

Read: PostgreSQL date_trunc function

Example 1: Cast an integer to a float

In PostgreSQL, pass the first value as an integer and the second value as a float to cast or convert from int to float.

Use the below command:

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

After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, the cast function provides integer and float values and converts the 5137 integer into a float.

Read PostgreSQL generate_series

Example 2: Cast an integer to a string

In PostgreSQL, use the following query to cast or convert from int to string.

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

After executing the above query, I got the expected output as shown in the screenshot below.

The above command converted 123 integers to strings, as we can see that we provided 123 and the data type as TEXT to the cast function.

Example 3: Cast an integer to a boolean

In PostgreSQL, use the following query to cast or convert from int to boolean.

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

            OR

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

You can check out the screenshot below, and I got the expected output.

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

Example 4: Cast integer to timestamp

Use the following query in PostgreSQL to cast or convert from int to timestamp.

SELECT to_timestamp(20210921) --CAST TO TIMESTAMP

After executing the above query, I got the expected output as shown in the screenshot below.

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.

Example 5: Cast an integer to a 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

I got the expected output as shown in the screenshot below.

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

1. Text to convert into a date.
2. Date format like “YYYYMMDD” or “DDMMYYYY”, etc.

Also Read: PostgreSQL TO_TIMESTAMP function

Example 6: Cast an integer to a character

In PostgreSQL, use the following command to cast or convert from int to character.

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

The screenshot below is for your reference, which it shows I got the expected output.

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

Read: PostgreSQL change column data type

Example 7: Cast an integer to a bit

In PostgreSQL, use the following command to cast or convert from int to bit.

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

After executing the above query, I got the expected output.

In the above output, the integer 44 is converted by the cast function to 01100 bits, 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 the primary column name in PostgreSQL

Example 8: Cast an integer to a bytea

There is no special function in PostgreSQL for casting or converting from int to byte, so we will create our own int-to-byte function that can convert from int to byte.

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: The above command will not show anything in the output dialog when run in the pgAdmin application, so run it in the command line or terminal.

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

As we can see in the above output, it converted the integer 12345 to bytea \x3039. Check out the above screenshot for your reference.

In this PostgreSQL tutorial, we have learned about “PostgreSQL cast int” using different inbuilt functions or operators.

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