Product Documentation

Appearance

All platforms

Implicit Casting Examples ​

This section shows some examples of queries in which an implicit cast converts a value or expression of a given data type to another data type.

Casts for Dates and Timestamps ​

The following example implicitly casts a TIMESTAMP column (matchday) to a character string in order to return the result of the SUBSTR function.

premdb=# select matchday, substr(matchday,1,4) yr from match where htid=3 and atid=56;
     matchday       |  yr  
---------------------+------
 2010-11-10 00:00:00 | 2010
(1 row)

The following example inserts a DATE value into a TIMESTAMP column:

premdb=# insert into match(matchday) select current_date;
INSERT 0 1
premdb=# select * from match where matchday=(select current_date);
 seasonid |      matchday       |  htid  |  atid  | ftscore | htscore 
----------+---------------------+--------+--------+---------+---------
   [NULL] | 2020-09-25 00:00:00 | [NULL] | [NULL] | [NULL]  | [NULL]
(1 row)

This example uses a table called chartime, which contains four VARCHAR columns. However, these columns contain VARCHAR strings that represent TIME, TIMESTAMP, TIMESTAMPTZ, and DATE values.

premdb=# \d chartime
         Table "public.chartime"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 c1     | character varying(12) | 
 c2     | character varying(24) | 
 c3     | character varying(24) | 
 c4     | character varying(10) | 

Distribution: Hash (c1)

premdb=# select * from chartime;
   c1    |         c2          |           c3           |     c4     
----------+---------------------+------------------------+------------
 00:00:00 | 2012-12-22 00:00:00 | 2012-12-22 00:00:00-07 | 2012-12-22
 12:55:00 | 2020-09-25 12:55:00 | 2020-09-25 12:55:00-07 | 2020-09-25
(2 rows)

Implicit casts make it possible to query and compare these columns directly with datetime functions such as EXTRACT. For example:

premdb=# select extract(day from c2) from chartime;
 date_part 
-----------
       25
       22
(2 rows)

You can use an EXPLAIN command with the VERBOSE option to see the implicit cast on the c2 column in this example:

premdb=# explain (verbose) select extract(day from c2) from chartime;
                                              QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 id   rows_planned   mem_planned   mem_actual   workers   node   
  1            100      8.00Mi H         0.00       all   SELECT    
                                                           (EXTRACT(day FROM chartime.c2::TIMESTAMPTZ))
                                                           distribute on (chartime.c1)
  3            100     57.00Mi H         0.00       all   SCAN chartime   
                                                           (chartime.c2)
                                                           distribute on (chartime.c1)
...

A join condition between one a VARCHAR column in the chartime table and the match.matchday TIMESTAMP column also benefits from an implicit cast:

premdb=# select * from match, chartime where match.matchday=chartime.c2;
 seasonid |      matchday       | htid | atid | ftscore | htscore |    c1    |         c2          |           c3           |     c4     
----------+---------------------+------+------+---------+---------+----------+---------------------+------------------------+------------
      21 | 2012-12-22 00:00:00 |   44 |   78 | 2-1     | 1-1     | 00:00:00 | 2012-12-22 00:00:00 | 2012-12-22 00:00:00-07 | 2012-12-22
      21 | 2012-12-22 00:00:00 |   45 |   67 | 1-2     | 1-0     | 00:00:00 | 2012-12-22 00:00:00 | 2012-12-22 00:00:00-07 | 2012-12-22
      21 | 2012-12-22 00:00:00 |   46 |   51 | 0-1     | 0-0     | 00:00:00 | 2012-12-22 00:00:00 | 2012-12-22 00:00:00-07 | 2012-12-22
...

Again you can use the EXPLAIN command to see the implicit cast:

premdb=# explain select * from match, chartime where match.matchday=chartime.c2;
                                               QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1         396783       all   SELECT    
 10         396783       all   INNER JOIN ON (chartime.c2::TIMESTAMP = match.matchday)   
 13        8812544       all   |-SCAN match   
                              |   match.matchday = bloom(2) AND scan_constraints: min_max(match.matchday)
  2            100       all   |-BUILD    
  3            100       all     DISTRIBUTE REPLICATE    
  5            100       all     SCAN chartime   
...

In this example, a VARCHAR column contains a character string for a date. When the DATEADD function is applied to this string, the highest type in the datetime hierarchy (TIMESTAMPTZ) is used for the implicit cast:

premdb=# \d vardate
          Table "public.vardate"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 c1     | character varying(10) | 

Distribution: Hash (c1)

premdb=# insert into vardate values('2020-12-31');
INSERT 0 1
premdb=# select dateadd(month, 1, c1) from vardate;
       dateadd         
------------------------
 2021-01-31 00:00:00-08
(1 row)

premdb=# explain verbose select dateadd(month, 1, c1) from vardate;
                                              QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 id   rows_planned   mem_planned   mem_actual   workers   node   
  1            100     32.00Mi H         0.00       all   SELECT    
                                                           (DATEADD(month, $0, vardate.c1::TIMESTAMPTZ)
                                                           distribute on (vardate.c1)
  3            100     57.00Mi H         0.00       all   SCAN vardate   
                                                           (vardate.c1)
                                                           distribute on (vardate.c1)
 ...

Numbers as Character Strings ​

When the CONCAT function concatenates data with different types, the values are implicitly cast to VARCHAR. In this case, teamid and capacity are integer columns:

premdb=# select concat(teamid,' ',name,' ',capacity) from team limit 3;
       concat         
-----------------------
 37 Stoke City 27902
 38 Sunderland 49000
 39 Swansea City 20520
(3 rows)

premdb=# explain verbose select concat(teamid,' ',name,' ',capacity) from team limit 3;
                                                                QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 id   rows_planned   mem_planned   mem_actual   workers   node   
  1              3     32.98Mi H         0.00    single   SELECT    
                                                           (team.teamid::VARCHAR(6) || $0 || team.name || $1 || team.capacity::VARCHAR(11))
                                                           distribute single
  2              3      4.00Mi H         0.00    single   LIMIT $4   
                                                           (team.teamid::VARCHAR(6) || $0 || team.name || $1 || team.capacity::VARCHAR(11))
                                                           distribute single
  4            100     57.00Mi H         0.00    single   SCAN team   
                                                           (team.teamid, team.name, team.capacity)
                                                           distribute single
...

You can implicitly cast an integer column to a character string in order to apply a LIKE condition:

premdb=# select * from team where capacity like '75%';
 teamid | htid | atid |       name        |  nickname  |    city    |   stadium    | capacity 
--------+------+------+-------------------+------------+------------+--------------+----------
    25 |   26 |   75 | Manchester United | Red Devils | Manchester | Old Trafford |    75635
(1 row)

premdb=# explain select * from team where capacity like '75%';
                               QUERY PLAN                                 
---------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1              1    single   SELECT    
  3              1    single   SCAN team   
                                team.capacity::VARCHAR(64000)  LIKE '$0'

The following query tries to subtract one number from another number, using SUBSTR to extract the numbers from the ftscore and htscore. However, these columns are stored as character strings:

premdb=# \d match
               Table "public.match"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                |

By default, an implicit cast to a TIMESTAMPTZ is chosen and attempted for the SUBSTR expression, but the query fails.

premdb=# select seasonid,htid,atid,ftscore,htscore,substr(ftscore,1,1)-substr(htscore,1,1) goaldiff 
from match 
where seasonid=22 and htid=2 and atid between 50 and 75 
order by 1,2,3;
ERROR:  invalid datetime format
DETAIL:   [reason => invalid input syntax for type timestamp with time zone: "1"]

If one of the arguments in the expression is explicitly cast to an integer, the query succeeds:

premdb=# select seasonid,htid,atid,ftscore,htscore,
substr(ftscore,1,1)::smallint-substr(htscore,1,1) goaldiff 
from match 
where seasonid=22 and htid=2 and atid between 50 and 75 
order by 1,2,3                                                                                                                                                   ;
 seasonid | htid | atid | ftscore | htscore | goaldiff 
----------+------+------+---------+---------+----------
      22 |    2 |   52 | 1-3     | 1-1     |        0
      22 |    2 |   61 | 2-0     | 0-0     |        2
      22 |    2 |   63 | 0-0     | 0-0     |        0
      22 |    2 |   65 | 2-0     | 0-0     |        2
      22 |    2 |   67 | 1-1     | 0-0     |        1
      22 |    2 |   68 | 2-0     | 0-0     |        2
      22 |    2 |   69 | 2-0     | 1-0     |        1
      22 |    2 |   73 | 2-0     | 1-0     |        1
      22 |    2 |   74 | 1-1     | 0-1     |        1
      22 |    2 |   75 | 0-0     | 0-0     |        0
(10 rows)

Adding Two Character Strings ​

This example shows the expected behavior when both sides of an addition operator are character strings. The query returns an error because the expected calculation is ambiguous in this context:

premdb=# create table varnum(c1 char(5), c2 varchar(5));
CREATE TABLE
premdb=# insert into varnum values(1,1);
INSERT 0 1
premdb=# insert into varnum values(10,20);
INSERT 0 1
premdb=# select c1+c2 from varnum;
ERROR:  operator is not unique: character + character varying
LINE 1: select c1+c2 from varnum;
                ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Note that when you cast one side of the addition operator explicitly, the other side is implicitly cast to match that type. In the first example, column c2 is implicitly cast to an integer; in the second example, c2 is implicitly cast to an interval:

premdb=# select c1::int+c2 from varnum;
 ?column? 
----------
       2
      30
(2 rows)

premdb=# select c1::interval+c2 from varnum;
 ?column? 
----------
 00:00:02
 00:00:30
(2 rows)

MACADDR Cast to MACADDR8 ​

Seven rows are inserted into the following table, and the MACADDR values for column c1 are cast implicitly to MACADDR8 for insertion into column c2.

premdb=# \d mac
     Table "public.mac"
 Column |   Type   | Modifiers 
--------+----------+-----------
 c1     | macaddr  | 
 c2     | macaddr8 | 

Distribution: Hash (c1)

premdb=# insert into mac 
premdb-# values('08:00:2b:01:02:03', '08:00:2b:01:02:03'),
premdb-#       ('08-00-2b-01-02-03', '08-00-2b-01-02-03'),
premdb-#       ('08002b:010203', '08002b:010203'),
premdb-#       ('08002b-010203', '08002b-010203'),
premdb-#       ('0800.2b01.0203', '0800.2b01.0203'),
premdb-#       ('0800-2b01-0203', '0800-2b01-0203'),
premdb-#       ('08002b010203', '08002b010203');
INSERT 0 7
premdb=# select * from mac;
       c1         |           c2            
-------------------+-------------------------
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
 08:00:2b:01:02:03 | 08:00:2b:ff:fe:01:02:03
(7 rows)

IPV4 Cast to IPV6 ​

The following example shows an IPV4 value cast to an IPV6 value for storage in an IPV6 column:

premdb=# select * from ipv4_ipv6;
     c1       |                   c2                    
---------------+-----------------------------------------
 19.120.51.251 | 684d:1111:0222:3333:4444:5555:0006:0077
(1 row)

premdb=# insert into ipv4_ipv6(c2) select c1 from ipv4_ipv6;
INSERT 0 1
premdb=# select * from ipv4_ipv6;
     c1       |                   c2                    
---------------+-----------------------------------------
 [NULL]        | 0000:0000:0000:0000:0000:ffff:1378:33fb
 19.120.51.251 | 684d:1111:0222:3333:4444:5555:0006:0077
(2 rows)

premdb=# explain (verbose) insert into ipv4_ipv6(c2) select c1 from ipv4_ipv6;
                                      QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 id   rows_planned   mem_planned   mem_actual   workers   node   
  2              1      3.00Gi H         0.00       all   INSERT INTO ipv4_ipv6   
                                                           (NULL, ipv4_ipv6.c1::IPV6)
                                                           distribute on (NULL)
  3              1    272.00Mi H         0.00       all   DISTRIBUTE ON HASH(NULL)   
                                                           (NULL, ipv4_ipv6.c1::IPV6)
                                                           distribute on (NULL)                                                        distribute on (ipv4_ipv6.c1)
...