CODEWITHSUNDEEP
cpsqlrdbmspostgres15
Basavaraj Kittali
Basavaraj Kittali

Reputation: 109

How do i prevent updated row from shifting or changing to last row in Postgres using C code?

I'm new to Postgres and i have been creating table's using C program, so now I have run into a issue where a updated row is moving or shifting to the last row of the table and I have to prevent this from happening using a query. I have provided the "C" code snippet to insert, select and update queries below. :

const char *insert_query = "INSERT INTO characters (sr_no, Name, id) VALUES ($1, $2, $3);";
const char *paramValuesInsert[3] = {characters.sr_no, characters.name, characters.id};

PGresult *insert_res = PQexecParams(conn, insert_query, 3, NULL, paramValuesInsert, NULL, NULL, 0);
sr_no name id
1 Roger 01
2 Saitama 02
3 Kaido 03
4 Itachi 04
5 Zoro 05
6 Luffy 06

now when i use update query, before that I need to use Select query, the code snippet has been given below:

const char *select_query = "SELECT * FROM characters WHERE id = $1 ORDER BY sr_no;";
const char *paramValuesSelect[1] = {characters.id};

PGresult *select_res = PQexecParams(conn, select_query, 1, NULL, paramValuesSelect, NULL, NULL, 0);

and now when i use update query, code snippet is as follows:

const char *update_query = "UPDATE characters SET name = $1 WHERE id = $2 ;";
char name[12];  // Allocate enough space for the integer as a string
snprintf(name, sizeof(name), "%s", characters.name);
const char *paramValuesUpdate[2] = {name, characters.id};
            
PGresult *update_res = PQexecParams(conn, update_query, 2, NULL, paramValuesUpdate, NULL, NULL, 0);

I'm getting the following table which is not as per serial number. | sr_no | name | id |
|:-----:|:----------:|:--:| | 1 | Roger | 01 | | 2 | Saitama | 02 | | 4 | Kaido | 04 | | 5 | Itachi | 05 | | 3 | Ichigo | 03 | | 6 | Sindbad | 06 |

Here the sr_no 3 and 6 have been updated they have moved to last since they have been updated. Now I don't want this to happen, they should get updated at the row they are present and serial order should be as same as before updating. Is there any other queries which I should use or is there any kind of permission to be given, please help me with this. Thanks very much in advance.

Upvotes: 0

Views: 117

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

Rows in a table have no fixed order. Every UPDATE can change the order, and even if you don't modify the table, concurrent sequential scans could see a different ordering of the table rows. There is no way to change this behavior (well, for the second, there is the compatibility parameter synchronize_seqscans).

If your queries expect rows to be in a certain order in the table, they are wrong. If you want a certain order in the result set of an SQL query, you must use an ORDER BY clause.

Upvotes: 0

Related Questions