Skip site navigation (1) Skip section navigation (2)

CAST and timestamp

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: CAST and timestamp
Date: 2004-12-20 21:58:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hi All,

I am receiving a quantity as text and a seperate date and time as text.  The
quantity is written into a varchar(20) column and the date and time are
written into char(8) and char(6) columns respectively.  I would like to
convert the information for proper storage.  When I perform a query to CAST
the quantity into a real and CAST the two text columns into a timestamp column
I receive errors.  I have tried to find the documentation on the CASTs to no
avail.  I need to know how to convert the quantity peroperly and I would
prefer to not specify the time zone and use the value from the host computer.
 Any information would be appreciated.

IPADB=# \d data_transfer.tbl_inventory_scanner
   Table "data_transfer.tbl_inventory_scanner"
   Column    |         Type          | Modifiers 
 employee_id | character varying(20) | 
 item_id     | character varying(20) | not null
 quantity    | character varying(20) | 
 scan_date   | character(8)          | not null
 scan_time   | character(6)          | not null
Indexes: tbl_inventory_scanner_pkey primary key btree (scan_date, scan_time,

IPADB=# SELECT * FROM data_transfer.tbl_inventory_scanner;
 employee_id | item_id | quantity | scan_date | scan_time 
 1116A       | SAC38   | 55       | 20041220  | 160933
 1116A       | SEB12   | 555      | 20041220  | 160947
 1116A       | SEBM106 | 888      | 20041220  | 160953
 1116A       | B346.0  | 555      | 20041220  | 161003
 1116A       | B346.5  | 888      | 20041220  | 161011
(5 rows)

IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
FROM data_transfer.tbl_inventory_scanner;
ERROR:  Cannot cast type character to timestamp without time zone

IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
ERROR:  Cannot cast type character varying to real

Kind Regards,

99main Internet Services


pgsql-novice by date

Next:From: sarlav kumarDate: 2004-12-20 22:25:26
Subject: slony replication
Previous:From: George WeaverDate: 2004-12-20 18:59:58
Subject: Re: Connection problem with 8 Beta 5

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group