基本的なPostgreSQLの操作方法

はじめに

PostgreSQLの勉強をしている際、インストールした後、どうやって動かせばいいか分からず時間がかかってしまったので、備忘録として手順をまとめました。
参考記事にとてもお世話になりました。

環境

macOS: Big Sur version 11.4
PostgreSQL: 14.4

PostgreSQLの起動・確認・終了

起動

zsh

brew services start postgresql

確認

zsh

brew services list

終了

zsh

brew services stop postgresql

環境変数の設定

# PostgreSQLで使用するデータを格納
export PGDATA=/usr/local/var/postgres

zshを使っているのであれば、~/.zshrcにbashであれば~/.bash_profileに上記の内容を記入する。

データベース操作

データベースに接続

zsh

# スーパーユーザーとしてログイン
psql postgres

# ログインコマンドのオプション
# ホスト名はデフォルトで-h localhost, ポート番号はデフォルトで -p 5432で変更がない場合は省略可能
psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名

psqlを終了

sql

\q

コマンドラインへ戻るが、PostgreSQLサーバーを停止したわけではないので注意。

データベース一覧の確認

sql

\l

データベース作成

sql

create database データベース名;

dbnameというデータベースが作成されます。

データベース名の変更

sql

alter database データベース名1 rename to データベース名2;

カレントデータベースは変更できないので注意。

使用するデータベースの選択

sql

\c データベース名;

データベース削除

sql

drop database データベース名;

ロール作成

作成

sql

CREATE ROLE ロール名;
# または
CREATE ROLE ロール名 LOGIN; #LOGIN属性をつける

似たものに、CREATE USERがあるのですが、違いはCREATE ROLEではLOGIN属性がつかず、CREATE USERではLOGIN属性が付くようです。CREATE ROLEを使うのが標準みたいです。
CREATE ROLE ロール名 LOGIN;とCREATE USER ロール名;は同じ結果となる。

参考記事：【PostgreSQL】CREATE USERとCREATE ROLEの違い

属性変更

ロールをLOGIN属性なしで作成して、後からLOGIN属性を付けたい場合は、以下のようにします。

sql

ALTER ROLE ロール名 LOGIN;

その他の属性については以下PostgreSQL公式ドキュメントが参考になるかと思います。

22.2. Role Attributes

テーブル操作

テーブル一覧の確認

sql

\dt

テーブルの作成

sql

create table テーブル名(カラム名1 データ型1, カラム名2 データ型2, ...);

# 例
create table users(id integer, name string);

テーブル情報の確認

sql

\d テーブル名

テーブル名の変更

sql

alter table テーブル名1 rename to テーブル名2;

テーブルの削除

sql

drop table テーブル名;

ファイルの実行

sql

\i ファイル名.sql

データ型

sql

# 数値
int # 整数型
real # 実数型
serial # 連番 MYSQLでいうAUTO_INCREMENT属性

# 文字
char(数値) #文字数が決まっている場合
varchar(数値) #文字数が決まっていない場合。数値は上限を表す

# 真偽値
boolean

# 日付
date # 日付
time # 時間
timestamp # 日付と時間の両方

詳しいデータ型については以下の公式マニュアルに記載されています。

Chapter 8. Data Types

データ制約

not null # 空の値が入った場合エラーにする
unique # 重複をエラーとする
check # 
default # デフォルト値を指定
primary key # 主キー、値を一意に決める。テーブルにつき1つのみ設定可能 (not null + unique)

詳しいデータ制約については以下の公式マニュアルに記載されています。
5.4. Constraints

データ作成例

.sqlファイルを作ってそこに書き込みます。実行したい場合は、データベース内で\i ファイル名;で実行します。

drop table if exists users;

create table users (
	id serial, primary key,
	title varchar(255) not null,
	body text check(length(body) > 5),
	is_draft boolean default TRUE, 
	created timestamp default 'now'
);

データ操作 (CRUD)

レコードの挿入 (INSERT)

insert into テーブル名 (カラム名1, カラム名2, ...) values ('値1', '値2', ...);

値は文字列の場合はシングルクォーテーションで囲む。

レコードの読み (Read)

select * from テーブル名;

全てのレコードを読み込む

select カラム名1, カラム名2 from テーブル名;

レコードのカラム名1, カラム名2を読み込む

条件をつける

■where

select * from テーブル名 where カラム名1 > 5.5;

全レコードの内カラム名1が5.5より大きいレコードを読み込む。演算子として=, !=, <>, >, >=, <, <=が使える

■並び替え order by　数値は昇降順、文字列はアルファベット順に並び替え
昇順

select * from テーブル名 order by カラム名;

降順

select * from テーブル名 order by カラム名 desc;

■読み込み数の制限

select * from テーブル名 limit 数値;

数値分だけ読み込む。並び替えの後にも使える

■集計

select count(*) from テーブル名;

レコード数をカウントする

select distinct カラム名 from テーブル名;

カラムの中のユニークな値だけを読み込む

レコードの更新 (UPDATE)

update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2;

カラム名2が値2の全レコードに対してカラム名1を値1に更新する

■whereの条件はor, andでつなげることができる

update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2 OR カラム名3 = カラム名3;
update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2 and カラム名3 = カラム名3;

レコードの削除 (Delete)

■全レコードの削除

delete from テーブル名;

■特定のレコードの削除

delete from テーブル where カラム名 = 値;
# 演算子は=, !=, <>, <, <=, >, >=が使える

テーブル構造の変更

■カラムの追加

alter table テーブル名 add カラム名 型 (データ制約);

■カラムの削除

alter table テーブル名 drop カラム名;

■カラムの変更

alter table テーブル名 rename カラム名1 to カラム名2;

■型の変更

alter table テーブル名 alter カラム名 type (変更先の)型;

■インデックスの追加

create index インデックス名（任意で付ける） on テーブル名(カラム名);

"インデックス名"は以下のようになる。名命例:"カラム名_index"

\d 

Table "public.テーブル名"
 Column        |          Type          | Collation | Nullable |              Default              
---------------+------------------------+-----------+----------+---------------------------------
 id            | integer                |           | not null | nextval('users_id_seq'::regclass)
 カラム名_index | 型                      |           |          |
Indexes:
    "テーブル名_pkey" PRIMARY KEY, btree (id)
    "インデックス名" btree (カラム名)

# インデックスの削除
drop index インデックス名(任意でつけた名前);

インデックスを付けると、検索は早くなるがレコード挿入の速度は遅くなるので注意。

参考記事

• PostgreSQL入門
• 【PostgreSQLをMacで使う方法】インストールから基本的な使い方まで紹介します
• PostgreSQLへの接続と切断
• [Mac]PostgreSQLをhomebrewでインストールする方法
• 【PostgreSQL】ユーザーを作成する（CREATE USER文、CREATEUSER）
• 【PostgreSQL】CREATE USERとCREATE ROLEの違い
• MySQLとPostgreSQLコマンド比較表
• PostgreSQL 14.4 Documentation
• PostgreSQLの環境をmacのローカルに作成＆SQL操作（＆ついでにDataGripへの接続）