Suppose we need to report the interval elapse for the user to complete every step of an entire operation. Scenario 6: Converting a String to Interval datatype To convert a string to timestamp datatype, we can do it writing: SELECT '02-12-2020 13:13:21'::timestamp Scenario 5: Converting a String to Timestamp datatype However, any number other than 0 and 1 means nothing from Boolean perspective and hence 3 cannot be converted to Boolean datatype. Also in short hand, T for True and F for false. However, if we try to convert the value 3 to Boolean datatype, it will fail with the error:īoolean values are represented as 1 for True and 0 for False. If we need to convert a string datatype to Boolean datatype, then we can use any of the following syntaxes: SELECT CAST('true' AS BOOLEAN), Scenario 4: Casting a String to Boolean datatype Instead, we have to use double precision to make it work. This code: SELECT '10.25'::DOUBLE PRECISION We might need to convert a string to double datatype, for which we would expect to write something like: SELECT '10.25'::DOUBLEĭouble is actually not a data type in PostgreSQL and hence it fails above. Scenario 3: Casting a String to Double datatype The obvious reason for the above cast failure is that the string date was not in any of the permissible formats discussed above. However, again if we try to CAST any string that is not in any of the standard date formats (for example, 01-0c-1994), the function will return an appropriate error as follows: The common date formats that can be used in string date are: MM/DD/YYYY, MM-DD-YYYY, MM-DD-YY, MM/DD/YY, YYYY/MM/DD, YYYY-MM-DD. We can write something like: SELECT CAST ('01-01-1990' AS DATE) In such case, consider we have a date of birth of an employee in a string format (say in MM-DD-YYYY format) and we want to convert it to a date datatype. Suppose we need to do some date calculations like estimating the age of an employee. Scenario 2: Casting a String to Date datatype Always note that the value being casted should be qualified to be casted to the resultant datatype. So if we drop the character part from the above example, it will work fine. So when we specify it to be cast to integer, the "to be casted" object should comprise of numbers only. The reason is that 100ab comprises of both numbers and characters. We get a proper result set where 100 is returned as an integer and not a string.īut if we want to cast an alphanumeric text, say "100ab " to integer, which is not possible. Like we discussed above, suppose we need to do some mathematical calculations on a number, which is actually stored in a varchar datatype column. If we write something like: SELECT '100'::integer We will understand few such DOs and DONTs with some simple examples. To use the CAST operator to type cast from datatype to another, we also need to ensure that the data can be converted to the target datatype or qualifies to be of the target datatype too. Just like other operations, CAST in PostgreSQL also has it's own DOs and DONTs. We can do it either writing: SELECT CAST(97 AS varchar) ĭifferent scenarios of CAST in PostgreSQL This means we need to convert an integer datatype to string datatype. Suppose we want the number 97 to be returned to us a String datatype. Instead of using the above syntax, we can also use the following condensed syntax: expression::type The target_type is the final datatype to which you want to convert the above expression to.The expression can be a constant or a table column or any expression that finally resolves to a value,.The syntax for using the CAST operator is: CAST ( expression AS target_type ) This can be done using the CAST operator. So, to add the numbers, we would need to cast them to integer once. In such case, the number/integer from such column will be returned as a varchar (or string). However, though the column stores numbers/integers, the datatype has been set as varchar by the database administrator. Suppose we need to add 2 numbers stored in 2 columns of a row/record in a table. Let's understand why do we need the casting technique (or briefly the CAST operator) in PostgreSQL with a very simple yet practical example. This article will illustrate the functions of CAST operator in PostgreSQL with hands-on examples. We can perform various cast operations in PostgreSQL for converting one datatype to another, such as the String datatype to the Integer datatype (or the Boolean datatype or datetime datatype) and vice versa. Cast is a technique in PostgreSQL with which we can convert a value of one datatype into another.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |