概要

二つの日付から経過した年月日を計算するにはage関数を使用します。

結果は次の例のようにinterval値（XX years XX mons XX days）という値になります。

--誕生日から年齢を計算する
age( 新しいほうの日付 , 古いほうの日付 ) 

-- 実行例　※日付の前の「timestamp」という文字は必要なので、そのまま記述して下さい。
select age(timestamp '2020/1/1',timestamp '1990/1/1');   -- 30 years
select age(timestamp '2020/1/1',timestamp '1995/12/1');  -- 24 years 1 mon
select age(timestamp '2020/1/11',timestamp '1998/4/1');  -- 21 years 9 mons 10 days

 

 

実行例（呼び出し例）

上のままでは少々使いづらいので、年、月、日付だけ取得する例です。

例えば「21 years 9 mons 10 days」なら年の21、月の9だけ、日の10だけ取得する方法です。

--2つの日付から経過した年だけを取得
select date_part('year',age(timestamp '2020/1/1',timestamp '1990/1/1'));   -- 30
select date_part('year',age(timestamp '2020/1/1',timestamp '1995/12/1'));  -- 24
select date_part('year',age(timestamp '2020/1/11',timestamp '1998/4/1'));  -- 21

--2つの日付から経過した月だけを取得
select date_part('month',age(timestamp '2020/1/1',timestamp '1990/1/1'));   -- 0
select date_part('month',age(timestamp '2020/1/1',timestamp '1995/12/1'));  -- 1
select date_part('month',age(timestamp '2020/1/11',timestamp '1998/4/1'));  -- 9

--2つの日付から経過した日だけを取得
select date_part('day',age(timestamp '2020/1/1',timestamp '1990/1/1'));   -- 0
select date_part('day',age(timestamp '2020/1/1',timestamp '1995/12/1'));  -- 0
select date_part('day',age(timestamp '2020/1/11',timestamp '1998/4/1'));  -- 10

 

概要

誕生日から年齢を計算するにはage関数を使用します。

結果は次の例のようにinterval値（XX years XX mons XX days）という値になります。

--誕生日から年齢を計算する
age( 誕生日の日付 ) 

-- 実行例
select age(timestamp '1980-01-01');  -- 41 years 1 day
select age(timestamp '1990-04-01');  -- 30 years 9 mons 1 day
select age(timestamp '1997-07-07');  -- 23 years 5 mons 26 days

 

 

実行例（呼び出し例）

上のままでは少々使いづらいので、年、月、日付だけ取得する例です。

--誕生日から年（何歳か）を取得
select date_part('year', age(timestamp '1980-01-01'));  -- 41
select date_part('year', age(timestamp '1997-07-07'));  -- 23

--誕生日から月（何ヶ月か）を取得
select date_part('month', age(timestamp '1980-01-01'));  -- 0
select date_part('month', age(timestamp '1997-07-07'));  -- 5

--誕生日か日（何日か）を取得
select date_part('day', age(timestamp '1980-01-01'));    -- 1
select date_part('day', age(timestamp '1997-07-07'));    -- 26

 

 

 

関連記事

・日時、時刻の書式設定をする（yyyymmdd形式）

・日付の加算、週の加算、月の加算

・2つの日付の差を計算する

・誕生日から年齢を計算する

 

デフォルト値（初期値）を追加、削除する

テーブルの列にデフォルト値を追加、削除する方法です。

--デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

--例：「m_school」テーブルの列「hoge_flg」を初期値「1」で設定する
ALTER TABLE m_school ALTER COLUMN hoge_flg SET DEFAULT 1;


--デフォルト値の削除
ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP DEFAULT;

--例：「m_school」テーブルの列「hoge_flg」のデフォルト値を削除する
ALTER TABLE m_school ALTER COLUMN hoge_flg DROP DEFAULT;

 

 

 

関連記事

・列を追加・削除する方法、桁数を変更する方法

・列にNOT NULL制約を追加・削除する方法

・外部キーを追加・削除する方法

 

外部キー制約を追加、削除する

テーブルに外部キーを追加・削除する方法です。

--外部キー制約の追加
ALTER TABLE テーブル名
ADD FOREIGN KEY (外部キーを付けるテーブルの列名) 
REFERENCES 参照先テーブル名 (参照先列名);

/*例：「department」テーブルに対する外部キーを、
列「department_code」で設定する*/
ALTER TABLE employee ADD FOREIGN KEY (department_code) 
REFERENCES department (department_code);


--外部キー制約の削除
ALTER TABLE テーブル名 DROP CONSTRAINT 外部キー名;

--例：「employee」テーブルの外部キー「employee_department_code_fkey」を削除する
ALTER TABLE employee DROP CONSTRAINT employee_department_code_fkey;

 

 

関連記事

・列を追加・削除する方法、桁数を変更する方法

・列にデフォルト値（初期値）を追加・削除する方法

・列にNOT NULL制約を追加・削除する方法

 

Not Null制約を追加、削除する

テーブルの列にNot Null制約を追加、削除する方法です。

--Not Null制約の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;

--例：「m_school」テーブルの列「hoge」をNOT NULLにする
--すでにNullが入っている場合はエラーになるので、値を入れてから実行します
ALTER TABLE m_school ALTER COLUMN hoge SET NOT NULL;


--Not Null制約の削除
ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP NOT NULL;

--例：「m_school」テーブルの列「hoge」をNOT NULL制約を削除する
ALTER TABLE m_school ALTER COLUMN hoge DROP NOT NULL;

 

 

デフォルト値（初期値）を追加、削除する

テーブルの列にデフォルト値を追加、削除する方法です。

--デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

--例：「m_school」テーブルの列「hoge_flg」を初期値「1」で設定する
ALTER TABLE m_school ALTER COLUMN hoge_flg SET DEFAULT 1;


--デフォルト値の削除
ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP DEFAULT;

--例：「m_school」テーブルの列「hoge_flg」を初期値「1」で設定する
ALTER TABLE m_school ALTER COLUMN hoge_flg DROP DEFAULT;

 

 

関連記事

・列を追加・削除する方法、桁数を変更する方法

・列にデフォルト値（初期値）を追加・削除する方法

・外部キーを追加・削除する方法

 

offsetとは

offsetは、「offset 開始位置」と書き、開始位置より後のデータを取得する時に使用します。

※ 開始位置は0からカウントアップするので、気を付けて下さい。

limitと一緒に使うことで、「開始位置から〇行取得する」というような使い方ができます。

--5行目からデータを取得する（offsetは0からカウントに注意）
select * from テーブル名 offset 4;

--2行目からデータを取得する
select * from テーブル名 offset 1;

--5行目からデータを2行取得する（limitは1からカウント）
select * from テーブル名 limit 2 offset 4;

--4行目からデータを5行、列名1の昇順で取得する
select * from テーブル名 order by 列名1 limit 5 offset 3;

--4行目からデータを5行、列名1の降順で取得する
select * from テーブル名 order by 列名1 desc limit 5 offset 3;

 

文字列中にシングルクオーテーションを入れる

文字列中にシングルクオーテーション「'」を入れるには、

入れたいシングルクオーテーションの前にさらにシングルクオーテーションを付けます。

 

例文

文字列中にシングルクオーテーション「'」を入れる例です。

--文字列中にシングルクオーテーションを入れる
--シングルクオーテーションを2つつなげるとエスケープとなる

select '彼は''ありがとう''と言った';    --彼は'ありがとう'と言った
select '彼は ''ありがとう'' と言った';  --彼は 'ありがとう' と言った

 

構文まとめを書いています。ぜひ一度見てみて下さい

「基本構文・こんな時どう書くんだっけ？」をまとめました

型textからvarchar(character varying)への変換

型 text から varchar( character varying ) への変換例です。

※ 変換する文字列はテスト的に「xxxx5xxxx0xxxx5xxxx0」（20文字）としています。

--textからvarchar文字数指定あり（10桁）へ変換する
select cast(cast('xxxx5xxxx0xxxx5xxxx0' as text) as varchar(10));
--結果："xxxx5xxxx0"（文字数を超えてもエラーにはならず、その桁数で切られます）

--textからvarchar文字数指定なしへ変換する
select cast(cast('xxxx5xxxx0xxxx5xxxx0' as text) as varchar);
--結果："xxxx5xxxx0xxxx5xxxx0"

 

 

 

型varchar(character varying)からtextへの変換

型 varchar( character varying ) から text への変換例です。

--varchar文字数指定あり（10桁）からtextへ変換する
select cast(cast('xxxx5' as varchar(10)) as text);
--結果："xxxx5"

select cast(cast('xxxx5xxxx0xxxx5xxxx0' as varchar(10)) as text);
--結果："xxxx5xxxx0"

 

テーブルが削除できない時の対処方法

ここでは、テーブルを削除しようとした時、エラーが発生した場合の対処方法を説明します。

 

 

１．外部キー制約がある時

外部キーで他のテーブルから参照されているテーブルを削除すると、

『constraint 外部キー名 on table テーブル名1 depends on table テーブル名2』

というエラーメッセージが表示されます。

 

このメッセージは、『他のオブジェクトが依存しているため、テーブル名2を削除できません。』

という意味です。この場合、本来テーブル名２を削除したいのに、

テーブル名１から参照がかかっているため削除できないことを示しています。

 

このエラーの対処方法は次の3つです。

※すべてを行うのではなくどれか一つで大丈夫です。

 

対処方法１

参照しているテーブル（上のメッセージではテーブル名1）を先に消す

--参照元のテーブルを先に消した後、本来削除したいテーブルを消す
DROP TABLE 参照元のテーブル名;   　　-- 参照元のテーブルの削除
DROP TABLE 本来消したいテーブル名;   -- 削除したいテーブルの削除

 

対処方法２

参照しているテーブル（上のメッセージではテーブル名1）の外部キー制約を消す

--参照元のテーブルから外部キー制約を削除した後、本来消したいテーブルを消す
ALTER TABLE 参照元テーブル名 DROP CONSTRAINT 外部キー名; --外部キー削除
DROP TABLE 本来消したいテーブル名;                      -- 削除したいテーブルの削除

 

対処方法３

DROP ・・・ CASCADE文を使用する

--削除したいテーブル名の後ろに「CASCADE」をつける
DROP TABLE 本来消したいテーブル名 CASCADE;  -- 削除したいテーブルの削除

※ CASCADEをつけた場合、関わる制約・参照を削除します。この場合は外部キーを自動で削除します。

 

 

２．削除するテーブルがVIEWで使われている時

VIEWで使用されているテーブルを削除すると、

『view ビュー名 depends on table テーブル名1』

というエラーメッセージが表示されます。

このメッセージは、『ビュー名はテーブル名1に依存しています。』という意味です。

 

対処方法１

削除したいテーブルを使っているビューを先に消す

--削除したいテーブルを使っているビューを先に消す
DROP VIEW ビュー名;   　　         -- ビューの削除
DROP TABLE 本来消したいテーブル名;  -- 削除したいテーブルの削除

 

対処方法２

DROP ・・・ CASCADE文を使用する

--削除したいテーブル名の後ろに「CASCADE」をつける
DROP TABLE 本来消したいテーブル名 CASCADE;  -- 削除したいテーブルの削除

※ CASCADEをつけた場合、関わる制約・参照を削除します。この場合はビューを自動で削除します。

 

 

余談：エラーにならないケース

次の場合は、テーブル削除してもエラーとならないケースです。

※ すべての場合を検証していませんので、一例ととらえて下さい。

 

１）削除したテーブルが関数から参照されていた時

関数で使用されているテーブルを削除してもエラーにはなりませんでした。

当然ですが、関数実行時にテーブルが存在しないエラーが発生します。

 

２）削除したテーブルがトリガーから参照されていた時

関数同様にエラーになりません。

また、トリガーで呼ばれる関数から参照されていても１．同様エラーにはなりません。

列挙型（ENUM）の書き方

PostgreSQLでの列挙型（ENUM型）の作り方・使い方を紹介します。

--列挙型を定義
CREATE TYPE 列挙型の名前 AS ENUM (値1 , 値2 , 値3, ・・・);

--例：色の列挙型を定義する
CREATE TYPE color AS ENUM ('RED', 'YELLOW', 'BLUE');

 

 

列挙型の使い方

上の定義で作成した列挙型の使用する方法です。

--例：色の列挙型を定義する
CREATE TYPE color AS ENUM ('RED', 'YELLOW', 'BLUE');

--テーブル作成時に列挙型を使用
CREATE TABLE car (
    code character varying(8),
    name character varying(120),
    colortype color
);

--データ登録
INSERT INTO car VALUES('001','A車','RED');   --成功
INSERT INTO car VALUES('002','B車','GREEN'); --GREENはないためエラーになる

 

 

列挙型の削除

列挙型を削除する方法です。

すでに列挙型をテーブルで使用している場合は、削除できないので注意して下さい。

削除するにはそのテーブルを削除、またはそのテーブルの列定義を変更する必要があります。

--列挙型を削除
DROP TYPE color; --すでに使われている場合はエラーとなる