refirio.org

テーブルを扱うためのメモ。

SQLでテーブル一覧を表示

SELECT
  pg_class.relname AS relname
FROM
  pg_class INNER JOIN pg_namespace
ON
  pg_class.relnamespace = pg_namespace.oid
WHERE
  pg_class.relkind = 'r' AND pg_namespace.nspname = 'public';

SQLでテーブル仕様を表示

SELECT
  column_name, data_type, is_nullable
FROM
  information_schema.columns
WHERE
  table_schema = 'public' AND table_name = 'テーブル名';

SQLでテーブル作成コードを表示

SELECT
  CASE
    WHEN tb.relkind = 'r' THEN(
      SELECT 'CREATE TABLE "テーブル名"(' || chr(10) || array_to_string(
        ARRAY(
          SELECT ' "' || "Column" || '" '|| "Type" || "Modifiers" || "Index" FROM(
            /* Column */
            SELECT
              at.attnum, ns.nspname AS schema, tb.relname AS table, at.attname AS "Column",
              /* Type */
              CASE
                WHEN at.attinhcount <> 0 OR at.attisdropped THEN null
                ELSE
                  CASE
                    WHEN tp.typname = 'int2'   THEN 'SMALLINT'
                    WHEN tp.typname = 'int4'   THEN 'INTEGER'
                    WHEN tp.typname = 'int8'   THEN 'BIGINT'
                    WHEN tp.typname = 'float4' THEN 'REAL'
                    WHEN tp.typname = 'float8' THEN 'DOUBLE PRECISION'
                    WHEN tp.typname = 'bpchar' THEN 'CHAR'
                    ELSE UPPER(tp.typname)
                  END ||
                  CASE
                    WHEN at.attlen >= 0             THEN ''
                    WHEN at.atttypmod < 4           THEN ''
                    WHEN tp.typname <> 'numeric'    THEN '(' || at.atttypmod - 4 || ')'
                    WHEN (at.atttypmod & 65535) = 4 THEN '(' || (at.atttypmod >> 16) || ')'
                    ELSE '(' || (at.atttypmod >> 16) || ',' || (at.atttypmod & 65535) - 4 || ')'
                  END
              END AS "Type",
              /* Modifiers */
              CASE
                WHEN at.attnotnull THEN ' NOT NULL'
                ELSE ''
              END ||
              CASE
                WHEN ad.adbin IS NULL THEN ''
                ELSE ' DEFAULT ' || UPPER(pg_get_expr(ad.adbin, tb.oid))
              END AS "Modifiers",
              /* one-column Index */
              CASE
                WHEN ix.indexrelid IS NULL THEN ''
                ELSE
                  CASE
                    WHEN ix.indisprimary THEN ' PRIMARY KEY'
                    WHEN ix.indisunique  THEN ' UNIQUE'
                    ELSE ''
                  END
              END AS "Index"
            FROM
              pg_attribute at
              INNER JOIN pg_type tp ON at.atttypid = tp.oid
              LEFT OUTER JOIN pg_attrdef ad ON ad.adrelid = tb.oid AND ad.adnum = at.attnum
              LEFT OUTER JOIN pg_index ix ON ix.indrelid = tb.oid AND ix.indnatts = 1 AND at.attnum = ix.indkey[0]
              LEFT OUTER JOIN pg_class ic ON ix.indexrelid = ic.oid
              LEFT OUTER JOIN pg_am    am ON ic.relam = am.oid
            WHERE
              tb.oid = at.attrelid AND at.attnum >= 1
          ) AS columns ORDER BY attnum
        ), ',' || chr(10)
      )
      ||
      (
        SELECT
          CASE
            WHEN COUNT(*) = 0 THEN ''
            ELSE ',' || chr(10) || ' ' || array_to_string(
              ARRAY(
                SELECT
                  CASE
                    WHEN indisprimary THEN 'PRIMARY KEY '
                    ELSE 'UNIQUE '
                  END
                  || substr(indexdef, strpos(indexdef, '('), strpos(indexdef, ')') - strpos(indexdef, '(') + 1) || ' /* '||index||' */'
                FROM
                (
                  SELECT
                    ic.relname AS index, ns.nspname AS schema, tb.relname AS table --, ia.attname, ia.attnum
                    , ix.indnatts, ix.indisunique, ix.indisprimary, am.amname
                    , ix.indkey
                    , pg_get_indexdef(ic.oid) AS indexdef
                  FROM
                    pg_index ix
                    INNER JOIN pg_class ic ON ix.indexrelid = ic.oid
                    INNER JOIN pg_am    am ON ic.relam = am.oid
                  WHERE
                    ix.indrelid = tb.oid AND ix.indnatts > 1 AND (ix.indisprimary OR ix.indisunique)
                ) AS def ORDER BY indisprimary desc, index
              ), ','||chr(10)
            )
          END
        FROM
          pg_index ix
        WHERE
          ix.indrelid = tb.oid AND ix.indnatts > 1 AND (ix.indisprimary OR ix.indisunique)
      ) || chr(10) || ')'
    )
    END
FROM
  pg_class tb INNER JOIN pg_namespace ns ON tb.relnamespace = ns.oid
WHERE
  tb.relname = 'テーブル名'

コマンドラインでデータベースの内容をエクスポート

pg_dump -U postgres -d データベース名 > C:\～エクスポート先～\任意のファイル名.sql

コマンドラインでデータベースの内容をインポート

psql -U postgres -d データベース名 -f C:\～インポート元～\インポートするファイル

PHPでデータベースの内容をエクスポート

putenv('PGHOST=ホスト名');
putenv('PGPORT=ポート番号');
putenv('PGUSER=ユーザー名');
putenv('PGPASSWORD=パスワード');
putenv('PGDATABASE=データベース名');
print('<pre>');
passthru('"C:\\postgresql\\bin\\pg_dump.exe" -i --inserts');
print('</pre>');
exit;

PostgreSQLを導入してみたので、忘れないうちにメモ。

主な参考サイト

• PostgreSQLインストール
• PostgreSQLの使い方
• PostgreSQLへの接続

トラブル

PHPからPostgreSQLに接続する際、以下のようなエラーになった。

Call to undefined function pg_connect()

php.ini で php_pdo_pgsql.dll と php_pgsql.dll を有効にする必要がある。それでもダメなら、Windowsの環境変数にPHPへのパス（C:\xampp\php など）を書き込めば回避できるみたい。

コマンドプロンプトからの操作例

#ユーザー名を指定してPostgreSQLにログイン
psql -U postgres

#ユーザー名とデータベースを指定してPostgreSQLにログイン
psql -U postgres -d test

#データベース作成
CREATE DATABASE test;

#データベース一覧
¥l

#データベース選択
¥c test

#テーブル作成
CREATE TABLE sample1(
  id   integer,
  name text
);

#テーブル一覧
\dt

#テーブル定義確認
\d sample1

#SQLを発行
INSERT INTO sample1 VALUES(2, 'test2');
SELECT * FROM sample1;

PHPからの、pg_connect()での接続例

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>テスト</title>
</head>
<body>
<?php

$con = pg_connect('host=localhost dbname=test user=postgres password=1234');
if (!$con) {
  exit('データベースに接続できませんでした。');
}

$result = pg_query($con, 'SELECT * FROM sample1');
while ($data = pg_fetch_array($result)) {
  echo '<p>' . $data['id'] . ':' . $data['name'] . "</p>\n";
}

$con = pg_close($con);
if (!$con) {
  exit('データベースとの接続を閉じられませんでした。');
}

?>
</body>
</html>

PHPからの、PDOでの接続例

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>テスト</title>
</head>
<body>
<?php

try {
  $pdo = new PDO('pgsql:dbname=test;host=localhost', 'postgres', '1234');
} catch (PDOException $e) {
  exit('データベースに接続できませんでした。' . $e->getMessage());
}

$stmt = $pdo->query('SELECT * FROM sample1');
if (!$stmt) {
  $info = $pdo->errorInfo();

  exit($info[2]);
}

while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo '<p>' . $data['id'] . ':' . $data['name'] . "</p>\n";
}

$pdo = null;

?>
</body>
</html>