PostgreSQL Concepts: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 23: Line 23:
It seems that the database name is case sensitive, even if a database is created with "CREATE DATABASE TEST_DB", the database name becomes "test_db", and this is what it should be used in the connect URL. "jdbc:postgres://localhost/test_db" will work, but "jdbc:postgres://localhost/TEST_DB" won't.  
It seems that the database name is case sensitive, even if a database is created with "CREATE DATABASE TEST_DB", the database name becomes "test_db", and this is what it should be used in the connect URL. "jdbc:postgres://localhost/test_db" will work, but "jdbc:postgres://localhost/TEST_DB" won't.  
</font>
</font>
=Cluster=
Cluster operations:
* [[PostgreSQL_DDL_Operations#initdb|Create a database cluster]]


=Schema=
=Schema=

Revision as of 20:35, 6 October 2023

Internal

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.

Database Name Case Sensitivity

Verify this:

It seems that the database name is case sensitive, even if a database is created with "CREATE DATABASE TEST_DB", the database name becomes "test_db", and this is what it should be used in the connect URL. "jdbc:postgres://localhost/test_db" will work, but "jdbc:postgres://localhost/TEST_DB" won't.

Cluster

Cluster operations:

Schema

Each database has by default a public schema.

Tablespace

Authentication

User

Users are shared across databases.

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

User Operations


Master User

An administrative user that exists when the RDBMS instance is created and that has privileges to create other database and other users. It is used to bootstrap the administration of the RDBMS instance, by defining all users, objects, and permissions in the databases of your DB instance. Master Username must start with a letter. The RDS documentation refers to it as "Master username".

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

Timestamp

Numeric Types

SMALLINT

2 byte integer.

INT

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

BIGINT

8 byte integer.

DECIMAL

NUMERIC

REAL

DOUBLE PRECISION

SERIAL

BIGSERIAL

TODO