postgreSQL備忘録 - プログラミンGOO

★dkt_searchの初期DB作成についてはSpringSecurityのドキュメントを参照

【管理コマンド基本操作】

・ログイン

psql -U postgres  //管理者　※本来はユーザ(ロールという)を作成してそちらを使用すること
\l //データベース一覧
\c databateName	//データベースに移動 
\dt	//table一覧
\d tableName	//table詳細
select * from TABLENAME;	※ここからコロンが必要なので注意
update tableName set columnName = value where …

・その他のよく使うコマンド

\d	tableName	//table詳細
\l	//db一覧
\q	//quit
\x	//拡張表示on,off
\?	//よく使うコマンド一覧を表示

【DB基本操作】

・値の追加

insert into tableName (columnName, columnName2) values ('clmvalue', 'clm2value'), ('clmvalue2', 'clm2value2');

・抽出

select * from tableName;

・更新

update tableName set columnName = value where …

・行削除

delete from tableName where …

【データ】

数値	： integer(int), real, serial
文字	： char(5), varchar(255), text
真偽	： boolean TRUE FALSE t f
日付	： date, time, timestamp

【構造変更】

■カラム追加

alter table tableName add column columnName dataType;

■カラム削除

alter table tableName drop columnName;

■リネーム

alter table tableName rename to newColumnName;

型変更

alter table tableName alter columnName type newType;

booleanとsmallintなど、扱うタイプが違う場合、『この時はこれ』と条件を明示しないとエラーになる
https://qiita.com/seiro/items/ade4c220dfe4acb0ef4b
※smallintをbooleanに変更

ALTER TABLE users ALTER enabled TYPE boolean
  USING CASE
    WHEN enabled = 1 THEN TRUE
    WHEN enabled = 0 THEN FALSE
    ELSE null
  END;

【テーブル】

テーブル作成

create table tableName (columnName varchar(255), columnName2 text)

名前変更

alter table tableName rename to newTableName

テーブル削除

drop table tableName

【値の取得】クエリ

■基本

select * from tableName where id = 1;

■関連（バインド）

select tableName.columnName, tableName2.columnName2 from tableName, tableName2 where (例) tableName.id = tableName2.foreing_id;

省略形 ※上記と同義

select tn.columnName, tn2.columnName2 from tableName tn, tableName2 tn2 where (例) tn.id = tn2.foreing_id;

【制約】

not null
unique
check(length(columnName) > 5)	//ほかにもいろいろ
default	//例）created timestamp default 'now', is_draft bolean default TRUE
primary key (not null, unique)

・UNIQUE制約について
MySQLはUNIQUE KEYだが
PostgresqlではUNIQUE INDEX またはUNIQUE制約
UNIQUE制約はカラムの制約なので簡単につけ外しはしないので、基本的にはUNIQUE INDEX？
参考：https://okwave.jp/qa/q6326929.html

■外部キー制約
子テーブルのカラムに外部キーを追加することで、親テーブルとの関係性を持たせる。
・外部キーの追加

ALTER TABLE "child" ADD CONSTRAINT "keyName" FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;"

childテーブルのparent_idカラムにkeyNameという名前の外部キーを追加する。
このカラムはparentテーブルのidを参照する。
ON DELETE CASCADE：親オブジェクトが変更された場合の処理をどうするかを設定する。この設定では削除処理が行われた場合に子オブジェクトも削除する。

・外部キーの削除

ALTER TABLE "users" DROP COMSTRAINT "users_to_account";

・外部キー制約・参照元の確認

\d tableName

・読み方

外部キー制約:
    "keyName" FOREIGN KEY (client_id) REFERENCES client(id)

親テーブルがあり、そこに向かって外部キー参照をしていることを示す。※自身は子テーブルである
このテーブル（子）のclient_idというカラムに、keyNameという外部キーが設定されているよ。このカラムはclient（親）テーブルのidカラムを参照しているよ。

参照元:
    TABLE "schedule" CONSTRAINT "keyName" FOREIGN KEY (client_id) REFERENCES client(id)

子テーブル（schedule）があり、そこから外部キー参照されていることを表す。※自身は親テーブルである
scheduleテーブル（子）のclient_idカラムから外部キー参照されているよ。外部キーはこのテーブル：clientテーブル（親）のidカラムを参照しているよ。

【インデックス】

インデックスを付けることで大量のデータを処理する際に処理速度を向上させることができる。
インデックスとは付箋。例えば規則性のある商品番号などに設定すると効果的。
逆に、カテゴリーなど、カラムとして管理してしまった方が良いものはインデックスではなくカラムにするほうが適切。
また、インデックスをつけすぎると結局規則性を検索できなくなるため逆効果になることもある。

create index indexName on tableName(columnName);
drop index indexName;	//削除

【ビュー】※よく使うsql文を登録しておく機能

作成

create view viewName as sql;

呼び出し

select * from viewName;

削除

drop view viewName;

ビュー一覧表示

\dv

【トランザクション】

・途中でselect文で確認することもできるよ

開始

begin;

終了

commit;

中止

rollback;

【postgresql管理用コマンド】

・ヘルプ

help

■データベース一覧

psql -l

■データベース作成

createdb dbName

//★このへんはsql文ではなくpostgresqlの管理用コマンドでcreate databace dbNameのラッパー
//なので普通にコマンドプロンプトで実行するとエラーになる。
//その場合は普通にsql文で実行しよう
https://oshiete.goo.ne.jp/qa/2052007.html

※ホスト名の調べ方

ipconfig /all

削除

dropdb dbName

接続

psql dbName

～Mysqlとの違い～
・『`』(バッククォート) ：全て不要
・SET AUTOCOMMIT = 0; ：\set autocommit = 0FF; ※大文字小文字が区別されるので注意
・tinyint(1) ：smallint
・ALTER TABLE `authorities` ADD UNIQUE KEY `username` (`username`,`authority`);
：CREATE UNIQUE INDEX username ON authorities (username,authority);

【appendix】

■バージョン

psql --version

■ユーザの作成
https://qiita.com/wb773/items/248e6e083b2fe12e820a

■外部ファイルからインストール

\i fileName

【導入】

■postgresQLのインストール
参考：https://qiita.com/tom-sato/items/037b8f8cb4b326710f71

※PSQLも一緒にインストールされる
C:\Program Files\PostgreSQL\13\bin　のpslq.exe

■パスを通す
C:\Program Files\PostgreSQL\13\bin　で登録しておく

■コマンドプロンプト再起動

■バージョン確認

psql -V

■コマンドプロンプトからpsqlを使用
・環境変数のPathを通す
C:\Program Files\PostgreSQL\12\bin
※psql.exeがあるフォルダ

・ログイン

psql -u postgres -d postgres

■パスワードを忘れた
サーバの操作に使用：pg_ctl
-D ：ディレクトリ指定　※どこを指定すればいいかわからん
https://www.postgresql.jp/document/10/html/app-pg-ctl.html

https://qiita.com/ritya/items/b1ae186f3f6308c52289
こっちは一瞬で出来た

・再起動は以下でできた

pg_ctl restart -D "C:\Program Files\PostgreSQL\12\data"

GUIで再起動する場合　※これで-Dのパスも調べられた
スタートボタン右クリック＞サービスとアプリケーション＞サービス(時間かかる)＞postgresql
ここでパスとか停止起動で再起動
https://www.dbonline.jp/postgresql/install/index4.html

・原因、なぜか管理者ではなくユーザ名でログインさせられていた。どゆこと…
psql -U postgres　で管理者ログイン
パスワードは初期設定したもの。

【エラー対応】

■列●●は存在しません
updateコマンドを実行したがなぜか値が更新できない
update client_media set media_type = “t” where id = 49;
『列tは存在しません』という謎のエラーが出る。

＞＞SQLでは文字列はシングルクォーテーションで囲うこと

■三角関係を持つテーブル
クライアント＞スケジュール、スケジュール＞ジャンル、ジャンル＞クライアントというような三角関係を持つテーブルをOneToOneで作成したらデータがうまく更新されなかった。
そもそも論だが、テーブル設計において、三角関係になる構造は作らないのが基本らしい。
今回のような処理もそうだが、取れるデータもどこから取るか？で変わってきてしまうことがあるらしい。
参考：データベース設計の際に気をつけていること - 食べチョク開発者ブログ