alter column (using + where)

Question

I work with postgresql 9.3, I use this query to convert timestamp column to UTC.

ALTER TABLE table 
ALTER timestamp TYPE timestamp 
USING timestamp AT TIME ZONE 'UTC' ;

I need to add WHERE cluose to the query :

ALTER TABLE table 
ALTER timestamp TYPE timestamp where timestamp < '2015-01-06 00:00:00'
USING timestamp AT TIME ZONE 'UTC' ;

but, this is didn't work . there no clear resource on how to do it.

what is the requirement for altering column for particular rows? — SelvaS
– SelvaS, Commented Mar 11, 2015 at 7:30

pensnarik · Accepted Answer · 2015-03-11 07:18:29Z

2

You could apply ALTER TYPE construction only to whole column, not to some rows. If you want to convert timestamp value you should use UPDATE instead.

answered Mar 11, 2015 at 7:18

pensnarik

1,2723 gold badges12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yes , you're right. it works i used : UPDATE table set timestamp = timestamp AT TIME ZONE 'UTC' where timestamp < '2015-01-06 00:00:00'

Vao Tsun · Accepted Answer · 2015-03-11 07:22:41Z

2

you can't alter table column type using where, because "where" is DML and "alter" is DDL. Use update whe

answered Mar 11, 2015 at 7:22

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges