MySQLからPostgreSQLに切り替えて、自動増分値をどのように実行できるか疑問に思っていました。 PostgreSQLドキュメントでデータ型の「シリアル」を見ましたが、使用すると構文エラーが発生します(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;
ユーザーシリアルデータタイプよりも、独自のデータタイプを使用する方が適しています。
シーケンスはMySQL auto_incrementと同等のように見えますが、いくつかの微妙な、しかし重要な違いがあります。
シリアル列は、失敗したクエリでインクリメントされます。 これにより、行の削除だけでなく、失敗したクエリからの断片化につながります。 たとえば、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)
これは以前の回答で@trevによって指摘されました。
これを手動でシミュレートするには、uidを4に設定します。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);
次のエラーメッセージで失敗します。
エラー:重複するキー値は、一意の制約「table1_pkey」に違反します。 詳細:キー(uid)=(5)はすでに存在しています。
対照的に、MySQLはこれを以下に示すように優雅に処理します。
INSERT INTO table1 (uid, col_b) VALUES(4, 4);
次に、uidを設定せずに別の行を挿入します。
INSERT INTO table1 (col_b) VALUES(3);
クエリは失敗せず、uidは5にジャンプします。
+-----+-------+
| uid | col_b |
+-----+-------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
| 5 | 3 |
+-----+-------+
テストは、MySQL 5.6.33、Linux(x86_64)およびPostgreSQL 9.4.9で実行されました。
Postgres 10以降、SQL標準で定義されているID列もサポートされています。
create table foo
(
id integer generated always as identity
);
明示的に要求されない限りオーバーライドできないID列を作成します。 次の挿入は、「常に生成」として定義された列で失敗します。
insert into foo (id)
values (1);
ただし、これは却下できます。
insert into foo (id) overriding system value
values (1);
「デフォルトで生成」オプションを使用する場合、これは基本的に既存の「シリアル」実装と同じ動作です。
create table foo
(
id integer generated by default as identity
);
値が手動で提供される場合、基になるシーケンスも手動で調整する必要があります-「シリアル」列の場合と同じです。
---。
ID列はデフォルトでは主キーではありません(「シリアル」列のように)。 1つにする必要がある場合は、主要なキー制約を手動で定義する必要があります。
古い質問を再ハッシュ化して申し訳ありませんが、これはGoogleにポップアップ表示された最初のスタックオーバーフローの質問/回答でした。
この投稿(Googleで最初に取り上げられた)では、PostgreSQL 10のより更新された構文の使用について説明しています。 https://blog.2ndquadrant.com/postgresql-10-identity-columns/。
たまたま:
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);
それが役立つことを願っています:)。
SERIALまたはシーケンスフィールドに直接挿入しないように注意する必要があります。そうしないと、シーケンスが挿入された値に達したときに書き込みが失敗します。
-- 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;
質問のコンテキストと@ sereja1cのコメントへの返信で、「SERIAL」を作成すると暗黙的にシーケンスが作成されるため、上記の例では-です。
CREATE TABLE foo (id SERIAL,bar varchar);
CREATE TABLE
は、シリアル列 foo.id
のシーケンス foo_id_seq
を暗黙的に作成します。 したがって、IDに特定のデータ型を必要としない限り、「SERIAL」[4バイト]は使いやすさのために適しています。
この方法は確実に機能します。
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');
これは次のリンクで詳細を確認できます。 http://www.postgresqltutorial.com/postgresql-serial/。