Why does row get thrown to bottom when updating table in PostgreSQL Python?

December 9, 2021

I’m trying to update a table in PostgreSQL with Python, but there is something I don’t quite get. The table contains 5 countries:

[(1,'Italy'), (2,'Poland'),(3,'South Korea'),(4,'Taiwan'),(5,'United States')]

When I update the table, say, replacing Poland with another country (e.g., Kazakhstan), the index shifts position to the bottom. So now you have:

[(1,'Italy'),(3,'South Korea'),(4,'Taiwan'),(5,'United States'),(2,'Poland')]

I also tried using ORDER BY, but then I get a syntax error. Why is that? And how can I maintain the index position?

Here is the code:

def update_countries():
    return ("""
                UPDATE countries
                SET country_name = 'Spain'
                WHERE country_id = 5;
             """)

conn = None
try:
    conn = psycopg2.connect(
        database = 'social',
        user = 'postgres',
        password = 'abc123',
        host = 'localhost',
        port = '5432')

    cur = conn.cursor()

    country_table = update_countries()
    cur.execute(country_table)
    print("[+] Data updated successfully!")
    conn.commit()

except (Exception, psycopg2.DatabaseError) as error:
    print('{}[-] Failed to connect.'.format(error))
    conn.rollback()
finally:
    if conn is not None:
        conn.close()

And here is the result:

Thank you in advance for taking the time to help.

>Solution :

SQL tables have no order. Without an explicit ORDER BY in the SELECT statement, you are not even guaranteed the same order between two consecutive SELECT:s.