Help us understand the problem. What is going on with this article?

誰でも分かる!PostgreSQLでDB構築!

はじめに

ここ数年、歳をとったからなのか、体調の良し悪しが激しいので、体調変化の要因を考えてみようという考えから、まずは2019年を、生活データの取得の時間にしようと思っています。

PC操作が苦手な妻も入力ができるように(というか変な入力ができないように)excelではなく、RDBMSを使おうと考えた次第です。ちゃんとフロントページもこれから作りますが、まずはDB構築します。
今回は食事のDBを作成していきます。

DBの起動

$ psql -d データベース名;

postgresをまず起動します。

$ psql -d postgres;

※パスワードでエラーが出る場合は

$ psql -d postgres -U postgres

で管理者を指定して、パスワードpostgresで入ってください。

DBの作成と削除

DBの作成

# CREATE DATABASE データベース名;

kenkoという名のDBを作成します。

# CREATE DATABASE kenko;

DB一覧の確認

# \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+--------+----------+-------------+-------------+-------------------
 kenko     | hoge | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 postgres  | hoge | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 

権限の指定もできます。こちらを参照に。

削除する時は以下を入力します。

# DROP DATABASE データベース名;

テーブルの作成

テーブルは以下のように作成をすることができます。

# CREATE TABLE テーブル名
# (カラム1 データ型 制約,
# カラム1 データ型 制約,
# カラム1 データ型 制約,
# PRIMARY KEY(カラム1));

列ごとに以下のような制約をつけることができます。

制約 意味
PRIMARY KEY 主キーとして利用。
UNIQUE 同列内で重複する値を避ける。
REFERENCES 表名(列名) 外部キーとする。参照先は指定した表名(列名)
CHECK(条件) 条件に合った値のみの入力
NOT NULL NULLは入力できない。
DEFAULT 値 デフォルト値の指定

こちらを参考。

先ほど作ったkenkoというDBにkenko_recordを作成します。

# CREATE TABLE kenko_record
# (id char(4) not null,
# name text not null,
# food text not null,
# PRIMARY KEY(id));

以下のコマンドでビューを含むテーブル一覧

# \d

テーブルができてます。

           List of relations
 Schema |     Name     | Type  | Owner  
--------+--------------+-------+--------
 public | kenko_record | table | hiropy
(1 row)

以下のコマンドでビューを含まないテーブル一覧

# \dt

補足:データ型

文字型

データ型 最大長 概要
VARCHAR(n) 1GB 長さn文字の可変長文字列
CHAR(n) 1GB 長さn文字の固定長文字データ。指定した長さより短い値を挿入したときは、残りは空白で埋められる
TEXT 1GB 長さ指定なしの可変長文字列

数値型

データ型 最大長 概要
INTEGER 4バイト 整数型。数値の範囲と保存のサイズ、性能のバランスNUMBERが良い
SMALLINT 2バイト 範囲の狭い整数型
BIGINT 8バイト 範囲の広い整数型
NUMERIC 1000桁 正と負の固定小数点数。小数点より右側の桁数と全体の桁数を指定できる
REAL 4バイト 単精度の浮動小数点数
DOUBLE PRECISION 8バイト 倍精度の浮動小数点数

日付型

データ型 最大長 概要
DATE 4バイト 1日単位で日付のみを表すデータ
TIMESTAMP 8バイト 日付と時刻の両方を表すデータ

バイナリ型

データ型 最大長 概要
bytea 1GB 可変長のバイナリデータ
ラージオブジェクト 2GB データベース内に格納される

DBへの登録

insert文で、カラムに対応したデータ型でデータを登録することができます。

# INSERT INTO テーブル名
# (カラム1,カラム2,カラム3) VALUES 
# (カラム1の新データ, カラム2の新データ, カラム3の新データ);

複数のレコードを登録する時は以下のように、連続して記述することで、登録できます。

# INSERT INTO テーブル名
# (カラム1,カラム2,カラム3)VALUES
# (カラム1の新データ, カラム2の新データ, カラム3の新データ),
# (カラム1の新データ, カラム2の新データ, カラム3の新データ),
# (カラム1の新データ, カラム2の新データ, カラム3の新データ);

こちらを参考。

先ほど作ったkenko_recordテーブルに、データを登録します。

kenko=# INSERT INTO kenko_record(id,name,food) VALUES (1,'hoge','おにぎり');

一応select文で内容を確認しておきます。

kenko=# select * from kenko_record;

以下のように返ってきました。

  id  | name |   food   
------+------+----------
 1    | hoge | おにぎり
(1 row)

ついでにcsvファイルにメモしていたものを追加

読み込む前に、カラムが一致しているかファイル確認。※catでもlessでもワイルドカードはなんでもいいです。

$ more health.csv
<U+FEFF>id,name,food^M2,hoge2,サンドイッチ^M3,hoge3,焼肉^M4,hoge4,うどん

一応テーブル定義も見ておきます。

# \d kenko_record;

\COPYコマンドを使ってCSVをテーブルに投入します。
COPY 3 と返ってきたので大丈夫そうですね。

# \copy kenko_record from 'health.csv' csv header;
COPY 3

一応確認

# select * from kenko_record;
  id  | name  |     food     
------+-------+--------------
 1    | hoge  | おにぎり
 2    | hoge2 | サンドイッチ
 3    | hoge3 | 焼肉
 4    | hoge4 | うどん

CSV取り込みに関してはこちら が分かりやすいです。

テーブルの変更と削除

変更は少し長いです。

# ALTER TABLE テーブル名 rename column 変更したいカラム名 to 新しいカラム名;

削除は簡単です。これだけ。

# DROP TABLE テーブル名;

パラメタに関してはこちらを参考。

ビューの作成と削除

今回は、まだビューを作る必要はないですが、複雑なクエリをビューにしておくことで、参照がしやすくなったり、見せたくないカラムなどを隠すことができます。

# CREATE VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;

削除も簡単。

# DROP VIEW ビュー名

終わり

参考

[PostgreSQL]よく使うコマンドまとめ

hiroyuki_mrp
統計や機械学習、その他について投稿。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away