PostgreSQL コマンド メモ
基本操作
全データベース表示
psql -l
データベースのリスト表示
\l
データベースに入る
psql "データベース名"
テーブル一覧を表示
team3db=> \d
テーブル削除
team3db=> drop table "テーブル名";
DROP TABLE
テーブルの内容参照
team3db=> select "列名" from "テーブル名"
Ex) select name from ab_menu;
Ex) select * from ab_menu;
(*はすべての列の意味)
カラムのデータ型変更
ALTER TABLE "テーブル名" ALTER COLUMN "カラム名" TYPE "データ型";
データの挿入
INSERT INTO "テーブル名" VALUES (22, 'Cheese', 9.99);
複数は→
INSERT INTO "テーブル名" VALUES (22, 'Cheese', 9.99), (42, 'Chee', 9.47);
(推奨？)
INSERT INTO テーブル名 ("カラム名1", "カラム名2", "カラム名3") VALUES (22, 'Cheese', 9.99);
データの削除
delete from "テーブル名" where "カラム名"='データ';
ex) delete from myschema.accesslog where type='search';
全データ削除
delete from テーブル名;
備考
スタート
sudo service postgresql start
起動しているか確認
sudo service postgresql status
終了
sudo service postgresql stop
ロールのリスト
\du
ユーザの作成
CREATE ROLE "ユーザ名";
CREATE ROLEのオプション
ex) CREATE ROLE "ユーザ名" LOGIN CREATEDB;
LOGIN : 作成したユーザ（ロール）でログイン可能
CREATEDB : データベースの作成が可能
ユーザの作成(デフォルトでCREATE ROLEのオプションLOGINが付与)
CREATE USER "ユーザ名";
ユーザの削除
DROP ROLE "ユーザ名";
存在するユーザの確認(初期設定で存在するユーザまで全て)
SELECT rolname FROM pg_roles;
(ロールのリストを\duで表示しても問題はない)
データベースの作成
CREATE DATABASE "データベース名" OWNER = "ユーザ名" TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
TEMPLATE = : 使用するテンプレートを指定
テンプレートに指定した設定済の照合順序、作成済みのテーブル等の内容が引き継がれる。
他のデータベースの内容を引き継がず、新しいデータベースを作成する場合"template0"
ENCODING = : 使用する文字コードを指定
UTF8を指定
LC_COLLATE : 照合順序の指定
照合順序は、ORDER BY句でのソート順等に影響。'C'の文字ソートはコード順となる。
日本語を考慮した「ja_JP.UTF-8」も指定もできるがパフォーマンスが悪くなる可能性。
LC_CTYPE : データベースで使用する文字の大文字、小文字、数字といった分類の指定
日本語を考慮した「ja_JP.UTF-8」ではLOWER関数で全角の英字も小文字への変換対象として扱われる。
データベースの削除
DROP DATABASE "データベース名"
メモ
権限
スーパーユーザーor特権で (例:データベースの所有者) ログイン
このユーザーは、データベースの作成、削除、ユーザーの追加など全操作を実行可能
postgres=#
一般ユーザーとしてログイン
限定的な権限のみで、主にデータの参照や更新を行う
postgres=>
"Peer authentication failed" の対処
設定ファイルを開く
cd /etc/postgresql/<version>/main/ (verは14でした)
sudo nano pg_hba.conf
local や host の行で "peer" などの認証メソッドが設定されている場合、これを適切な認証メソッド（例: md5、password）に変更します。
再度読み込み
sudo service postgresql restart
Ubuntuのユーザ名で作った場合は特にパスワード変更をmd5にした後はできないので、peerに戻してログインしてパスワードを設定してからmd5にする
初期設定
OSのアップデート
sudo apt update
Postgres をインストール
sudo apt install postgresql
Postgres の再起動を実行
sudo /etc/init.d/postgresql restart
デフォルトのpostgresユーザでpostgresにログイン
sudo -u postgres -i
postgressでpsql
psql
Ubuntuのユーザに権限を与える
CREATE ROLE "ubuntuユーザ名" LOGIN CREATEDB;
一度終了
\q
psqlでpostgresにログイン
psql -d postgres
Discussion