PostgreSQL – how to flush a queue
Resetting the queue ID for the master key in the PostgreSQL database.
Enter two commands into the SQL console:
Where:
operations – the name of the table in which we will change;
id – the name of the column that we want to drop;
seq – we leave it as it is.
Errors
In case such errors occur:
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint “portfolio_pk”
DETAIL: Key (id) = (2) already exists.psycopg2.errors.NotNullViolation: null value in column “id” violates not-null constraint
DETAIL: Failing row contains[2020-10-09 19:54:42] [23505] ERROR: duplicate key value violates unique constraint “operations_id_key”
[2020-10-09 19:54:42] Detail: Key (id) = (1) already exists.sqlalchemy.orm.exc.FlushError: Instance
has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. Ensure also that this flush () is not occurring at an inappropriate time, such as within a load () event.
To solve these problems, let’s reset the identifiers differently. Let’s set the value high to avoid duplication, and then reset to normal:
UPDATE operations SET id = 10,000,000 + NEXTVAL (‘operations_id_seq’);
ALTER SEQUENCE operations_id_seq RESTART WITH 1;
UPDATE operations SET id=NEXTVAL(‘operations_id_seq’);
