Convert data type of column to timestamp using explicit cast

Question

I'm running the following command to fix the error I've encountered with the Datefield() in my django model.

django.db.utils.ProgrammingError: column "end_date" cannot be cast 
automatically to type timestamp with time zone
HINT:  Specify a USING expression to perform the conversion.

Here is the line I'm running which doesn't seem to fix the problem:

ALTER TABLE merged_subscription ALTER COLUMN end_date TYPE timestamp USING to_timestamp(col, 'DD-MON-YYY');

I'm not very familiar with postresql. Is there something wrong with the logic here?

Here is my Django model for reference:

class Subscription(models.Model):
start_date = models.DateField()
end_date = models.DateField()
date = models.DateTimeField(auto_now_add=True, blank=True)
issue_one = models.ForeignKey(Issue, blank=True, null=True, related_name='issue_one')
issue_two = models.ForeignKey(Issue, blank=True, null=True, related_name='issue_two')
issue_three = models.ForeignKey(Issue, blank=True, null=True, related_name='issue_three')
def __unicode__(self):
    return unicode(self.start_date)

score 0 · Accepted Answer · edited May 23 '17 at 12:11

ALTER TABLE merged_subscription
 ALTER COLUMN end_date TYPE timestamp
 USING to_timestamp(end_date, 'DD-MON-YYY');

Replace the placeholder col with the actual column name.
Assuming that strings in end_date comply with the given (uncommon!) pattern. (?)

Are you sure you don't have 'DD-MON-YYYY'?

If you actually have a date column, you don't need a USING clause. There is an implicit cast between date and timestamp, and an assignment cast is all that's required.

ALTER TABLE merged_subscription
 ALTER COLUMN end_date TYPE timestamp;

Details:

Generate series of dates - using date type as input

Convert data type of column to timestamp using explicit cast

1 Answers1