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;