바이너리 문자열 함수·연산자

바이너리 문자열 함수·연산자 (bytea)

이진 데이터를 다룰 때는 텍스트 함수만으로는 부족할 때가 많아요. PostgreSQL에는 bytea 타입, 즉 바이너리 문자열을 검사하고 조작하기 위한 함수·연산자가 마련되어 있어요. 대부분은 앞서 본 텍스트 문자열 함수와 목적·문법이 동일해서, 텍스트 함수를 알면 반은 이해한 셈이에요. 핵심 함수와 예시를 중심으로 살펴볼게요.

출처: PostgreSQL 공식 문서 — Binary String Functions and Operators

SQL 표준 바이너리 문자열 함수·연산자

SQL 표준에서는 일부 문자열 함수가 인자를 콤마 대신 키워드로 구분해요. PostgreSQL도 이들을 지원하는데, 주요 예는 이런 것들이에요.

함수/연산자	설명	예시
`bytea` `\|\|` `bytea` → `bytea`	두 바이너리 문자열을 이어 붙여요.	`'\x123456'::bytea \|\| '\x789a00bcde'::bytea` → `\x123456789a00bcde`
`bit_length(bytea)` → `integer`	문자열의 비트 수를 반환해요(`octet_length`의 8배).	`bit_length('\x123456'::bytea)` → `24`
`octet_length(bytea)` → `integer`	문자열의 바이트 수를 반환해요.	`octet_length('\x123456'::bytea)` → `3`
`btrim(bytes, bytesremoved)` → `bytea`	시작과 끝에서 `bytesremoved`에 포함된 바이트만으로 이뤄진 가장 긴 문자열을 제거해요.	`btrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x345678`
`ltrim(bytes, bytesremoved)` → `bytea`	시작에서만 제거해요.	`ltrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x34567890`
`rtrim(bytes, bytesremoved)` → `bytea`	끝에서만 제거해요.	`rtrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x12345678`
`position(substring IN bytes)` → `integer`	`bytes` 안에서 지정한 `substring`의 첫 시작 인덱스를 반환해요. 없으면 0.	`position('\x5678'::bytea in '\x1234567890'::bytea)` → `3`
`substring(bytes [FROM start] [FOR count])` → `bytea`	`start`번째 바이트에서 시작해 `count` 바이트까지 추출해요.	`substring('\x1234567890'::bytea from 3 for 2)` → `\x5678`
`overlay(bytes PLACING new FROM start [FOR count])` → `bytea`	`start`번째부터 `count` 바이트를 `new`로 교체해요. `count` 생략 시 `new`의 길이로 기본값 적용.	`overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3)` → `\x12020390`
`trim([LEADING\|TRAILING\|BOTH] bytesremoved FROM bytes)` → `bytea`	시작·끝·양쪽에서 제거해요(기본 `BOTH`).	`trim('\x9012'::bytea from '\x1234567890'::bytea)` → `\x345678`

그 밖의 바이너리 문자열 함수

추가 함수들 중 일부는 위 SQL 표준 함수를 구현하기 위해 내부적으로 쓰여요. 자주 쓰는 것들을 볼게요.

bit_count(bytes) → bigint: 설정된 비트 수(popcount)를 반환해요. bit_count('\x1234567890'::bytea) → 15
get_byte(bytes, n) → integer / set_byte(bytes, n, newvalue) → bytea: n번째 바이트를 읽거나 설정해요. get_byte('\x1234567890'::bytea, 4) → 144, set_byte('\x1234567890'::bytea, 4, 64) → \x1234567840
get_bit(bytes, n) → integer / set_bit(bytes, n, newvalue) → bytea: n번째 비트를 읽거나 설정해요. get_bit('\x1234567890'::bytea, 30) → 1
length(bytea) → integer: 바이트 수를 반환해요. length('\x1234567890'::bytea) → 5. 인코딩을 지정한 length(bytes, encoding)은 그 인코딩 기준 문자 수를 반환해요.
md5(bytea) → text / sha224·sha256·sha384·sha512(bytea) → bytea: 해시를 계산해요. md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18b4958c334c82d8b1
crc32(bytea) → bigint / crc32c(bytea) → bigint: CRC-32(또는 CRC-32C) 값을 계산해요. crc32('abc'::bytea) → 891568578
reverse(bytea) → bytea: 바이트 순서를 뒤집어요. reverse('\xabcd'::bytea) → \xcdab
substr(bytes, start [, count]) → bytea: substring과 동일해요.

get_byte·set_byte는 첫 바이트를 바이트 0으로 셉니다. get_bit·set_bit는 각 바이트 안에서 오른쪽부터 비트를 세는데, 예를 들어 비트 0은 첫 바이트의 최하위 비트, 비트 15는 두 번째 바이트의 최상위 비트예요.

헷갈리기 쉬운 지점: md5는 역사적인 이유로 text 타입의 16진수 값을 반환해요. 반면 SHA-2 계열은 bytea를 반환하지요. 둘 사이를 오갈 땐 encode·decode 함수를 쓰면 돼요. 16진수 텍스트로 얻으려면 encode(sha256('abc'), 'hex'), bytea 값을 얻으려면 decode(md5('abc'), 'hex')처럼요.

텍스트/바이너리 변환 함수

문자 집합(인코딩) 간 변환이나, 임의 바이너리 데이터를 텍스트 형태로 나타내는 함수들이에요. 이 함수들에서 text 타입은 데이터베이스 기본 인코딩으로, bytea 타입은 다른 인자가 지정한 인코딩으로 해석돼요.

convert(bytes, src_encoding, dest_encoding) → bytea: 한 인코딩의 텍스트를 나타내는 바이너리 문자열을 다른 인코딩으로 변환해요. convert('text_in_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638
convert_from(bytes, src_encoding) → text: 바이너리 문자열을 데이터베이스 인코딩의 text로 변환해요.
convert_to(string, dest_encoding) → bytea: text를 지정 인코딩의 바이너리 문자열로 변환해요.
encode(bytes, format) → text / decode(string, format) → bytea: 바이너리 데이터를 텍스트 표기로 인코딩하거나 되돌려요. 지원 형식은 base64, escape, hex 세 가지예요. encode('123\000\001', 'base64') → MTIzAAE=

`encode`·`decode`가 지원하는 형식

base64: RFC 2045 Section 6.8 형식이에요. 인코딩된 줄은 76자에서 끊기는데, MIME의 CRLF 대신 줄바꿈(newline)만 사용해요. decode는 캐리지 리턴·줄바꿈·공백·탭 문자를 무시하고, 잘못된 base64 데이터(잘못된 패딩 포함)가 들어오면 오류를 내요.
escape: 0바이트와 상위 비트가 설정된 바이트를 8진수 이스케이프(\nnn)로 변환하고, 백슬래시는 두 배로 늘려요. 나머지는 그대로 표현해요. decode는 백슬래시 뒤에 백슬래시 하나 또는 8진수 세 자리가 오지 않으면 오류를 내요.
hex: 4비트마다 16진수 한 자리(0~~f)로 표현하고, 각 바이트의 상위 자리를 먼저 써요. encode는 a~~f를 소문자로 출력해요. 가장 작은 데이터 단위가 8비트라 encode의 결과 문자 수는 항상 짝수예요. decode는 대문자·소문자 모두 받고, 홀수 개 문자처럼 잘못된 hex 데이터면 오류를 내요.

정수와 `bytea` 사이의 형변환

정수 값을 bytea로 캐스팅하면 정수 타입의 너비에 따라 2·4·8바이트가 돼요. 결과는 2의 보수(two's complement) 표현이며 최상위 바이트가 먼저 와요.

1234::smallint::bytea        → \x04d2
cast(1234 as bytea)          → \x000004d2
cast(-1234 as bytea)         → \xfffffb2e
'\x8000'::bytea::smallint    → -32768
'\x8000'::bytea::integer     → 32768

bytea를 정수로 캐스팅할 때 bytea 길이가 정수 타입 너비를 초과하면 오류가 발생해요. 또한 집계 함수 string_agg(Section 9.21)와 large object 함수(Section 33.4)도 함께 참고하면 좋아요.

바이너리 문자열 함수·연산자