Intro to PostgreSQL Data Types
Jul 28, 2017 · 2 min read
There are several great Relational Database Management Systems (otherwise known as RDBMS) like MySQL, Oracle, Sqlite 3. Generally all of these RDBMS’s follow the SQL standard language but each has its own dialect. However today I will be discussing more about PostgreSQL and its own dialect.
Some popular data types in PostgreSQL are string, numeric, and date/timestamps. Here are a few examples of these data types:
String Datatypes:
- VARCHAR(n) — Allows a variable amount of text up to n characters.
- TEXT — Allows a variable string length, aka you can type anythan you want 👌
Numeric Datatypes:
- BOOL/BOOLEAN — Everyone knows what a boolean is right? True or False values only.
- MONEY — Pretty self explanatory here, used for currency values. 💸 💰 🤑
- INTEGER — 4-byte signed number (signed meaning no negative numbers)
- SERIAL — Another 4-byte signed number, however unlike INTEGER this data type is auto incrementing.
Date/Time Datatypes:
- DATE — Date is simply displayed as ‘YYYY-MM-DD’.
- TIMESTAMP — Time stamp is essentially the same as date but with time added: ‘YYYY-MM-DD HH:MM:SS’.
- TIME — Time as you can guess only displays the time as ‘HH:MM:SS’.
- ** Another feature to note is that for TIMESTAMP and TIME you can choose to have time zones included or not included. (example in postgres: created_at timestamp with time zone)
And to sum everything up, heres a quick example of creating a table in PostgreSQL:
CREATE TABLE users (
id SERIAL,
username VARCHAR(90),
text_info TEXT,
birthday DATE,
created_at timestamp with time zone
);
*PostgreSQL Data Type Cheat Sheet*: