English 中文(简体)
邮电部如有增长,应进行最快的检查
原标题:Fastest check if row exists in PostgreSQL

我有一只需要插入的行文,但这些插文总是用批号。 因此,我想检查一下,在表格中是否有一个单行,因为当时我知道他们都是插入的。

因此,它不是主要的关键检查,而是太重要了。 我只想对单行进行录音,例如<代码>exists。 我猜想。

但是,由于我对PengreSQLI说了很新的话,而是问谁知道谁。

我的行文结构如下:

userid | rightid | remaining_count

因此,如果表格中包含有<条码>的使用<>>>>>>条码的行文,就是指所有列的行文。

最佳回答
问题回答

How about simply:

select 1 from tbl where userid = 123 limit 1;

<代码>123 是使用您重新插入的批量。

上述询问将视是否有专用记录而退回一个空洞或一个单行。

If this turns out to be too slow, you could look into creating an index on tbl.userid.

if even a single row from batch exists in table, in that case I don t have to insert my rows because I know for sure they all were inserted.

即便你的方案中断了中转,为了保持这一现实,我建议你确保妥善管理数据库交易(即整个批次都放在单一交易中)。

INSERT INTO target( userid, rightid, count )
  SELECT userid, rightid, count 
  FROM batch
  WHERE NOT EXISTS (
    SELECT * FROM target t2, batch b2
    WHERE t2.userid = b2.userid
    -- ... other keyfields ...
    )       
    ;

BTW: if you want the whole batch to fail in case of a duplicate, then (given a primary key constraint)

INSERT INTO target( userid, rightid, count )
SELECT userid, rightid, count 
FROM batch
    ;

你们想要做的是:要么成功,要么失败。

select true from tablename where condition limit 1;

I : 这就是用于检查外国钥匙的。

在你看来,你也可以这样做:

insert into yourtable select $userid, $rightid, $count where not (select true from yourtable where userid = $userid limit 1);
SELECT 1 FROM user_right where userid = ? LIMIT 1

如果你的成果包含一行,你就不必插入。 否则,插入你的记录。

如果你想到实情,你可以像现在这样利用“伙伴关系”。

 PERFORM 1 FROM skytf.test_2 WHERE id=i LIMIT 1;
  IF FOUND THEN
      RAISE NOTICE   found record id=% , i;  
  ELSE
      RAISE NOTICE   not found record id=% , i;  
 END IF;

如@MikeM所指出的。

select exists(select 1 from contact where id=12)

<<>index>关于接触,通常可将时间成本降低到1毫秒。

CREATE INDEX index_contact on contact(id);




相关问题
摘录数据

我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。

Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...

How to join attributes in sql select statement?

I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.

What text encoding to use?

I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...

SQL LIKE condition to check for integer?

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...

热门标签