PostgreSQL 자동 증가

PostgreSQL 자동 증가

---

MySQL에서 PostgreSQL로 전환 중이며 자동 증가 값을 어떻게 할 수 있는지 궁금합니다. PostgreSQL 문서에서 "serial"데이터 유형을 보았지만이를 사용할 때 구문 오류가 발생합니다 (v8.0에서).

---

예, SERIAL은 동등한 기능입니다.

CREATE TABLE foo (
id SERIAL,
bar varchar);

INSERT INTO foo (bar) values ('blah');
INSERT INTO foo (bar) values ('blah');

SELECT * FROM foo;

1,blah
2,blah

SERIAL은 시퀀스에 대한 테이블 생성 시간 매크로입니다. SERIAL을 기존 열로 변경할 수 없습니다.

---

와 같은 다른 정수 데이터 유형을 사용할 수 있습니다 smallint.

예 :

CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

사용자 직렬 데이터 유형 보다는 자신의 데이터 유형을 사용하는 것이 좋습니다 .

---

이미 존재하는 테이블의 id에 시퀀스를 추가하려면 다음을 사용할 수 있습니다.

CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');

---

시퀀스가 MySQL auto_increment 와 동일 해 보이지만 몇 가지 미묘하지만 중요한 차이점이 있습니다.

1. 실패한 쿼리는 시퀀스 / 시리얼을 증가시킵니다.

직렬 열은 실패한 쿼리에서 증가합니다. 이로 인해 행 삭제뿐만 아니라 실패한 쿼리에서 조각화가 발생합니다. 예를 들어 PostgreSQL 데이터베이스에서 다음 쿼리를 실행합니다.

CREATE TABLE table1 (
  uid serial NOT NULL PRIMARY KEY,
  col_b integer NOT NULL,
  CHECK (col_b>=0)
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

SELECT * FROM table1;

다음과 같은 출력이 표시되어야합니다.

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
(2 rows)

uid가 1에서 2가 아닌 1에서 3으로 이동하는 방법에 유의하십시오.

다음을 사용하여 자신 만의 시퀀스를 수동으로 생성 한 경우에도 이러한 문제가 발생합니다.

CREATE SEQUENCE table1_seq;
CREATE TABLE table1 (
    col_a smallint NOT NULL DEFAULT nextval('table1_seq'),
    col_b integer NOT NULL,
    CHECK (col_b>=0)
);
ALTER SEQUENCE table1_seq OWNED BY table1.col_a;

MySQL이 어떻게 다른지 테스트하려면 MySQL 데이터베이스에서 다음을 실행하십시오.

CREATE TABLE table1 (
  uid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  col_b int unsigned NOT NULL
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

조각화없이 다음을 얻어야합니다 .

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
+-----+-------+
2 rows in set (0.00 sec)

2. 직렬 열 값을 수동으로 설정하면 향후 쿼리가 실패 할 수 있습니다.

이것은 이전 답변에서 @trev에 의해 지적되었습니다.

이를 수동으로 시뮬레이션하려면 uid를 4로 설정하면 나중에 "충돌"합니다.

INSERT INTO table1 (uid, col_b) VALUES(5, 5);

테이블 데이터 :

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
(3 rows)

다른 삽입물 실행 :

INSERT INTO table1 (col_b) VALUES(6);

테이블 데이터 :

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
   4 |     6

이제 다른 삽입을 실행하면 :

INSERT INTO table1 (col_b) VALUES(7);

It will fail with the following error message:

ERROR: duplicate key value violates unique constraint "table1_pkey" DETAIL: Key (uid)=(5) already exists.

In contrast, MySQL will handle this gracefully as shown below:

INSERT INTO table1 (uid, col_b) VALUES(4, 4);

Now insert another row without setting uid

INSERT INTO table1 (col_b) VALUES(3);

The query doesn't fail, uid just jumps to 5:

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
|   4 |     4 |
|   5 |     3 |
+-----+-------+

Testing was performed on MySQL 5.6.33, for Linux (x86_64) and PostgreSQL 9.4.9

---

Starting with Postgres 10, identity columns as defined by the SQL standard are also supported:

create table foo 
(
  id integer generated always as identity
);

creates an identity column that can't be overridden unless explicitly asked for. The following insert will fail with a column defined as generated always:

insert into foo (id) 
values (1);

This can however be overruled:

insert into foo (id) overriding system value 
values (1);

When using the option generated by default this is essentially the same behaviour as the existing serial implementation:

create table foo 
(
  id integer generated by default as identity
);

When a value is supplied manually, the underlying sequence needs to be adjusted manually as well - the same as with a serial column.

---

An identity column is not a primary key by default (just like a serial column). If it should be one, a primary key constraint needs to be defined manually.

---

Sorry, to rehash an old question, but this was the first Stack Overflow question/answer that popped up on Google.

This post (which came up first on Google) talks about using the more updated syntax for PostgreSQL 10: https://blog.2ndquadrant.com/postgresql-10-identity-columns/

which happens to be:

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);

Hope that helps :)

---

You have to be careful not to insert directly into your SERIAL or sequence field, otherwise your write will fail when the sequence reaches the inserted value:

-- Table: "test"

-- DROP TABLE test;

CREATE TABLE test
(
  "ID" SERIAL,
  "Rank" integer NOT NULL,
  "GermanHeadword" "text" [] NOT NULL,
  "PartOfSpeech" "text" NOT NULL,
  "ExampleSentence" "text" NOT NULL,
  "EnglishGloss" "text"[] NOT NULL,
  CONSTRAINT "PKey" PRIMARY KEY ("ID", "Rank")
)
WITH (
  OIDS=FALSE
);
-- ALTER TABLE test OWNER TO postgres;
 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das", "den", "dem", "des"}', 'art', 'Der Mann küsst die Frau und das Kind schaut zu', '{"the", "of the" }');


 INSERT INTO test("ID", "Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (2, 1, '{"der", "die", "das"}', 'pron', 'Das ist mein Fahrrad', '{"that", "those"}');

 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das"}', 'pron', 'Die Frau, die nebenen wohnt, heißt Renate', '{"that", "who"}');

SELECT * from test; 

---

In the context of the asked question and in reply to the comment by @sereja1c, creating SERIAL implicitly creates sequences, so for the above example-

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE would implicitly create sequence foo_id_seq for serial column foo.id. Hence, SERIAL [4 Bytes] is good for its ease of use unless you need a specific datatype for your id.

---

This way will work for sure, I hope it helps:

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');

or

INSERT INTO fruits VALUES(DEFAULT,'apple');

You can check this the details in the next link: http://www.postgresqltutorial.com/postgresql-serial/

---

Since PostgreSQL 10

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);

참고URL : https://stackoverflow.com/questions/787722/postgresql-autoincrement