PostgreSQL内部查询与准备语句 [英] PostgreSQL inner queries with prepared statements

问题描述

我有一个表来存储联系人。

我想抓住一个列的最大值，其中添加user_id是{some number}并将其设置为相同

我正在使用准备的语句：

  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 是用户的ID正在添加此联系人，还有另一列作为索引（ id ），这是一个常见的串行列，每列行 c_user_serial 是每个用户递增的序列号。假设有一位用户添加了一个联系人，因此它是 1 。在其他用户添加了许多联系人之后，当该用户添加他的第二个联系人时，我希望此列存储 2 ，所以自动增量类型的列，但应该增加每个用户。 / p>

不知道如何使用内部查询来获取列的最大值，并使用当前插入的递增值。

解决方案

这个查询会做你所要求的：

  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; 
  

您原来忘记插入 user_id 这是必需的。我添加了它。

然而，这有几个主要问题：

1. 当前的最大可能是并发交易 之间的 竞争条件的主体，并且是不可靠的。 （虽然 系列




2. 如果没有包含 c_user = $ 1 被发现，没有插入。可能或可能不是你想要的。




3. 如果 max（c_user_serial）返回NULL， c_user_id 中的值被插入。
   
   如果 c_user_id 被定义为NOT NULL，那不会发生。

为避免出现问题2.和3.并从 1开始

  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在此转换为NULL， COALESCE 默认为 1 在这种情况下

简单解决方案

，简单解决方案是：

  pg_prepare（$ db，添加
，'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））; 
  

确保 CONTACTS 正确转义表名，或者您已经开放到 SQL注入！

如果您正在寻找没有间隙的序列，您必须处理更新和 DELETE 某种方式，这将不可避免地使您的序列随着时间的推移，这是其中的原因之一，为什么整个想法不是那么好。

另一个更重要的原因是我提到的比赛条件。 没有廉价而优雅的解决方案。 （有解决方案，但是或多或少昂贵...）

如果可能的话，坚持一个简单串行列。在检索数据时，您可以随时从1开始附加每个用户的数字：

• 复合密钥组的每组行的序列号

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.

解决方案

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:

1. 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.)

2. If no row with c_user = $1 is found, nothing is inserted. May or may not be what you want.

3. 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

这篇关于PostgreSQL内部查询与准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！