Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

CAST Operator: Conversion between two data types

PostgreSQL supports the CAST operator to convert a value from one datatype to another datatype.

Syntax:
CAST(constant|column|expression AS target_type)

In the above syntax, an expression can be a constant, table column, or expression which you want to convert and target_type is the target data type in which you want to get the result.

The CAST operator can be used with SELECT clause, WHERE clause, FROM clause, and INSERT, UPDATE, and DELETE statements.

The following example shows the simple cast operation that converts constant values to the specified target datatype.

Example: Conversion of Contant Values
SELECT 
	CAST('100' AS INTEGER),
	CAST(200 AS TEXT),
   	CAST ('2011-01-15' AS DATE),
	CAST ('15 minutes' AS INTERVAL),
	CAST('2022-06-15 15:10:00' AS TIMESTAMP);

In the above example, it converts text '100' to INTEGER, number 200 to TEXT, string '2011-01-15' to DATE, string '15 minutes' to INTERVAL, and string '2022-06-15 15:10:00' to TIMESTAMP. The above casting will display the following result in pgAdmin:

CAST Operator

If constant, expression or column cannot be converted to target datatype, PostgreSQL will raise an error like bellow.

CAST Operator

The CAST operator can be used to cast a string to a boolean datatype, as shown below. Please note Boolean data type considers true, T, 1 as true and false, F, 0 as false.

Example: Convert to Boolean
SELECT 
   CAST('true' AS BOOLEAN),
   CAST('false' as BOOLEAN),
   CAST('T' as BOOLEAN),
   CAST('F' as BOOLEAN),   
   CAST('1' as BOOLEAN),
   CAST('0' as BOOLEAN);
CAST Operator

The CAST operator can be applied to an expression that evaluates to some value or table column also.

PostgreSQL does implicit type conversion, for example, if you try to perform a mathematical operation on one numeric value and another text datatype with a numeric value.

Sample Table

Above 100 is numeric and '2' is text, when we try to perform an addition operation between them, PostgreSQL converts text to an integer, performs addition to 100, and gives the result as integer.

PostgreSQL supports type cast operator :: to convert the value of one data type to another.

Sample Table

Note: The cast syntax using the cast operator :: is PostgreSQL-specific and does not belong to SQL standards.

CAST Column Values

Let's use the following Employee table to see how to cast column values.

Sample Table

The above Salary column is Integer, we can CAST it to NUMERIC with 10 digits and precision as 2.

Example: Cast Column Values
SELECT first_name, last_name, CAST(salary AS NUMERIC(10,2))  FROM Employee;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.