Re: display to_timestamp in quotas or convert to char ? - Mailing list pgsql-admin

czezz wrote:
> I have following table in the DB.
> 
> Server2=# \d ss21_file
>               Table "srx.ss21_file"
>       Column       |          Type          | Modifiers
> -------------------+------------------------+-----------
> value             | character varying(30)  |
> fileindex         | bigint                 |
> filename          | character varying(80)  |
> filetime          | character varying(100) |
> filesizeinrecords | bigint                 |
> filesizeinbytes   | bigint                 |
> sourceid          | character varying(300) |
> Indexes:
>     "ss21_filetime" UNIQUE, btree (filetime)
> 
> When executing following query I get following error:
> 
> Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID
> FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> ERROR:  operator does not exist: character varying < timestamp with time zone
> LINE 1: ...ytes, sourceID   FROM ss21_file WHERE filetime < to_times...
>                                                              ^
> HINT:  No operator matches the given name and argument type(s). You might need to add explicit type
> casts.
> 
> My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition
> value there is converted to timestamp.

Right.  SQL is a typed language, so every expression has a data type.

"filetime" is "character varying", and the result of "to_timestamp" is
"timestamp with time zone":

test=> \df to_timestamp
                                  List of functions
   Schema   |     Name     |     Result data type     | Argument data types |  Type
------------+--------------+--------------------------+---------------------+--------
 pg_catalog | to_timestamp | timestamp with time zone | double precision    | normal
 pg_catalog | to_timestamp | timestamp with time zone | text, text          | normal

PostgreSQL cannot find an operator "<" with these argument types, even considering
implicit casts (see http://www.postgresql.org/docs/current/static/typeconv-func.html).

> When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04
> 22:13:14.000' (NOTE: single quotas) it works quite good.

That is because there is a comparison operator "text" < "text".

> If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character
> varying" or display its value in single quotas ?

You could explicitly case the result of "to_timestamp" to "text", like this:
  ... WHERE filetime < to_char(to_timestamp(...), '...')


But there are other solutions:

1) Define the "filetime" column as type "timestamp with time zone".
   This is normally the best solution.

2) Explicitly cast "filetime" to "timestamp with time zone", like this:
   ... WHERE to_timestamp(filetime, ...) < to_timestamp(...)
   This has the advantage of checking "filetime" for correctness.

Yours,
Laurenz Albe