9

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

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

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

  • 0

    Code:

    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
    ;
    

  • 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;      
    
      };
    
    });
    

  • 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. Требуется PostgreSQL

    8.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 или позже:

    Is SELECT or INSERT in a function prone to race conditions?