SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

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 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK