0

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!

joe
  • 8,383
  • 13
  • 61
  • 109
  • Where are you planning on using this? In migration? – Sardorbek Imomaliev Jun 23 '17 at 05:07
  • 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 Jun 23 '17 at 05:10
  • 1
    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 Jun 23 '17 at 05:32
  • @e4c5 Thank you very much. I will try my best following your reply – joe Jun 23 '17 at 05:41

2 Answers2

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.

Nishank Gupta
  • 46
  • 4
  • Thank you for your response. I got the answer to my root causes by e4c5 already – joe Jun 23 '17 at 06:15
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)

Shah Vipul
  • 625
  • 7
  • 11