概要
いつもはmysqlを使うが、色々なデータベースに触れる機会があり苦しめられたので、
備忘録として役に立ったことを書いていく。随時更新していく。
記録
GUIを使う。(pgAdim)
brew使ってインストールすると楽。Applicationの下にpgAdminが入る。
brew install postgresql
psql を使う
コマンドラインから操作したい人はこれがおすすめっぽい。
接続は、
psql -h <host_name> -U <role_name 最初はpostgresと入れる。> -d <database_name>
https://dev.classmethod.jp/articles/postgresql-organize-command/
これがよく使うコマンドをまとめていて参考になる。以下私も使うたびにググっていたコマンドを書いていく。
データベース一覧を取得する
# \l
データベースを切り替える
# \c <database_name>
データベース作成
# create database "<database name>"
データベースを削除
# drop database <database_name>
テーブル一覧を取得する
# \d
注意！！ダブルクオーテーションをつけることと、各sqlの最後にセミコロンをつけるのを忘れないようにする！
現在のユーザーを表示する
# select current_user;
ユーザーを切り替える
# \connect - <user_name>
スキーマを作る
# create schema <schema_name>;
現在のスキーマを確認する
# select current_schema;
スキーマ一覧を取得する
# \dn
スキーマを切り替える
# set search_path to <schema_name>;
このスキーマを意識しておかないと、psycopg2 というドライバを使ったときにテーブルが見つからないエラーが出てはまったので注意！
テーブル一覧を取得する
SELECT
*
FROM
pg_catalog.pg_tables
WHERE
schemaname != 'pg_catalog'
AND schemaname != 'information_schema';
テーブルをドロップする時
drop table "table_name"
というふうにテーブル名を " " で囲む。
スキーマという概念に関して
https://www.dbonline.jp/postgresql/schema/index1.html
これがわかりやすかった。
テーブル見つからないエラー
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "test_column" of relation "table1" does not exist
https://kumachan.biz/2017/01/27_1927/4668/
これが大変ありがたかった。
つまり、スキーマ名（テーブル名）がきちんとサーチパスに入っているか確認が必要。
SHOW search_path;
として調べ、
SET search_path TO スキーマ名;
として設定しておく。
特定のテーブルのカラム名とコメントを取得
--指定したテーブルのカラム一覧とコメントを取得するクエリ
select
information_schema.columns.column_name,
information_schema.columns.data_type,
(select description from pg_description where
pg_description.objoid=pg_stat_user_tables.relid and
pg_description.objsubid=information_schema.columns.ordinal_position
)
from
pg_stat_user_tables,
information_schema.columns
where
pg_stat_user_tables.relname='テーブル名'
and pg_stat_user_tables.relname=information_schema.columns.table_name
この時 'テーブル名' を "テーブル名" とかにしてもダメなので注意。
テーブルのメタデータ取得
SELECT
col.table_name AS "table_name"
, col.ordinal_position AS "No"
, column_name AS "カラム名"
, col.data_type AS "データ型"
, col.character_maximum_length AS "# of character"
, col.character_octet_length AS "# of bytes"
, col.numeric_precision AS "digits"
, col.numeric_scale AS "fraction digits"
, col.datetime_precision AS "under milli sec"
, col.interval_type AS "unit of interval"
, col.is_nullable AS "NULL_OK?"
, col.column_default AS "default value"
FROM
information_schema.columns col
WHERE
col.table_name = 'テーブル名'
alter table add を使う時の注意点
もしレコードがすでに入っているテーブルに新しいカラムを alter table add で作る時、not null というオプションを与えることはできない。
なぜならもともと入っているレコードには新しいカラムのデータはもちろん入っていないため、not null の制約を満たせないから。
psycopg2.errors.NotNullViolation: column "*********" contains null values
みたいなエラーが出る。
したがって、このようなオペレーションをしたい時はレコードがない状態の空のテーブルに対して行う必要がある。
ちなみに、mysqlの場合だとこのエラーは出ない、mysqlはそこまで厳密ではない？？