To match the values in the payment_date column with a list of dates, you need to cast them to date values that have the date part only.
To do that you use the :: cast operator:
payment_date::date
For example, if the timestamp value is 2007-02-15 22:25:46.996577, the cast operator will convert it to 2007-02-15.
PostgreSQL CAST：将一种类型的值转换为另一种类型
在许多情况下，您需要将一种类型的值转换为另一种类型的值。PostgreSQL提供CAST（）函数和强制转换操作符（::）来实现这一点。
PostgreSQL CAST() function
CAST(<code>value</code> AS target_type );
In this syntax:
- First, provide a
valuethat you want to convert. It can be a constant, a table column, or an expression. - Then, specify the target data type to which you want to convert the
value.
The CAST() returns a value after it has been cast to the specified target data type. If the CAST() function cannot cast the value to a target type, it’ll raise an error. The error message will depend on the nature of the conversion failure.
PostgreSQL cast operator (::)
value::target_type
In this syntax:
valueis a value that you want to convert.target_typespecifies the target type that you want to cast the value to.
The cast operator :: returns a value after casting the value to the target_type or raise an error if the cast fails.
Notice that the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard
#平时查询，不考虑数据库多个版本问题，使用简洁的::来转换类型，否则建议使用cast来转换类型
dvdrental=# select now()::date;
now
------------
2025-09-11
(1 row)
dvdrental=# select now();
now
-------------------------------
2025-09-11 11:38:22.792593+08
(1 row)
dvdrental=# select cast(now as date);
ERROR: column "now" does not exist
LINE 1: select cast(now as date);
^
dvdrental=# select cast(now() as date);
now
------------
2025-09-11
(1 row)
dvdrental=# select now();
now
-------------------------------
2025-09-11 11:38:45.798917+08
(1 row)
dvdrental=# select cast ('1000' as integer);
int4
------
1000
(1 row)
dvdrental=# select cast ('2c' as integer);
ERROR: invalid input syntax for type integer: "2c"
LINE 1: select cast ('2c' as integer);
^
dvdrental=# select cast('2025-01-01' as date), cast('01-oct-2015' as date);
date | date
------------+------------
2025-01-01 | 2015-10-01
(1 row)
dvdrental=# select cast('10.2' as double);
ERROR: type "double" does not exist
LINE 1: select cast('10.2' as double);
^
dvdrental=# select cast('10.2' as double precision);
float8
--------
10.2
(1 row)
dvdrental=# select cast('10.2' as float);
float8
--------
10.2
(1 row)
dvdrental=# SELECT
dvdrental-# CAST('true' AS BOOLEAN),
dvdrental-# CAST('false' as BOOLEAN),
dvdrental-# CAST('T' as BOOLEAN),
dvdrental-# CAST('F' as BOOLEAN);
bool | bool | bool | bool
------+------+------+------
t | f | t | f
(1 row)
dvdrental=# SELECT '2019-06-15 14:30:20'::timestamp;
timestamp
---------------------
2019-06-15 14:30:20
(1 row)
dvdrental=# SELECT
dvdrental-# '15 minute' :: interval,
dvdrental-# '2 hour' :: interval,
dvdrental-# '1 day' :: interval,
dvdrental-# '2 week' :: interval,
dvdrental-# '3 month' :: interval;
interval | interval | interval | interval | interval
----------+----------+----------+----------+----------
00:15:00 | 02:00:00 | 1 day | 14 days | 3 mons
(1 row)
dvdrental=# SELECT CAST('2024-02-01 12:34:56' AS DATE);
date
------------
2024-02-01
(1 row)
dvdrental=# SELECT CAST('30 days' AS TEXT);
text
---------
30 days
(1 row)
dvdrental=# SELECT CAST('{"name": "John"}' AS JSONB);
jsonb
------------------
{"name": "John"}
(1 row)
dvdrental=# SELECT CAST(9.99 AS INTEGER);
int4
------
10
(1 row)
dvdrental=# SELECT CAST(ARRAY[1, 2, 3] AS TEXT);
array
---------
{1,2,3}
(1 row)
dvdrental=# SELECT '{1,2,3}'::INTEGER[] AS result_array;
result_array
--------------
{1,2,3}
(1 row)
dvdrental=# CREATE TABLE ratings (
dvdrental(# id SERIAL PRIMARY KEY,
dvdrental(# rating VARCHAR (1) NOT NULL
dvdrental(# );
CREATE TABLE
dvdrental=# INSERT INTO ratings (rating)
dvdrental-# VALUES
dvdrental-# ('A'),
dvdrental-# ('B'),
dvdrental-# ('C');
INSERT 0 3
dvdrental=# INSERT INTO ratings (rating)
dvdrental-# VALUES
dvdrental-# (1),
dvdrental-# (2),
dvdrental-# (3);
INSERT 0 3
dvdrental=# SELECT * FROM ratings;
id | rating
----+--------
1 | A
2 | B
3 | C
4 | 1
5 | 2
6 | 3
(6 rows)
dvdrental=# SELECT
dvdrental-# id,
dvdrental-# CASE WHEN rating~E'^\d+$' THEN CAST (rating AS INTEGER) ELSE 0 END as rating
dvdrental-# FROM
dvdrental-# ratings;
id | rating
----+--------
1 | 0
2 | 0
3 | 0
4 | 1
5 | 2
6 | 3
(6 rows)
