Re: problem with select into and timestamp.

To case text to timestamp is not a problem,
so you can use:
    ......
    text(mst_date||' '||mst_time)::timestamp,
    text(mst_date||' '||mst_time)::timestamp,
    ......
to get around this!




Jie Liang

-----Original Message-----
From: Geoff [mailto:geoff@metalogicplc.com]
Sent: Monday, August 12, 2002 12:47 AM
To: 'Jie Liang'; 'Pgsql-Admin (E-mail) '
Subject: RE: [ADMIN] problem with select into and timestamp.


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)