MySQLユーザーがPostgreSQLに入門

PostgreSQLの正式名称も知らないMySQLユーザーが色々PostgreSQLをさわる記事となっております

正式名称

PostgreSQL
読み方はポスト、グレス、キューエル
ポスト、グレ、エスキューエルじゃないのか...なんか変わったところで区切るんだな！？

MySQLとの違い

なるほど完全に理解した(ｷﾘｯ

環境構築

まずは環境構築から、本題ではないので折りたたみの中に記載しておきます
OSはmac(Sonoma)、Laravel11、PostgreSQLは17.2で構築しています

環境構築手順

ディレクトリ構成はこれで

my-laravel11-project
├── docker
│   ├── nginx
│   │   └── default.con
│   └── php
│       └── Dockerfile 
├── docker-compose.yml
└── その他 Laravel プロジェクトのファイル (app, config, etc...)

各ファイルはこちら

docker-compose.yml

version: '3.8'
services:
  # Laravel + PHP-FPM
  app:
    build:
      context: .
      dockerfile: ./docker/php/Dockerfile
    container_name: laravel11-app
    # Laravel のソースをコンテナ内の /var/www/html にマウント
    volumes:
      - ./:/var/www/html

    expose:
      - "9000"
    depends_on:
      - db

  # Nginx (Webサーバー)
  web:
    image: nginx:alpine
    container_name: laravel11-nginx
    # ソースコードと設定ファイルをマウント
    volumes:
      - ./:/var/www/html
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf
    depends_on:
      - app
    ports:
      - "8000:80"

  # PostgreSQL 17.2
  db:
    image: postgres:17.2
    container_name: laravel11-postgres
    environment:
      POSTGRES_DB: laravel_db
      POSTGRES_USER: laravel_user
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"

デフォルトポート番号も違うんですねぇ
MySQL→3306
PostgreSQL→5432

Dockerfile

FROM php:8.2-fpm

# 必要なパッケージをインストール
RUN apt-get update && apt-get install -y \
    git \
    curl \
    libpq-dev \
    libzip-dev \
    zip \
    unzip

# PostgreSQL拡張とその他拡張をインストール
RUN docker-php-ext-install pdo pdo_pgsql

# Composer をインストール
COPY --from=composer:latest /usr/bin/composer /usr/bin/composer

# 作業ディレクトリを指定
WORKDIR /var/www/html

default.conf

server {
    listen 80;
    server_name localhost;

    # プロジェクトの Document Root
    root /var/www/html/laravel/public;

    index index.php index.html;

    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    # PHP-FPMへ処理を渡す設定
    location ~ \.php$ {
        include fastcgi_params;
        fastcgi_pass app:9000; # docker-compose.yml の service 名:expose ポート
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    }
}

以上のファイルを作りましたらルートディレクトリであるmy-laravel11-projectにて、以下コマンドを実行しコンテナを作成

docker compose up -d --build

コンテナに入りlaravelプロジェクトを作成

docker compose exec app bash
composer create-project laravel/laravel="11.*"

envファイル

データベースの設定項目をdocker-compose.ymlの記載に合わせて編集

DB_CONNECTION=pgsql
DB_HOST=db
DB_PORT=5432
DB_DATABASE=laravel_db
DB_USERNAME=laravel_user
DB_PASSWORD=secret

ここまできたら以下のURIにアクセスすることでlaravelの初期画面が表示されます
http://localhost:8000/

エラーが出た場合は、環境構築はこの記事の本題ではないので後は各自で調べてもらうようにお願いしたい！
以上、環境構築でした

色々と触ってみる

まずはデータベース一覧の表示から

-- DBにアクセス
psql -U laravel_user -d postgres
\l

おおー、表示に必要なコマンドの文字数がMySQLと違って少ない！

-- MySQLの場合
SHOW DATABASES;

次はテーブル一覧の表示まで

\c laravel_db 
\d

地味なこととはいえ、MySQLより入力文字が少ないので楽ですねぇ...

-- MySQLの場合
USE laravel_db;
SHOW TABLES;

テーブルのカラム情報を確認してみましょう

\d users

MySQLだと、いっつもcolumnsのスペルを忘れてしまうので\dでカラム情報を見ることができるのはとても助かる...
そしてPostgrSQLだと、index情報も一緒に表示してくれるんですね

-- MySQLの場合
SHOW COLUMNS FROM users;

CRUD操作

usersテーブルにレコードを登録

INSERT INTO
    users (name, email, password)
VALUES
    ('太郎', '[e-mail]', 'hashed_password_here');

登録したレコードを確認

SELECT * FROM users;

登録したレコードを更新

UPDATE
    users
SET
    name = '太郎改'
WHERE
    email = '[e-mail]';

登録したレコードを削除

DELETE FROM
    users
WHERE
    id = 1;

CRUD操作はMySQLと操作感変わらないですね

create function

-- 関数を作成
CREATE OR REPLACE FUNCTION double_number(input_num INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN input_num * 2;
END;
$$;

-- 作成した関数を呼び出し
SELECT double_number(5);

SQLの実行時間の確認

SQLの実行時間は、MySQLだとデフォルトで表示されていますがPostgreSQLでは以下のコマンドの入力が必要

\timing
-- offにする場合はもう一度\timingを実行する

index関連

indexの部分は特にMySQLとPstgreSQLで違いを感じた

indexの追加

CREATE INDEX users_name_index ON users (name);
-- indexを確認
\d users

PostgreSQLは様々なインデックスタイプをサポートしているので、インデックスのタイプも表示される(画像の赤枠部分)んですね、ほえぇ〜

実行計画の確認

コマンドはMySQLと同じだが表示形式がかなり違う

EXPLAIN SELECT * FROM users WHERE name = 'Lura Kuvalis';

MySQLのEXPLAINの結果と全く違うので、実行計画の読み方はキャッチアップが必要そうですね

Laravel上で触ってみる

CRUD操作を一通りやってみましょう
お手軽に確認するためにルートクロージャで確認します

<?php

use Illuminate\Support\Facades\Route;
use App\Models\User;

// ユーザー取得
Route::get('/users', function () {
    $users = User::all();
    return response()->json($users);
});

// ユーザー作成
Route::get('/create-user', function () {
    $user = User::create([
        'name' => '佐藤 花子',
        'email' => '[e-mail]',
        'password' => Hash::make('anothersecurepassword'),
    ]);

    return response()->json([
        'message' => 'ユーザーが正常に作成されました。',
        'user' => $user,
    ], 201);
});

// ユーザー更新（Update）
Route::get('/update-user', function () {
    $user = User::query()
        ->where('name', '佐藤 花子')
        ->first();

    $user->name = '田中 太郎';
    $user->save();

    return response()->json([
        'message' => 'ユーザーが正常に更新されました。',
        'user' => $user,
    ]);
});

// ユーザー削除（Delete）
Route::get('/users-delete', function () {
    $user = User::query()
        ->where('name', '田中 太郎')
        ->first();

    $user->delete();

    return response()->json(['message' => 'ユーザーが正常に削除されました。']);
});

まずはユーザー情報の取得

ユーザーを作成

ユーザー情報を更新

ユーザーを削除

Eloquentを使った簡単なCRUD操作では、MySQLとPostgrSQLで違いは感じませんでした

ドキュメント

とてつもなくありがたいことに、日本語で翻訳されています
日本語翻訳があるのはMySQLと同じですね

型

マニュアルをざっと見た感じにはなるのですが、型の取り扱いは結構違ってきているように思います
bool型なんかは特に差をかなり感じます

おわりに

基本的なCRUD操作では両者の違いはほぼないように感じました
ただ、index周りは軽く触っただけでも違いを感じますね
それとテーブル情報の確認系のコマンドが、PostgreSQLでは短い入力で済むので個人的にはありがたいです