In PostgreSQL, I need to make a LEFT JOIN between two columns that are different in data type. One of them is varchar and the other is time
LEFT JOIN my_table ON r.varchar_time = my_table.time
How can I compare them, by using to_char?
0
votes
You really ought to provide some sample data with this question, or we have to guess what it looks like.
– Paul Maxwell
2 Answers
1
votes
Type conversions may use an abbreviated syntax such as ::time so, you may be able to use:
LEFT JOIN my_table ON r.varchar_time::time = my_table.time
However if there are any values that don't convert to time that may error. You might go in the opposite direction, but now you must ensure that the format produced by the varchar conversion matches the other column's format.
LEFT JOIN my_table ON r.varchar_time = my_table.time::varchar
Either way you have potential problems. Ideally that varchar column would be time as well.
The other possible issue I see with this is that time precision might be a problem as well, but this depends on how you populate the columns.
test:
select
(current_timestamp::time)::varchar "varchar"
, current_timestamp::time "time"
+-----------------+------------------+
| varchar | time |
+-----------------+------------------+
| 00:50:20.703713 | 00:50:20.7037130 |
+-----------------+------------------+
As you can see from this test the "format" of your varchar data will matter
1
votes
Postgres has functions to convert from one datatype to another. From the docs:
to_timestamp(text, text) , timestamp with time zone, converts string to time stamp
Example: to_timestamp('05 Dec 2000', 'DD Mon YYYY')
Your code might look like this, using whichever format you need to match the other column.
LEFT JOIN my_table ON to_timestamp(r.varchar_time, 'HH:mm:ss') = my_table.time
