Path
「コントロールパネル」
>「システムとセキュリティ」
>「システム」
>「システムの詳細設定」
>「システムのプロパテ画面の環境変数」
>「環境変数の画面のシステム環境変数(S)」
>「Path」
ログイン方法
$ 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を作成する例
DROP DATABASE IF EXISTS database_sample;
CREATE DATABASE database_sample;
データベースの削除
DROP DATABASE テーブル名で削除できない場合
dropdb -U postgres database_sample
データベース一覧確認
¥l
データベースの変更
¥c DB名
A5M2を使う場合
- DBごとに接続を追加する
→ DB「database_sample」と「database_sample2」を追加した例 - MySQLではデータベースを「mysql」にすれば、追加したDBが一覧でみられるが、postgresqlはDBごとに接続先を追加しなければならない。
上手くいかない時
| 症状 | 確認 | |
|---|---|---|
| CREATEしたDBがどこにも表示されない | DBの接続先追加で、DB名をデフォルトの「postgres」にしていないか | 追加したDB名にしないと表示されない。 |
テーブル作成
- table_sampleを作成する例
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型にはつけられない
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]' | 先頭数字以外 |