How to reset primary key sequence from python program

Question

How to reset the sequence for IDs on PostgreSQL tables

I know that I can reset from terminal, but I want to reset it from Django python program. I am now trying to use RAW sql

postgres=# \c uihspot
You are now connected to database "uihspot" as user "postgres".
uihspot=# \dt
                    List of relations
 Schema |             Name             | Type  |  Owner
--------+------------------------------+-------+----------
 public | amway_accessrule             | table | postgres
 public | amway_log                    | table | postgres
 public | auth_group                   | table | postgres
 public | auth_group_permissions       | table | postgres
 public | auth_permission              | table | postgres
 public | auth_user                    | table | postgres
 public | auth_user_groups             | table | postgres
 public | auth_user_user_permissions   | table | postgres
 public | django_admin_log             | table | postgres
 public | django_content_type          | table | postgres
 public | django_migrations            | table | postgres
 public | django_session               | table | postgres
 public | member_profile_memberprofile | table | postgres
 public | ruckus_login_accesslog       | table | postgres
 public | ruckus_login_membertomac     | table | postgres
 public | sales_sales                  | table | postgres
 public | uploaded_files_uploadedfile  | table | postgres
(17 rows)

uihspot=# select currval('sales_sales_id_seq');
ERROR:  currval of sequence "sales_sales_id_seq" is not yet defined in this session
uihspot=# select currval('sales_sales_seq');
ERROR:  relation "sales_sales_seq" does not exist
LINE 1: select currval('sales_sales_seq');
                       ^
uihspot=# select currval('sales_sales_id_seq');
ERROR:  currval of sequence "sales_sales_id_seq" is not yet defined in this session
uihspot=# select currval('uihspot_sales_sales_id_seq');
ERROR:  relation "uihspot_sales_sales_id_seq" does not exist
LINE 1: select currval('uihspot_sales_sales_id_seq');
                       ^
uihspot=# select currval('uihspot.sales_sales_id_seq');
ERROR:  schema "uihspot" does not exist
LINE 1: select currval('uihspot.sales_sales_id_seq');

But I can not find the table!

My goal is to solve Postgres "Database is in recovery mode" which is raises through Django. I have check with the log file in the /var/log/postgres. It raise when I user bulk_create. Then I guess it might be a problem from id overflow. Then I am trying to reset sequence_id from the python not from terminal. I upload 1 round will increase id by ~100k — joe
– joe, Commented Jun 23, 2017 at 5:10
No you are barking up the wrong tree here. That error message clearly said, you just need to try the query again (in your other question). But as I mentioned in the answer, loading data through django objects.bulk_create simple isn't the right way to do this. — e4c5
– e4c5, Commented Jun 23, 2017 at 5:32
@e4c5 Thank you very much. I will try my best following your reply — joe
– joe, Commented Jun 23, 2017 at 5:41

Nishank Gupta · Accepted Answer · 2017-06-23 06:09:11Z

0

Just in case you are open to doing it from outside Python code (one time practice):-

You can use Pg admin 3 (Available for Both Linux and Windows ) .

Just select the database and then execute the sql:

ALTER SEQUENCE "my_table_name_id_seq" RESTART WITH 1;

Suggestion : First try on a copy and then on the main db.

I hope this is helpful.

answered Jun 23, 2017 at 6:09

Nishank Gupta

564 bronze badges

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

1 Comment

joe Over a year ago

Thank you for your response. I got the answer to my root causes by e4c5 already

Shah Vipul · Accepted Answer · 2021-01-31 10:35:11Z

0

Best Way

python3 manage.py dbshell
ALTER SEQUENCE "modelName_tableNAme_id_seq" RESTART WITH 1;

This will reset django modle object start again from zero(0)

answered Jan 31, 2021 at 10:35

Shah Vipul

7759 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to reset primary key sequence from python program

2 Answers 2

1 Comment

Best Way

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Best Way

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related