PostgreSQL DDL文でtable,view等を作成する

PostgreSQLのtable,view等を作成するDDL文のサンプルです。
DDLとは、Data Definition Languageの略でデータを定義する言語です。

※シノニムはありません。

データベースの一覧

\l

psqlのコマンドでは、円マークとエルでデータベースの一覧が表示されます。

SELECT * FROM pg_database;

上記SQLでも取得できます。

データベースを作成

CREATE DATABASE test1;

CREATE DATABASEの後にデータベース名を指定します。

データベースを削除

DROP DATABASE test1;

DROP DATABASEの後にデータベース名を指定します。

テーブルの一覧

¥dt

psqlのコマンドでは、円マークとdtでテーブルの一覧が表示されます。

select * from pg_tables
where schemaname not in('pg_catalog','information_schema')

上記SQLでも取得できます。

テーブルを作成

CREATE TABLE employee(
	id INT,
	name VARCHAR(20) not null,
	romaji VARCHAR(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

2行目は、整数型のINT型を指定しています。
3行目は、not null制約をつけています。
7行目は、プライマリーキーを指定しています。2行目のidを指しています。

以下は、上記テーブルにデータを登録するinsert文です。

INSERT INTO employee VALUES (1,'鈴木','suzuki','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (2,'田中','tanaka','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (3,'佐藤','sato','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');

作成されたテーブルのcreate table文を確認すると以下のように表示されます。

CREATE TABLE IF NOT EXISTS public.employee
(
    id integer NOT NULL,
    name character varying(20) COLLATE pg_catalog."default" NOT NULL,
    romaji character varying(20) COLLATE pg_catalog."default",
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    CONSTRAINT employee_pkey PRIMARY KEY (id)
)

if not exists

create tableの後にif not existsを付けるとテーブルが存在しない場合のみテーブルを作成します。

CREATE TABLE IF NOT EXISTS employee(
	id INT,
	name VARCHAR(20) not null,
	romaji VARCHAR(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

一時テーブルを作成

一時テーブルの作成は、createとtableの間にtempを追加します。
セッションが終了するとテーブルも削除されます。

CREATE TEMP TABLE employee(
	id INT,
	name VARCHAR(20) not null,
	romaji VARCHAR(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

テーブル定義を確認

SELECT column_name,is_nullable,data_type,character_maximum_length
FROM information_schema.columns
WHERE table_name = 'employee';

テーブルを削除

DROP TABLE employee;
DROP TABLE employee CASCADE;

他のオブジェクトからの依存がある場合は、最後にCASCADEをつけます。

DROP TABLE IF EXISTS employee_test;

drop tableの後にif existsを付けるとテーブルが存在しなくてもエラーになりません。

テーブルの列の属性を変更

ALTER TABLE employee ALTER COLUMN 
	id TYPE VARCHAR(20)
;

employeeテーブルの項目idをvarchar型に変更しています。

テーブルに列を追加

ALTER TABLE employee ADD
	mail VARCHAR(30),
	url VARCHAR(100)
);

テーブルの最後尾に項目mail,urlが追加されます。

テーブルの列を削除

ALTER TABLE employee DROP
	mail
;

テーブルの項目mailを削除しています。データ型と桁数の指定は不要です。

テーブルの列名を変更

ALTER TABLE employee 
RENAME COLUMN name TO Namae;

テーブルの項目名のnameをNamaeに変更しています。

テーブルの列をNOT NULLにする

テーブルの項目名のnameをNOT NULLに変更しています。

ALTER TABLE employee 
ALTER COLUMN name SET NOT NULL;

テーブルの項目名のnameをNOT NULLに変更しています。

テーブルの列をNULL可にする

ALTER TABLE employee 
ALTER COLUMN name DROP NOT NULL;

テーブルの項目名のnameをNULLに変更しています。

テーブル名を変更

ALTER TABLE employee 
RENAME TO employee2;

テーブル名のemployeeをemployee2に変更しています。

ビューの一覧

\dv

psqlのコマンドでは、円マークとdvでビューの一覧が表示されます。

SELECT * FROM pg_views
where schemaname not in('pg_catalog','information_schema')

上記SQLでも取得できます。

ビューを作成

CREATE VIEW v_employee AS
	select
		id,
		name
	from
		employee;

select文でemployeeテーブルからビューを作成しています。
複数のテーブルを結合したsqlも作成できます。

ビューを変更

CREATE OR REPLACE VIEW v_employee AS
	select
		id,
		name,
		romaji
	from
		employee;

1行目は、OR REPLACEを追加しています。
5行目は、元々のviewに存在しなかった項目のromajiを追加しています。

ビューを削除

DROP VIEW v_employee;

drop viewを使用します。項目の指定は不要です。

シーケンス

シーケンスは、順序とも呼ばれます。指定した値の増分を取得できます。

シーケンスを作成

CREATE SEQUENCE employee_id
	START WITH 1
	INCREMENT BY 1;

2行目は、最初は1から始まると指定しています。
3行目は、増分です。1ずつ増えます。

シーケンスの採番

select nextval('employee_id') --1

select nextval('employee_id') --2

1行目は、nextvalで次の値を取得します。再度、nextvalを使用すると設定した増分で2になります。
シーケンス名はシングルコーテーションでくくります。

シーケンスを削除

DROP SEQUENCE employee_id;

DROP SEQUENCEを使用します。

PostgreSQLのバージョンを確認する

SELECT version();

データベース	データベースの一覧 / データベースを作成 / データベースを削除
テーブル	テーブルの一覧 / テーブルを作成 / テーブル定義を確認 / テーブルを削除
	if not exists / 一時テーブルを作成
alter table	テーブルの列の属性を変更 / テーブルに列を追加 / テーブルの列を削除
	テーブルの列名を変更 / テーブルの列をNOT NULLにする
	テーブルの列をNULL可にする / テーブル名を変更
ビュー	ビューの一覧 / ビューを作成 / ビューを変更 / ビューを削除
シーケンス	シーケンスを作成 / シーケンスの採番 / シーケンスを削除
バージョン	PostgreSQLのバージョンを確認する