SQL - PostgreSQL, Data Types

PostgreSQL

 

 

✅ Data Types

---

CREATE TYPE gender_type AS ENUM ('male', 'female');
CREATE TABLE users (
	-- 0 < char(n) varchar(n) < 10,485,760
	username CHAR(10) NOT NULL UNIQUE,
	email VARCHAR(50) NOT NULL UNIQUE,
	gender gender_type NOT NULL,
	interests TEXT[] NOT NULL,
	-- 1 GB
	-- > 2KB TOAST (the oversized-attribute storage technique)
	bio TEXT,
	profile_photo BYTEA,
	-- SMALLINT
	-- Signed: -32,768 to 32,767
	-- INTGER
	-- Signed: -2,147,483,648 to 2,147,483,647
	-- BIGINT
	-- Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
	-- SMALLSERIAL (1 to 32,767)
	-- SERIAL (1 to 2,147,483,647)
	-- BIGSERIAL (1 to 9,223,372,036,854,775,807)
	-- DECIMAL & NUMERIC(precision, scale) 10.53 4p 2s
	-- REAL (6 deciaml digits) & DOUBLE PRECISION (15 decimal digits)
	age SMALLINT NOT NULL CHECK (age >= 0),
	is_admin BOOLEAN NOT NULL DEFAULT FALSE,
	-- 4713 BC to 294276 AD
	joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	birth_date DATE NOT NULL,
	bed_time TIME NOT NULL,
	graduation_year INTEGER NOT NULL CHECK (graduation_year BETWEEN 1901 AND 2115),
	internship_period INTERVAL
);

 

1. ENUM

MySQL에서는 ENUM을 바로 사용하여 타입을 지정할 수 있었지만 PostgreSQL에서는 먼저 타입을 선언하면서 ENUM을 사용해야 한다.

 

2. SET

SET을 모방하여 TEXT[]로 TEXT로 구성된 리스트라고 명시할 수 있다.

 

3. TEXT

PostgreSQL에서는 TINYTEXT, MEDIUMTEXT, LONGTEXT 같은 거 없이 TEXT만 있다.

(최대 길이 1GB)

(MySQL은 최대 4GB)

 

4. BYTEA

Byte Array라는 뜻, 이진 데이터를 저장한다.

이것도 마찬가지로 최대 길이 1GB

(만약에 postgreSQL에 더 큰 파일을 저장하고 싶으면 Large Object API가 필요하다. 하지만 역시 파일을 데이터베이스에 저장하는 건 권장하지 않는다.)

 

5. TINYINT

PostgreSQL은 TINYINT가 없다.

SMALLINT, INTEGER, BIGINT가 있다.

 

6. UNSIGNED

PostgreSQL은 UNSIGNED Number가 없다.

그래서 양수 조건을 추가하려면 CHECK를 사용한다.

 

7. SERIAL

PostgreSQL은 SMALLSERIAL, SERIAL, BIGSERIAL이 있다.

1부터 시작한다. (AUTO_INCREMENT가 된다)

 

8. TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ)

TIMESTAMP와 같은 걸 저장하지만, 서버가 위치한 TIMEZONE도 함께 저장된다.

너무 기니까 짧게 TIMESTAMPTZ으로 적을 수도 있다.

 

9. ON UPDATE

PostgreSQL은 MySQL과 다르게 ON UPDATE 구문이 없다.

수동으로 Trigger를 만들어서 직접 업데이트 해줘야 한다.

 

TYPE에 대해

postgresql에는 text나 varchar같은 기본 타입과, 강의에서 정의한 gender_type처럼 사용자 정의 타입이 있습니다.

이들은 아래처럼 조회가능합니다.
selet * from pg_type;

한편, 특정 타입에 속한 데이터만을 보고 싶을 때가 있는데, 예컨대, 사용자가 생성한 enum 객체는
pg_enum에 있습니다.

생성한 enum을 자세히 보려면 다음 같이 사용 가능합니다.
SELECT * FROM pg_type as pt JOIN pg_enum as pe ON pt.oid =pe.enumtypid ;

 

https://www.postgresql.org/docs/16/datatype-geometric.html

8.8. Geometric Types

 

 

✅ Type Casting

---

SELECT
	joined_at::DATE
FROM
	users;

TIMESTAMPTZ 타입인 joined_at에서 DATE 부분만 추출해서 가져옴

 

https://www.postgresql.org/docs/8.1/functions-datetime.html

Date/Time Functions and Operators

 

 

✅ GENERATED ALWAYS AS IDENTITY

---

CREATE TABLE genres (
	genre_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

BIGSERIAL을 사용하면 AUTO_INCREMENT 자동 적용이 되면서 PRIMARY KEY로 사용할 수 있지만,
BIGINT와 AS IDENTITY를 사용하여 AUTO_INCREMENT를 적용시키고 GENERATED ALWAYS를 사용하여 사용자가 데이터를 직접 넣을 수 없게 하는 것이 SQL 표준이다.(genre_id는 수정도 불가능해짐)