ProgrammingError while migrating Django DB to Postgre

Using Django
1

Hello,

I am trying to migrate my django project to use a postgreSQL database. My settings.py database dict looks like this.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'anubhav',
        'USER': 'anubhav',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '2929',
    }
}

I am using the Postgres.app installer for the DB, and therefore the password is empty and the DB name and user is the username, as given in their website. However, I get the following error.

django.db.utils.ProgrammingError: syntax error at or near "100000000000000"

How do I resolve this?

2

See the docs for the settings for the database engine

1 Like
3

Hey @KenWhitesell,

Thanks a lot, will check it out. Meanwhile, as I was offline from forums, I was looking at alternative databases as well. Basically, I need a good database to replace SQLite3 when I deploy my web app. While looking for alternatives to Postgre, I found Djongo, a MongoDB connector for django. Setup was much easier than Postgre, so is this a viable option for production, or should I still go for Postgre?

4

I believe that Djongo is only effective as a secondary database, not as a replacement for the core Django environment. (At least it wasn’t suitable for that role the last time I looked at it.) This means that implementing Djongo means you’re going to be installing and managing two databases.

While I’m a huge PostgreSQL fan, I acknowledge it’s not everyone’s cup of tea. If you’re looking for alternatives, I think you would find MariaDB (or MySQL) to be an adequate alternative for most purposes.

But since you already have PostgreSQL installed, I wouldn’t see a reason to switch. (I’d also create a user with a password - I wouldn’t leave access unprotected.)

Ken

5

In that case, Djongo has indeed got better. My primary question was whether it will, unlike SQLite3, allow two users to commit to the database at the same time. This is actually more about MongoDB, not Djongo itself. Also, if I try the backend which you mentioned, I get the same error.

6

Can you confirm that you’re actually running PostgreSQL on port 2929 instead of its normal 5432?

What operating system are you running this on?

Also, what command is generating this error, and can you post the complete stack dump?

Finally, can you confirm that you can connect to that database using those credentials using the psql command line interface?

7

Confirmed.

Screenshot 2020-08-11 at 9.35.13 AM

Running this on macOS Catalina, fully updated.

8

Error is generated by python manage.py migrate.

FULL STACK DUMP BELOW

Operations to perform:
  Apply all migrations: admin, app1, auth, contenttypes, sessions
Running migrations:
  Applying app1.0001_initial...Traceback (most recent call last):
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.SyntaxError: syntax error at or near "100000000000000"
LINE 1: ...har(264) NOT NULL PRIMARY KEY, "password" varchar(1000000000...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 234, in handle
    fake_initial=fake_initial,
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/migrations/operations/models.py", line 92, in database_forwards
    schema_editor.create_model(model)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 307, in create_model
    self.execute(sql, params or None)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/anubhav/Desktop/PathX/Hackathon_ENV/lib/python3.5/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: syntax error at or near "100000000000000"
LINE 1: ...har(264) NOT NULL PRIMARY KEY, "password" varchar(1000000000...
9

Ok, so there’s something wrong in the migration file app1.0001_initial… (whatever the full name happens to be).

Was this an automatically generated migration file, or one manually created (or edited)?

What’s the output from manage.py sqlmigrate app1 001_initial… (again, whatever the full name really is)?

I can guess that it’s likely that the database engines could create different migration files for the different databases. You might want to delete your app’s migrations and recreate them with makemigrations.

10

I’ve done that @KenWhitesell, same error.

12

Automatically generated.

I’ll check and get back to you in 1 hr, currently afk.

1 Like
13

Also please find out what version of Django and psycopg2 you’re using.

14

Django 3.0, psycopg-binary latest release. Python 3.8.

15

Your stack trace doesn’t show Python 3.8. It shows that you’re using 3.5.

From the Django 3.0 release notes:

The Django 2.2.x series is the last to support Python 3.5.

16

Ah my bad then. I’ll do python3.8 manage.py migrate then, must have been a mistake on my end. From memory, error is the same if I do that.

17

You might also want to re-do the makemigrations using 3.8 as well to make sure they were created under the right version.

1 Like
18

@KenWhitesell, I have deleted all migrations, so the 0001… file isn’t there anymore. Same output w/ python3.8. How do I use the sqlmigrate command with the 0001 file? Never used it before, so require some guidance.

19

Ok, I figured it out. Here’s the output.

System check identified some issues:

WARNINGS:
app1.Faculty.date_joined: (fields.W161) Fixed default value provided.
        HINT: It seems you set a fixed date / time / datetime value as default for this field. This may not be what you want. If you want to have the current date as default, use `django.utils.timezone.now`
app1.Report.date: (fields.W161) Fixed default value provided.
        HINT: It seems you set a fixed date / time / datetime value as default for this field. This may not be what you want. If you want to have the current date as default, use `django.utils.timezone.now`
app1.Students.date_joined: (fields.W161) Fixed default value provided.
        HINT: It seems you set a fixed date / time / datetime value as default for this field. This may not be what you want. If you want to have the current date as default, use `django.utils.timezone.now`
BEGIN;
--
-- Create model Activity
--
CREATE TABLE "app1_activity" ("name" varchar(264) NOT NULL, "description" varchar(1000) NOT NULL, "admin" varchar(264) NOT NULL, "primary_k" varchar(264) NOT NULL PRIMARY KEY, "batches" integer NOT NULL);
--
-- Create model Assignment
--
CREATE TABLE "app1_assignment" ("student" varchar(264) NOT NULL, "name" varchar(264) NOT NULL, "date" varchar(264) NOT NULL, "admin" varchar(264) NOT NULL, "month" varchar(264) NOT NULL, "year" varchar(264) NOT NULL, "refLink" varchar(200) NOT NULL, "content" varchar(2500) NOT NULL, "activity" varchar(264) NOT NULL, "submitted" boolean NOT NULL, "studentLink" varchar(200) NOT NULL, "submittedDate" varchar(264) NOT NULL, "studentDesc" varchar(2500) NOT NULL, "finalMarks" integer NOT NULL, "primary_k" varchar(264) NOT NULL PRIMARY KEY);
--
-- Create model BatchActivity
--
CREATE TABLE "app1_batchactivity" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(264) NOT NULL, "activity" varchar(264) NOT NULL, "start_time" varchar(264) NOT NULL, "end_time" varchar(264) NOT NULL, "day" varchar(264) NOT NULL, "number" integer NOT NULL);
--
-- Create model Faculty
--
CREATE TABLE "app1_faculty" ("id" serial NOT NULL PRIMARY KEY, "firstName" varchar(264) NOT NULL, "lastName" varchar(264) NOT NULL, "date_joined" date NOT NULL, "admin" varchar(264) NOT NULL, "profile_photo" varchar(200) NOT NULL);
--
-- Create model Report
--
CREATE TABLE "app1_report" ("student" varchar(264) NOT NULL, "subject" varchar(264) NOT NULL, "date" date NOT NULL, "content" varchar(2500) NOT NULL, "month" varchar(264) NOT NULL, "year" varchar(264) NOT NULL, "admin" varchar(264) NOT NULL, "marks" integer NOT NULL, "primary_k" varchar(264) NOT NULL PRIMARY KEY);
--
-- Create model StudentActivity
--
CREATE TABLE "app1_studentactivity" ("id" serial NOT NULL PRIMARY KEY, "student" varchar(264) NOT NULL, "activity" varchar(264) NOT NULL);
--
-- Create model StudentBatch
--
CREATE TABLE "app1_studentbatch" ("id" serial NOT NULL PRIMARY KEY, "student" varchar(264) NOT NULL, "batch" integer NOT NULL, "activity" varchar(264) NOT NULL);
--
-- Create model Students
--
CREATE TABLE "app1_students" ("firstName" varchar(264) NOT NULL, "lastName" varchar(264) NOT NULL, "email" varchar(264) NOT NULL PRIMARY KEY, "password" varchar(100000000000000) NOT NULL, "age" integer NOT NULL CHECK ("age" >= 0), "school" varchar(264) NOT NULL, "locality" varchar(264) NOT NULL, "date_joined" timestamp with time zone NOT NULL, "admin" varchar(264) NOT NULL);
--
-- Create model UserProfileInfo
--
CREATE TABLE "app1_userprofileinfo" ("id" serial NOT NULL PRIMARY KEY, "type" boolean NOT NULL, "center_name" varchar(264) NOT NULL, "finish_value" boolean NOT NULL, "user_id" integer NOT NULL UNIQUE);
CREATE INDEX "app1_activity_primary_k_c67150e3_like" ON "app1_activity" ("primary_k" varchar_pattern_ops);
CREATE INDEX "app1_assignment_primary_k_6ecfde59_like" ON "app1_assignment" ("primary_k" varchar_pattern_ops);
CREATE INDEX "app1_report_primary_k_2b59b9cd_like" ON "app1_report" ("primary_k" varchar_pattern_ops);
CREATE INDEX "app1_students_email_851730b8_like" ON "app1_students" ("email" varchar_pattern_ops);
ALTER TABLE "app1_userprofileinfo" ADD CONSTRAINT "app1_userprofileinfo_user_id_91096755_fk_auth_user_id" FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED;
COMMIT;
20

Ok, so based upon the stack trace at the beginning and the generated sql, it’s complaining about the app1.students model. (That’s the table with the malformed sql clause.) Please post the definition for that model.

21

I have resolved the issue. The first thing I did was reduce the max_length of the student model, which seemed to be the first issue. Then, it had said that sessions already exists. After creating a new DB, it seems to work. Thanks for your assistance, I really could not have resolved it without your help.

1 Like