FAQ
Hi,

I want to convert a column of type integer to type 'time without time zone'.

If I have a table with an integer column

some_int
----------
12
345
1622
1

Then, I add a column to the table of type 'time without time zone'
(some_time).
and, I can perform the update:

UPDATE tester SET some_time = CAST (to_char(some_int, 'FM99909:99') AS
time without time zone);

some_int | some_time
----------+-----------
12 | 00:12:00
345 | 03:45:00
1622 | 16:22:00
1 | 00:01:00


My question is: Is it possible to alter the 'some_int' column directly
without having to create a temporary 'some_time' holding column?

Thanks in advance,
C

Search Discussions

  • Michael Glaesemann at Jun 27, 2007 at 4:22 pm

    On Jun 27, 2007, at 10:36 , Carol Cheung wrote:

    My question is: Is it possible to alter the 'some_int' column
    directly without having to create a temporary 'some_time' holding
    column?
    Are you trying to alter the table to replace your some_int column
    with a some_time column? I believe you can do this in two steps.

    SELECT *
    FROM some_data;
    some_int
    ----------
    12
    345
    1622
    1
    (4 rows)

    ALTER TABLE some_data
    ALTER some_int TYPE TIME WITHOUT TIME ZONE
    USING CAST(to_char(some_int, 'FM99909:99') AS TIME WITHOUT
    TIME ZONE);

    ALTER TABLE some_data
    RENAME some_int TO some_time;

    SELECT *
    FROM some_data;
    some_time
    -----------
    00:12:00
    03:45:00
    16:22:00
    00:01:00
    (4 rows)

    Hope this helps.

    Michael Glaesemann
    grzm seespotcode net
  • Carol Cheung at Jun 27, 2007 at 10:09 pm
    Michael,
    Thank you for you help.
    This solution worked.

    On 27/06/2007 12:22, Michael Glaesemann wrote the following:
    On Jun 27, 2007, at 10:36 , Carol Cheung wrote:

    My question is: Is it possible to alter the 'some_int' column directly
    without having to create a temporary 'some_time' holding column?
    Are you trying to alter the table to replace your some_int column with a
    some_time column? I believe you can do this in two steps.

    SELECT *
    FROM some_data;
    some_int
    ----------
    12
    345
    1622
    1
    (4 rows)

    ALTER TABLE some_data
    ALTER some_int TYPE TIME WITHOUT TIME ZONE
    USING CAST(to_char(some_int, 'FM99909:99') AS TIME WITHOUT TIME
    ZONE);

    ALTER TABLE some_data
    RENAME some_int TO some_time;

    SELECT *
    FROM some_data;
    some_time
    -----------
    00:12:00
    03:45:00
    16:22:00
    00:01:00
    (4 rows)

    Hope this helps.

    Michael Glaesemann
    grzm seespotcode net



    ---------------------------(end of broadcast)---------------------------
    TIP 4: Have you searched our list archives?

    http://archives.postgresql.org

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 27, '07 at 3:37p
activeJun 27, '07 at 10:09p
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase