PostgreSQLでselect句に定数が含まれていた場合、下記エラーが出力されることがあります。後に続いて型を決めてやれば問題は解消します。::textの部分です。
failed to find conversion function from unknown to text
select * from (select a_column, 'const'::text as "b_column" from c_table) as d_table
メモでした。
pg_dumpでは-tオプションで単一または複数のtable/view/sequenceを指定して定義の抽出が可能ですが、functionの指定はできません。これを解決する、裏技チックな方法が英語ページにしか掲載されていなかったので、日本語ページの一人目になるべく本記事を執筆しました。
pg_dump -Fc -s | pg_restore -P 'funcname(args)' > function_define.dmp
-sオプションでスキーマ（定義）のみを抽出します。（ここでいうスキーマとは、テーブルの完全修飾名の前半に使う名称（schema）ではないので注意。単なる定義情報（雑に言うとcreate table）のことを指します。この注意事項は本家にも記されています。ややこしいですね。）
これと–schemaオプションと混乱しないでください。”schema”という単語を異なる意味で使用しています。pg_dump
結果をパイプでpg_restoreに繋いでいます。pg_restoreはcustom形式しか処理できないため、-Fcオプションを付与しています。（デフォルトはplainです。）
-Pは名称を指定してcustomからfunctionの定義を取り出すことができます。また、pg_restoreは特に接続先データベースが指定されていない場合、標準出力にplain形式で吐き出すため、リダイレクトでファイルに出力しています。
データベース名が指定された場合、pg_restoreはそのデータベースに接続し、アーカイブを直接そのデータベースにリストアします。 データベース名が指定されなかった場合は、データベースを再構築するために必要となるSQLコマンドが含まれたスクリプトが作成されます（ファイルもしくは標準出力に書き出されます）。pg_restore
functionに引数（args）がある場合、正しい引数の数だけ、型を指定してやる必要がありますので注意が必要です。不明の場合は下記コマンドで確認できるでしょう。
pg_dump -Fp -s | less
私の環境では2回以上-Pを指定した場合、最後に指定したfunction以外が無視されました。そのため、下記のように工夫する必要があります。
pg_dump -Fc -s | pg_restore -P 'funcname(args)' > function_define.dmp pg_dump -Fc -s | pg_restore -P 'funcname2(args)' >> function_define.dmp pg_dump -Fc -s | pg_restore -P 'funcname3(args)' >> function_define.dmp
最終的な出力ファイルを見ると、SETが重複して出力されていますが、特に弊害はありません。
違うschemaに存在する、同じ構造のtableに対して、pg_dumpでデータを抽出/挿入する方法を記します。最初にオチを言うと、\COPYを使用したほうが楽です。
下記の通り、test1.testからtest2.testに対してデータをコピーします。
postgres=# \dn
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
test1 | postgres
test2 | postgres
(7 rows)
postgres=# \d test1.test
Table "test1.test"
Column | Type | Modifiers
--------+------+-----------
val | text |
postgres=# \d test2.test
Table "test2.test"
Column | Type | Modifiers
--------+------+-----------
val | text |
postgres=# select * from test1.test;
val
------------
testvalue1
testvalue2
testvalue3
(3 rows)
postgres=# select * from test2.test;
val
-----
(0 rows)
対象テーブルに対して、pg_dumpをplainでダンプします。customで色々と試してみましたが、結局徒労に終わりました。
pg_dump --file=./test.dmp --format=p --data-only --table=test1.test
plainのため、中身はviで見ることができます。
-bash-4.1$ ll total 12 drwx------. 2 postgres postgres 4096 Jun 29 08:59 backups drwx------. 12 postgres postgres 4096 Oct 8 22:38 data -rw-r--r--. 1 postgres postgres 463 Oct 8 23:06 test.dmp -bash-4.1$ cat test.dmp -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; SET search_path = test1, pg_catalog; -- -- Data for Name: test; Type: TABLE DATA; Schema: test1; Owner: postgres -- COPY test (val) FROM stdin; testvalue1 testvalue2 testvalue3 \. -- -- PostgreSQL database dump complete --
スキーマが書かれている箇所を手で直してしまっても構いませんが、コマンドで自動化してしまいます。
sed -i.`date "+%Y%m%d-%H%M%S"` -e 's/test1/test2/g' test.dmp
念のため、diffで確認してみます。
diff test.dmp*
12c12 < SET search_path = test2, pg_catalog; --- > SET search_path = test1, pg_catalog; 15c15 < -- Data for Name: test; Type: TABLE DATA; Schema: test2; Owner: postgres --- > -- Data for Name: test; Type: TABLE DATA; Schema: test1; Owner: postgres
後は普通にpsqlで流し込みます。
psql --file=test.dmp
下記の通り確認してみます。成功です。
postgres=# select * from test2.test;
val
------------
testvalue1
testvalue2
testvalue3
(3 rows)
今回はpg_dumpにこだわってみたのですが、\COPYを使用するほうが楽だと思います。対象のテーブルをコマンド実行時に指定できますしね。