Subject: =?UTF-8?Q?display_to=5Ftimestamp_in_quotas_?=
 =?UTF-8?Q?or_convert_to_char?=
From: =?UTF-8?Q?czezz?= <czezz@o2.pl>
To: pgsql-admin@postgresql.org
Mime-Version: 1.0
Message-ID: <657111c9.5817f52.5548a8e0.89d25@o2.pl>
Date: Tue, 05 May 2015 13:26:24 +0200
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hello,
This post might be unreadable in your mail client. If so try to copy it in =
to notepad as there is a long query statement.


I have following table in the DB.

Server2=3D# \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=3D# 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 zo=
ne
LINE 1: ...ytes, sourceID   FROM ss21_file WHERE filetime < to_times...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might n=
eed to add explicit type casts.

My assumption is that, it is because "filetime" column is "character varyin=
g" and in WHERE condition value there is converted to timestamp.
When I execute this same query but instead to_timestamp I use regular strin=
g eg. '2015-05-04 22:13:14.000' (NOTE: single quotas) it works quite good.
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 ?

Server2=3D# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords,=
 fileSizeInBytes, sourceID   FROM ss21_file WHERE filetime < '2015-05-04 22=
:13:14.000';
        value         | fileindex |               filename                |=
        filetime         | filesizeinrecords | filesizeinbytes |           =
   sourceid

----------------------+-----------+---------------------------------------+=
-------------------------+-------------------+-----------------+-----------=
------------------
-------
-903097975225368365  |     94859 | ss21_20141118020501_1166.DAT | 2014-11-1=
8 02:05:30.109 |              2775 |          490560 | coll_SS21-Group_0_14=
1627273
0_1634
-2781431802746610881 |     94868 | ss21_20141118042502_1175.DAT | 2014-11-1=
8 04:25:30.154 |              1803 |          310688 | coll_SS21-Group_0_14=
1628113
0_1643
-4731211533677294393 |     94878 | ss21_20141118065501_1185.DAT | 2014-11-1=
8 06:55:30.380 |             14745 |         2640848 | coll_SS21-Group_0_14=
1629013

Best regards,
czezz


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin