EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial CAST in PostgreSQL
 

CAST in PostgreSQL

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 8, 2023

CAST in PostgreSQL

 

 

Introduction to CAST in PostgreSQL

In the case of handling transactions within multiple databases, data conversion is the basic requirement that is supported by almost all programming paradigms. PostgreSQL provides us with the CAST operator, which we can use to convert one data type to another data type. We can have various cast operations in the PostgreSQL, like converting string to integers, converting string to date and date to a string, casting to Boolean, etc.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

CAST (exp AS target_type );

Explanation:

  • target_type: Define the target data type in which we are converting the value of the exp.

How does the CAST Operator work in PostgreSQL?

The cast operator converts one data type to another, where the table column or an expression’s data type is decided to be. The target data type is the data type to which the expression will get converted. The syntax of the CAST operator’s another version is as follows as well:

Syntax:

Expression::type

Consider the following example to understand the working of the PostgreSQL CAST:

Code:

SELECT
'222'::INTEGER,
'13-MAR-2020'::DATE;

Output: Illustrate the following snapshot to understand the result of the above statement.

CAST in PostgreSQL - 1

Examples to Implement CAST in PostgreSQL

Now, Let’s look at the following examples, which converts one data type to another.

1. STRING to an Integer CAST

1. Use the following statement to do the conversion:

Code:

SELECT
CAST ('111' AS INTEGER);

Output: Illustrate the following snapshot to understand the result of the above statement:

conversion

2. The PostgreSQL CAST operator raises an error if the given value is not convertible to the target data type. Consider the following example for the same,

Code:

SELECT
CAST ('1SS' AS INTEGER);

Output: PostgreSQL will give us the following error after executing the above SQL statement: the value contains a character.

raises an error

2. STRING to DATE CAST

Convert a STRING constant to DATE type using the following statement:

Code:

SELECT
CAST ('2020-03-13' AS DATE),
CAST ('13-MAR-2020' AS DATE);

Output: Illustrate the following snapshot to understand the result of the above statement:

STRING constant

3. STRING to DOUBLE CAST

1. Now, try to convert a STRING constant to a DOUBLE type using the following statement:

Code:

SELECT
CAST ('22.2' AS DOUBLE);

Output: PostgreSQL will give us the following error after executing the above SQL statement: the value contains precision.

error

2. To execute the above statement correctly; we have to use the following syntax where instead of DOUBLE, we have to use DOUBLE PRECISION,

Code:

SELECT
CAST ('22.2' AS DOUBLE PRECISION);

Output: Illustrate the following snapshot to understand the result of the above statement:

DOUBLE

4. STRING to Boolean CAST

Convert a STRING constant to Boolean type using the following statement, where the ‘FALSE’, ‘false’, ‘f’ and ‘F’ gets converted to false, and ‘TRUE’, ‘true’, ‘t’ and ‘T’ gets converted to true as follows:

Code:

SELECT
CAST('FALSE' as BOOLEAN),
CAST('false' as BOOLEAN),
CAST('F' as BOOLEAN),
CAST('f' as BOOLEAN),
CAST('TRUE' AS BOOLEAN),
CAST('true' AS BOOLEAN),
CAST('T' as BOOLEAN),
CAST('t' as BOOLEAN);

Output: Illustrate the following snapshots to understand the result of the above statement:

CAST in PostgreSQL - 7

5. STRING to Timestamp CAST

1. Convert a STRING constant to timestamp type using the following statement

Code:

SELECT '2020-03-13 12:40:00'::timestamp;

Output: Illustrate the following snapshot to understand the result of the above statement:

CAST in PostgreSQL - 8

6. String to interval CAST

Use the following statement to do the conversion:

Code:

SELECT '5 minute'::interval,
'5 hour'::interval,
'5 day'::interval,
'5 week'::interval,
'5 month'::interval;

Output: Illustrate the following snapshot to understand the result of the above statement:

CAST in PostgreSQL - 9

7. CAST with table

1. Now, let’s create a new table of name ‘Grades’, which will have a column named’Grade’ using CREATE TABLE statement as follows:

Code:

CREATE TABLE Grades (
Grade VARCHAR(1)
);

2. Now, insert some data into the ‘Grades’ table using the INSERT statement as follows:

Code:

INSERT INTO Grades(Grade)
VALUES
('A'),
('B'),
('C'),
('D');

3. Illustrate the Grades table’s content with the help of the following snapshot and SQL statement.

Code:

SELECT
Grade
FROM
Grades;

Output:

CAST in PostgreSQL - 10

4. Now, suppose the requirement is changed where we have to store the grades in numerical format instead of character, so using the following statement, we can insert numerical values in the Grades table.

Code:

INSERT INTO Grades(Grade)
VALUES
('1'),
('2'),
('3'),
('4');

5. The Grades table will store mixed numerical and character types of ratings. Illustrate the content of the Grades table with the help of the following snapshot and SQL statement.

Code:

SELECT
Grade
FROM
Grades;

Output:

CAST in PostgreSQL - 11

6. So we will convert all values in the Grade column of the Grades table to integer type using the following statement,

Code:

SELECT
CASE
WHEN grade~E'^\\d+$' THEN
CAST (grade AS INTEGER)
ELSE
0
END as grade
FROM
Grades;

Output: Illustrate the result of the above statement using the following snapshot

CAST in PostgreSQL - 12

Conclusion

From the above article, we hope you understand how to use the PostgreSQL CAST operator and how the PostgreSQL CAST works to convert one data type to another. Also, we have added some examples of PostgreSQL CAST operators to understand them in detail.

Recommended Articles

We hope that this EDUCBA information on “CAST in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL FETCH
  2. PostgreSQL Database
  3. Cursors in PostgreSQL
  4. How to Work PostgreSQL RANK()?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW