概要

いつもは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はそこまで厳密ではない？？