Функция PostgreSQL для последнего вставленного идентификатора
Как в PostgreSQL получить последний идентификатор, вставленный в таблицу?
В MS SQL есть SCOPE_IDENTITY().
Пожалуйста, не советуйте мне использовать что-то вроде этого:
select max(id) from table
16 ответов
(tl;dr: Перейти к варианту 3: Вставить с возвратом)
Напомним, что в postgresql отсутствует концепция "id" для таблиц, а только последовательности (которые обычно, но не обязательно, используются в качестве значений по умолчанию для суррогатных первичных ключей с псевдотипом SERIAL).
Если вы заинтересованы в получении идентификатора недавно вставленной строки, есть несколько способов:
Опция 1: CURRVAL(<sequence name>);,
Например:
INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
SELECT currval('persons_id_seq');
Имя последовательности должно быть известно, оно действительно произвольно; в этом примере мы предполагаем, что таблица persons имеет id столбец создан с SERIAL псевдо-типа. Чтобы не полагаться на это и чувствовать себя более чистым, вы можете использовать вместо pg_get_serial_sequence:
INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
SELECT currval(pg_get_serial_sequence('persons','id'));
Предостережение: currval() работает только после INSERT (который выполнил nextval()), в том же сеансе.
Вариант 2: LASTVAL();
Это похоже на предыдущее, только в том, что вам не нужно указывать имя последовательности: она ищет самую последнюю измененную последовательность (всегда внутри вашего сеанса, то же предупреждение, что и выше).
И то и другое CURRVAL а также LASTVAL полностью безопасны одновременно. Поведение последовательности в PG спроектировано таким образом, что другой сеанс не будет мешать, поэтому нет риска возникновения условий гонки (если другой сеанс вставит еще одну строку между моим INSERT и моим SELECT, я все равно получу правильное значение).
Однако у них есть тонкая потенциальная проблема. Если в базе данных есть какой-то TRIGGER (или RULE), то при вставке в persons таблица, делает некоторые дополнительные вставки в другие таблицы... затем LASTVAL вероятно, даст нам неправильное значение. Проблема может даже случиться с CURRVAL, если дополнительные вставки сделаны в том же самом persons таблица (это гораздо реже, но риск все еще существует).
Вариант 3: INSERT с RETURNING
INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;
Это самый чистый, эффективный и безопасный способ получить удостоверение личности. Это не имеет никакого риска предыдущего.
Недостатки? Почти ничего: вам может понадобиться изменить способ вызова оператора INSERT (в худшем случае, возможно, ваш уровень API или DB не ожидает, что INSERT вернет значение); это не стандартный SQL (кого это волнует); он доступен с Postgresql 8.2 (декабрь 2006...)
Вывод: если можете, перейдите к варианту 3. В другом месте предпочтите 1.
Примечание: все эти методы бесполезны, если вы собираетесь получить последний глобально вставленный идентификатор (не обязательно в вашем сеансе). Для этого вы должны прибегнуть к select max(id) from table (конечно, это не будет читать незафиксированные вставки из других транзакций).
Смотрите предложение RETURNING оператора INSERT. По сути, INSERT удваивается как запрос и возвращает вам значение, которое было вставлено.
Ответ Леонблоя довольно полный. Я бы добавил только тот особый случай, когда нужно получить последнее вставленное значение из функции PL/pgSQL, где ОПЦИЯ 3 не подходит точно.
Например, если у нас есть следующие таблицы:
CREATE TABLE person(
id serial,
lastname character varying (50),
firstname character varying (50),
CONSTRAINT person_pk PRIMARY KEY (id)
);
CREATE TABLE client (
id integer,
CONSTRAINT client_pk PRIMARY KEY (id),
CONSTRAINT fk_client_person FOREIGN KEY (id)
REFERENCES person (id) MATCH SIMPLE
);
Если нам нужно вставить запись клиента, мы должны обратиться к записи человека. Но скажем, мы хотим разработать функцию PL/pgSQL, которая вставляет новую запись в клиент, но также заботится о вставке новой записи о человеке. Для этого мы должны использовать небольшое изменение ВАРИАНТА 3 leonbloy:
INSERT INTO person(lastname, firstname)
VALUES (lastn, firstn)
RETURNING id INTO [new_variable];
Обратите внимание, что есть два предложения INTO. Следовательно, функция PL/pgSQL будет определена так:
CREATE OR REPLACE FUNCTION new_client(lastn character varying, firstn character varying)
RETURNS integer AS
$BODY$
DECLARE
v_id integer;
BEGIN
-- Inserts the new person record and retrieves the last inserted id
INSERT INTO person(lastname, firstname)
VALUES (lastn, firstn)
RETURNING id INTO v_id;
-- Inserts the new client and references the inserted person
INSERT INTO client(id) VALUES (v_id);
-- Return the new id so we can use it in a select clause or return the new id into the user application
RETURN v_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Теперь мы можем вставить новые данные, используя:
SELECT new_client('Smith', 'John');
или же
SELECT * FROM new_client('Smith', 'John');
И мы получаем недавно созданный идентификатор.
new_client
integer
----------
1
Другие ответы не показывают, как можно использовать значения, возвращаемые
RETURNING. Вот пример, в котором возвращаемое значение вставляется в другую таблицу.
WITH inserted_id AS (
INSERT INTO tbl1 (col1)
VALUES ('foo') RETURNING id
)
INSERT INTO tbl2 (other_id)
VALUES ((select id from inserted_id));
Вы можете использовать предложение RETURNING в операторе INSERT, как показано ниже
wgzhao=# create table foo(id int,name text);
CREATE TABLE
wgzhao=# insert into foo values(1,'wgzhao') returning id;
id
----
1
(1 row)
INSERT 0 1
wgzhao=# insert into foo values(3,'wgzhao') returning id;
id
----
3
(1 row)
INSERT 0 1
wgzhao=# create table bar(id serial,name text);
CREATE TABLE
wgzhao=# insert into bar(name) values('wgzhao') returning id;
id
----
1
(1 row)
INSERT 0 1
wgzhao=# insert into bar(name) values('wgzhao') returning id;
id
----
2
(1 row)
INSERT 0
Вы можете использовать RETURNING id после запроса вставки.
INSERT INTO distributors (id, name) VALUES (DEFAULT, 'ALI') RETURNING id;
и результат:
id
----
1
В приведенном выше примере id указывается автоматически.
SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))
Конечно, вам нужно указать имя таблицы и имя столбца.
Это будет для текущего сеанса / подключения http://www.postgresql.org/docs/8.3/static/functions-sequence.html
Смотрите пример ниже
CREATE TABLE users (
-- make the "id" column a primary key; this also creates
-- a UNIQUE constraint and a b+-tree index on the column
id SERIAL PRIMARY KEY,
name TEXT,
age INT4
);
INSERT INTO users (name, age) VALUES ('Mozart', 20);
Затем для получения последнего вставленного идентификатора используйте это для таблицы "user". Seq имя столбца "id"
SELECT currval(pg_get_serial_sequence('users', 'id'));
Для тех, кому нужно получить всю запись данных, вы можете добавить
returning *
до конца вашего запроса, чтобы получить весь объект, включая идентификатор.
Лучше использовать Insert с возвратом. Хотя уже есть такие же ответы, я просто хочу добавить, если вы хотите сохранить это в переменной, вы можете сделать это
insert into my_table(name) returning id into _my_id;
Postgres имеет встроенный механизм для того же самого, который в том же запросе возвращает идентификатор или все, что вы хотите, чтобы запрос возвращал. вот пример. Предположим, у вас есть созданная таблица, в которой есть 2 столбца column1 и column2, и вы хотите, чтобы column1 возвращалась после каждой вставки.
# create table users_table(id serial not null primary key, name character varying);
CREATE TABLE
#insert into users_table(name) VALUES ('Jon Snow') RETURNING id;
id
----
1
(1 row)
# insert into users_table(name) VALUES ('Arya Stark') RETURNING id;
id
----
2
(1 row)
Попробуй это:
select nextval('my_seq_name'); // Returns next value
Если это возвращает 1 (или что-то еще из start_value для вашей последовательности), тогда сбросьте последовательность обратно к исходному значению, передав флаг false:
select setval('my_seq_name', 1, false);
Иначе,
select setval('my_seq_name', nextValue - 1, true);
Это восстановит значение последовательности в исходное состояние, а "setval" вернет значение последовательности, которое вы ищете.
Это поможет, если ваш столбец id является основным и проиндексированным. Вы можете использовать:
SELECT
*
FROM
table
LIMIT 1
ORDER BY id DESC
;
У меня была эта проблема с Java и Postgres. Я исправил это, обновив новую версию Connector-J.
postgresql-9.2-1002.jdbc4.jar
https://jdbc.postgresql.org/download.html: версия 42.2.12
Основываясь на ответе @ooZman выше, похоже, что это работает для PostgreSQL v12, когда вам нужно ВСТАВИТЬ следующее значение «последовательности» (аналогично auto_increment), не обманывая ничего в счетчиках ваших таблиц. (Примечание: я не тестировал его в более сложных конфигурациях кластера БД ...)
Псевдо-код
$insert_next_id = $return_result->query("
select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1; ")
Если вам просто нужно извлечь последний вставленный идентификатор без вставки чего-либо, вы можете использовать следующий пример
SELECT id FROM users ORDER BY id DESC LIMIT 1;
Надежда может помочь.