Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YpZdO-0002EY-Co for pgsql-admin@arkaria.postgresql.org; Tue, 05 May 2015 09:59:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YpZdN-00062a-TE for pgsql-admin@arkaria.postgresql.org; Tue, 05 May 2015 09:59:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YpZdM-00061i-Ni for pgsql-admin@postgresql.org; Tue, 05 May 2015 09:59:24 +0000 Received: from moh2-ve1.go2.pl ([193.17.41.186]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1YpZdJ-0004Wp-Mb for pgsql-admin@postgresql.org; Tue, 05 May 2015 09:59:23 +0000 Received: from moh2-ve1.go2.pl (unknown [10.0.0.186]) by moh2-ve1.go2.pl (Postfix) with ESMTP id 2998344D041 for ; Tue, 5 May 2015 11:59:10 +0200 (CEST) Received: from o2.pl (unknown [10.0.0.7]) by moh2-ve1.go2.pl (Postfix) with SMTP for ; Tue, 5 May 2015 11:59:10 +0200 (CEST) Subject: =?UTF-8?Q?display_to=5Ftimestamp_in_?= =?UTF-8?Q?quotas_or_convert_to_char_=3F?= From: =?UTF-8?Q?czezz?= To: pgsql-admin@postgresql.org Mime-Version: 1.0 Message-ID: <22c93a48.7dc1250c.5548946d.f29b0@o2.pl> Date: Tue, 05 May 2015 11:59:09 +0200 X-Originator: 84.74.156.204 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin 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 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 Table "srx.ss21_file" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Column=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Type=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Modifiers -------------------+------------------------+----------- value=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 | character varying(30)=C2=A0 | fileindex=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | bigint=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 | filename=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | character = varying(80)=C2=A0 | filetime=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | character = varying(100) | filesizeinrecords | bigint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | filesizeinbytes=C2=A0=C2=A0 | bigint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | sourceid=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | character = varying(300) | Indexes: =C2=A0=C2=A0=C2=A0 "ss21_filetime" UNIQUE, btree (filetime) When executing following query I get following error: Server2=3D# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords,= fileSizeInBytes, sourceID=C2=A0=C2=A0 FROM ss21_file WHERE filetime < to_t= imestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'); ERROR:=C2=A0 operator does not exist: character varying < timestamp with ti= me zone LINE 1: ...ytes, sourceID=C2=A0=C2=A0 FROM ss21_file WHERE filetime < to_ti= mes... =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^ HINT:=C2=A0 No operator matches the given name and argument type(s). You mi= ght need 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=C2=A0=C2=A0 FROM ss21_file WHERE filetime < '201= 5-05-04 22:13:14.000'; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 value=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 | fileindex |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 filename=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 filetime=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 | filesizeinrecords | filesizeinbytes |=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 sourceid ----------------------+-----------+---------------------------------------+= -------------------------+-------------------+-----------------+-----------= ------------------ ------- -903097975225368365=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0 94859 | ss21_2014111802= 0501_1166.DAT | 2014-11-18 02:05:30.109 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2775 |=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 490560 | coll_SS21-Group_0_141627273 0_1634 -2781431802746610881 |=C2=A0=C2=A0=C2=A0=C2=A0 94868 | ss21_20141118042502_= 1175.DAT | 2014-11-18 04:25:30.154 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1803 |=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 310688 | coll_SS21-Group_0_141628113 0_1643 -4731211533677294393 |=C2=A0=C2=A0=C2=A0=C2=A0 94878 | ss21_20141118065501_= 1185.DAT | 2014-11-18 06:55:30.380 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 14745 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 2640848 | coll_SS21-Group_0_141629013 --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin