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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
