I have a table of information that is already populated and I am trying to update that table based on LIKE values contained in a json array.
My code:
db.execute(
"""
UPDATE table1
SET (id_2, url_2) =
(
SELECT (data ->> 'episodeGuid')::varchar(100) as id_2,
(data ->> 'trackViewUrl')::text as url_2
)
FROM json_array_elements(%s::json) AS item(data)
WHERE (podcasts.release_date) LIKE (data ->> 'releaseDate')::timestamp::date
""",
[Json(track_info)]
)
I am getting an error operator does not exist: date ~~ date. I have done the research and this type of error seems to be when comparing two different types of data. But the data types are both date type and it even says that in the error.
There is another table containing 'title' that corresponds to 'trackName' in the json data and using the line WHERE (LOWER(podcasts.title)) LIKE LOWER(data ->> 'trackName'), the query runs fine but the Title data does not always match. The date information does match but which is why I want to use it instead.
After trying various things, I finally got it to work with by converting both values to text with WHERE (podcasts.release_date)::text LIKE (data ->> 'releaseDate')::timestamp::date::text
I would like some help understanding what is happening here. Why does PSQL not compare them as dates but will as text?