PostgreSQL と MySQL の違い

PostgreSQL には AUTO INCREMENT は無い。
serial で代替。
bigserial , serial がデータ型として存在。
UNIQUE制約や PRIMARY KEY制約は自動ではつかない。

# MySQL
CREATE TABLE tasks (
    id INT(10) NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

# PostgreSQL
CREATE TABLE tasks (
    id SERIAL NOT NULL,
    title VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

まちゃ

PostgreSQL には UNSIGNED制約は無い。
CHECK 制約で代替可能。（MySQL より色々出来る）

# MySQL
CREATE TABLE sales (
  year SMALLINT UNSIGNED NOT NULL,
  month SMALLINT UNSIGNED NOT NULL,
  price INT UNSIGNED NOT NULL,
  UNIQUE (year, month)
);

# PostgreSQL
create table sales (
  year SMALLINT CHECK(1900<year AND year<3000) NOT NULL,
  month SMALLINT CHECK(1<= month AND month<=12) NOT NULL,
  price INT CHECK(price >=0) NOT NULL,
  UNIQUE (year, month)
);

まちゃ

PostgreSQL には更新日時の自動更新機能が無い。
FUNCTION で代替する模様。

# MySQL
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

※ 個人的にはあまり FUNCTION を多様したくないため、UPDATE のクエリ上で更新をかけている

まちゃ

MySQL で言う INSERT ... ON DUPLICATE KEY UPDATE を PostgreSQL で実現したい場合、 INSERT ... ON CONFLICT ... DO UPDATE SET ... で代替。

ちなみに、MySQL では DUPLICATE KEY UPDATE が走る場合に AUTO INCREMENT を指定したカラムが更新されるが、PostgreSQL の SERIAL は更新されなかった。

CREATE table sales_test (
  id SERIAL,
  year SMAILLINT CHECK(1900<year AND year<3000) NOT NULL,
  month SMAILLINT CHECK(1<= month AND month<=12) NOT NULL,
  price INT CHECK(price >=0) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (year, month)
);

INSERT INTO sales_test (year, month, price) VALUES (2021, 1, 100000), (2021, 2, 200000), (2021, 3, 300000);
INSERT INTO sales_test (year, month, price) VALUES (2021, 1, 110000), (2021, 2, 220000), (2021, 3, 330000) ON CONFLICT (year, month) DO UPDATE SET price=excluded.price;

実行結果

まちゃ

PostgreSQL には Row Level Security がある。

toB SaaS 等でマルチテナントデータを扱う時などに便利。