Вопрос по sql, postgresql, node.js – Вернуть id, если строка существует, иначе ВСТАВИТЬ

9

Я пишу функцию в node.js для запроса таблицы PostgreSQL.
Если строка существует, я хочу вернуть столбец id из строки.
Если он не существует, я хочу вставить его и вернуть идентификатор (insert into ... returning id).

Я пробовал вариантыcase а такжеif else заявления и не может заставить его работать.

Я не хочу обновлять что-либо, если оно уже существует. Я просто хочу вернуть значение (в любом случае). Jared
Update  Postgres 9.5 получаетUPSERT особенность. Увидетьblog post by Craig Kerstiens. Basil Bourque
возможный дубликатInsert if not exists, else return id in postgresql Clodoaldo Neto
возможный дубликатUpsert in Postgres using node.js a_horse_with_no_name

Ваш Ответ

4   ответа
7

Я бы предложил выполнить проверку на стороне базы данных и просто вернуть id для nodejs.

Пример:

CREATE OR REPLACE FUNCTION foo(p_param1 tableFoo.attr1%TYPE, p_param2 tableFoo.attr1%TYPE) RETURNS tableFoo.id%TYPE AS $$
  DECLARE
  v_id tableFoo.pk%TYPE;
  BEGIN
    SELECT id
    INTO v_id
    FROM tableFoo
    WHERE attr1 = p_param1
    AND attr2 = p_param2;

    IF v_id IS NULL THEN
      INSERT INTO tableFoo(id, attr1, attr2) VALUES (DEFAULT, p_param1, p_param2)
      RETURNING id INTO v_id;
    END IF;

    RETURN v_id:

  END;
$$ LANGUAGE plpgsql;

А затем на стороне Node.js (в этом примере я использую node-postgres):

var pg = require('pg');
pg.connect('someConnectionString', function(connErr, client){

  //do some errorchecking here

  client.query('SELECT id FROM foo($1, $2);', ['foo', 'bar'], function(queryErr, result){

    //errorchecking

    var id = result.rows[0].id;      

  };

});
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded Jared
Error: User Rate Limit Exceeded Jared
0

create table t (
    id serial primary key,
    a integer
)
;

insert into t (a)
select 2
from (
    select count(*) as s
    from t
    where a = 2
    ) s
where s.s = 0
;
select id
from t
where a = 2
;
2

Как то так, если вы на PostgreSQL 9.1

with test_insert as (
   insert into foo (id, col1, col2)
   select 42, 'Foo', 'Bar'
   where not exists (select * from foo where id = 42)
   returning foo.id, foo.col1, foo.col2
)
select id, col1, col2
from test_insert
union 
select id, col1, col2
from foo
where id = 42;

Это немного длинновато, и вам нужно повторить идентификатор для проверки несколько раз, но я не могу думать о другом решении, которое включает в себя один оператор SQL.

Если ряд сid=42 существует, записываемый CTE ничего не вставит, и, следовательно, существующая строка будет возвращена второй объединяющей частью.

При тестировании этого я на самом деле думал, что новый ряд будет возвращен дважды (поэтомуunion неunion all), но оказывается, что результат второго оператора select фактически оценивается перед выполнением всего оператора, и он не видит вновь вставленную строку. Таким образом, в случае вставки новой строки она будет взята из & quot; возврата & quot; часть.

10

Решение в одном операторе SQL. Требуется PostgreSQL8.4 или позже, хотя.
Рассмотрим следующую демонстрацию:

Испытательная установка:

CREATE TEMP TABLE tbl (
  id  serial PRIMARY KEY
 ,txt text   UNIQUE   -- obviously there is unique column (or set of columns)
);

INSERT INTO tbl(txt) VALUES ('one'), ('two');

Команда INSERT / SELECT:

WITH v AS (SELECT 'three'::text AS txt)
    ,s AS (SELECT id FROM tbl JOIN v USING (txt))
    ,i AS (
       INSERT INTO tbl (txt)
       SELECT txt
       FROM   v
       WHERE  NOT EXISTS (SELECT * FROM s)
       RETURNING id
       )
SELECT id, 'i'::text AS src FROM i
UNION  ALL
SELECT id, 's' FROM s;
  • The first CTE v is not strictly necessary, but achieves that you have to enter your values only once.

  • The second CTE s selects the id from tbl if the "row" exists.

  • The third CTE i inserts the "row" into tbl if (and only if) it does not exist, returning id.

  • The final SELECT returns the id. I added a column src indicating the "source" - whether the "row" pre-existed and id comes from a SELECT, or the "row" was new and so is the id.

  • This version should be as fast as possible as it does not need an additional SELECT from tbl and uses the CTEs instead.

Чтобы обезопасить себя от возможных условий гонки в многопользовательской среде:
Также для обновленных методов с использованием нового UPSERT в Postgres9.5 или позже:

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededdynamic SQL with EXECUTEError: User Rate Limit ExceededDOError: User Rate Limit ExceededsearchError: User Rate Limit Exceededlike this oneError: User Rate Limit Exceededwary of SQL injection.

Похожие вопросы