loading..
Русский    English
14:10
листать

Вставка строк в таблицу, содержащую автоинкрементируемое поле стр. 5

Столбцы IDENTITY в PostgreSQL

Начиная с PostgreSQL 10, появилась возможность объявления столбца identity. Вот синтаксис, который несколько отличается от используемого в SQL Server:


  1. <имя_столбца> <тип> GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  2. [ (START WITH <старт> INCREMENT BY <приращение> ) ]


По умолчанию старт и приращение равны 1. Начнем с параметра ALWAYS и создадим аналог таблицы Printer_Inc, которая использовалась ранее для примеров в SQL Server:

  1. CREATE TABLE Printer_Inc 
  2.     (
  3.      code INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  4.      model VARCHAR (4) NOT NULL ,
  5.      color CHAR (1) NOT NULL ,
  6.      type VARCHAR (6) NOT NULL ,
  7.      price FLOAT NOT NULL 
  8.     );

Давайте вставим пару строк и посмотрим результат:

  1. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  2. (1000,'y','jet',600),
  3. (1001,'n','laser',800);

"code"    "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800

Как видно, по умолчанию значения в столбце identity начинаются с 1 и увеличиваются с шагом 1.

Теперь попробуем вставить строку с заданным значением в столбце code:

  1. INSERT INTO Printer_Inc VALUES
  2. (3, 1003,'n','laser',850);

Сервер возвращает ошибку:

ERROR:  Столбец "code" является столбцом идентификации со свойством GENERATED ALWAYS. В столбец "code" можно вставить только значение по умолчанию Hint: Для переопределения укажите OVERRIDING SYSTEM VALUE.

Такое поведение согласуется с поведением identity в SQL Server. Однако тут же дается совет использовать переопределение системного значения (OVERRIDING SYSTEM VALUE). Попробуем:

  1. INSERT INTO Printer_Inc
  2. OVERRIDING SYSTEM VALUE
  3. VALUES(3, 1003,'n','laser',850);

"code"     "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800
3     "1003"     "n"     "laser"     850

А если, воспользовавшись этим способом, заменить строку с существующим значением в столбце code?

  1. INSERT INTO Printer_Inc
  2. OVERRIDING SYSTEM VALUE
  3. VALUES(3, 1004,'n','laser',777.99);

Нет, не удается. Поскольку тут мы нарушаем ограничение первичного ключа, то получаем соответствующую ошибку.

Теперь проверим, откуда продолжится нумерация, если вставить значение не по порядку.

  1. INSERT INTO Printer_Inc
  2. OVERRIDING SYSTEM VALUE
  3. VALUES(10, 1010,'n','laser',777.99);
  4.  
  5. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  6. (1004,'y','jet',6500);

Если выполнить эти операторы по одному, а не в пакете, то получим:

"code"     "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800
3     "1003"     "n"     "laser"     850
10     "1010"     "n"     "laser"     777.99
4     "1004"     "y"     "jet"     6500

Таким образом, нумерация продолжится с последнего достигнутого значения последовательного счетчика, что потенциально приведет к ошибке (нарушения ограничения первичного ключа) при достижении значения 10.

Давайте теперь попробуем сдвинуть начало отсчета и заодно изменить шаг приращения для существующей таблицы:

  1. -- Удалим последние строки для чистоты эксперимента
  2. DELETE FROM Printer_Inc WHERE code>3;
  3. -- Изменим начальное значение счетчика
  4. ALTER TABLE Printer_Inc 
  5. ALTER COLUMN code 
  6. SET  START WITH 20; 
  7. -- Изменим величину приращения
  8. ALTER TABLE Printer_Inc 
  9. ALTER COLUMN code 
  10. SET  INCREMENT BY 10;
  11. -- перезапустим счетчик
  12. ALTER TABLE Printer_Inc 
  13. ALTER COLUMN code 
  14. RESTART;
  15. -- Вставим пару строк
  16. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  17. (1004,'y','jet',680); 
  18. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  19. (1005,'y','jet',670); 
  20. -- Посмотрим результат
  21. SELECT * FROM Printer_Inc;

"code"     "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800
3     "1003"     "n"     "laser"     850
20     "1004"     "y"     "jet"     680
30     "1005"     "y"     "jet"     670

Теперь рассмотрим второй вариант (GENERATED BY DEFAULT), который допускает явную вставку значений в автоинкрементируемый столбец.

  1. ALTER TABLE Printer_Inc 
  2. ALTER COLUMN code
  3. SET GENERATED BY DEFAULT;
  4.  
  5. INSERT INTO Printer_Inc VALUES
  6. (40, 1006,'n','laser',880);
  7.  
  8. SELECT * FROM Printer_Inc;

"code"     "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800
3     "1003"     "n"     "laser"     850
20     "1004"     "y"     "jet"     680
30     "1005"     "y"     "jet"     670
40     "1006"     "n"     "laser"     880

Как видим, нам не пришлось для этого указывать OVERRIDING SYSTEM VALUE. А каким будет следующее значение?

  1. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  2. (1007,'y','jet',6600);

ОШИБКА:  повторяющееся значение ключа нарушает ограничение уникальности "printer_inc_pkey"SQL state: 23505Detail: Ключ "(code)=(40)" уже существует.

Т.е. PostgreSQL пытается продолжить нумерацию с последнего автоинкрементированного значения. Тогда придется передвинуть начальное значение и перезапустить identity:

  1. ALTER TABLE Printer_Inc 
  2. ALTER COLUMN code 
  3. SET  START WITH 50;
  4.  
  5. ALTER TABLE Printer_Inc 
  6. ALTER COLUMN code 
  7. RESTART;
  8.  
  9. INSERT INTO Printer_Inc(model,color,type,price) VALUES
  10. (1007,'y','jet',6600);
  11.  
  12. SELECT * FROM Printer_Inc;

"code"     "model"     "color"     "type"     "price"
1     "1000"     "y"     "jet"     600
2     "1001"     "n"     "laser"     800
3     "1003"     "n"     "laser"     850
20     "1004"     "y"     "jet"     680
30     "1005"     "y"     "jet"     670
40     "1006"     "n"     "laser"     880
50     "1007"     "y"     "jet"     6600


Bookmark and Share
Страницы: 1 2 3 4 5
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
Самый правильный редизайн сайта без потери позиций и трафика от студии "ПроПремиум".
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.