PostgreSQL (pronounced post-gress-Q-L) is a popular open-source database with a very complete library of analytical functions. In order of richesse et performance sqlite < mysql< postgreSQL

Postgres.app is a full-featured PostgreSQL app for MacOS . Postgres.app includes psql, a versatile command line client for PostgreSQL:

• To connect with psql, double click a database.
• To connect directly from the command line, type psql. If you’d rather use a graphical client, see below.

But it’s not the only option; there are plenty of great graphical client:

• pgAdmin
• Postico


PSQL commands:

• \list or \l: list all databases
• \c db_name : connect to database
• \dt: list all tables in the current database
• \du: list of users
• \connect: connect to a specific database
• \q: Quit/Exit
• \d table_name: Show table definition including triggers
• \dy: List events
• \df: List functions
• \di: List indexes
• \dn: List schemas
• \dv: List views
• \df function_name : Show function SQL code.
• \x: Pretty-format query results instead of the not-so-useful ASCII tables
• \copy (SELECT * FROM table_name) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV

To connect to a PostgreSQL server with Python, please first install the psycopg2 library:

pip install psycopg2

• brew install postgresql

  • To migrate existing data from a previous major version of PostgreSQL run:

    brew postgresql-upgrade-database

  • To have launchd start postgresql now and restart at login:

    brew services start postgresql

  • Or, if you don't want/need a background service you can just run to start:

    pg_ctl -D /usr/local/var/postgres start

• if error postgres already running on 5432 :

  • You can stop the process by finding the PID with :

    lsof -i :5432

  • and then killing it with

    kill -9 <PID>

• Start by entering the following on the command line, this opens the psql command line:

  psql postgres

• when Postgres is installed, it automatically creates a database user that matches your username, so that you can get started right away.

• Start postgresql and psql command line

pg_ctl -D /usr/local/var/postgres start
psql postgres

• Load The DVD rental database available at postgresql-sample-database.

• create new databases and restore the downloaded unzipped database-sample to it:

  CREATE DATABASE dvdrental;
  pg_restore -d db_name /path/to/your/file/dvdrental -c -U db_user

• Creating databases and users

  • CREATE DATABASE test;
    CREATE USER no-one WITH LOGIN PASSWORD 'qwerty';

  • CREATE TABLE employees(
     ID             INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL  );

• https://postgresapp.com/