PostgreSQLの基本コマンドとSQL

# PostgreSQLの基本コマンドとSQL

初心者向け。気が向いたら追記していく。だんだん基本から離れてしまいそう。

### 起動

```
# postgresユーザで実行
$ pg_ctl start
```

systemdの場合（PostgreSQL 12の場合）

```
# rootユーザで実行
# systemctl start postgresql-12
```

### 停止

```
# postgresユーザで実行
$ pg_ctl stop

# セッションを強制終了し停止。
$ pg_ctl stop -m fast
# 強制停止。次回起動時にリカバリ処理が実行される。
$ pg_ctl stop -m immediate
```

systemdの場合（PostgreSQL 12の場合）

```
# rootユーザで実行
# systemctl stop postgresql-12
```

## データベース作成・削除・一覧表示

```
$ createdb [データーベース名]

$ dropdb [データベース名]

$ psql -l
```

## SQL実行

```
$ echo 'select now()' | psql
```

## ターミナルの操作

### データベースへ接続

```
$ psql -h [ホスト名] -p [ポート番号] -U [ユーザ名] [データベース名]

# 以下の場合、ローカルホストに対してpostgresデータベースへユーザpostgresで接続。
$ psql
```

### 接続終了

```
\q
```

### SQLファイル実行

```
\i [ファイル名]
```

### SQL実行時間の出力

```
\timing on
```

### ログの出力

```
出力開始
\o out.log
出力終了
\o
```

### その他

```
# データベースの一覧
\l
# テーブルの一覧
\d, \dt, \d+, \dt+
# テーブル定義の確認（インデックス含む）
\d [テーブル名]
# 拡張表示（レコードの縦・横切り替え）
\x
# データベース切り替え
\c [データーベース名]
```

### 現在時刻の表示

```
# 日付・日時
select now()
select current_timestamp
# 日付
select current_date
# 時刻
select current_time
```

### 実行計画の表示

```
explan [SQL文]
```

### 統計情報の取得

```
analyze [テーブル名]
```

### テーブル作成

```
create table test01 (
  val1 serial, -- 自動増分4バイト整数
  val2 numeric(10, 2), -- 数値
  val3 char(10), -- 固定長文字列
  val4 varchar(10), -- 可変長文字列
  val5 timestamp, -- 日付と時刻
  val6 date, -- 日付
  val7 bytea, -- バイナリ
  primary key (val1, val2) -- 主キー
);

# テーブルが存在しない場合作成する場合
# create table if not exists テーブル名
```

### インデックス作成

```
create index [インデックス名] on [テーブル名](カラム名, カラム名...)
```

### テーブル・インデックスの削除

```
# テーブルの削除
drop table [テーブル名]
drop table if exists [テーブル名]

# インデックスの削除
drop index [インデックス名]
```

### テーブル名変更

```
alter table [変更前] rename to [変更後];
```

### コメント追加

```
# テーブルコメント追加
comment on table test is 'テストテーブル';
# カラムコメント追加
comment on column test.id is 'テストカラム';
```

### テーブルの複製

```
create table [複製先] (like [複製元]);
```

### 主キーの追加・削除

```
# 主キーを削除する
alter table [テーブル名] drop constraint [主キー名];
# NO NULL制約も削除する場合
alter table [テーブル名] alter column [カラム名] drop not null;

# 主キーを追加する
alter table [テーブル名] add constraint [主キー名] primary key([カラム名]);
```


### 制約の追加と削除

```
# 制約の追加（NOT NULLの場合）
alter table [テーブル名] alter column [カラム名] set not null;

# 制約の削除（NOT NULLの場合）
alter table [テーブル名] alter column [カラム名] drop not null;
```

### カラム追加・削除

```
# カラムを追加する（例：varchar(10)）
alter table [テーブル名] add column [カラム名] varchar(10);

# カラムを削除する
alter table [テーブル名] drop column [カラム名];
```


### ビュー作成・削除

```
create view [ビュー名] as [query]

drop view [ビュー名]
```

### シーケンスの操作

```sql
# シーケンスを作成する
create sequence seq_test
    increment by 1
    maxvalue 99999999
    start with 1
    cycle
;

# シーケンスを進める
select nextval('seq_test');

# 現在値を取得する
select currval('seq_test');

# 初期化する
select setval('seq_test', 1, false);

# シーケンスを削除する
drop sequence seq_test;
```


### INSERT文（複数行一括）

複数行を一括でinsertする例。

```sql
# insert into test01 values(1, 'aa', now());
INSERT 0 1
# insert into test01 values(1, 'aa', now()), (2, 'bb', now());
INSERT 0 2
```


### 定期的にSQLを実行

基本ではないけど。。。

```
# \watchを使用。1秒ごとにselect now()を実行する場合
# select now(); \watch 1
              now              
-------------------------------
 2020-08-23 01:19:53.867535+02
(1 row)

Sun 23 Aug 2020 01:19:53 AM CEST (every 1s)

              now              
-------------------------------
 2020-08-23 01:19:53.868241+02
(1 row)

Sun 23 Aug 2020 01:19:54 AM CEST (every 1s)
```

### CSV出力

```
$ psql -c "SQL文" -Atq -F, > [出力ファイル名]
```

### プロセスを停止

```
# プロセスを停止
select pg_cancel_backend([プロセスID]);
# プロセスを強制停止
select pg_terminate_backend([プロセスID]);
```