More than 5 years have passed since last update.

Postgres SQL例　自分用メモ crossfish21

PostgreSQL

Posted at 2018-05-30

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を削除

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Postgres SQL例 自分用メモ crossfish21

Postgres SQL例　自分用メモ crossfish21