• ..

JSON Schema

    データーベースの作成と削除

    データベースの作成

    create database データベース名を使います。

    postgres=# create database foo;
    CREATE DATABASE
    
    postgres=# \l
                                     List of databases
       Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
    -----------+----------+----------+------------+------------+-----------------------
     foo       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
    (4 rows)

    またデータベースを一覧するには\lコマンドを使います。fooデータベースができていることが確認できました。

    データベースの削除

    drop database データーベース名を使います。

    postgres=# drop database foo;
    DROP DATABASE
    
    postgres=# \l
                                     List of databases
       Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
    -----------+----------+----------+------------+------------+-----------------------
     postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
    (3 rows)

    消せました。

    テーブルの作成と削除

    テーブルを作成する

    create table テーブル名を実行します。

    create table foo (
      id serial primary key not null
    );

    ちゃんと作成されたか\dtで確認します。

    postgres=# \dt
             List of relations
     Schema | Name | Type  |  Owner
    --------+-------+-------+----------
     public | foo  | table | postgres
    (1 row)

    また\d fooのようにするとテーブルの構造を見ることができます。

    postgres=# \d foo
                                Table "public.foo"
     Column |  Type   | Collation | Nullable |             Default
    --------+---------+-----------+----------+---------------------------------
     id     | integer |           | not null | nextval('foo_id_seq'::regclass)
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (id)

    テーブルを削除する

    drop table テーブル名を実行します。上記でfooテーブルを作ったのでdrop table foo;ですね。

    postgres=# drop table foo;
    DROP TABLE

    ちなみにここでもう一度fooテーブルを削除しようとすると、以下のようにエラーになります。

    postgres=# drop table foo;
    ERROR:  table "foo" does not exist

    これはtableの後にif exists(存在する時)を付けることで回避できます。

    postgres=# drop table if exists foo;
    NOTICE:  table "foo" does not exist, skipping
    DROP TABLE

    同じようにcreate tableも既にある場合はエラーになってしまいますが、こちらの場合はif not exists(存在しない時)を付けてあげると回避できます。

    create table if not exists foo (id text);
    NOTICE:  relation "foo" already exists, skipping
    CREATE TABLE

    現在の接続状況を調べる

    \conninfoコマンドを使います。以下はその例です。

    postgres=# \conninfo
    You are connected to database "postgres" as user "postgres" on host "0.0.0.0" at port "4288".

    上記は、以下のdockerで建てた Postgresql 環境です。

    docker run --name db --rm -p 4288:5432 -e POSTGRES_PASSWORD=postgres postgres:latest 

    選択中のテーブルスキーマを変更する

    ちょっと準備として、foopublicという2つのスキーマがあり、今はpublicを選択したいる状態というにします。

    postgres=# create schema foo;
    CREATE SCHEMA
    
    postgres-# \dn
      List of schemas
      Name  |  Owner
    --------+----------
     foo    | postgres
     public | postgres
    (2 rows)
    
    postgres=# select current_schema;
     current_schema
    ----------------
     public
    (1 row)

    ここでfooを選択するにはset search_path to <スキーマ名>を実行するだけです。(to=でもいけます)

    postgres=# set search_path = foo;
    SET
    
    postgres=# select current_schema;
     current_schema
    ----------------
     foo
    (1 row)

    挿入 (insert) 、更新 (update) 、削除 (delete)

    挿入 (insert)

    INSERT INTO文を使い以下のような形でデータを挿入できます。括弧の中のカラム名の並びとそのデータとなる値は同じ位置に置く必要があります。

    INSERT INTO table_name (column1, column2, ...) VALUEAS (value1, value2, ...);

    values以下は,でつなげて書くことにより一度に複数レコードを挿入することができます。

    INSERT INTO table_name (column1, column2, ...) VALUEAS
      (value1, value2, ...),
      (value1, value2, ...),
      (value1, value2, ...);

    更新 (update)

    UPDATE文を使い以下のような形でデータを挿入できます。

    UPDATE table_name set foo = 'updated foo';

    上記ではすべてのレコードのfooカラムの値をupdated fooに更新してしまいます。更新データを絞り込むにはwhereを使うことができます。

    UPDATE table_name SET foo = 'updated foo' WHERE id = 1;

    これでid1のものだけを更新することができます。

    また複数カラムを一度に更新したい場合はset以下の値の代入部分を,で繋げることで可能です。

    UPDATE table_name
      SET foo = 'updated foo', bar = 'updated bar'
      WHERE id = 1;

    削除 (delete)

    DELETE FROM文を使い以下のような形でデータを削除できます。

    DELETE FROM table_name;

    これもUPDATEのようにそのままだと、そのテーブルのレコードすべてが対象になってしまうのでWHEREで絞ることができます。

    DELETE FROM table_name WHERE id = 1;

    これでid1のものだけを削除することができます。

    insert 時に作ったレコードを返す

    これはinsertでレコードを追加した後にselect ... limit 1をせずに一度に終わらせるものです。

    確認用の準備

    以下のようにfooテーブルを作っておきます。

    postgres=# create table foo (id serial primary key not null, text text not null);
    CREATE TABLE

    実際に確認

    まず、普通にinsertした時はこうなります。「1件追加しました」という情報だけ返っています。

    postgres=# insert into foo (text) values ('hoge');
    INSERT 0 1

    returning を使う

    ですが、後ろにreturning *を追加すると今入れたレコードが返るようになります。

    postgres=# insert into foo (text) values ('fuga') returning *;
     id | text
    ----+------
      2 | fuga
    (1 row)
    
    INSERT 0 1

    *select ...のカラム指定と同じように指定できます。したがって、カラム名を指定すればそれだけを返すようにできます。

    postgres=# insert into foo (text) values ('piyo') returning text;
     text
    ------
     piyo
    (1 row)
    
    INSERT 0 1

    中からシェルコマンドを実行する

    \!の後ろにシェルコマンドを書いて実行します。こんな感じです。

    \! cat foo.txt

    vimなどもちゃんとエディターが開いてくれます。

    ただ.sqlファイルの実行は\iという便利なコマンドがあるのでそちらを使ったほうがいいと思います。

    コマンドラインから変数を使う SQL を流す

    -v, --set, --variableオプションを使います。その後key=valueの形で設定していきます。
    設定した値は.sqlファイルの中で:key:を頭に付けて参照できます。

    foo というスキーマに hoge テーブルを作る

    fooは変数で渡す形で書くとこのような SQL になります。

    create table :schema.hoge ();
    -- CREATE TABLE

    この内容が書かれている.sqlファイルは、schemaという変数を定義した上で実行されることを期待しています。そのように変数を実行し、かつ SQL を流すコマンドは以下です。

    psql -v schema=foo < create-hoge.sql
    # CREATE TABLE

    ちゃんとできていればいい感じです。

    psql -c '\dt foo.*'
    #         List of relations
    #  Schema | Name | Type  |  Owner
    # --------+------+-------+----------
    #  foo    | hoge | table | postgres
    # (1 row)

    コマンドラインで SQL を書いて直接実行する

    -c, --commandオプションを使います。あとに SQL やpsql内部で使えるコマンドを書きます。

    psql -c 'select 1 as one'
    #  one
    # -----
    #    1
    # (1 row)

    カラムを縦並びで表示

    psql内部では\xコマンドでカラムの縦・横並びを切り替えできました。これはコマンドラインでは-x, --expandedオプションで切り替えられます。

    psql -x -c 'select 1 as one'
    # -[ RECORD 1 ]
    # one | 1

    ダンプとリストア

    ダンプ

    ダンプにはそれ用のコマンドpg_dumpがありこれでダンプファイルの作成ができます。これの接続時のオプションなどはpsqlと同じです。

    以下でoutput.sqlにダンプできます。

    PGPASSWORD=password pg_dump \
      -h host \
      -p port \
      -U username \
      foo_database > output.sql

    リストア

    ダンプされたファイルはそのままpsqlで扱える形なので、シェルのリダイレクションで取り込むことができます。databaseがない場合は先に作っておきます。

    PGPASSWORD=password pg_dump \
      -h host \
      -p port \
      -U username \
      -c 'create database foo_database'
    
    PGPASSWORD=password pg_dump \
      -h host \
      -p port \
      -U username \
      foo_database < output.sql

    クエリを一定時間毎に実行

    \watchコマンドを使います。SQL文; \watch 2のように実行すると SQL 文を2秒毎に実行してくれます。

    watchコマンドを確認する

    ターミナルを2つ開いて以下のようにして見てみます。テーブルはfooという名前のものを使います。

    1. テーブルに3秒毎にinsertする
    2. テーブルの最新1件を2秒毎に表示する

    準備

    とりあえず以下でfooを作ります。

    create table foo (
      id serial primary key not null
    );

    1番目の方では以下でinsertを走らせます。\watch 3で3秒毎走らせるということになります。
    ちなみにdefault valuesはすべてのカラムでデフォルト値を使うということになるのでvaluesを省略できます、

    insert into foo default values; \watch 3

    2番目の方では以下でselectを走らせます。

    select * from foo order by id desc limit 1; \watch 2

    これで3秒毎に新しいidのデータが増えていくので、その度selectで取得するデータも変わるはずです。

    実行結果

    右が1です。左側には1,2回毎に新しいレコードが表示されています。(\xで縦表示にしています)

    色々と結果を見ながらいじりたい時に便利だと思います。

    変数を使う

    \set name valueを使います。例えば以下でtablenamepostsという値を設定しています。

    postgres=# \set tablename posts

    設定できたかどうかは\setと実行します。恐らく最後に出てくるはずです。

    postgres=# \set
    ...
    tablename = 'posts'

    何か-を挟んだもの(table-nameみたいな)変数名はうまく SQL の中で使えなかったので、避けたほうがいいかもしれないです。

    では、使ってみます。

    postgres=# create table :tablename (content text);
    CREATE TABLE
    
    postgres=# insert into :tablename values ('aaa');
    INSERT 0 1
    
    postgres=# select * from :tablename;
     content
    ---------
     aaa
    (1 row)

    「もう必要ないな」となったら\unset nameで削除できます。

    postgres-# \unset tablename
    
    postgres=# \set
    ...

    好きな値を表示

    DO $$ ... $$で SQL の塊を作ることができます。
    RAISEはトランザクション時に予期せぬ事が起こったときに使うものみたいですが、これを使ってログみたいな情報を流せるみたいですね。

    DO $$
    BEGIN
      RAISE NOTICE 'aaa % %', 'b', 'c';
    END
    $$;
    
    NOTICE:  aaa b c
    DO

    環境変数

    PGHOST

    -hオプションと同様に接続先のホストを指定できます。

    psql -h ...
    PGHOST=... psql

    PGPORT

    -pオプションと同様に接続先のホストを指定できます。

    psql -p ...
    PGPORT=... psql

    PGUSER

    -Uオプションと同様に接続先のホストを指定できます。

    psql -U ...
    PGUSER=... psql

    PGPASSWORD

    psqlコマンドを実行した時にパスワードを聞かれずにログインすることができます。

    PGPASSWORD=... psql

    fish shell で環境変数を設定

    以下のような Docker で Postgresql を起動するとして、

    docker run --rm -p 54321:5432 postgres

    .config/fish/config.fishに以下のように書くとpsqlだけで接続できるようになります。

    set -gx PGHOST 0.0.0.0
    set -gx PGUSER postgres
    set -gx PGPORT 54321
    set -gx PGPASSWORD ''

    クエリの実行にどれだけかかっているか調べる

    \timingコマンドを先に実行します。

    \timing
    -- Timing is on.

    あとは好きなクエリを実行するとTime: 1 msのようにかかった時間も表示しれくれるようになります。

    alter table hoge add id serial;
    -- ALTER TABLE
    -- Time: 9.109 ms