Category: PostgreSQL

create table person (
	id int,
	first_name varchar(50),
	last_name varchar(50),
	gender varchar(7),
	birth_day date
);

testdb=# \d person
Table “public.person”
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+———
id | integer | | |
first_name | character varying(50) | | |
last_name | character varying(50) | | |
gender | character varying(7) | | |
birth_day | date | | |

### テーブルに制約をつける

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	birth_day date not null,
	email varchar(150)
);

bigserial 整数を自動決定
primary key キーとして扱う
not null 空白を許可しない

### テーブルにデータを挿入

insert into person (
	first_name,
	last_name,
	gender,
	birth_day
) values ('Anne', 'Smith', 'Femail', DATE '1988-01-09');

insert into person (
	first_name,
	last_name,
	gender,
	birth_day,
	email
) values ('Jake', 'Jone', 'Male', DATE '1990-01-10', 'jake@gmail.com');

testdb=# select * from person;
id | first_name | last_name | gender | birth_day | email
—-+————+———–+——–+————+—————-
1 | Anne | Smith | Femail | 1988-01-09 |
2 | Jake | Jone | Male | 1990-01-10 | jake@gmail.com
(2 rows)

データの自動生成
https://mockaroo.com/

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null
);

$ \i /home/vagrant/dev/app/person.sql
$ select * from person;

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null,
	car_id BIGINT REFERENCES car (id),
	unique(car_id)
);

create table car (
	id BIGSERIAL not null primary key,
	make varchar(50) not null,
	model varchar(50) not null,
	price varchar(50) not null
);

BIGINT REFERENCESで関連づけるのか。なるほど、勉強になるね。

テーブルを作成
$ CREATE TABLE test(image bytea);

bytea型はバイナリデータを保存。1Gまで可能
https://www.postgresql.jp/document/9.6/html/datatype-binary.html

画像データの登録

$dsn = "pgsql:dbname=testdb host=localhost port=5432";
$user = "user1";
$password = "password";

try {
	$dbh = new PDO($dsn, $user, $password);
	print('connection success<br>');

	$stmt = $dbh->prepare("insert into test (image) values (:image)");

	$fp = fopen("images.jpeg", "rb");
	$stmt->bindParam(':image', $fp, PDO::PARAM_LOB);
	$stmt->execute();

} catch(PDOException $e){
	print("Error:".$e->getMessage());
	die();
}

画像の表示

$dsn = "pgsql:dbname=testdb host=localhost port=5432";
$user = "user1";
$password = "password";

try {
	$dbh = new PDO($dsn, $user, $password);

	$stmt = $dbh->prepare("select image from test");
	$stmt->execute();
	$stmt->bindColumn("image", $image, PDO::PARAM_LOB);
	$stmt->fetch();
	header("Content-Type: image/jpeg");
	fpassthru($image);

	unset($db);
} catch(PDOException $e){
	print("Error:".$e->getMessage());
	die();
}

なるほど、DBに画像保存も可能なのね。
データサイズが気になるが、軽量な画像であれば良さそう

PostgreSQLは削除フラグがついて見えなくなっているだけのため、削除データを定期的にきれいにする必要があり、この処理をVACUUMという。

$ psql –version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)
$ sudo -u postgres psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type “help” for help.

postgres=# \l
postgres=# \c testdb;
testdb=# \d department;
testdb=# select * from department;

testdb=# delete from department where department_code = ‘c’;
testdb=# vacuum verbose department;
INFO: vacuuming “public.department”
INFO: scanned index “pk_department” to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: table “department”: removed 1 dead item identifiers in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index “pk_department” now contains 5 row versions in 2 pages
DETAIL: 1 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table “department”: found 1 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 747
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

vacuum ${table_name} でテーブルをvacuumできる。
なるほど。

autovacuumの設定はpostgres.confで設定されている。

PostgreSQLはデータを消しても実際には消えておらず、削除フラグがついていて見えなくなっているだけの状態。この削除データは定期的に綺麗にする必要があり、vaccumと言う。

$ psql –version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)

ddl.sql

CREATE TABLE item (
	ItemID int,
	ItemName character varying(20)
);

insert into item values(0001, 'ゼリー'), (0002, 'プリン'), (0003, 'ヨーグルト');

### sqlに実行
$ psql -h HostName -p PortNumber -U UserName -d DatabaseName -f ddl.sql

$ sudo -u postgres psql -d testdb -f ddl.sql
CREATE TABLE
INSERT 0 3

testdb=# select * from item;
itemid | itemname
——–+————
1 | ゼリー
2 | プリン
3 | ヨーグルト
(3 rows)

なるほど、勉強になる。

スキーマ(Schema)とは、1つのデータベースの中に複数設定することができる名前空間

$ psql –version
$ sudo -u postgres psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+———-+———-+———+———+———————–
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
(4 rows)
# \c testdb
You are now connected to database “testdb” as user “postgres”.

postgres=# \dn
List of schemas
Name | Owner
——–+———-
public | postgres
(1 row)

postgres=# create schema bank1;
CREATE SCHEMA
postgres=# create schema bank2;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
——–+———-
bank1 | postgres
bank2 | postgres
public | postgres
(3 rows)

### 現在のスキーマ
postgres=# select current_schema();
current_schema
—————-
public
(1 row)

### スキーマの変更
postgres=# set search_path = bank1;
SET
postgres=# select current_schema();
current_schema
—————-
bank1
(1 row)

### スキーマを指定してテーブル作成
create table bank1.data (
id integer,
name varchar(256)
);

スキーマのテーブルができている
# \dt bank1.*
List of relations
Schema | Name | Type | Owner
——–+——+——-+———-
bank1 | data | table | postgres
(1 row)

PHPから接続する場合はスキーマ指定が必要
なるほど、スキーマの概念を理解した

phpPgAdminは、PostgreSQLサーバをwebブラウザ上で管理できるツール
コマンドなして操作できる

Github: https://github.com/phppgadmin/phppgadmin
Source: https://sourceforge.net/projects/phppgadmin/

### UbuntuへのphpPgAdminインストール
$ wget http://downloads.sourceforge.net/project/phppgadmin/phpPgAdmin%20%5Bstable%5D/phpPgAdmin-5.1/phpPgAdmin-5.1.tar.gz
$ tar zxvf phpPgAdmin-5.1.tar.gz
$ rm phpPgAdmin-5.1.tar.gz

### 設定
conf/config.inc.php

	$conf['default_lang'] = 'japanese';

$ php -S 192.168.56.10:8000

なるほど、なんとなくわかった。

### 文字から数値、数値から文字へ変換
cast(A as B) で型変換を行う

select cast(‘0123’ as integer);
select cast(‘0123.45’ as numeric);
select cast(‘1234567.345’ as numeric(10,2));
select cast(‘1234567.345’ as numeric(10,3));
select cast(‘1234567.345’ as numeric(10,5));
select cast(‘-123456.789’ as numeric);
select cast(‘-123456.789’ as numeric(8,2));
select cast(‘abc’ as integer);

### 文字の切り取り
select * from left(‘abcedfghijk’, 3);
select * from left(‘abcedfghijk’, 5);
select * from right(‘abcedfghijk’, 3);
select * from right(‘abcedfghijk’, 5);
select * from substring(‘abcedfghijk’, 3,4);
select * from substring(‘abcedfghijk’, 5,3);
select * from substring(‘abcedfghijk’, 3,length(‘abcedfghijk’));
select * from substring(‘abcedfghijk’, 3,length(‘abcedfghijk’)-3);

### 前0埋め、後0埋め
– 前
select lpad(‘12345’, 8, ‘0’);
– 後
select rpad(‘12345’, 8, ‘0’);

### 数値を文字に変換
select * from to_char(12345.67, ‘FM999,999.999’);

### 日付、週、月
日付 + cast( ‘5 days’ as INTERVAL ) — 5日加える
日付 + cast( ‘5 weeks’ as INTERVAL ) — 5週加える
日付 + cast( ‘5 months’ as INTERVAL ) — 5か月加える

–年月日時分の書式
select to_char(now(),’YYYY/MM/DD HH24:MI:SS’); –2020/04/01 22:34:56

–年月日の書式
select to_char(now(),’YYYYMMDD’); –20200401
select to_char(now(),’YYYY/MM/DD’); –2020/04/01
select to_char(now(),’YYYY.MM.DD’); –2020.04.01
select to_char(now(),’YYYY年MM月DD日’); –2020年04月01日

–月日の書式
select to_char(now(),’MMDD’); –0401
select to_char(now(),’MM/DD’); –04/01
select to_char(now(),’MM.DD’); –04.01
select to_char(now(),’MM月DD日’); –04月01日

–時刻の書式
select to_char(now(), ‘HH:MI:SS’); –10:34:56（12時間表記）
select to_char(now(), ‘HH24:MI:SS’); –22:34:56（24時間表記）
select to_char(now(), ‘HH24時MI分SS秒’); –22時34分56秒

### 配列の要素数を取得
array_length(配列や配列の変数名, 1)

ニャルほどー
さて、Dockerやるかー

create user user1;
create user user1 with password ‘pass’;
create user user1 with password ‘pass’ valid until ‘Jan 1 2022’;
create user user1 with password ‘pass’ superuser;
create user user1 with password ‘pass’ createdb createrole;

オプション
h: サーバホスト
p: ポート
U: user
d: データベース作成権限
D: データベース作成できない権限
l: ログイン権限
L: ログイン不可権限
r: ユーザー作成権限
R: ユーザー作成不可権限
s: スーパーユーザー権限
S: スーパーユーザー不可権限
P: パスワード設定

なるほど、少しPostgreSQLにも慣れてきた。というより、CRUDまでいくと、やった感はある。

### character型
– character(整数)と書き、その桁数分、必ず文字で埋まる列
– 文字が定義した長さに満たない場合、半角スペースで埋まる
– 長さがオーバーしている場合はそこで自動的に切られる

create table ${table} (${列名} character(桁数));
alter table ${table} add column ${列名} character(10);
select cast(‘Hello’ as character(10));

### character varying(整数)、character varying
– character varying(整数)とvarchar(整数)は同じ
– スペース埋めがされない、登録した文字の長さになる
– 整数を省略したcharacter varingは1GBまで入る文字列

create table ${table} (${列名} character varing(桁数));
alter table ${table} add column ${列名} character varing(10);
select cast(‘Hello’ as character varing(10));

### text型
character varyingと同じもの

### integer
整数値を保存できる型
最小値は-2,147,483,648、最大値は2,147,483,647
select cast(2.4 as integer);
select cast(2.5 as integer);

### smallint, bigint
smallint, bigintはintegerと同じく整数値を保存できる型
smallintは、最小値は-32,768、最大値は32,767
bigintは、最小値は-9,223,372,036,854,775,808、最大値は9,223,372,036,854,775,807

### numeric, decimal
numericとdecimalは小数が可能な数値型。どちらも同じ意味

### boolean
true, false, nullの3つの値

なるほど、大分OKだわ