PostgreSQL 데이터 타입

이 장에서는 PostgreSQL 데이터 타입에 대해 논의할 것입니다. 데이터 타입은 테이블을 생성할 때 각 필드에 설정하는 것입니다.

데이터 타입 설정의 이점:

PostgreSQL는 다양한 데이터 타입을 제공합니다. 사용자는 CREATE TYPE 명령어를 사용하여 데이터베이스에서 새로운 데이터 타입을 생성할 수 있습니다. PostgreSQL의 데이터 타입은 많으며, 아래에서 자세히 설명하겠습니다.

수치 타입

수치 타입은 다음과 같습니다. 2 바이트,4 바이트 또는 8 바이트의 정수와 함께 4 바이트 또는 8 바이트의 부호 부호수와 선택 가능한 정밀도의 십진수로 구성됩니다.

가용한 수치 타입은 다음 표에 나와 있습니다.

화폐 타입

money 타입은 고정 소수점 정밀도를 가진 화폐 금액을 저장합니다.

numeric, int 및 bigint 타입의 값은 money로 변환될 수 있으며, 화폐 타입을 처리할 때 부정확한 원단위 오류의 가능성이 있으므로 부호 부호수를 사용하지 않는 것이 좋습니다.

문자 타입

PostgreSQL가 지원하는 문자 타입은 다음 표에 나와 있습니다:

날짜/시간 타입

PostgreSQL가 지원하는 날짜와 시간 타입은 다음 표에 나와 있습니다.

부울 타입

PostgreSQL는 표준 boolean 데이터 타입을 지원합니다.

boolean은 "true"(참) 또는 "false"(거짓) 두 가지 상태와, "unknown"(알 수 없음) 상태가 있으며, NULL로 표시됩니다.

열거 유형

열거 유형은 정적 값과의 순서가 있는 값 집합을 포함하는 데이터 유형입니다.

PostgreSQL의 열거 유형은 C 언어의 enum 유형과 유사합니다.

기타 유형과 달리 열거 유형은 CREATE TYPE 명령어를 사용하여 생성해야 합니다.

CREATE　TYPE　mood　AS　ENUM　('sad',　'ok',　'happy');

일주일 중 몇 일을 생성하는 방법을 보여줍니다:

CREATE　TYPE　week　AS　ENUM　('Mon',　'Tue',　'Wed',　'Thu',　'Fri',　'Sat',　'Sun');

기타 유형과 마찬가지로, 생성된 열거 유형은 테이블과 함수 정의에 사용할 수 있습니다.

CREATE　TYPE　mood　AS　ENUM　('sad',　'ok',　'happy');
CREATE　TABLE　person　(
　　　　name text,
　　　　current_mood　mood
);
INSERT　INTO　person　VALUES　('Moe',　'happy');
SELECT　*　FROM　person　WHERE　current_mood　=　'happy';
　name　|　current_mood　
------+--------------
　Moe　　|　happy
(1　row)

기하학 유형

기하학 데이터 유형은 두 차원 평면 물체를 나타냅니다.

아래 표는 PostgreSQL가 지원하는 기하학 유형을 나열합니다.

가장 기본적인 유형: 점. 이는 다른 유형의 기초입니다.

네트워크 주소 유형

PostgreSQL는 IPv를 저장하기 위해 사용할 수 있는 유형을 제공합니다.4 IPv6 MAC 주소 데이터 유형.

이 데이터 유형을 사용하여 네트워크 주소를 저장하는 것은 텍스트 유형보다 좋습니다.因为这些类型提供输入错误检查和特殊的操作和功能。

inet 또는 cidr 데이터 유형을 정렬할 때 IPv4 IPv 뒤에 항상 주소가 있습니다.6 주소 앞에, IPv에 포장되거나 매핑된 주소도 포함됩니다.6 주소에 있는 IPv4 주소를 의미합니다. 예를 들어 ::10.2.3.4 또는 ::ffff:10.4.3.2입니다.

비트 문자열 타입

비트 문자열은 1 와 0의 문자열로 사용될 수 있습니다. 이들은 비트 마스크를 저장하고 시각화하는 데 사용될 수 있습니다. 우리는 두 가지 SQL 비트 타입이 있습니다: bit(n)과 bit varying(n)， 여기서 n은 정수입니다.

bit 타입의 데이터는 정확하게 길이 n과 일치해야하며, 짧거나 길은 데이터를 저장하려고 시도하면 오류가 발생합니다. bit varying 타입 데이터는 최대 길이 n의 변형 가능 타입입니다. 더 긴 문자열은 거부됩니다. 길이가 없는 bit은 bit(1)， 길이가 없는 bit varying은 길이 제한이 없다는 의미입니다.

텍스트 검색 타입

전체 텍스트 검색은 자연어 문서 집합을 통해 쿼리와 일치하는 검색을 찾는 것입니다.

PostgreSQL은 전체 텍스트 검색을 지원하는 두 가지 데이터 타입을 제공합니다：

UUID 타입

uuid 데이터 타입은 RFC 4122，ISO/IEF 9834-8:2005 及相关标准定义的通用唯一标识符(UUID)。 （一些系统认为这个数据类型为全球唯一标识符，或GUID。） 这个标识符是一个由算法产生的 128 비트 식별자로, 동일한 알고리즘을 사용하여 다른 방식으로 생성된 식별자와는 다를 수 없도록 합니다. 따라서, 분산형 시스템에서는 이 식별자가 시퀀스보다 더 나은 유일성 보장을 제공합니다. 시퀀스는 단일 데이터베이스에서만 유일성을 보장할 수 있습니다.

UUID는 소문자 16진수 시퀀스로 작성되며, 구분자로 나누어 몇 그룹으로 나뉘며, 특히 하나의 그룹8비트 숫자+3그룹4비트 숫자+그룹12비트 숫자를 의미하며, 총 32 개의 숫자는 128 비트, 이 표준의 UUID 예제는 다음과 같습니다:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML 타입

xml 데이터 타입은 XML 데이터를 저장하는 데 사용될 수 있습니다. XML 데이터를 text 타입에 저장하는 장점은 구조적 정확성을 확인할 수 있으며, 또한 함수가 타입 안전성을 확인할 수 있습니다. 이 데이터 타입을 사용하려면, 컴파일 시 configure을 사용해야 합니다. --with-libxml.

xml은 XML 표준에서 정의된 양식이 좋은 "문서"를 저장할 수 있으며, XML 표준에서 정의된 XMLDecl? content 定义的"内容"片段， 大致上，这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。

创建XML值

使用函数 xmlparse: 来从字符数据产生 xml 类型的值：

XMLPARSE(DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE(CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON 类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

此外还有相关的函数来处理 json 数据：

数组类型

PostgreSQL 允许将字段定义成变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp (
　　　　name text,
　　　　pay_by_quarter integer[],
　　　　schedule text[][]
);

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。

我们也可以使用 "ARRAY" 关键字，如下所示：

CREATE TABLE sal_emp (
　　　name text,
　　　pay_by_quarter integer ARRAY[4],
　　　schedule text[][]
);

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
　　　　VALUES ('Bill',
　　　　{10000,　10000,　10000,　10000}
　　　　'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
　　　　VALUES ('Carol',
　　　　{2　25000,　25000,　25000}
　　　　'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

배열 접근

이제 이 테이블에서 몇 가지 쿼리를 실행할 수 있습니다.

먼저, 배열의 하나의 요소에 접근하는 방법을 보여드립니다. 이 쿼리는 두 번째 분기에 급여가 변한 직원 이름을 검색합니다:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
　name
-------
　Carol
(1　row)

배열의 인덱스 숫자는 괄호안에 적혀 있습니다.

배열 수정

우리는 배열의 값을 수정할 수 있습니다:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}
　　　　WHERE name = 'Carol';

또는 ARRAY 구조자 문법을 사용합니다:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
　　　　WHERE name = 'Carol';

배열 검색

배열 내의数值를 검색하려면, 배열의 각 값을 확인해야 합니다.

예를 들어:

SELECT　*　FROM sal_emp WHERE pay_by_quarter[1] =　10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[2] =　10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[3] =　10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[4] =　10000;

또한, 다음 문장을 사용하여 배열 중 모든 요소 값이 10000의 행:

SELECT　*　FROM sal_emp WHERE　10000 = ALL (pay_by_quarter);

또는 generate_subscripts 함수를 사용할 수 있습니다. 예를 들어:

SELECT　*　FROM
　　　(SELECT pay_by_quarter,
　　　　　　　　　　　generate_subscripts(pay_by_quarter,　1) AS s
　　　　　　FROM sal_emp) AS foo
　WHERE pay_by_quarter[s] =　10000;

복합형 타입

복합형 타입은 한 행이나 하나의 기록의 구조를 나타냅니다; 그것은 실제로는 필드 이름과 그들의 데이터 타입 목록에 불과합니다. PostgreSQL는 복합형 타입을 간단한 데이터 타입처럼 사용할 수 있습니다. 예를 들어, 테이블의 특정 필드를 복합형 타입으로 선언할 수 있습니다.

복합형 타입 선언

아래는 두 개의 복합형 타입 정의의 간단한 예제입니다:

CREATE TYPE complex AS (
　　　　doubler precision
　　　　i double precision
);
CREATE TYPE inventory_item AS (
　　　　name text,
　　　　supplier_id integer,
　　　　price numeric
);

CREATE TABLE 문법과 유사하지만, 여기서는 필드 이름과 타입만 선언할 수 있습니다.

타입을 정의하면, 그것을 사용하여 테이블을 생성할 수 있습니다:

CREATE TABLE on_hand (
　　　　item inventory_item,
　　　　count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice',　42　1.99),　1000);

복합 타입 값 입력

복합 타입 값이 텍스트 상수로 작성되는 경우, 필드 값을 둘러싸고 콤마로 구분한 원형 괄호 안에 작성합니다. 값이 콤마나 원형 괄호를 포함하고 있다면, 그 값을 둘러싸는 것이 필수적입니다.

복합 타입 상수의 일반 형식은 다음과 같습니다:

( val1　, val2　, ... )'

예를 들어:

'("fuzzy dice",421.99)'

복합 타입 접근

복합 타입 필드의 도메인에 접근하려면, 점 및 도메인 이름을 작성하면 됩니다. 이는 테이블 이름에서 필드를 선택하는 것과 매우 유사합니다. 실제로, 테이블 이름에서 필드를 선택하는 것과 너무 유사해서, 분석기가 혼동할 수 있기 때문에 종종 원형 괄호를 사용합니다. 예를 들어, on_hand 인스턴스 테이블에서 서브 도메인을 선택해야 할 수 있습니다. 아래와 같이 작성할 수 있습니다:

SELECT item.name FROM on_hand WHERE item.price >　9.99;

이렇게 작성하면 작동하지 않습니다. 왜냐하면 SQL 문법에 따르면 item은 테이블 이름에서 필드 이름이 아닌 것으로 선택됩니다. 아래와 같이 작성해야 합니다:

SELECT (item).name FROM on_hand WHERE (item).price >　9.99;

또한, 여러 테이블 쿼리에서 테이블 이름을 사용해야 한다면, 이렇게 작성하세요:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price >　9.99;

지금 원형 괄호 객체는 item 필드를 가리키는 참조로 올바르게 해석되고, 그 중에서 서브 도메인을 선택할 수 있습니다.

범위 타입

범위 데이터 타입은 특정 요소 타입이 특정 범위 내의 값을 나타냅니다.

예를 들어, timestamp 범위는 회의실이 예약된 시간 범위를 대표할 수 있습니다.

PostgreSQL에 내장된 범위 타입은 다음과 같습니다:

• int4range — integer 범위

• int8range — bigint 범위

• numrange — 숫자 범위

• tsrange — 타임스탬프 범위

• tstzrange — 타임스탬프와 시간대 범위

• daterange — 날짜 범위

또한, 자신의 범위 타입을 정의할 수 있습니다.

CREATE　TABLE　reservation　(room　int,　during　tsrange);
INSERT　INTO　reservation　VALUES
　　　　(1108,　'[2010-01-01　14:30,　2010-01-01　15:30)');
--　포함
SELECT　int4range(10　20)　@>　3;
--　重叠
SELECT　numrange(11.1　22.2)　&&　numrange(20.0,　30.0);
--　상한 추출
SELECT　upper(int8range(15　25));
--　교차 계산
SELECT　int4range(10　20)　*　int4range(15　25);
--　범위가 비어 있는지 여부
SELECT　isempty(numrange(1　5));

범위 값의 입력은 다음 형식을 따라야 합니다:

(하한, 상한)
(하한, 상한]
[하한, 상한)
[하한, 상한]
공백

원형 괄호나四方 괄호는 하한과 상한이 포함되거나 포함되지 않는지 표시합니다. 마지막 형식은 공백, 공백 범위(값을 포함하지 않는 범위)를 나타냅니다.

--　포함합니다3포함하지 않습니다7또한, 그 사이의 모든 포인트를 포함합니다
SELECT　'[37')4range;
--　포함하지 않습니다3또한7또한, 그 사이의 모든 포인트를 포함합니다
SELECT　'(37')4range;
--　단일 값만 포함합니다4
SELECT　'[44]'::int4range;
--　포인트(표준화된 '공백')를 포함하지 않습니다
SELECT　'[44')4range;

객체 식별자 타입

PostgreSQL는 내부적으로 객체 식별자(OID)를 다양한 시스템 테이블의 주키로 사용합니다.

동시에, 시스템은 사용자가 생성한 테이블에 OID 시스템 필드를 추가하지 않습니다(또는 테이블 생성 시 WITH OIDS를 선언하거나 설정 매개변수 default_with_oids를 켜서 설정하지 않은 경우). oid 타입은 객체 식별자를 나타냅니다. 이 외에도 oid는 다음과 같은 별명을 가집니다: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, 그리고 regdictionary.

가상 타입

PostgreSQL 타입 시스템은 특수 용도의 항목을 포함하고 있으며, 그 카테고리에 따라 가상 타입이라고 불립니다. 가상 타입은 필드의 데이터 타입으로 사용할 수 없지만, 함수의 매개변수나 결과 타입을 선언하는 데 사용할 수 있습니다. 가상 타입은 함수가 단순히 특정 SQL 데이터 타입을 받고 반환하는 것 이상의 용도가 있을 때 매우 유용합니다.

다음 테이블은 모든 가상 타입을 나열합니다：