PostgreSQLの使い方メモ

current_db=> SET search_path=users,orders,catalogs;
SET

current_db=> show search_path;
       search_path       
-------------------------
 users, orders, catalogs

current_db=> create user {username};

current_db=> ALTER USER {username} WITH PASSWORD '{password}';

current_db=> \du
                                                                                                                          ロール一覧
    ロール名     |                                   属性                                   |                                                                          所属グルー
プ                                                                           
-----------------+--------------------------------------------------------------------------+------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
 postgres        | ロール作成可, DB作成可                                                  +| {rds_superuser}
                 | パスワードの有効期限 infinity                                            | 
 rds_ad          | ログインできません                                                       | {}
 rds_iam         | ログインできません                                                       | {}
 rds_password    | ログインできません                                                       | {}
 rds_replication | ログインできません                                                       | {}
 rds_superuser   | ログインできません                                                       | {pg_read_all_data,pg_write_all_data,pg_monitor,pg_signal_backend,pg_checkpoint,pg_u
se_reserved_connections,pg_create_subscription,rds_replication,rds_password}
 rdsadmin        | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス+| {}
                 | パスワードの有効期限 infinity                                            | 
 rdsproxyadmin   | パスワードの有効期限 infinity                                            | {}

current_db=> SELECT current_user;
 current_user 
--------------
 xxxxx

psql -h {hostname} -U {username} -d {dbname} --password

current_db=> \l
                                                データベース一覧
         名前         |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |       アクセス権限       
----------------------+----------+------------------+-------------+-------------------+--------------------------
 main                 | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | 
 postgres             | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | 
 rdsadmin             | rdsadmin | UTF8             | en_US.UTF-8 | en_US.UTF-8       | rdsadmin=CTc/rdsadmin
 template0            | rdsadmin | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/rdsadmin             +
                      |          |                  |             |                   | rdsadmin=CTc/rdsadmin
 template1            | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/postgres             +
                      |          |                  |             |                   | postgres=CTc/postgres

% psql -l

% createdb {dbname}

current_db=> GRANT CREATE ON DATABASE <dbname> TO <username>;
GRANT

ALTER DATABASE {dbname} OWNER TO {username};

current_db=> \dn
        スキーマ一覧
  名前  |      所有者       
--------+-------------------
 public | pg_database_owner

current_db=> select current_schema;
 current_schema 
----------------
 public

current_db=> create schema <schema name>;
CREATE SCHEMA

CREATE TYPE orders.meeting_type AS ENUM ('google', 'zoom');

CREATE TYPE orders.meeting_state AS ENUM ('init', 'done', 'canceled');

create table orders.meetings(
  id SERIAL NOT NULL,
  order_id integer,
  meeting_type meeting_type,
  created_at timestamp,
  updated_at timestamp, 
  state meeting_state,
  schedule timestamp, 
  link varchar(255), 
  PRIMARY KEY (id)
);

current_db=> \dT+ orders.meeting_type
                                               List of data types
 Schema |        Name         | Internal name | Size | Elements |    Owner     | Access privileges | Description 
--------+---------------------+---------------+------+----------+--------------+-------------------+-------------
 orders | orders.meeting_type | meeting_type  | 4    | google  +| xxx |                   | 
        |                     |               |      | zoom     |              |                   | 

current_db=> \dt
               リレーション一覧
 スキーマ |   名前   |    型    |    所有者    
----------+----------+----------+--------------
 orders   | meetings | テーブル | xxxxxx

current_db=> \d orders.meetings;
 id           | integer                     |          | not null      | nextval('orders.meetings_id_seq'::regclass)
 order_id     | integer                     |          |               | 

current_db=> ALTER TABLE "Meeting" ADD COLUMN eventLink text;

current_db=> ALTER TABLE "Meeting" RENAME COLUMN link TO videoLink;

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

\x