PostgreSQL Concepts

Internal

• PostgreSQL

Connection Types

Local Socket

This is the default type of connection, then the psql client is collocated with the database.

TCP

The default port is 5432.

Database

A PostgresSQL usually comes with 4 pre-existing databases (postgres, admin, template0, template1). "postgres" is fit for general use and it should be used by default.

Schema

Each database has by default a public schema.

Authentication

User

Users are shared across databases.

The "user" concept is equivalent with the "role" concept. They mean the same thing.

User Operations

Role

The "role" concept is equivalent with the "user" concept. They mean the same thing.

Role Attributes

A specific role may:

• be a superuser
• create another role
• create a database

Data Types

Identity

• http://www.postgresqltutorial.com/postgresql-identity-column/
• http://www.postgresqltutorial.com/postgresql-serial/

Numeric Types

• https://www.postgresql.org/docs/9.1/static/datatype-numeric.html

SMALLINT

2 byte integer.

INT

4 byte integer. This is a typical choice for integers.

BIGINT

8 byte integer.

BIGSERIAL

TODO