postgresql 데이터 타입과 옵션

postgreSQL 데이터 타입과 옵션들을 확인해 봅시다.

PostgreSQL Data Types

The data type can be any of the following:

• boolean: Use "boolean" or "bool" to declare a true or false value.
• character values
  • char: holds a single character
  • char (#): holds # number of characters. Spaces will be inserted to fill any extra room.
  • varchar (#): holds a maximum of # number of character. Can contain less.
• integer values
  • smallint: whole number between -32768 and 32767.
  • int: whole number between -214783648 and 214783647.
  • serial: Auto-populated integer number.
• floating-point values
  • float (#): floating point number with at least # points of precision.
  • real: 8-byte floating point number
  • numeric (#,after_dec): real number with # number of digits, and after_dec digits after decimal
• date and time values
  • date: stores a date value
  • time: stores a time value
  • timestamp: stores a date and time value
  • timestamptz: stores a timestamp that includes timezone data
  • interval: stores the difference between two timestamp values
• geometric data
  • point: stores a pair of coordinates that define a point
  • line: stores a set of points that map out a line
  • lseg: stores data that defines a line segment
  • box: stores data that defines a rectangle
  • polygon: stores data that defines any enclosed space
• device specifications
  • inet: stores an IP address
  • macaddr: stores a device MAC address

PostreSQL Column and Table Constraints

Column definitions can also have constraints that provide rules for the type of data found in the column. The following can be used as space-separated values following the data type:

• NOT NULL: column cannot have null value
• UNIQUE: column value must not be the same for any record. Null is always considered a unique value
• PRIMARY KEY: combination of the above two constraints. Can only be used once per table
• CHECK: ensure that a condition is true for values in the column
• REFERENCES: value must exist in a column in another table