Обсуждение: convert column of integer type to time type?
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
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
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