カテゴリー別アーカイブ: Postgresql

PostgreSQLでSQL発行時のエラー対応

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

メモでした。

PostgreSQLのpg_dumpで単一のfunctionのみ取得する

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を使用したほうが楽です。

環境

PostgreSQL
8.4.20
CentOS
6.7

前提条件

下記の通り、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を使用するほうが楽だと思います。対象のテーブルをコマンド実行時に指定できますしね。