Clever Geek Handbook

PostgreSQL Internal Queries with Prepared Statements

I have a table for storing contacts.

I want to get the maximum value of a column where adding user_id is {some number} and sets it to the same column value for the current insert record.

I am 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 adds this contact, there is another column as an index ( id

), which is a common column serial

that increases for each row, c_user_serial

is a sequential number that increases for each user. Let's say one user added one contact to be 1

. After other users have added many contacts, when this user adds their second contact, I want this column to keep 2

, so automatically increase the number of columns, but that should increase per user.

Not sure how to use inner queries here to get the max value of a column and use the increment for the current insert.

+3


source to share


1 answer


This request will 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;

The original forgets to insert user_id

itself, which is necessary. I added it.

However , this comes with two main problems:

  • The current "maximum" may be subject to a race condition between concurrent transactions and is unreliable. (While secure when accessed at the same time.) serial

  • If line with is c_user = $1

    not found, nothing is inserted. It may or may not be what you want.

  • If max(c_user_serial)

    returns NULL, c_user_id

    another NULL value is added for .
    If c_user_id

    NOT NULL is specified, this cannot be.

To avoid problems 2.and 3.and start with 1

for each new user:

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 string" is not converted to NULL here, and COALESCE

the default is 1

in this case.



A simple solution

All together, a simple solution :

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 to CONTACTS

include the escaped table name or you are wide open to SQL injection !

If you are looking for sequences without whitespace, you have to deal with UPDATE

and in DELETE

some way that will inevitably hundreds of your sequences over time, which is one of the reasons why the whole idea is not good .

Another, 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 it is possible to stick to one open column serial

for all rows. You can always attach numbers for each user, starting at 1 when receiving data:

+1


source






More articles:


All Articles