Notes to myself
I have been reviewing database to refresh my knowledge of SQL. I have used both SQLite and PostgreSQL before. For my recent project What to Watch I’ve used PostgreSQL as I was planning to deploy it Heroku. Here are some notes to myself about PostgreSQL.
What is PostgreSQL?
PostgreSQL is a general-purpose object-relational database management system.
Data Types
The full list of Data Types can be found here Data Type full list. I will only list some main PostgreSQL data types such as Character, Number, Serial, Temporal Data, Boolean.
Here are some examples:
Character
PostgreSQL offers three character data types: CHAR(n)
, VARCHAR(n)
, and
TEXT
.
CHAR(n) - fixed-length character strings
VARCHAR(n) - Variable-length strings
TEXT - Strings of any length(unlimited length character string)
Numeric
PostgreSQL supports two distinct types of numbers, Integers
and
Floating-point numbers
. Each of these type has several subtypes.
Integer subtypes
SMALLINT(Small integer) - 2-byte signed integer that has a range from -32,768 to
32,767.
INT(Integer) - 4-byte integer that has a range from -2,147,483,648 to
2,147,483,647.
Serial - It's the same as integer except that PostgreSQL will automatically
generate and populate values into the SERIAL column.
Floating-Point Number subtypes
In PostgreSQL there are three main types of floating-point numbers:
float(n) - is a floating-point number whose precision - is at least, n, up to a
maximum of 8 bytes.
real - is a 4-byte floating-point number.
numeric or numeric(p,s): is a real number with p digits with s number after the
decimal point. The numeric(p,s) is the exact number
Temporal Data Types
Temporal data types store time-related information: Data, time, timestamp, interval, timestamptz
Boolean
Boolean data type includes TRUE, FLASE, NULL. Postgres is not limited by
storing only true
and false
values into boolean columns. In boolean columns
we can insert values like 1
, 0
, t
or f
and they will be interpreted as
true
or false
.
In my next post, I will give some instances with the data types listed above.