Skip to content

PostgreSQL:DataTypes

Data types

타입

설명

smallint

2 바이트 정수

integer

4 바이트 정수

bigint

8 바이트 정수

decimal(a,a)/numeric(a,s)

10진수형

real

6자리 단정도 부동소수점

double precision

15 자리 배정도 부동소수점

serial

4 바이트 일련번호

bigserial

8 바이트 일련번호

date

일자

time

시간

timestamp

일자시간

char(문자수)/character

고정길이 문자열 (최대 4096 문자)

varchar(문자수)/charcter varying

가변길이 문자열 (최대 4096 문자)

text

무제한 텍스트

Large Object

oid형

boolean/bool

true/false

JSON type

Postgresql은 JSON 데이터 타입을 지원한다.

  • JSON: PostgreSQL 9.2부터 JSON 데이터 타입이 추가되어서 칼럼에 JSON 객체를 저장할 수 있다. 이는 아주 유용한 기능인데 RDB의 기능을 그대로 쓰면서 NoSQL처럼 Schemaless의 이점을 취할 수 있다.
  • JSONB: PostgreSQL 9.4부터는 JSONB 데이터타입이 추가되었다. 문서에 따르면 Insert는 JSON보다는 느리지만, 바이너리 형식으로 저장되어서 처리가 훨씬 유연하다.

Do not use varchar by default

Don't use the type varchar(n) by default. Consider varchar (without the length limit) or text instead.

Why not?

varchar(n) is a variable width text field that will throw an error if you try and insert a string longer than n characters (not bytes) into it.

varchar (without the (n)) or text are similar, but without the length limit. If you insert the same string into the three field types they will take up exactly the same amount of space, and you won't be able to measure any difference in performance.

If what you really need is a text field with an length limit then varchar(n) is great, but if you pick an arbitrary length and choose varchar(20) for a surname field you're risking production errors in the future when Hubert Blaine Wolfe­schlegel­stein­hausen­berger­dorff signs up for your service.

Some databases don't have a type that can hold arbitrary long text, or if they do it's not as convenient or efficient or well-supported as varchar(n). Users from those databases will often use something like varchar(255) when what they really want is text.

If you need to constrain the value in a field you probably need something more specific than a maximum length - maybe a minimum length too, or a limited set of characters - and a check constraint can do all of those things as well as a maximum string length.

When should you?

When you want to, really. If what you want is a text field that will throw an error if you insert too long a string into it, and you don't want to use an explicit check constraint then varchar(n) is a perfectly good type. Just don't use it automatically without thinking about it.

Also, the varchar type is in the SQL standard, unlike the text type, so it might be the best choice for writing super-portable applications.

varchar(n) 대신 글자수 제한 방법

PostgreSQL:Constraints#글자 수 제한 항목 참조. 간단히: 

ALTER TABLE names ADD CONSTRAINT namechk CHECK (char_length(name) <= 255);

See also

Favorite site