0

Please help me with this error:

File "/Users/ecommerce/proshopvenv/lib/python3.8/site-packages/django/db/backends/utils.py", 
line 84, in _execute

return self.cursor.execute(sql, params)

psycopg2.errors.CannotCoerce: cannot cast type date to time without time zone
LINE 1: ..." ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time


The above exception was the direct cause of the following exception:
"a lot of code, and:"

django.db.utils.ProgrammingError: cannot cast type date to time without time zone
LINE 1: ..." ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time

In models i have:

    createdAt = models.DateTimeField(auto_now_add=True)

and how i understand from docs https://docs.djangoproject.com/en/3.2/ref/models/fields/

"The auto_now and auto_now_add options will always use the date in the default timezone at the moment of creation or update."

But zone doesn't create, or error in something else, i creating new database, so i can delete all tables

Maybe there is step in django where it initials time zone?

Readed this topic postgreSQL alter column data type to timestamp without time zone and many others but can't fix it to 'migrate' without errors

Marat Mag
  • 23
  • 1
  • 6
  • Make sure You have a line in your setting.py which looks like this `TIME_ZONE = 'Asia/Kolkata'` – Vivek Anand May 02 '21 at 13:02
  • The issue is this: `ALTER COLUMN "createdAt" TYPE time USING "createdAt"::time"`. Something is trying to alter the `type` of your `createdAt` field away from some form of `date`(I presume) to a `time` type. That is not what you want. You need to track down what is doing this. – Adrian Klaver May 02 '21 at 15:36
  • TIME_ZONE = 'Asia/Kolkata' - i tried this also – Marat Mag May 02 '21 at 16:53
  • I think there is problem with modifying `date` to `time` without `time zone` – Marat Mag May 02 '21 at 17:13

3 Answers3

3

Yes there a problem modifying a date to time with or without time zone. The problem being that in Postgres date does not have a time component so any successful attempt to get time results in '00:00:00'. Try

select current_date::timestamp::time;

Given that then assuming your objective is the change the type then just drop and re-add the column;

alter table test_table drop createdat;
alter table test_table add  createdat time  default '00:00:00'::time;

Of course I have never figured out what good time is without the date. Which came first, '10:15:00' or '11:15:00'? Well if 10:15 was today and 11:15 was yesterday then 11:15 came first. Unless you define it as hours-since-midnight. So perhaps a better option:

alter table test_table 
      alter column createdat 
               set data type timestamp 
             using createdat::timestamp; 

See Demo Yes, timestamp takes more space, but is much more flexible.

Belayer
  • 13,578
  • 2
  • 11
  • 22
1

First i want say Thanks to Belayer, but have no rating to vote for his answer

I needed to Alter columns from type "date" to make them "timestamp with time zone", because my Django models configuration wanted to be them like this createdAt = models.DateTimeField(auto_now_add=True)

so i run alter table "base_product" alter column "createdAt" type timestamp with time zone;

But there was strange thing for me, all tables where there was type "date" modifyed to "timestamp with time zone" (it was good, because there where few other places where i need that), but only not in table "base_product" where i run query, and also didn't want to modify it automatically any more

so then i run ALTER TABLE "base_product" ALTER COLUMN "createdAt" DROP DEFAULT, ALTER COLUMN "createdAt" TYPE timestamp with time zone USING timestamp with time zone 'epoch' + "createdAt", ALTER COLUMN "createdAt" SET DEFAULT now();

i read that from postgres docs https://www.postgresql.org/docs/9.1/sql-altertable.html

Marat Mag
  • 23
  • 1
  • 6
0

In my case what a did was, delete de migrations to start from where all work fine, then I delete the field, apply a migrations and then a Added like a new column. And it works. Doing it in that way the django doesn't know tha instead of alter a column, he think that I create a new one.

Jup
  • 11
  • 1