postresql を使うときの備忘録

## 概要
いつもはmysqlを使うが、色々なデータベースに触れる機会があり苦しめられたので、
備忘録として役に立ったことを書いていく。随時更新していく。


## 記録

### GUIを使う。(pgAdim)

https://www.postgresql.org/ftp/pgadmin/pgadmin4/v4.19/macos/

brew使ってインストールすると楽。Applicationの下にpgAdminが入る。
```bash
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 スキーマ名;
```
として設定しておく。


### 特定のテーブルのカラム名とコメントを取得

```sql


--指定したテーブルのカラム一覧とコメントを取得するクエリ
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
```

この時 'テーブル名'　を　"テーブル名"　とかにしてもダメなので注意。


### テーブルのメタデータ取得

```sql


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``` の制約を満たせないから。

```bash
psycopg2.errors.NotNullViolation: column "*********" contains null values
```
みたいなエラーが出る。
したがって、このようなオペレーションをしたい時はレコードがない状態の空のテーブルに対して行う必要がある。

ちなみに、mysqlの場合だとこのエラーは出ない、mysqlはそこまで厳密ではない？？