EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial CAST in PostgreSQL

CAST in PostgreSQL

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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()?
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW