PostgreSQL「ぽすとぐれすきゅーえる」

&tag(PostgreSQL,サンプル,PHP);

トラブルシューティング

Windows版をインストールしたらデータベースにEUC_JP、SJISを使用できない。

インストール時(initdb時)にロケールを「C」(デフォルト)でインストールすると使用できるようになる

JDBCのインストール

公式サイト からコンパイル済み jar をダウンロードする

関数

  • 関数を作成する前に下記の文を実行する
    createlang plpgsql(言語名) template1(DB名)

PL/PGSQL関数

長くなってきたので別ページへ

SQL関数

  • 誕生日(DATE型)から年齢(SMALLINT型)を返す関数
    CREATE FUNCTION func_getage(DATE) RETURNS SMALLINT AS '
      SELECT EXTRACT(YEAR FROM AGE($1))::SMALLINT;
    ' LANGUAGE SQL;

使用例

列名の変更

列名を変更するには、次のようにします。

ALTER TABLE products RENAME COLUMN product_no TO product_number;

型変換(指定?)の方法

  1. CAST('10' AS INTEGER);
  2. '10'::INTEGER;
  3. INTEGER '10';

NULL値の置き換え

  • 日本語ドキュメント(COALESCE)
  • COALESCE() は、引数のうち NULL でないものを返す
  • ■ヌル判定
    COALESCE(value [, ...])
    COALESCE関数は、NULLでない自身の最初の引数を返します。
    すべての引数がNULLの場合にのみNULLが返されます。
    データを表示の目的で取り出す際、NULL値の代わりにデフォルト値を使う場合、
    時として便利なことがあります。以下に例を示します。 
    
    SELECT coalesce(null, 'abc', 'def');
    
     case
    ------
     abc
    
    SELECT coalesce(null, null, 'def');
    
     case
    ------
     def

[PHP-users 18891]Re: 検索結果を横軸に配置するには?

[PHP-users 2995] PEAR+PostgresでInsert時のシリアルを知りたい

単純に
SELECT nextval('some_sequence') AS next_id;
としてsequenceから一意的な番号を取得し、結果を取り出します。

$query = "SELECT nextval('$seq_name') AS next_id";
$query_id = pg_Exec($this->link_id, $query);
$res = pg_Fetch_Array($query_id, 0, PGSQL_ASSOC);

を実行して取得した値をinsertに利用すればOkです。

テーブルを csv 出力

COPY system_name TO '/tmp/system_name_tabale.txt' DELIMITERS ',';

あるカラムで重複しているレコードの件数を数える (SQLの目次)

SELECT 項目名1,COUNT(*) FROM テーブル名 
  GROUP BY 項目名1 HAVING COUNT(*) >1 

大文字小文字を区別しない「パターンマッチング」(ILIKE,正規表現)

ILIKE
大文字小文字を区別しないマッチを行うのであればLIKEの替わりにILIKEキーワードを使うことができます。これは標準SQLではなく、PostgreSQLの拡張です。
正規表現
演算子説明
~正規表現にマッチ、大文字小文字の区別あり'thomas' ~ '.*thomas.*'
~*正規表現にマッチ、大文字小文字の区別なし'thomas' ~* '.*Thomas.*'
!~正規表現にマッチしない、大文字小文字の区別あり'thomas' !~ '.*Thomas.*'
!~*正規表現にマッチしない、大文字小文字の区別なし'thomas' !~* '.*vadim.*'

psqlで日本語を入力する

psqlを起動するときに

$ psql -d DB_NAME -n

とすると日本語が使える

配列

  • 元データ
     name  |      pay_by_quarter       |           schedule            
    -------+---------------------------+-------------------------------
     Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
     Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
  • 1~1個目の配列から1~2個目のデータを取得
    SELECT schedule[1:1][1:2] FROM sal_emp WHERE name = 'Bill';
  • 結果
         schedule      
    -------------------
     {{meeting,lunch}}

DB初期化

initdb -d DB_DIRECTORY --encoding=EUC_JP --no-locale

DB作成

createdb -E EUC_JP DB_NAME

dump(ラージオブジェクトを含まない)

pg_dump -Ft DB_NAME > db.tar

restore(ラージオブジェクトを含まない)

pg_restore -d NEW_DB_NAME db.tar

ランダムに1行だけ取得したいとき

メーリングリストより

(1)select ... order by (oid * v1) % v2 ;
(2)order by random()

と2通り考えられる。
(2)はランダム性としてはより高いですが
(1)には再現性があるというメリットがあります。ですから

select ... offset 20 limit 20
select ... offset 20 limit 40

上のような処理でページ分けをするなら(1)のような手法が必須だと思います。
そうでなければ(2)の方がいいでしょう。

SLECTの結果からINSERT

INSERT INTO tbl_name_A SELECT * FROM table_name_B;

リンク


Last-modified: 2016-07-10 (日) 02:00:29 (651d)