Re: problem with select into and timestamp.
От | Geoff |
---|---|
Тема | Re: problem with select into and timestamp. |
Дата | |
Msg-id | FDCF842C8B1DD311833C0090277C04B50C9542@augusta.planmatics.co.uk обсуждение исходный текст |
Ответ на | problem with select into and timestamp. ("Geoff" <geoff@metalogicplc.com>) |
Список | pgsql-admin |
yes, that's what I want, but I get another error... here's my sql. insert into document_status ( version, cdate, mdate, filename, direction, partner, msgtype, docref, status, statusdesc, unit ) select 1, (mst_date||' '||mst_time)::timestamp, (mst_date||' '||mst_time)::timestamp, mst_filename, mst_direction, mst_partner, mst_msgtype, mst_docref, mst_status, mst_statusdesc, mst_unit from ectmst the only trouble is I get a cast error. ERROR: Cannot cast type 'character' to 'timestamp with time zone'. I suppose this is because the date and time fields were previously character ( TEXT ) fields..... is there a way around this? many thanks Geoff -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jie Liang Sent: 09 August 2002 19:28 To: 'Geoff '; 'Pgsql-Admin (E-mail) ' Subject: Re: [ADMIN] problem with select into and timestamp. I hope this is what you want: urldb=# create table foo(d date,t time); CREATE urldb=# insert into foo values(CURRENT_DATE,CURRENT_TIME); INSERT 97260539 1 urldb=# select * from foo; d | t ------------+----------------- 2002-08-09 | 11:20:12.214437 (1 row) urldb=# create table foobar (newdate timestamp); CREATE urldb=# insert into foobar(newdate) select (d||' '||t)::timestamp as newdate from foo; INSERT 97260628 1 urldb=# select * from foobar; newdate ------------------------------- 2002-08-09 11:20:12.214437-07 (1 row) urldb=# \d foobar Table "foobar" Column | Type | Modifiers ---------+--------------------------+----------- newdate | timestamp with time zone | Jie Liang -----Original Message----- From: Geoff To: Pgsql-Admin (E-mail) Sent: 8/9/2002 3:32 AM Subject: [ADMIN] problem with select into and timestamp. I've got a small problem with "select into". I've got an old table with date and time as seperate text columns. I want to insert these into a new table into 1 column as timestamp format. I spent this morning looking at the 7.2 docs and spent ages working on something that's not supported (i.e. the timestamp (date '2002-08-09', time '11:29:00') ) function...arg! could someone show me the light on how I can get this working correctly and make my day easier. many thanks Geoff ----------------------------------------------------------------------- This message is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receive this message in error please contact the sender by return e-mail and delete the message from your computer. Any opinions contained in this message are those of the author and are not given or endorsed by Metalogic PLC unless otherwise clearly indicated in this message and the authority of the author to bind Metalogic is duly verified. Metalogic PLC accepts no liability for any errors or omissions in the context of this message which arise as a result of internet transmission. ----------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
В списке pgsql-admin по дате отправления: