PostgreSQL inner queries with prepared statements

Question

I have a table to store contacts.

I want to grab the max value of a column where adding user_id is {some number} and set that as the same column value for the current inserting record.

I'm using prepared statements:

pg_prepare($db, "add", 'INSERT INTO '.CONTACTS.' (c_user_serial,c_name,c_company,c_email)  VALUES ($1, $2, $3, $4)');

$insert_co = pg_execute($db, "add", array({(MAX OF c_user_serial where c_user_id = 1234) + 1 increment },$name,$company,$email));

c_user_id is the ID of the user who is adding this contact, there is another column as index (id) that is a common serial column that increments for every row, c_user_serial is a serial number that increments per user. Let's say one user added one contact so it is 1. After other users added many contacts, when this user adds his second contact I want this column to store 2, so an auto increment kind of column but that should increment per user.

Not sure how to use inner queries here to get the max value of the column and use the incremented value for the current insertion.

I tried to write an answer, but then I noticed that this: `(MAX OF c_user_id where c_user_id = 1234)` doesn't make sense - If you know the c_user_id value is 1234, then the MAX is surely 1234. so there is no point to using the MAX function. Did something get mixed up in asking the question? — Turophile, May 26 '15 at 22:52
I tried to fix your question, but it's unclear what kind of number you want to retrieve, like Turophile commented. You also need to define what to do if the subquery would find NULL or no row. — Erwin Brandstetter, May 26 '15 at 23:38

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

This query would do what you are asking for:

INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email)
SELECT max(c_user_serial) + 1, $1, $2, $3, $4
FROM   tbl
WHERE  c_user_id = $1;

Your original forgets to insert user_id itself, which is needed. I added it.

However, this carries a couple of principal problems:

The current "maximum" can be to subject of a race condition between concurrent transactions and is unreliable. (While a serial is safe with concurrent access.)
If no row with c_user = $1 is found, nothing is inserted. May or may not be what you want.
If max(c_user_serial) returns NULL, another NULL value is inserted for c_user_id.
If c_user_id is defined NOT NULL, that cannot happen.

To avoid problem 2. and 3. and start with 1 for every new user instead:

INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email)
VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1)
       , $1, $2, $3, $4)

"no row" is converted to NULL here and COALESCE defaults to 1 in this case.

Simple solution

Everything put together, the simple solution is:

pg_prepare($db, "add"
     , 'INSERT INTO ' . CONTACTS . ' (c_user_serial, c_user_id, c_name, c_company, c_email)
        VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1)
               , $1, $2, $3, $4)');    
$insert_co = pg_execute($db, "add", array($user_id,$name,$company,$email));

Make sure that CONTACTS holds a properly escaped table name or you are wide open to SQL injection!

If you are looking for sequences without gaps, you must deal with UPDATE and DELETE somehow, which will inevitable honeycomb your sequences over time, which is one of the reasons, why the whole idea is not that good.

The other, more important reason is the race condition I mentioned. There is no cheap and elegant solution for it. (There are solutions, but more or less expensive ...)

If at all possible stick to one plain serial column for all rows. You can always attach numbers per user starting from 1 when retrieving data:

Serial numbers per group of rows for compound key

serial number is ok for all contacts, but I definetly need this partioned serial coulm too, because it doesnt look godd if we pass a different ID in the URL while deleting/updating and show a different number in the list (#You can always attach numbers per user starting from 1 when retrieving data:), so I need a column that really exists in DB, but Im not going to put that in my INSERT statement, but the DB engine should do an auto increment with user id partioning, seems it is possible with stored procedures, buit not very clear yet — CodeRows, May 27 '15 at 21:22

PostgreSQL inner queries with prepared statements

1 Answers1

Simple solution

Linked