Name | Aliases | Description |
---|---|---|
bigint | int8 | signed eight-byte integer |
bigserial | serial8 | autoincrementing eight-byte integer |
bit [ (n) ] | fixed-length bit string | |
bit varying [ (n) ] | varbit [ (n) ] | variable-length bit string |
boolean | bool | logical Boolean (true/false) |
box | rectangular box on a plane | |
bytea | binary data (“byte array”) | |
character [ (n) ] | char [ (n) ] | fixed-length character string |
character varying [ (n) ] | varchar [ (n) ] | variable-length character string |
cidr | IPv4 or IPv6 network address | |
circle | circle on a plane | |
date | calendar date (year, month, day) | |
double precision | float8 | double precision floating-point number (8 bytes) |
inet | IPv4 or IPv6 host address | |
integer | int, int4 | signed four-byte integer |
interval [ fields ][ (p) ] time span | ||
json | textual JSON data | |
jsonb | binary JSON data, decomposed | |
line | infinite line on a plane | |
lseg | line segment on a plane | |
macaddr | MAC (Media Access Control) address | |
macaddr8 | MAC (Media Access Control) address (EUI-64 format) | |
money | currency amount | |
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision | ||
path | geometric path on a plane | |
pg_lsn | PostgreSQL Log Sequence Number | |
point | geometric point on a plane | |
polygon | closed geometric path on a plane | |
real | float4 | single precision floating-point number (4 bytes) |
smallint | int2 | signed two-byte integer |
smallserial | serial2 | autoincrementing two-byte integer |
serial | serial4 | autoincrementing four-byte integer |
text | variable-length character string | |
time [ (p) ][ without time zone ] | time of day (no time zone) | |
time [ (p) ] with time zone | timetz | time of day, including time zone |
timestamp [ (p) ][ without time zone ] | date and time (no time zone) | |
timestamp [ (p) ] with time zone | timestamptz | date and time, including time zone |
tsquery | text search query | |
tsvector | text search document | |
txid_snapshot | user-level transaction ID snapshot | |
uuid | universally unique identifier | |
xml | XML data |
Name | Storage Size | Description | Range |
---|---|---|---|
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807 |
decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
smallint
는 일반적으로 디스크 공간이 부족한 경우에 사용된다.bigint
는 범위가 불분명할 때 사용한다.serial
을 이용하여 자동으로 생성되게 한다. -- mysql
"idx" int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY
-- postgresql
"idx" serial PRIMARY KEY,
Name | Description |
---|---|
character varying(n), varchar(n) | variable-length with limit |
character(n), char(n) | fixed-length, blank padded |
text | variable unlimited length |
Name | Storage Size | Description |
---|---|---|
boolean | 1 byte | state of true or false |
Name | Storage Size | Description | Low Value | High Value | Resolution |
---|---|---|---|---|---|
timestamp [ (p) ][ without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ][ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ (p) ] with time zone | 12 bytes | time of day (no date), with time zone | 00:00:00+1459 | 24:00:00-1459 | 1 microsecond |
interval [ fields ][ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
insert into tdatetime.datetime_test (
t_date, t_time, t_timestamp, t_timestamptz
) values (now(), now(), now(), now());
SELECT now(), now() - INTERVAL '1 year 3 hours 20 minutes'
AS "3 hours 20 minutes ago of last year"
now | 3 hours 20 minutes ago of last year |
---|---|
2019-02-25 17:41:20 | 2018-02-25 14:21:20 |
오라클에서는 UTC를 바탕으로 시간을 계산해서 진행하는 반면 postgresql에는 글로벌 타임 개념이 존재하여, 나라와 도시만 설정하면 해당 타임존의 시간이 자동으로 변환되어 출력한다.
SET TIME ZONE 'Asia/Seoul';
SET timezone ='Asia/Seoul';
select t_timestamp, to_char(t_timestamp, 'YYYY-mm-dd') as change_datetime from tdatetime.datetime_test;
결과 왼쪽: 원본 / 오른쪽: formatting
t_timestamp | change_datetime |
---|---|
2020-05-01 14:57:38.365598 | 2020-05-01 |
CREATE TABLE test_array (
array1 integet[]
array2 integer[3][3]
array3 text[]
array4 varchar(100)[]
array4 json[]
);
create table test(
t_json json,
t_jsonb jsonb,
t_json_array json[]
);