PostgreSQLでバイナリデータを効率的に扱う！変換メソッドとエラー対策ガイド
2025-05-31
以下に主要な関数とその役割を説明します。
bytea型とは？
-
encode(data bytea, format text) → text:bytea型のバイナリデータを、指定されたテキスト形式に**エンコード（符号化）**してtext型として返します。formatには以下の値が指定できます。'base64': Base64形式にエンコードします。'hex': 16進数形式（各バイトが2桁の16進数で表現され、\xが前置される）にエンコードします。これがデフォルトの出力形式になることが多いです。'escape': PostgreSQLの伝統的なエスケープ形式でエンコードします。印字不可能な文字やバックスラッシュなどをエスケープシーケンス（\nnnのような8進数表記）に変換します。
- 例
SELECT encode('\x414243'::bytea, 'base64'); -- 結果: 'QUJD' SELECT encode('Hello'::bytea, 'hex'); -- 結果: '\x48656c6c6f'
-
decode(string text, format text) → bytea:- 指定されたテキスト形式の文字列を、
bytea型のバイナリデータに**デコード（復号）**して返します。 formatはencode関数と同じく、'base64'、'hex'、'escape'が指定できます。- 例
SELECT decode('QUJD', 'base64'); -- 結果: '\x414243' (ABCのバイナリ表現) SELECT decode('\x48656c6c6f', 'hex'); -- 結果: 'Hello' (Helloのバイナリ表現)
- 指定されたテキスト形式の文字列を、
-
convert(bytes bytea, src_encoding name, dest_encoding name) → bytea:src_encodingでエンコードされたテキストを表すバイナリ文字列（bytea型）を、dest_encodingでエンコードされたバイナリ文字列（bytea型）に変換します。- これは、バイナリデータが実際には異なる文字エンコーディングのテキストである場合に、そのエンコーディングを変換するのに役立ちます。
- 例
SELECT convert('テキスト'::bytea, 'UTF8', 'EUC_JP'); -- UTF8のバイナリをEUC_JPのバイナリに変換
-
convert_from(bytes bytea, src_encoding name) → text:src_encodingでエンコードされたテキストを表すバイナリ文字列（bytea型）を、データベースのデフォルトエンコーディングのtext型に変換します。- バイナリとして格納されたテキストデータを、PostgreSQLが通常のテキストとして扱えるようにする際に使用します。
- 例
SELECT convert_from('\xe38386\xe382ad\xe382b9\xe38388'::bytea, 'UTF8'); -- 結果: 'テキスト'\xe38386\xe382ad\xe382b9\xe38388は「テキスト」のUTF8バイナリ表現）
-
convert_to(string text, dest_encoding name) → bytea:- データベースのデフォルトエンコーディングの
text型文字列を、dest_encodingでエンコードされたバイナリ文字列（bytea型）に変換します。 - テキストデータを特定のエンコーディングのバイナリデータとして格納したい場合などに使用します。
- 例
SELECT convert_to('テキスト', 'UTF8'); -- 結果: '\xe38386\xe382ad\xe382b9\xe38388'
- データベースのデフォルトエンコーディングの
PostgreSQLにおける「Binary String: convert」は、主にbyteaデータ型とtextデータ型の間で、あるいは異なる文字エンコーディング間でデータを変換するための関数群を指します。これにより、生バイナリデータと人間が読めるテキストデータを柔軟に扱えるようになります。
convert/convert_from/convert_to: バイナリデータが実際には特定のエンコーディングのテキストである場合に、そのエンコーディングを変換したり、text型とbytea型を相互に変換したりする際に使います。encode/decode: バイナリデータをテキスト形式（Base64、Hex、Escape）と相互変換する際に使います。
一般的なエラー
-
- 説明
このエラーは、指定されたエンコーディング（"XXX"）に対して、入力されたバイトシーケンス（0xYYY）が有効でない場合に発生します。特にconvert_fromやconvert_to関数、あるいはクライアントとサーバー間の文字コード変換で頻繁に見られます。例えば、UTF-8エンコーディングのデータベースにShift-JISの不正なバイトシーケンスを挿入しようとしたり、その逆の変換を行おうとしたりする場合です。 - 原因
- エンコーディングの不一致
データの実際のエンコーディングと、PostgreSQLが期待しているエンコーディングが異なっている。 - 不正なバイトシーケンス
入力データ自体が破損しているか、指定されたエンコーディングのルールに合致しないバイトの並びを含んでいる。 - クライアントとサーバーの文字コード設定の不一致
クライアント（アプリケーション、psqlなど）が使用している文字コードと、PostgreSQLサーバーやデータベースの文字コード設定が異なる場合、自動変換に失敗することがあります。
- エンコーディングの不一致
- 例
-- データベースがUTF8なのに、SJISの不正なバイトシーケンスを変換しようとする SELECT convert_from('\x8365'::bytea, 'UTF8'); -- 0x8365はSJISでは有効なバイト列だが、UTF8では無効なことが多い
- 説明
-
ERROR: character with byte sequence 0xYYY in encoding "XXX" has no equivalent in encoding "ZZZ"- 説明
あるエンコーディング（"XXX"）内の特定の文字（0xYYYで始まるバイトシーケンス）が、別のエンコーディング（"ZZZ"）に同等の表現を持たない場合に発生します。これは、文字セットの範囲が異なる場合に起こります。 - 原因
- 文字セットの範囲
変換元エンコーディングには存在するが、変換先エンコーディングには存在しない文字（例えば、特定の絵文字や特殊文字）を変換しようとしている。
- 文字セットの範囲
- 例
-- UTF-8からSJISへ、SJISで表現できない文字を変換しようとする SELECT convert(''::text::bytea, 'UTF8', 'SJIS'); -- 絵文字はSJISでは表現できない
- 説明
-
ERROR: invalid input syntax for type bytea- 説明
bytea型への入力値の構文が正しくない場合に発生します。これは主にdecode関数や、リテラルとしてbyteaを直接指定する際に起こります。 - 原因
decode関数で指定するformat（'hex', 'base64', 'escape'）と、実際のstringの形式が一致していない。byteaリテラル（E'\\x...'や'\x...'）の形式が間違っている。
- 例
SELECT decode('InvalidHex', 'hex'); -- 'InvalidHex'は有効な16進数ではない SELECT '\xG1'::bytea; -- 'G'は16進数ではない
- 説明
-
ERROR: out of memory/ERROR: invalid memory alloc request size- 説明
非常に大きなバイナリデータをテキスト形式（特に'hex'や'base64'）に変換しようとした際に、メモリが不足したり、PostgreSQLが扱える最大サイズを超えたりした場合に発生することがあります。pg_dumpでbytea型のデータを含むテーブルをダンプする際にも発生することがあります。 - 原因
- データサイズの過大
変換しようとしているバイナリデータが非常に大きい。テキスト形式にエンコードすると、通常、元のバイナリデータよりもサイズが大きくなります（例：hexは2倍、base64は約1.33倍）。
- データサイズの過大
- 例
pg_dumpで大きなbytea列を持つテーブルをダンプしようとした際。
- 説明
-
エンコーディングの確認と調整
- データベースのエンコーディングを確認
SHOW SERVER_ENCODING; SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'your_database_name'; - クライアントのエンコーディングを確認
- psqlの場合:
\encodingコマンドで現在のクライアントエンコーディングを確認できます。\encoding UTF8のように設定することも可能です。 - アプリケーションの場合: 使用しているプログラミング言語（Python, Java, PHPなど）やフレームワークの文字コード設定を確認し、PostgreSQLのエンコーディングと一致させるようにします。多くの場合、UTF-8が推奨されます。
- psqlの場合:
- client_encodingの設定
PostgreSQLセッションのclient_encodingパラメータを明示的に設定することも有効です。SET client_encoding TO 'UTF8'; -- または 'EUC_JP', 'SJIS' など - データベースのエンコーディング変更
もし可能であれば、データベースのエンコーディングをデータに合ったもの、またはUTF-8のような汎用的なものに変更することを検討します（既存のデータがある場合は、ダンプ＆リストアが必要になります）。
- データベースのエンコーディングを確認
-
convert系関数の適切な使用
convert_fromやconvert_toを使用する際には、元のデータが何のエンコーディングで格納されているかを正確に把握することが重要です。間違ったsrc_encodingを指定すると、不正なバイトシーケンスエラーが発生します。- 例えば、あるバイナリデータがSJISでエンコードされたテキストであると分かっている場合:
SELECT convert_from(your_bytea_column, 'SJIS'); - PostgreSQLサーバーが認識できるエンコーディング名を使用しているか確認してください（
SELECT * FROM pg_catalog.pg_encoding;で確認できます）。
-
encode/decode関数の使い方を再確認
encodeやdecodeを使用する場合、formatパラメータ（'hex', 'base64', 'escape'）と実際のデータ形式が一致していることを確認します。- 特に
decodeでは、入力文字列が指定されたフォーマットのルールに厳密に従っている必要があります。少しでも不正な文字や長さの不一致があるとエラーになります。 - 例: 16進数文字列は常に偶数長の文字数である必要があります。
-
大きなバイナリデータの扱い
bytea型の大きなデータを扱う場合、pg_dumpではなくCOPYコマンドのBINARYフォーマットを利用することを検討します。これにより、テキスト変換によるデータ量の増大やメモリの問題を回避できます。- アプリケーション側でデータを処理する場合は、一度に全てのデータをメモリに読み込むのではなく、ストリーム処理を検討します。
-
データ内容の検証
- エラーメッセージに表示される不正なバイトシーケンス（例:
0xYYY）を特定し、そのバイト列がなぜ不正なのか、どの文字エンコーディングであれば有効なのかを調査します。 - 例えば、
0x83のようなバイトが見られる場合、それはSJISの一部である可能性が高いです。
- エラーメッセージに表示される不正なバイトシーケンス（例:
-
環境設定の確認
- PostgreSQLのログファイルを確認し、エラーメッセージの詳細や関連する警告がないか調べます。
- OSのロケール設定がPostgreSQLの動作に影響を与える場合があるので、関連する環境変数（
LC_ALL,LC_CTYPE,LANGなど）も確認します。
PostgreSQL側のSQL関数
まず、PostgreSQL側でデータを操作するための基本的なSQL関数をおさらいします。
convert_from(bytes bytea, src_encoding name): 指定されたエンコーディングのbyteaを、データベースのエンコーディングのtextに変換します。convert_to(string text, dest_encoding name): データベースのエンコーディングのtextを、指定されたエンコーディングのbyteaに変換します。decode(string text, format text): テキスト形式の文字列をbyteaデータにデコードします。encode(data bytea, format text):byteaデータを指定されたテキスト形式（'base64', 'hex', 'escape'）にエンコードします。
Python (psycopg2)
PythonでPostgreSQLを操作するには、psycopg2ライブラリがよく使われます。bytea型はPythonのbytes型にマッピングされます。
import psycopg2
from psycopg2 import Error
# データベース接続情報
DB_HOST = "localhost"
DB_NAME = "your_database"
DB_USER = "your_user"
DB_PASSWORD = "your_password"
try:
# データベースに接続
conn = psycopg2.connect(
host=DB_HOST,
database=DB_NAME,
user=DB_USER,
password=DB_PASSWORD
)
cur = conn.cursor()
# テーブル作成（もし存在しなければ）
cur.execute("""
CREATE TABLE IF NOT EXISTS binary_data_storage (
id SERIAL PRIMARY KEY,
raw_bytes BYTEA,
hex_encoded_text TEXT,
base64_encoded_text TEXT
);
""")
conn.commit()
# --- データの挿入 ---
original_text = "こんにちは、世界！"
# Pythonの文字列をUTF-8バイト列にエンコード
original_bytes = original_text.encode('utf-8')
# PostgreSQLのencode関数を使ってHEX形式とBase64形式に変換して挿入
cur.execute("""
INSERT INTO binary_data_storage (raw_bytes, hex_encoded_text, base64_encoded_text)
VALUES (%s, encode(%s, 'hex'), encode(%s, 'base64'));
""", (original_bytes, original_bytes, original_bytes))
conn.commit()
print(f"挿入成功: '{original_text}'")
# --- データの取得と変換 ---
cur.execute("SELECT id, raw_bytes, hex_encoded_text, base64_encoded_text FROM binary_data_storage ORDER BY id DESC LIMIT 1;")
result = cur.fetchone()
if result:
id, raw_bytes_from_db, hex_text_from_db, base64_text_from_db = result
print("\n--- 取得データ ---")
print(f"ID: {id}")
print(f"RAW BYTEA (Python bytes): {raw_bytes_from_db}")
print(f"HEX ENCODED TEXT: {hex_text_from_db}")
print(f"BASE64 ENCODED TEXT: {base64_text_from_db}")
# Python側でのデコード
decoded_from_raw_bytes = raw_bytes_from_db.decode('utf-8')
print(f"RAW BYTEAからPythonでデコード: '{decoded_from_raw_bytes}'")
import binascii
decoded_from_hex_text = binascii.unhexlify(hex_text_from_db.encode('utf-8')).decode('utf-8')
print(f"HEX TEXTからPythonでデコード: '{decoded_from_hex_text}'")
import base64
decoded_from_base64_text = base64.b64decode(base64_text_from_db.encode('utf-8')).decode('utf-8')
print(f"BASE64 TEXTからPythonでデコード: '{decoded_from_base64_text}'")
# PostgreSQLのdecode関数を使って、HEX/BASE64 TEXTからBYTEAに戻す例
cur.execute("""
SELECT decode(%s, 'hex'), decode(%s, 'base64');
""", (hex_text_from_db, base64_text_from_db))
decoded_bytea_from_sql_hex, decoded_bytea_from_sql_base64 = cur.fetchone()
print(f"SQLのdecode(HEX)からPythonでデコード: '{decoded_bytea_from_sql_hex.decode('utf-8')}'")
print(f"SQLのdecode(BASE64)からPythonでデコード: '{decoded_bytea_from_sql_base64.decode('utf-8')}'")
# --- 文字エンコーディング変換の例 (convert_to/convert_from) ---
# データベースエンコーディングがUTF-8であると仮定
japanese_text_euc_jp = "日本語EUC-JP".encode('euc_jp') # EUC-JPバイト列をPythonで作成
# EUC-JPバイト列をPostgreSQLに挿入し、UTF8に変換して取得
cur.execute("""
INSERT INTO binary_data_storage (raw_bytes) VALUES (%s);
""", (japanese_text_euc_jp,))
conn.commit()
# PostgreSQLでEUC-JPからUTF-8に変換
cur.execute("""
SELECT convert_from(raw_bytes, 'EUC_JP') FROM binary_data_storage WHERE raw_bytes = %s;
""", (japanese_text_euc_jp,))
converted_text = cur.fetchone()[0]
print(f"\nPostgreSQLでEUC_JPからUTF8に変換されたテキスト: '{converted_text}'")
# UTF-8テキストをEUC-JPのbyteaに変換して挿入
cur.execute("""
INSERT INTO binary_data_storage (raw_bytes) VALUES (convert_to(%s, 'EUC_JP'));
""", ("新しい日本語",))
conn.commit()
print("新しい日本語をEUC_JPバイナリとして挿入しました。")
except Error as e:
print(f"データベースエラー: {e}")
finally:
if conn:
cur.close()
conn.close()
print("データベース接続を閉じました。")
解説
convert_toやconvert_fromは、特定のエンコーディングのテキストバイト列をPostgreSQL上で変換する際に使います。これは、異なる文字エンコーディングのテキストデータを扱う場合に特に重要です。encode関数でテキスト形式にエンコードされたデータは、Python側でbinascii.unhexlifyやbase64.b64decodeを使ってデコードできます。- データベースから
bytea型のデータ（raw_bytes_from_db）を取得した場合、それはPythonのbytes型なので、.decode('utf-8')などでデコードすることで通常の文字列に戻せます。 - Pythonの文字列を
byteaとしてデータベースに挿入する際は、.encode('utf-8')などで明示的にバイト列に変換します。 psycopg2はbytea型のデータをPythonのbytes型として扱います。
Java (JDBC)
Javaでは、bytea型は通常byte[]配列にマッピングされます。
import java.sql.*;
import java.io.UnsupportedEncodingException;
import java.util.Base64; // Java 8以降
public class BinaryStringConverter {
private static final String DB_URL = "jdbc:postgresql://localhost:5432/your_database";
private static final String DB_USER = "your_user";
private static final String DB_PASSWORD = "your_password";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// データベースに接続
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
System.out.println("データベースに接続しました。");
// テーブル作成（もし存在しなければ）
Statement stmt = conn.createStatement();
stmt.execute("""
CREATE TABLE IF NOT EXISTS binary_data_storage (
id SERIAL PRIMARY KEY,
raw_bytes BYTEA,
hex_encoded_text TEXT,
base64_encoded_text TEXT
);
""");
System.out.println("テーブルが準備できました。");
// --- データの挿入 ---
String originalText = "こんにちは、Java！";
byte[] originalBytes = originalText.getBytes("UTF-8"); // Java文字列をUTF-8バイト列に
// PostgreSQLのencode関数を使ってHEX形式とBase64形式に変換して挿入
String sqlInsert = "INSERT INTO binary_data_storage (raw_bytes, hex_encoded_text, base64_encoded_text) VALUES (?, encode(?::bytea, 'hex'), encode(?::bytea, 'base64'))";
pstmt = conn.prepareStatement(sqlInsert);
pstmt.setBytes(1, originalBytes); // raw_bytes (bytea)
pstmt.setBytes(2, originalBytes); // encode関数の引数
pstmt.setBytes(3, originalBytes); // encode関数の引数
pstmt.executeUpdate();
System.out.println("挿入成功: '" + originalText + "'");
// --- データの取得と変換 ---
String sqlSelect = "SELECT id, raw_bytes, hex_encoded_text, base64_encoded_text FROM binary_data_storage ORDER BY id DESC LIMIT 1;";
pstmt = conn.prepareStatement(sqlSelect);
rs = pstmt.executeQuery();
if (rs.next()) {
int id = rs.getInt("id");
byte[] rawBytesFromDb = rs.getBytes("raw_bytes");
String hexTextFromDb = rs.getString("hex_encoded_text");
String base64TextFromDb = rs.getString("base64_encoded_text");
System.out.println("\n--- 取得データ ---");
System.out.println("ID: " + id);
System.out.println("RAW BYTEA (Java byte[]): " + new String(rawBytesFromDb, "UTF-8"));
System.out.println("HEX ENCODED TEXT: " + hexTextFromDb);
System.out.println("BASE64 ENCODED TEXT: " + base64TextFromDb);
// Java側でのデコード
String decodedFromRawBytes = new String(rawBytesFromDb, "UTF-8");
System.out.println("RAW BYTEAからJavaでデコード: '" + decodedFromRawBytes + "'");
// HEXデコード (PostgreSQLのHEX形式は \x から始まるので除去して変換)
if (hexTextFromDb != null && hexTextFromDb.startsWith("\\x")) {
String cleanHex = hexTextFromDb.substring(2);
byte[] decodedHexBytes = hexStringToByteArray(cleanHex);
System.out.println("HEX TEXTからJavaでデコード: '" + new String(decodedHexBytes, "UTF-8") + "'");
}
// Base64デコード
byte[] decodedBase64Bytes = Base64.getDecoder().decode(base64TextFromDb);
System.out.println("BASE64 TEXTからJavaでデコード: '" + new String(decodedBase64Bytes, "UTF-8") + "'");
// PostgreSQLのdecode関数を使って、HEX/BASE64 TEXTからBYTEAに戻す例
String sqlDecode = "SELECT decode(?, 'hex'), decode(?, 'base64')";
pstmt = conn.prepareStatement(sqlDecode);
pstmt.setString(1, hexTextFromDb);
pstmt.setString(2, base64TextFromDb);
ResultSet decodeRs = pstmt.executeQuery();
if (decodeRs.next()) {
byte[] decodedByteaFromSqlHex = decodeRs.getBytes(1);
byte[] decodedByteaFromSqlBase64 = decodeRs.getBytes(2);
System.out.println("SQLのdecode(HEX)からJavaでデコード: '" + new String(decodedByteaFromSqlHex, "UTF-8") + "'");
System.out.println("SQLのdecode(BASE64)からJavaでデコード: '" + new String(decodedByteaFromSqlBase64, "UTF-8") + "'");
}
decodeRs.close();
// --- 文字エンコーディング変換の例 (convert_to/convert_from) ---
// データベースエンコーディングがUTF-8であると仮定
String japaneseText = "日本語EUC-JP";
byte[] japaneseBytesEUC_JP = japaneseText.getBytes("EUC_JP"); // EUC-JPバイト列をJavaで作成
// EUC-JPバイト列をPostgreSQLに挿入
pstmt = conn.prepareStatement("INSERT INTO binary_data_storage (raw_bytes) VALUES (?)");
pstmt.setBytes(1, japaneseBytesEUC_JP);
pstmt.executeUpdate();
// PostgreSQLでEUC-JPからUTF-8に変換して取得
pstmt = conn.prepareStatement("SELECT convert_from(raw_bytes, 'EUC_JP') FROM binary_data_storage WHERE raw_bytes = ?");
pstmt.setBytes(1, japaneseBytesEUC_JP);
rs = pstmt.executeQuery();
if(rs.next()){
String convertedText = rs.getString(1);
System.out.println("\nPostgreSQLでEUC_JPからUTF8に変換されたテキスト: '" + convertedText + "'");
}
// UTF-8テキストをEUC-JPのbyteaに変換して挿入
pstmt = conn.prepareStatement("INSERT INTO binary_data_storage (raw_bytes) VALUES (convert_to(?, 'EUC_JP'))");
pstmt.setString(1, "新しい日本語");
pstmt.executeUpdate();
System.out.println("新しい日本語をEUC_JPバイナリとして挿入しました。");
}
} catch (SQLException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
System.out.println("データベース接続を閉じました。");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 16進数文字列をバイト配列に変換するヘルパーメソッド
public static byte[] hexStringToByteArray(String s) {
int len = s.length();
byte[] data = new byte[len / 2];
for (int i = 0; i < len; i += 2) {
data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
+ Character.digit(s.charAt(i+1), 16));
}
return data;
}
}
解説
- Base64エンコード/デコードには
java.util.Base64クラスを使用します。 - PostgreSQLの
encode関数によってHEX形式で取得された文字列は、\xプレフィックスが付いているため、Java側でデコードする際にはそれを取り除く必要があります。hexStringToByteArrayのようなヘルパーメソッドが役立ちます。 - Javaの文字列をPostgreSQLの
byteaとして扱うには、String.getBytes("UTF-8")などでバイト列に変換します。 - JDBCでは、
bytea型のデータをPreparedStatement.setBytes()で設定し、ResultSet.getBytes()で取得します。
Node.js (pg)
Node.jsでは、pgライブラリがPostgreSQLとの連携に使われます。bytea型は通常Node.jsのBufferオブジェクトにマッピングされます。
const { Client } = require('pg');
// データベース接続情報
const dbConfig = {
user: 'your_user',
host: 'localhost',
database: 'your_database',
password: 'your_password',
port: 5432,
};
async function runExample() {
const client = new Client(dbConfig);
try {
await client.connect();
console.log('データベースに接続しました。');
// テーブル作成（もし存在しなければ）
await client.query(`
CREATE TABLE IF NOT EXISTS binary_data_storage (
id SERIAL PRIMARY KEY,
raw_bytes BYTEA,
hex_encoded_text TEXT,
base64_encoded_text TEXT
);
`);
console.log('テーブルが準備できました。');
// --- データの挿入 ---
const originalText = "こんにちは、Node.js！";
// Node.jsの文字列をUTF-8 Bufferに
const originalBuffer = Buffer.from(originalText, 'utf-8');
// PostgreSQLのencode関数を使ってHEX形式とBase64形式に変換して挿入
const insertQuery = `
INSERT INTO binary_data_storage (raw_bytes, hex_encoded_text, base64_encoded_text)
VALUES ($1, encode($2::bytea, 'hex'), encode($3::bytea, 'base64'));
`;
await client.query(insertQuery, [originalBuffer, originalBuffer, originalBuffer]);
console.log(`挿入成功: '${originalText}'`);
// --- データの取得と変換 ---
const selectQuery = `
SELECT id, raw_bytes, hex_encoded_text, base64_encoded_text
FROM binary_data_storage ORDER BY id DESC LIMIT 1;
`;
const res = await client.query(selectQuery);
if (res.rows.length > 0) {
const row = res.rows[0];
const id = row.id;
const rawBytesFromDb = row.raw_bytes; // Bufferオブジェクトとして取得
const hexTextFromDb = row.hex_encoded_text;
const base64TextFromDb = row.base64_encoded_text;
console.log('\n--- 取得データ ---');
console.log(`ID: ${id}`);
console.log(`RAW BYTEA (Node.js Buffer): ${rawBytesFromDb.toString('utf-8')}`);
console.log(`HEX ENCODED TEXT: ${hexTextFromDb}`);
console.log(`BASE64 ENCODED TEXT: ${base64TextFromDb}`);
// Node.js側でのデコード
const decodedFromRawBytes = rawBytesFromDb.toString('utf-8');
console.log(`RAW BYTEAからNode.jsでデコード: '${decodedFromRawBytes}'`);
// HEXデコード (PostgreSQLのHEX形式は \x から始まるので除去して変換)
if (hexTextFromDb && hexTextFromDb.startsWith('\\x')) {
const cleanHex = hexTextFromDb.substring(2);
const decodedHexBuffer = Buffer.from(cleanHex, 'hex');
console.log(`HEX TEXTからNode.jsでデコード: '${decodedHexBuffer.toString('utf-8')}'`);
}
// Base64デコード
const decodedBase64Buffer = Buffer.from(base64TextFromDb, 'base64');
console.log(`BASE64 TEXTからNode.jsでデコード: '${decodedBase64Buffer.toString('utf-8')}'`);
// PostgreSQLのdecode関数を使って、HEX/BASE64 TEXTからBYTEAに戻す例
const decodeSQLQuery = `
SELECT decode($1, 'hex'), decode($2, 'base64');
`;
const decodeRes = await client.query(decodeSQLQuery, [hexTextFromDb, base64TextFromDb]);
const decodedByteaFromSqlHex = decodeRes.rows[0].decode; // 'decode'はPostgreSQLの関数名
const decodedByteaFromSqlBase64 = decodeRes.rows[0].decode1; // 2番目のカラムは'decode1'などになる
console.log(`SQLのdecode(HEX)からNode.jsでデコード: '${decodedByteaFromSqlHex.toString('utf-8')}'`);
console.log(`SQLのdecode(BASE64)からNode.jsでデコード: '${decodedByteaFromSqlBase64.toString('utf-8')}'`);
// --- 文字エンコーディング変換の例 (convert_to/convert_from) ---
// データベースエンコーディングがUTF-8であると仮定
const japaneseTextEUC_JP = "日本語EUC-JP";
const japaneseBufferEUC_JP = Buffer.from(japaneseTextEUC_JP, 'eucjp'); // EUC-JP BufferをNode.jsで作成
// EUC-JPバイト列をPostgreSQLに挿入
await client.query("INSERT INTO binary_data_storage (raw_bytes) VALUES ($1)", [japaneseBufferEUC_JP]);
// PostgreSQLでEUC-JPからUTF-8に変換して取得
const convertFromQuery = `
SELECT convert_from(raw_bytes, 'EUC_JP') FROM binary_data_storage WHERE raw_bytes = $1;
`;
const convertFromRes = await client.query(convertFromQuery, [japaneseBufferEUC_JP]);
if(convertFromRes.rows.length > 0){
const convertedText = convertFromRes.rows[0].convert_from;
console.log(`\nPostgreSQLでEUC_JPからUTF8に変換されたテキスト: '${convertedText}'`);
}
// UTF-8テキストをEUC-JPのbyteaに変換して挿入
const convertToQuery = `
INSERT INTO binary_data_storage (raw_bytes) VALUES (convert_to($1, 'EUC_JP'));
`;
await client.query(convertToQuery, ["新しい日本語"]);
console.log("新しい日本語をEUC_JPバイナリとして挿入しました。");
}
} catch (err) {
console.error('エラーが発生しました:', err);
} finally {
await client.end();
console.log('データベース接続を閉じました。');
}
}
runExample();
- Base64変換は
Buffer.from(base64String, 'base64')とbuffer.toString('base64')で行います。 - PostgreSQLのHEX形式（
\x...）からNode.jsのBufferに変換する場合も、\xを取り除いてBuffer.from(hexString, 'hex')を使用します。 - 文字列から
Bufferへの変換はBuffer.from(string, encoding)で行い、Bufferから文字列への変換はbuffer.toString(encoding)で行います。 - Node.jsでは、
bytea型のデータはBufferオブジェクトとして扱われます。
- データベース内で直接変換せずに、アプリケーション側でバイナリデータのエンコード/デコード処理を行う。
bytea型以外の方法でバイナリデータを格納する。
それぞれについて詳しく説明します。
アプリケーション側でのエンコード/デコード処理
PostgreSQLのencode()やdecode()関数は非常に便利ですが、全ての処理をデータベースに任せる必要はありません。特に、データ量が多い場合や、アプリケーション側で特定のフォーマットに変換する必要がある場合は、アプリケーション側で処理を行う方が効率的であったり、柔軟性が高かったりします。
メリット
- 複数の異なるデータベースシステムへの移植性を高めやすい。
- アプリケーションのロジックで自由にエンコード/デコード方式を選択できる（PostgreSQLがサポートしない形式も可能）。
- データベースサーバーの負荷軽減。
デメリット
- SQLクエリ内で直接バイナリデータを操作する柔軟性が失われる。
- アプリケーションコードが少し複雑になる。
具体的な方法
-
Base64 変換
-
PostgreSQL側のデータ型
TEXTまたはVARCHAR -
アプリケーション側の処理
- 保存時
バイナリデータを各言語のBase64エンコーディング関数（Python:base64.b64encode, Java:Base64.getEncoder().encodeToString, Node.js:Buffer.toString('base64')）でテキストに変換してからデータベースに挿入します。 - 取得時
データベースからBase64文字列として取得し、各言語のBase64デコーディング関数（Python:base64.b64decode, Java:Base64.getDecoder().decode, Node.js:Buffer.from(string, 'base64')）でバイナリデータに戻します。
- 保存時
-
Java例
import java.util.Base64; byte[] originalBytes = "Hello, Binary Data!".getBytes("UTF-8"); String base64_string = Base64.getEncoder().encodeToString(originalBytes); // バイト列をBase64文字列に // -> DBに保存 (TEXT型) // DBから取得したbase64_stringをデコード byte[] decoded_bytes = Base64.getDecoder().decode(base64_string);
-
-
-
PostgreSQL側のデータ型
TEXTまたはVARCHAR -
アプリケーション側の処理
- 保存時
バイナリデータを各言語の16進数エンコーディング関数（Python:binascii.hexlify, Java:DatatypeConverter.printHexBinary/ 自作ヘルパー、Node.js:Buffer.toString('hex')）でテキストに変換してからデータベースに挿入します。 - 取得時
データベースから16進数文字列として取得し、各言語の16進数デコーディング関数（Python:binascii.unhexlify, Java:DatatypeConverter.parseHexBinary/ 自作ヘルパー、Node.js:Buffer.from(string, 'hex')）でバイナリデータに戻します。
- 保存時
-
Python例
import binascii original_bytes = b"Hello, Binary Data!" hex_string = binascii.hexlify(original_bytes).decode('ascii') # バイト列を16進数文字列に # -> DBに保存 (TEXT型) # DBから取得したhex_stringをデコード decoded_bytes = binascii.unhexlify(hex_string.encode('ascii'))
-
bytea型以外のバイナリデータ格納方法
大規模なバイナリデータ（画像、動画、ドキュメントなど）を扱う場合、bytea型にはいくつかの制約や推奨されない点があります。
- byteaの制約
- PostgreSQLのデフォルト設定では、
bytea型の最大サイズは約1GBです。（実際に数GBを格納することは可能ですが、メモリ使用量や性能の観点から非推奨です）。 - インデックス作成が困難、検索効率が低い。
- データベースのバックアップ/リストアに時間がかかる。
- データベースファイルが肥大化する。
- PostgreSQLのデフォルト設定では、
これらの問題を回避するための代替手段です。
-
Large Object (ラージオブジェクト) 機能の利用
- PostgreSQLの機能
PostgreSQLには「ラージオブジェクト」と呼ばれる、ファイルシステムのようなインターフェースをデータベース内で提供する機能があります。これは、lo_create(),lo_open(),lo_read(),lo_write()などの関数を使って操作します。データはpg_largeobjectというシステムカタログに格納されます。 - 方法
lo_create()でラージオブジェクトID（OID）を取得し、そのOIDをINTEGER型などでテーブルに格納します。バイナリデータ自体はラージオブジェクト関数を使って読み書きします。 - メリット
byteaのサイズ制約を受けない（理論上は無制限）。- データベーストランザクション内で管理できるため、データの整合性が保たれる。
- ストリームアクセスが可能。
- デメリット
byteaよりも操作が複雑になる（専用の関数を使用）。- 専用の
lo_unlink()関数で明示的に削除しないと、オブジェクトが残り続けてデータベースが肥大化する（「orphan large objects」）。 - 多くのドライバが
byteaを直接サポートするのに対し、ラージオブジェクトは追加のAPI呼び出しが必要な場合がある。
- 使用例
データベース内で管理したいが、非常に大きいデータ（動画のサムネイル、複雑なレポートのPDFなど）。
- PostgreSQLの機能
-
ファイルシステムへの格納
- 方法
バイナリデータをファイルシステム（サーバー上のディレクトリ、S3などのオブジェクトストレージ）に保存し、データベースにはそのファイルのパス（URLなど）のみをTEXT型などで格納します。 - メリット
- データベースのサイズを小さく保てる。
- ファイルのI/OがデータベースI/Oとは独立して行えるため、スケーラビリティが高い。
- ウェブサーバーなどで直接ファイルを提供できる。
- 非常に大きなファイルを扱える。
- デメリット
- データベースとファイルシステムの一貫性を保つためのロジック（ファイル削除、バックアップなど）をアプリケーション側で実装する必要がある。
- トランザクションの原子性（ACID特性）がファイルシステム操作には適用されない。
- ファイルシステムへのアクセス権限管理が必要。
- 使用例
Webサイトのユーザーアバター、アップロードされたドキュメント、ログファイルなど。
- 方法
- 大規模なバイナリデータで、データベーストランザクションの整合性を保ちたいが、byteaの制約を超えたい場合
- 検討
PostgreSQLのLarge Object機能。ただし、管理の複雑さを考慮に入れる必要がある。
- 検討
- 大規模なバイナリデータ（数十MB〜GB単位）や、ファイルとしての管理が妥当な場合
- 推奨
ファイルシステム（またはオブジェクトストレージ）への格納 + データベースにはパスを保存。
- 推奨
- 小〜中規模のバイナリデータで、SQL内の変換を避けたい、または特定のエンコーディングをアプリケーションで制御したい場合
- 推奨
TEXT型（HEXまたはBase64エンコード） + アプリケーション側でのエンコード/デコード。
- 推奨
- 小〜中規模のバイナリデータ（数MB〜数十MB程度まで）で、SQL内で直接操作したい場合
- 推奨
bytea型 + PostgreSQLのencode/decode関数。シンプルで使いやすい。
- 推奨