CODEWITHSUNDEEP
pythondjangopostgresql
Cheok Yan Cheng
Cheok Yan Cheng

Reputation: 42710

How to ensure Django generate postgres table schema for "timestamp with time zone" with default "now()"

I have the following django data model

class ApiLog(models.Model):
    name = models.TextField(blank=False, null=False)
    ts = models.DateTimeField(default=timezone.now, blank=False, null=False)
    ip_address = models.GenericIPAddressField(blank=True, null=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)

It ends up with database

django=# \d+ users_apilog
                                                           Table "public.users_apilog"
   Column   |           Type           | Collation | Nullable |                 Default                  | Storage  | Stats target | Description
------------+--------------------------+-----------+----------+------------------------------------------+----------+--------------+-------------
 id         | integer                  |           | not null | nextval('users_apilog_id_seq'::regclass) | plain    |              |
 name       | text                     |           | not null |                                          | extended |              |
 ts         | timestamp with time zone |           | not null |                                          | plain    |              |
 ip_address | inet                     |           |          |                                          | main     |              |
 user_id    | integer                  |           | not null |                                          | plain    |              |
Indexes:
    "users_apilog_pkey" PRIMARY KEY, btree (id)
    "users_apilog_user_id_2eb2b1cf" btree (user_id)
Foreign-key constraints:
    "users_apilog_user_id_2eb2b1cf_fk_users_user_id" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED

Since, this table will also be accessed by non-django app. I need to make sure the auto timestamp generation (For column ts) is fully handled by postgres, not python.

I don't expect to have

   Column   |           Type           | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 ts         | timestamp with time zone |           | not null |         | plain   |              |

I expect to have

   Column   |           Type           | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 ts         | timestamp with time zone |           | not null | now()   | plain   |              |

I had tried other technique like auto_now=True, auto_now_add=True, ...

But, none of them generate the table schema I want. By using any below, the generated table schema, its default column is still empty.

Upvotes: 1

Views: 799

Answers (1)

Cheok Yan Cheng
Cheok Yan Cheng

Reputation: 42710

Since, it is pretty common to use docker these day. I demonstrate how I solve it, based on docker development environment.

Step 0 : Make sure you had already committed generated 0001_initial.py

Step 1 : Generate empty migration file

docker-compose run --rm -v %cd%/django:/app -w /app django /app/manage.py makemigrations users --empty -n alter_ts_default_to_now

Step 2 : Use raw SQL in migration file

# Generated by Django 2.1 on 2018-08-25 09:27

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('users', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            "ALTER TABLE users_apilog ALTER COLUMN ts SET DEFAULT now()",
        )    
    ]

Step 3 : Run migration again

#!/bin/sh

python manage.py makemigrations users

python manage.py makemigrations

python manage.py migrate

echo "yes" | python manage.py collectstatic

exec "$@"

Upvotes: 1

Related Questions