PC関係のメモ

SELECT
    att.attnum AS NUM,
    att.attname AS COL_NAME,
    typ.typname AS COL_TYPE,
    com.description AS COL_COMMENT

FROM
    pg_class cls
    LEFT JOIN pg_attribute att ON cls.oid=att.attrelid
    LEFT JOIN pg_type typ ON att.atttypid=typ.oid
    LEFT JOIN pg_description com ON att.attrelid = com.objoid
                                    AND att.attnum = com.objsubid

WHERE

    att.attnum > 0
AND cls.relname = 't_hogehoge'

ORDER BY
    att.attnum

PostgreSQLでテーブルの列を取得するクエリ(2)

プライマリキーかどうかも表示してみた。

SELECT
    att.attnum AS NUM,
    att.attname AS COL_NAME,
    typ.typname AS COL_TYPE,
    dsc.description AS COL_COMMENT,
    con.contype AS PRIMARY_KEY

FROM
    pg_class cls
    LEFT JOIN pg_attribute att ON cls.oid = att.attrelid
    LEFT JOIN pg_type typ ON att.atttypid = typ.oid
    LEFT JOIN pg_description dsc ON att.attrelid = dsc.objoid
                                    AND att.attnum = dsc.objsubid
    LEFT JOIN pg_constraint con ON con.conrelid = cls.oid AND att.attnum = ANY(con.conkey)
WHERE

    att.attnum > 0
AND cls.relname = 't_hogehoge'
AND (con.contype='p' OR con.contype IS NULL)

ORDER BY
    att.attnum

PostgreSQLでシーケンス一覧を取得するクエリ

SELECT
 cs.relname, ct.relname, a.attname
FROM
 pg_class cs
 JOIN pg_depend d ON cs.oid=d.objid
 JOIN pg_class ct ON ct.oid=d.refobjid
 JOIN pg_attribute a ON d.refobjid=a.attrelid AND d.refobjsubid=a.attnum
WHERE
 cs.relkind='S'

標準ではアルファベットがN-gramじゃないので、例えばEstaraierでHyperEstraierはヒットしない。

これでは不便なので完全N-gramにしようと思ったら、DB作り直しということがわかった。

estmaster init cascket

cd _node
estcmd create -apn database

以上。