SAK 図書館
SAK 図書館
PostgreSQL 編18 - テーブルコピー、レコード追加、修正、削除、表切り捨て
■テーブルコピー
・問い合わせ結果で、新たにテーブルを作成することができる。
別テーブルへ移動するには、テーブルコピー後にオリジナルを delete、
新テーブルに整合性規則を追加するか、作成済みテーブルに insert で
select 追加すると良い。(下記のレコード追加も参照のこと。)
(表コピー)
create table workw as
select
*
from testm
where key like 'a%'
;
select * from workw;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
・集合問い合わせ結果で、新たにテーブルを作成することもできる。
create table work2w as
select
*
from testm
where key like 'a%'
union all
select
*
from testm
where key like 'a%'
;
select * from work2w;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
・空のテーブルをコピーしたい場合は、ありえないキーで抽出する。
create table work3w as
select
*
from testm
where key = '@'
;
select * from work3w;
key | data1 | data2 | data3
-----+-------+-------+-------
■レコード追加(レコード更新、インサート)
・テーブルやビューにレコードを追加する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
insert into testm (
key,
data1,
data2,
data3
)
values (
'a001',
100,
200,
300
);
・追加データの並びがテーブル定義と同一であるなら、項目名を省略できる。
insert into testm values (
'a001',
100,
200,
300
);
・default が指定されていれば、省略で default 追加することができる。
但し、Oracle のようにデータ値として、default と書くことはできない。
(update でも default 指定が可能。)
create table test4m (
key char(8),
data1 int8,
data2 int8,
data3 int8 default 123
);
insert into test4m values (
'a001',
100,
200
);
select * from test4m;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 100 | 200 | 123
・入力が必須でない項目は、省略して追加も可能である。
default 指定や not null 制約がない場合、NULL が割り当てられる。
insert into test4m values (
'a001',
100
);
select * from test4m;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 100 | 200 | 123
a001 | 100 | | 123
・問い合わせ結果を別テーブルへレコード追加することも可能である。
別テーブルへレコード移動するには、別テーブルへレコード追加後にオリジ
ナルを delete すると良い。(上記のテーブルコピーも参照のこと。)
(副問い合わせ、レコードコピー)
insert into workw
select
key,
data1,
data2,
data3
from testm where key like 'a%'
;
・特定の項目を固定値にしたりもできる。
insert into workw
select
key,
data1,
data2,
111
from testm where key like 'a%'
;
・特定の項目だけをセットすることもできる。
insert into workw
select
key,
data2
from testm where key like 'a%'
;
・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。
insert into testm (
key,
data1,
data2,
data3
)
values (
'a001',
100,
200,
NULL
);
・データ中にシングルコーテーション「'」を含める場合は、「''」と 2 個
続けて書くと 1 個の「'」が文字として入る。
insert into testm (
key,
data1,
data2,
data3
)
values (
'a0''1',
100,
200,
300
);
■レコード修正(レコード更新、アップデート)
・テーブルやビューのレコードを修正する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
where 句には select と同様に複雑な指定が可能である。
update testm set
data1 = 111,
data2 = 222,
data3 = 333
where key = 'a001'
;
・PostgerSQL では、データ値としての default が指定できないので、
insert は、省略することによって default 値で追加することができるが、
update では指定のしようがなかった。
update test4m set
data1 = 111,
data2 = 222,
data3 = default -- data3 = でもエラー
where key = 'a001'
;
・一項目だけの修正も可能である。
update testm set
data3 = 333
where key = 'a001'
;
・副問い合わせの結果を項目にセットすることもできる。
尚、update testm a set の別名指定はエラーとなる。
update testm set
data1 = 111,
data2 = (select count(*) from test2m b where b.code1 = testm.key),
data3 = 333
where key like 'a%'
;
・副問い合わせの結果を複数項目にセットするには、PostgreSQL では、
次のようにすると良いらしい。
(お教え下さった方、ありがとうございます。)
テストテーブルが testm しか残っていなかったので、testm ばかりの
記述になっていますが、要点はわかってもらえるかなと...
update testm set
data1 = subq.d1,
data2 = subq.d2
from
(select
key,
1234 as d1,
5678 as d2
from
testm b
where
b.key = 'a001') subq
where
testm.key = subq.key
;
key | data1 | data2 | data3
----------+-------+-------+-------
a011 | 1 | 2 | 3
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
a001 | 1234 | 5678 | 3
-- 次の Oracle風文法はエラーとなりますが、基本的なものをテストしただけ
-- なので、後は自分で調査したり、テストしたりしてみて下さい。
update testm set
(data1, data2) = (select 1, 2 from testm b where b.key = testm.key)
where key like 'a%'
;
・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。
update testm set
data1 = 111,
data2 = 222,
data3 = NULL
where key = 'a001'
;
■レコード削除(レコード更新、デリート)
・テーブルやビューのレコードを削除する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
where 句には select と同様に複雑な指定が可能である。
delete from testm
where key = 'a001'
;
・この例では、testm の key の先頭が「a」で始まるレコードを全て削除して
いる。
delete from testm
where key like 'a%'
;
・この例では、testm の全レコードを削除している。
件数が多いとレスポンスに問題がある。
テーブルを空にするのが目的なら、表切り捨て truncate を使う方が高速で
現実的である。
delete from testm;
■トランザクション
・PostgreSQL のトランザクションは、標準は自動コミットとなっている。
ADO - ODBC では、cn.BeginTrans で手動コミットになる。
JDBC では、cn.setAutoCommit(false); でオートコミットを解除して、
手動コミットにする。
■排他制御(レコードロック)
・レコード追加、修正、削除では、レコードレベルの排他制御が働く。
デッドロック回避の考え方は、他のファイルシンテムと同様である。
テーブルロック(表ロック) として lock table もある。
・問い合わせで行ロックする方法として、for update 句がある。
for update nowait は、ロック解除を待たずにエラーを返す。
次の例は、ロックされていたら待って問い合わせる。
select * from testm where key = 'a001'
for update
;
・nowait はエラー構文となった。
select * from testm where key = 'a001'
for update nowait
;
■表切り捨て
・truncate は、表を切り捨ててクリアする。
(テーブル切り捨て、テーブルクリア、表クリア、テーブル内データ消去)
truncate table testm;
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2024 Y.SAK