Postgres SQL例　自分用メモ  crossfish21

windows 限定コマンド

```
psql -U role21 -d postgres    ユーザrole21で、データベースpostgresにログイン。データベース名を指定しないと駄目

create database hsystem;    データベース作成はこれで  DB hsystemを作成
```
---
テーブル作成

```
単純なテーブル作成

create table schema01.validation_test (
  id integer not null
  , str1 character varying(255)
  , str2 character varying(255)
  , str3 character varying(255)
  , str4 text
  , str5 text
  , str6 text
  , integer1 integer
  , long1 bigint
  , double1 double precision
  , float1 real
  , double2 double precision
  , float2 real
  , bigdecimal1 numeric
  , biginteger1 numeric
  , date1 date
  , time1 time without time zone
  , timestamp1 timestamp(6) without time zone
  , primary key (id)
);




外部キー設定あり

create table sales_data(
id integer,
product_code varchar(255) NOT NULL,
sales integer NOT NULL,
date date NOT NULL,
staff_code varchar(255) NOT NULL,
note text,
CONSTRAINT sales_data_product_code_fkey FOREIGN KEY(product_code)
        REFERENCES staff_master(product_code) MATCH FULL
        ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT sales_data_staff_code_fkey FOREIGN KEY(staff_code)
        REFERENCES staff_master(staff_code) MATCH FULL
        ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);
```
---
DBの文字コードを確認する

```
SELECT character_set_name FROM information_schema.character_sets
```
---
重複したデータを排除する

```
hsystem=# select * from table02;
 id |  name  | age 
----+--------+-----
  1 | 日本語 |  25
  2 | 日本語 |  45
  3 | ABC    |  34
  4 | ABCDE  |  35
  5 | ABC    |  35

こういうtable02があるとして、



select distinct on(name) * from table02;　　nameカラムで重複を排除

 id |  name  | age 
----+--------+-----
  3 | ABC    |  34
  4 | ABCDE  |  35
  1 | 日本語 |  25

こうなる



select distinct on(age) * from table02;　　ageカラムで重複を排除

 id |  name  | age 
----+--------+-----
  1 | 日本語 |  25
  3 | ABC    |  34
  4 | ABCDE  |  35
  2 | 日本語 |  45

こうなる



※しかし、これだと重複しているデータのうち、どれが取得されたのかが分からない

select distinct on(name) * from table02 order by name ,id asc;    name,idの順で昇順ソートして取得

 id |  name  | age 
----+--------+-----
  3 | ABC    |  34
  4 | ABCDE  |  35
  1 | 日本語 |  25

name='ABC'なのはid=3,5のデータだが、idが小さいほうのデータを取得している。



select distinct on(name) * from table02 order by name ,id desc;　　今度は降順で

 id |  name  | age 
----+--------+-----
  5 | ABC    |  35
  4 | ABCDE  |  35
  2 | 日本語 |  45

id=5のデータを取得している。



※select distinct on(age) * from table02;　　これはエラーになる
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

どうも、distinct on(name)とした場合、order byの第一カラムには排除するものしか指定できないらしい
なので、二番目の指定をidにしている

これだと重複データの最初か最後のものしか取得できないが、例えば3番目のものを取得する方法とかがあるのかは不明

```
---
外部参照を設定してテーブルを作成する

```
create table working_data(
id integer,
staff_code varchar(255) NOT NULL,
date date NOT NULL,
attend boolean NOT NULL,
work_hour numeric NOT NULL,
extra_hour numeric NOT NULL,
note text,
CONSTRAINT working_data_staff_code_fkey FOREIGN KEY(staff_code)
        REFERENCES staff_master(staff_code) MATCH FULL
        ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);


working_data_staff_code_fkey  がキー名

staff_masterテーブルの、staff_codeカラムを、working_dataテーブルのstaff_codeカラムが参照する

ON UPDATE CASCADE ON DELETE CASCADE  で、削除時と更新時にデータが削除・変更される
```
---
コマンド一覧(使ったことのあるもの)

```
*Postgresにログイン("postgres"ユーザに切り替えるとログインになる)     su - postgres

*ログアウト   exit

*データベース作成   createdb DB名

*データベース削除   dropdb DB名

*エンコードを指定してデータベース作成   createdb -E UTF8 DB名

*データベースに接続する    psql DB名

*データベース切断    \q

*データベース一覧表示     psql -l

*テーブル一覧表示   \d

*テーブル情報表示  \d テーブル名

*接続可能なデータベース、所有者、エンコーディング一覧を表示    \list

*ロール情報一覧を表示   \du

*スキーマの一覧を表示   \dn

*スキーマの作成   create schema schema01    でスキーマschema01を作成する

*スキーマの削除   drop schema schema02    でスキーマschema02を削除

*カレントスキーマの変更   set search_path to schema01   でschema01をカレントスキーマに変更

*カレントスキーマの確認     select current_schema
 
*クライアント側のエンコード設定を確認  \encoding    ※encodingの後に引数を渡すと設定変更になってしまうので注意

*テーブルのデータをファイルへエクスポートする  \copy table02 to /home/postgres/tableData/copy1.txt;
(この場合はtable02テーブルのデータをcopy1.txtへ。SQL文を出力するのではない)

*ファイルからテーブルのデータをインポートする   \copy table02 from /home/postgres/tableData/copy2.txt;
(SQL文を実行するのではない)

*Postgresの再起動     systemctl restart postgresql-10.service   (10の部分はバージョン)

*Postgresの状態確認     systemctl status postgresql-10.service

*Postgresの起動     systemctl start postgresql-10.service

*Postgresの停止     systemctl stop postgresql-10.service

*自動起動するように設定     systemctl enable postgresql-10.service

*バージョン確認     psql --version

*ユーザの新しいパスワードを設定    sudo passwd user01  (user01はユーザ名)

*Role作成    create role role21 login createdb password 'blueAzarashi21';
「role21」というROLE名、「blueAzarashi21」というパスワードになる
login createdb　は、ログインとDB作成ができるRoleという意味

*Roleの削除    drop role role31;     role31というRoleを削除


```