PostgreSQLでDBとテーブルの作成と削除を行う

Sinatraで作成したメモアプリのDB化にあたりまずはPostgreSQLでのDBとテーブルの作成と削除する方法について調べた。

結論

psql でログインした状態で下記コマンドでDB/テーブルの作成/削除が行える。

  • DB作成: CREATE DATABASE DB名;
  • DB削除: DROP DATABASE DB名;
  • テーブル作成: CREATE TABLE テーブル名 (カラム名 データ型 制約);
  • テーブル削除: DROP TABLE テーブル名;

PostgreSQLの概念図

DBとテーブルの関係性が分からなくなったので再確認。 とてもわかり易いPostgreSQLの概念図を見つけたので画像引用させて頂きました。

PostgreSQLの概念図(データベースクラスタ/データベース/スキーマ/テーブル) - Qiita

  • クラスタ: PC、サーバ内のデータの保存領域。この中にデータベース、ロールを作成する。
  • データベース: 区分けされたデータの保存領域。アプリケーションで使用するデータはここから取得する。
  • テーブル: 保存する値を管理する表のようなもの。イメージはスプレッドシート

DBの作成と削除

例) test DBの作成と削除を行う。

psql でスーパーユーザkarlley にログインした状態でコマンドを実行する。

# スーパユーザkarlley でデフォルトDBのkarlley にログイン
$ psql -U karlley -d karlley
psql (14.4)
Type "help" for help.

# 作成
karlley=# CREATE DATABASE test;
CREATE DATABASE
karlley=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 test      | karlley | UTF8     | C       | C     |

# 削除
karlley=# DROP DATABASE test;
DROP DATABASE
karlley=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
(4 rows)

DB作成時にオーナーを指定したい場合はCREATE DATABASE DB名 owner=オーナー名 でオーナーを指定できる。

テーブルの作成と削除

例) test DB内に下記の構成のmemos テーブルの作成と削除を行う。

カラム名 データ型 制約
id CHAR(8) PRIMARY KEY
title TEXT NOT NULL
content TEXT なし

psql でスーパーユーザkarlley にログインした状態でコマンドを実行する。

# スーパユーザkarlley で作成したtest DBにログイン
$ psql (14.4)
Type "help" for help.

# テーブル作成
test=# CREATE TABLE memos(id CHAR(8) NOT NULL, titile TEXT NOT NULL, content TEXT, PRIMARY KEY (id));
CREATE TABLE

# テーブル作成を確認
test=# \d
        List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+---------
 public | memos | table | karlley
(1 row)

# カラム作成を確認
test=# \d memos
 id      | character(8) |           | not null |
 titile  | text         |           | not null |
 content | text         |           |          |

# テーブル削除
test=# DROP TABLE memos;
DROP TABLE

# テーブル削除を確認
test=# \d memos
Did not find any relation named "memos".

感想

SQLやデータ型について殆ど覚えていなかった。 特にデータ型については未だにどのデータ型を指定すれば良いか全然分からん状態なのできちんと調べる必要がありそうです。

参照

[macOS High Sierra][Homebrew] PostgreSQL のインストールからDB作成まで - Qiita

テーブルの作成(CREATE TABLE) | PostgreSQLではじめるDB入門

テーブルの削除(DROP TABLE) | PostgreSQLではじめるDB入門

SQLの基礎

FBCのプラクティスであるSinatraで作成したメモアプリをDB化するにあたってSQLの基礎を復習しました。

SQLとは

  • Structured Query Language
  • リレーショナルデータベース(RDBMS)を操作する為の言語
  • 標準SQL: ISO(国際標準化機構)で定められた標準規格を満たしたSQL
  • SQLには方言のようなものが存在する(標準SQL以外)

SQL の機能の分類

SQLは次の3つに分類されます。

  • DDL
    • Data Definition Language
    • データ定義言語
    • 操作: テーブルを作成、削除
    • コマンド: CREATEDROPALTER
  • DML
    • Data Manipulation Language
    • データ操作言語
    • 操作: データの挿入、更新、削除
    • コマンド: SELECTINSERTUPDATEDELETE
  • DCL
    • Data Control Language
    • データ制御言語
    • 操作: トランザクション、権限関係
    • コマンド: COMMITROLLBACKGRANTREMOVE

SQLの記述ルール

  • 最後に; を付ける
  • 大文字/小文字の区別無し
  • キーワード間にスペース、タブ、改行が使える
  • 全角スペースは使えない
  • 定数は'' で囲む

定数とはSQLに直に書く日付や文字列です。

まとめ

記述ルールですら覚えていなかった...実際に手を動かして覚えていこう。

参照

SQL概要 | PostgreSQLではじめるDB入門

SQLの基本的な記述ルール | PostgreSQLではじめるDB入門

PostgreSQLでDB作成する2つの方法

SinatraでメモアプリをDB化するにあたってPostgreSQLで新規でDB作成することになったのでメモ。 PostgreSQLのインストールはHomebrew経由で行った。

結論

PostgreSQLでDBを作成する方法は2つある。

  • 方法1: psql にログインしてCREATE DATABASE を実行
  • 方法2: ターミナルでcreatedb を実行

方法1: psql にログインしてCREATE DATABASE を実行

例) ユーザkarlleytestdb DBを作成する。

  1. psql を起動する為にpostgres DBに接続(接続するDBは他でもok)。
  2. -U でユーザ指定。
  3. -d で接続するDBを指定。
  4. CREATE DATABASEtestdb DB作成。
  5. testdb DBが作成されているか確認。
  6. 作成したtestdb DBに\c コマンドで切り替える。
# postgres DBに接続
$ psql -U karlley -d postgres
postgres=#

# testdb DB作成
postgres=# CREATE DATABASE testdb;
CREATE DATABASE

# testdb DBの作成を確認
postgres=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 testdb    | karlley | UTF8     | C       | C     |
(4 rows)

# 作成したtestdb DBへの切り替え
postgres=# \c testdb
You are now connected to database "testdb" as user "karlley".
testdb=#

方法2: ターミナルでcreatedb を実行

psql にログインせずにcreatedb コマンドでターミナル上でもDBは作成できます。

$ createdb 名

PostgreSQL: Documentation: 14: createdb

例) ユーザkarlley でtestdb DBを作成する。

# testdb DB作成
$ createdb testdb

# testdb の作成を確認
$ psql -l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 testdb    | karlley | UTF8     | C       | C     |
(4 rows)

感想

やはり殆ど覚えていなかったので良い復習になった。 過去にちゃんとメモしておいたのでサクッと復習できて良かった。

参照

PostgreSQL: Documentation: 14: createdb

PostgreSQLの基本操作

Sinatraで作成したメモアプリのDB化に伴いPostgreSQLを使うことになったので最低限の基本操作を復習しました。

基本操作

  • DB一覧表示
  • DBへの接続
  • psql の抜け方
  • バックスラッシュコマンド

DB一覧表示

psql -l でデータベース一覧を表示できます。

$ psql -l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 testdb    | karlley | UTF8     | C       | C     |
(5 rows)

DBへの接続

  • psql DB名 でデフォルトに設定されているユーザでDBに接続できます。
  • psql -U ユーザ名 DB名 で指定したユーザでDBに接続できます。

今回はHomebrew経由でmacにPostgreSQLをインストールしたのでpostgres ユーザは存在せず、存在するユーザはkarlley のみです。 DBへ接続するユーザの権限によって下記のようにプロンプトが変化します。

  • スーパーユーザ: =#
  • 一般ユーザ: =>

例) スーパーユーザ karlley でデータベースtemplate1 に接続

$ psql -U karlley template1
psql (14.4)
Type "help" for help.

template1=#

psql の抜け方

以下のどちらかのコマンドでpsql を抜けれます。

  • ctl + D
  • \q

バックスラッシュコマンド

バックスラッシュコマンドとはpsqlPostgreSQLに接続した状態で使えるコマンドです。

  • psql 終了: \q
  • バックスラッシュコマンドのヘルプ: \?
  • SQL のヘルプ: \h
  • DB 一覧: \l
  • ユーザ一覧: \du
  • DB切替: \c DB名

感想

うろ覚えだったので良い復習になった。 習慣的に使って体で覚えていきたい。

psqlコマンド実行時にFATAL: database "db_name" does not exist が出た時の対処

前回の記事でHomebrew経由でPostgreSQLmacにインストールしました。 Postgresqlインストール直後にpsql コマンドで以下のエラーが発生したので対処についてのメモです。

$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "karlley" does not exist

結論

psql コマンドを引数なしで実行するとユーザー名と同じ名前のデータベースに接続しようとします。 FATAL: database "karlley" does not exist と表示された原因はユーザ名と同じデータベースが存在しないのでエラーが発生したようです。 エラーの対処は次の2つです。

  • psql コマンドに引数で存在するデータベース名を指定する。
  • ユーザ名と同名のデータベースを作成する(psql コマンドに引数無しでユーザ名と同名のデータベースに接続する)。

ちなみに、引数無しでpsql コマンドを実行した際のデフォルトのデータベース、ユーザ名等は環境変数を設定することで指定可能です。

例) ユーザ名karlley でエラーが発生したのでユーザ名と同名のデータベースkarlley を作成する。

$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "karlley" does not exist

# ユーザ名と同名のデータベースを作成
$ createdb karlley

# ユーザ名と同名のデータベースに接続するのでエラーが発生しない
$ psql
psql (14.4)
Type "help" for help.

# データベースの作成を確認(一番上が作成したデータベース)
karlley=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 testdb    | karlley | UTF8     | C       | C     |
(5 rows)

感想

存在しないDBに接続しようとしたという単純なことが原因でしたがデフォルトの仕様をちゃんと理解する大事さを学べて良かったです。 入力している文字列がデータベース名なのかユーザ名なのかちゃんと理解してコマンド入力していないのも問題でした。 次はDBを作成してデータの保存と取得をやってみよう。

参照

PostgreSQL: Documentation: 14: psql

PostgreSQLの環境構築ができないFATAL: database "t" does not exist