PostgreSQL 備忘録

## Path
https://www.dbonline.jp/postgresql/install/index3.html

「コントロールパネル」
\>「システムとセキュリティ」
\>「システム」
\>「システムの詳細設定」
\>「システムのプロパテ画面の環境変数」
\>「環境変数の画面のシステム環境変数(S)」
\>「Path」

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/637823/88d76442-ec46-043b-d43a-6e378200ecef.png)

## ログイン方法
```
$ psql -U postgres
パスワード
```
## 上手くいかないとき
```
psql: エラー: サーバに接続できませんでした: Connection refused (0x0000274D/10061)
        サーバはホスト "localhost" (::1) で稼動しており、
        また、ポート 5432 で TCP/IP 接続を受け付けていますか?
サーバに接続できませんでした: Connection refused (0x0000274D/10061)
        サーバはホスト "localhost" (127.0.0.1) で稼動しており、
        また、ポート 5432 で TCP/IP 接続を受け付けていますか?
```
#### 解決策
```
pg_ctl -D "C:\Program Files\PostgreSQL\13\data" restart
```
postgresqlの再起動。

## データベース作成
* 「database_sample」というDBを作成する例

```sql
DROP DATABASE IF EXISTS database_sample;
CREATE DATABASE database_sample;
```
## データベースの削除
DROP DATABASE テーブル名で削除できない場合
```
dropdb -U postgres database_sample
```

## データベース一覧確認
```
¥l
```
## データベースの変更
```
¥c DB名
```


## A5M2を使う場合
* DBごとに接続を追加する<br>→ DB「database_sample」と「database_sample2」を追加した例
* MySQLではデータベースを「mysql」にすれば、追加したDBが一覧でみられるが、postgresqlはDBごとに接続先を追加しなければならない。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/637823/34f90401-fa3c-f5d8-7023-5c0173da9623.png)

### 上手くいかない時
|症状|確認||
|----|----|----|
|CREATEしたDBがどこにも表示されない|DBの接続先追加で、DB名をデフォルトの「postgres」にしていないか|追加したDB名にしないと表示されない。|


## テーブル作成
* table_sampleを作成する例

```sql
DROP TABLE  IF EXISTS table_sample;
CREATE TABLE table_sample(
    id integer, 
    name varchar(10)
)
```
## 型の注意
|項目|説明|備考|
|----|----|----|
|bit型 →boolean型|true or false|bit型はあるが、defaultで0や1をいれるとエラーになる|
|INT(5) →INTEGER|INT(5)のように長さをしていするとエラーになる||

## COLLATION
* 各カラムに定義する。例）COLLATE "ja-x-icu"
* INTEGER型にはつけられない

```sql
CREATE TABLE housework_works (
  works_id SERIAL NOT NULL,
  flag_display BOOLEAN DEFAULT TRUE,
  category_id INTEGER NOT NULL,
  housework_name VARCHAR(50) COLLATE "ja-x-icu" NOT NULL,

  PRIMARY KEY (works_id),
  FOREIGN KEY(category_id) REFERENCES housework_category(category_id)
);
```
 　
 
## AUTO INCRIMENT
* AUTO INCRIMENTの記述はできない。代わりにSERIAL型を使う

## シリアルキー
テーブルにSERIAL型でPRIMALY KEYに設定されている物がある場合、データ型Sequenceとして変数名が存在している。
この変数名は「テーブル名_列名_seq」というパターンで自動的に決められる

### シリアルキーの確認
```
select*from cooking_ingredients_ingredients_id_seq;
```

### シリアルキーの初期化
```
select setval ('cooking_ingredients_ingredients_id_seq', 1, false);
```
### シリアルキーの値変更
下の例では現状の最大idを調べて、10に設定
次回は11から始まる。
```
select MAX(ingredients_id) from cooking_ingredients;
select setval ('cooking_ingredients_ingredients_id_seq', 10);
```

## dump方法
```:dumpファイル作成方法
【基本】
##pg_dump -U postgres --format=p --file=バックアップファイルパス 対象のDB

【入力例 (Windows・Linux共通)】
pg_dump -U postgres --format=p --file=C:\test\test.sql sampledb
>pasword入力


```
```:リストア方法
【基本】
##createdb -U postgres リストア先のDB名
##psql -h localhost -U ユーザ名 -f DB名.sql リストア先のDB名

【入力例 (Windows・Linux共通)】
createdb -U postgres sampledb2
>pasword入力
psql -h localhost -U postgres -d sampledb2 -f C:\test\test.sql
>pasword入力

```

|ポイント|説明|
|----|----|
|localhost|ipアドレスは「ipconfig」で調べられる||
|DB名.dump|フルパス指定のこと||
|リストア先のDB|予めDBは作成しておくこと。||

##springbootで接続する際 build.gradle依存関係
```build.gradle
dependencies {
	runtimeOnly 'org.postgresql:postgresql'
}
```
## Springbootで接続する際 application.properties
```application.properties
spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/sample_table
spring.datasource.username=postgres
spring.datasource.password=passwordhogehoge
spring.jpa.open-in-view=True
```

## 日付の足算
### 本日＋3日の例
```
CURRENT_DATE + INTERVAL '3DAY',
```

## 条件分岐文
次回予定ラベルを条件分岐させる

```
SELECT 
	A.history_id,
    C.category_name,
	B.housework_name,
    CURRENT_DATE + INTERVAL '3DAY',
    CASE WHEN A.lasttime_housework_date + B.interval_day = CURRENT_DATE THEN '本日'
         WHEN A.lasttime_housework_date + B.interval_day < CURRENT_DATE THEN '超過'
         WHEN A.lasttime_housework_date + B.interval_day > CURRENT_DATE THEN ''
    END AS nexttime_schedule_label
FROM
housework_history AS A 
```
## うまくいかないとき
・ "本日"のようにダブルクウォートで囲うとエラーになる

## SELECT句の指定フィールドに固定値を追加
```
SELECT
 id,
 name,
 'xx' as test 
FROM
 test_table 
```
代入 同じ値 

## INSERT INTO
```
INSERT INTO products (id, name, remark) VALUES
    (1, '一郎', '長男'),
    (2, '次郎', '次男'),
    (3, '三郎', '三男');
```


## SELECT結果をINSERTする
### 全カラムを挿入する方法
```
INSERT INTO test1 SELECT*FROM test2;
```

### カラムを指定する方法
```
INSERT INTO 宛先テーブル(col1,col2)
SELECT
    col1,
    col2
FROM
    test_table
WHERE
    id = 1
```
### うまくいかない時
|ポイント|説明|備考|
|----|----|----|
|SELECT"またはその近辺で構文エラー|VALUS(SELECT*||

## 正規表現

|ポイント|説明|例|例解説|
|----|----|----|----|
|~|正規表現一致|WHERE id ~ '^[0-9]'|先頭数字
|!~|正規表現不一致|WHERE id !~ '^[0-9]'|先頭数字以外