PostgreSQL id column

Problem

You use PostgreSQL as database. Your model includes an id column type Serial with a sequence to emulate an auto increase like with MySQL.

Solution

You have two options:

  • 2. Sync the sequence with the highest id value

Option 1 (Never set the id column):

Make sure that your code never sets the id column in a PostgreSQL table. But that is easier said than done.

Option 2 (Sync the sequence with the highest id value):

Your second option is to sync the sequence with the highest id value used. For a one off shot this can be done manually with (P)SQL and I will show you how.

SQL

Assuming you used the field id, the following will do the sync:

Python Django Framework

Wrapped in a method can use the following:

def reset_sequence():
sequence_sql = connection.ops.sequence_reset_sql(no_style(), [$model_name])
with connection.cursor() as cursor:
for sql in sequence_sql:
cursor.execute(sql)
def reset_sequence():
sequence_sql = connection.ops.sequence_reset_sql(no_style(), [ProductAvailability])
with connection.cursor() as cursor:
for sql in sequence_sql:
cursor.execute(sql)

Explanation

PostgreSQL id columns work a bit different. As long as no value is set for the id column the sequence attached to it (comparable with a function) is executed and the data is in sync with the sequence.

Background

A serial id like 1,2,3,4,5, …, 100, 101, … as the primary key in a database is a decision that is not supported by all users of relational databases. There are multiple reasons for it:

Information disclosure

Depending on requirements you don´t are not allowed to expose too much information. For example imagine a social network says they have 10 Million users and you can easily verify as a user that there are only 20000.

Easily scrapable

Let say you have a public user profile link with the id in it. Now someone can make external calls and collect the public data of all users.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store