Как получить последний идентификатор, вставленный в таблицу? В MS SQL есть SCOPE_IDENTITY().
Пожалуйста, не рекомендуется использовать что-то вроде этого:
select max(id) from table
(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'));
Caveat: 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.
Примечание. Все эти методы бесполезны, если вы намерены получить последний глобально вставленный id (необязательно в вашем сеансе). Для этого вы должны прибегнуть к select max(id) from table
(конечно, это не будет читать незафиксированные вставки из других транзакций).
SELECT max(id)
сожалению, SELECT max(id)
не выполняет эту работу, как только вы начинаете удалять строки.
См. предложение RETURNING оператора INSERT. В принципе, INSERT удваивает запрос и возвращает значение, которое было вставлено.
вы можете использовать предложение 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
Ответ Леонблоя довольно полный. Я бы добавил только специальный случай, в котором нужно получить последнее вставленное значение из функции PL/pgSQL, где OPTION 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, которая вставляет новую запись в клиента, но также заботится о вставке новой записи человека. Для этого мы должны использовать небольшое изменение leonbloy ВАРИАНТ 3:
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
См. приведенный ниже пример
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);
Затем для получения последнего вставленного id используйте его для таблицы "user" seq column name "id"
SELECT currval(pg_get_serial_sequence('users', 'id'));
SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))
Конечно, вам нужно указать имя таблицы и имя столбца.
Это будет текущий сеанс/соединение http://www.postgresql.org/docs/8.3/static/functions-sequence.html
Для тех, кому нужно получить всю запись данных, вы можете добавить
returning *
до конца вашего запроса, чтобы получить весь объект, включая идентификатор.
Попробуйте следующее:
select nextval('my_seq_name'); // Returns next value
Если это возвращает 1 (или что-то другое - начало_значения для вашей последовательности), то reset последовательность возвращается к исходному значению, передавая флаг false:
select setval('my_seq_name', 1, false);
В противном случае
select setval('my_seq_name', nextValue - 1, true);
Это восстановит значение последовательности в исходное состояние, и "setval" вернется с нужной последовательностью.
Если вам просто нужно извлечь последний вставленный идентификатор без вставки, вы можете использовать следующий пример
SELECT id FROM users ORDER BY id DESC LIMIT 1;
Надежда может помочь.