Postgres alter column from time without time zone to int

Question

I am having trouble converting a table column TYPE from time without time zone to integer. Is that possible? Where integer will be equal to seconds.

I am using ALTER table exampleTable ALTER COLUMN time TYPE integer.

Answer 1

This should do it:

 ALTER TABLE your_table 
     ALTER COLUMN time TYPE integer USING 0;

ALTER COLUMN is usually only used when you want to preserve the data in that column. In your case the above should work, but doesn't buy you anything. Dropping the column and then adding a new one with the type integer and a default value of 0 would be just as efficient.

(Btw: you should not use a reserved word like time as a column name)

Edit
If you do want to convert the existing data then you can use this:

 ALTER TABLE your_table 
     ALTER COLUMN time_column TYPE integer USING extract(epoch from time_column);

Answer 2

Following up on the horse's answer, you can also add an expression in the using part, e.g.:

ALTER TABLE your_table 
ALTER COLUMN time TYPE integer USING (
  extract('hours' from time) * 3600 +
  extract('minutes' from time) * 60 +
  extract('seconds' from time)
);

http://www.postgresql.org/docs/current/static/sql-altertable.html