TL;DR

  • Postgres에는 테이블 별칭, 함수 호출, 형 변환, 복합 타입 등에서 직관과 다른 동작과 유용한 기능이 다수 존재함.
  • 별칭에 열 이름을 일부만 지정해도 나머지 열은 유지되며, select u from u는 테이블에 u 열이 있는지에 따라 열 또는 행 전체를 선택함.
  • Postgres는 열과 함수를 서로의 호출 문법으로 참조할 수 있고, 열 문법에서는 열이 함수보다 우선하며 함수 문법에서는 함수가 우선함.
  • 문자열의 unknown 타입은 연산자나 함수에 전달되기 전에 적절한 타입으로 변환될 수 있고, 연산자와 함수 모두 오버로딩을 지원함.
  • 테이블은 같은 이름의 복합 타입을 만들며, 테이블 반환 함수·간단한 table 문·인수 없는 select 등 다양한 SQL 문법도 제공함.

개요

  • Postgres에는 명확히 드러나지 않는 기능과 동작이 많으며, Squawk를 개발하면서 이러한 사례를 접함.

별칭은 추가 열을 가리지 않음

  • 테이블 별칭에 원본 테이블보다 적은 수의 열 이름을 지정하면, 이름을 지정하지 않은 나머지 열도 결과에 그대로 포함됨.
  • 예를 들어 세 열 a, b, c가 있는 테이블을 u (x, y)로 별칭 지정하면 a와 b는 각각 x, y로 바뀌고 c는 그대로 유지됨.

테이블인가, 열인가?

  • select u from u에서 선택 대상인 u가 테이블인지 열인지는 테이블 구조에 따라 달라짐.
  • 테이블 u에 u라는 열이 있으면 해당 열을 선택함.
  • u라는 열이 없으면 테이블 자체를 행으로 선택하며, 결과는 (1,2,3)처럼 표시됨.

함수 호출 식과 열 호출 문법

  • Postgres에서는 열을 일반적인 방식인 select a from t 또는 테이블 이름을 붙인 select t.a from t로 조회할 수 있음.
  • 함수 호출 문법을 이용해 select a(t) from t처럼 열 a를 선택할 수도 있음.
  • 반대로 d(t) 형태로 정의한 함수를 select t.d from t처럼 열 스타일 문법으로 호출할 수 있으며, 이는 select d(t) from t와 같음.
  • 두 문법이 충돌하면 열 스타일 문법에서는 열이 우선하고, 함수 스타일 문법에서는 함수가 우선함.

형 변환

  • SQL 표준 형 변환 문법으로 cast('1' as bigint)를 쓸 수 있으며, Postgres는 treat('1' as bigint)도 허용함.
  • Postgres 고유의 축약 문법은 '1'::bigint이며, 이중 콜론 문법이 더 간결하고 권장됨.
  • 선행 타입 문법인 bigint '1'도 사용할 수 있으며, 이는 함수 호출 형태인 "bigint"('1')와 같음.
  • Squawk는 형 변환 문법 간 전환을 위한 빠른 수정 기능을 제공함.

복합 타입

  • 복합 타입은 Postgres의 테이블과 매우 유사하며, create type employee as (name text, species text)처럼 정의함.
  • 복합 타입을 만든 뒤에는 값 ('Piglet', 'Pig')를 employee로 형 변환하고, (member).name과 (member).species처럼 필드에 접근할 수 있음.
  • create table도 내부적으로 같은 이름의 복합 타입을 만들므로, 테이블 employee가 있으면 employee 타입으로 값을 형 변환해 필드에 접근할 수 있음.
  • create table로 만들어진 employee 타입을 직접 삭제하려 하면 테이블 employee가 타입을 필요로 한다는 오류가 발생하며, 대신 테이블을 삭제하라는 안내가 표시됨.

함수의 테이블 반환

  • text, int8, float 같은 기본 타입 외에도 함수는 테이블을 반환할 수 있음.
  • 예를 들어 정수 인수를 받아 (f1 int, f2 text) 열을 반환하는 함수는 인수와 그 문자열 변환값을 각각 반환하며, (dup(42)).f2처럼 특정 열을 조회할 수 있음.

인수는 1부터 인덱싱됨

  • 함수 정의에서 첫 번째 위치 인수는 $1로 참조함.
  • 이름이 지정된 인수를 사용하면 숫자 인덱싱을 피할 수 있으며, 함수 본문에서 input으로 참조할 수 있음.
  • 호출 시에는 dup(42), dup(input => 42), dup(input := 42) 문법을 사용할 수 있음.

`table`과 `select *`

  • table t는 select * from t와 같은 결과를 내는 축약 문법임.
  • Squawk는 두 문법 간 변환을 위한 빠른 수정 기능을 제공함.

최소한의 `select`

  • 연결 상태 확인에는 흔히 select 1을 사용하지만, 인수 없이 select만 실행하는 더 짧은 문법도 있음.
  • 인수 없는 select는 열을 반환하지 않으며, 이를 하위 쿼리로 사용하면 select count(*) from (select)의 결과는 1임.

`char`와 `"char"`

  • '1'::"char"의 타입은 "char"이고, '1'::char의 타입은 character임.
  • 따옴표 없는 char는 character의 약칭이며 bpchar에 대응함. 이 형식은 구식이므로 text를 사용하는 편이 나음.
  • 따옴표가 있는 "char"는 1바이트만 저장함. 여러 문자가 든 문자열을 "char"로 형 변환하면 첫 문자만 남고 나머지는 무시됨.

알 수 없는 타입

  • 문자열 리터럴은 처음에 unknown 타입으로 취급됨. 숫자처럼 특정 타입으로 변환할 수 있으면 연산자에 전달되기 전에 해당 타입으로 변환됨.
  • 따라서 pg_typeof(1)은 integer, pg_typeof('1')은 unknown을 반환함.
  • '1' + 2는 내부적으로 '1'::integer + 2가 되어 3을 반환하고, '1' || 1은 '1' || 1::text로 처리되어 11을 반환함.
  • 같은 타입 추론과 변환은 함수에도 적용되므로, int8 인수를 받는 add2 함수에 '100'을 전달하면 결과 102를 얻음.
  • 연산자는 오버로딩될 수 있음. ||는 문자열을 이어 붙이는 데 쓰이지만, 정수 배열 두 개를 결합하는 데도 사용할 수 있음.

함수 오버로딩

  • Postgres는 연산자 오버로딩과 마찬가지로 함수 오버로딩을 지원함.
  • 일반적인 함수인 length에는 비트 문자열, bytea, 지정 인코딩의 bytea, character, 선분(lseg), 경로(path), text, tsvector의 길이 또는 개수를 다루는 오버로드가 있음.
  • 더 극단적인 사례인 in_range에는 오버로드가 16개 있음.

결론

  • Postgres에는 유용하면서도 독특한 기능이 다양하게 포함됨.
  • 여전히 제공되는 XML 기능은 여기서 다루지 않은 사례임.