PostgreSQLの基本的な使い方

前言
PostgreSQLは、オープンソースのオブジェクトリレーションシップ型データベース管理システム(ORDBMS)です.この文書は、初心者の参考と閲覧のためにPostgreSQL(以下pgと略称する)の基本的な操作を紹介することを目的としています.なお、以下のすべての操作はUbuntu 16.04で行われ、使用されるpgバージョンは9.6である.オペレーティング・システムとバージョンによっては、後述するリファレンス・ドキュメントの内容を参照してください.
インストール
Ubuntuはデフォルトでpgを統合しており、コマンドラインで直接インストールできます
以下のコマンドはpgのクライアントとサービス側をそれぞれインストールします.

$ sudo apt-get install postgresql-client
$ sudo apt-get install postgresql

インストールが完了したら、以下のコマンドでpgを起動します.

$ sudo service postgresql start

従って、ここのstartをstopとrestartに変更すれば、サービスの停止と再起動が可能となる
データベースの作成
最初のインストールが完了すると、pgのデフォルトではpostgresというデータベース・ユーザーとpostgresというデータベースが作成され、postgresというLinuxシステム・ユーザーも作成されます.実際、ここで作成したpostgresデータベースのユーザーはスーパー管理者として、私たちが後で作成したすべてのデータベースにアクセスし、新しいデータベースのユーザーの作成やユーザーパスワードの変更などの操作を行うことができます.
データベースを操作するには、通常コンソールにログインする必要があります.

$ sudo -u postgres psql

このコマンドはLinuxユーザーpostgresがスーパーユーザーpostgresとしてpostgresというデータベースにログインしていることを示しています.Ubuntuシステムなので、デフォルトではこの時点でパスワードを入力する必要はなく、pgのコンソールに直接アクセスします

postgres=#

通常はログイン後にpostgresユーザーにパスワードを設定します

postgres=# \password postgres

同じパスワードを2回入力すると修正に成功します
postgersユーザーは高い権限を持っているため、通常は直接この身分でログインするのではなく、現在のシステムユーザーと同じ名前のデータベースユーザーを作成するなど、新しいユーザーを作成します.

postgres=# CREATE USER dbuser WITH PASSWORD 'password';

新しいユーザーのデータベースを作成できます

postgres=# CREATE DATABASE exampledb OWNER dbuser;

次に、作成したユーザーを使用してコンソールにログインし、作成したデータベースに接続して一連の操作を行うことができます.

$ psql -U dbuser -d exampledb

共通のコンソールコマンド

\password               
\q                    
\h                    SQL     ，  \h select
\?                    psql    
\l                         
\c [database_name]         
\d                              
\d [table_name]               
\x                          
\du                       

よく使うSQL文

#     
CREATE TABLE table_name(name VARCHAR(20), birth DATE);

#     
INSERT INTO table_name(name, birth) VALUES('  ', '1994-08-23');

#     
SELECT * FROM table_name;

#     
UPDATE table_name set name = '  ' WHERE name = '  ';

#     
DELETE FROM table_name WHERE name = '  ' ;

#     
ALTER TABLE table_name ADD email VARCHAR(40);

#       
ALTER TABLE table_name ALTER COLUMN birth SET NOT NULL;

#        （          ）
ALTER TABLE table_name ALTER COLUMN email SET DEFAULT '[email protected]';

#        
ALTER TABLE table_name ALTER email DROP DEFAULT;

#      
ALTER TABLE table_name RENAME COLUMN birth TO birthday;

#     
ALTER TABLE table_name DROP COLUMN email;

#     
ALTER TABLE table_name RENAME TO backup_table;

#    
DROP TABLE IF EXISTS backup_table;

#    
\c postgres;
DROP DATABASE IF EXISTS hello;

バックアップとリカバリ

$ pg_dump --format=t -d db_name -U user_name -h 127.0.0.1 -O -W  > dump.sql
$ psql -h 127.0.0.1 -U user_name db_name < dump.sql

参考資料

PostgreSQL公式文書

チェン一峰のネットログ