PostgreSQL / helpful commands

• To know version

SELECT version(); #From PostgreSQL

psql -V #From bash

• HELP!

psql --help

To describe a table such as a column, type, modifiers of columns, etc., you use the following command:

• List available schema

To list all schemas of the currently connected database, you use the \dn command.

• List available functions

To list available functions in the current database, you use the \df command.

Quit psql

To quit psql, you use \q command and press enter to exit psql.

User

• to list all user accounts (or roles) in the current PostgreSQL database

Database

• List available databases

• List available tables

• Connect co another DB

To create a new database:

CREATE DATABASE lusiadas;

To create a database sales owned by user salesapp with a default tablespace of salesspace:

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

Create database:

CREATE USER jonathan;

Create a user with a password:

CREATE USER davide WITH PASSWORD 'jw8s0F4';

Create a user with a password that is valid until the end of 2004. After one second has ticked in 2005, the password is no longer valid.

CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

Create an account where the user can create databases:

CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB;

User

Create a user with no password:

CREATE USER jonathan;

Create a user with a password:

CREATE USER davide WITH PASSWORD 'jw8s0F4';

Create a user with a password that is valid until the end of 2004. After one second has ticked in 2005, the password is no longer valid.

CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

Create an account where the user can create databases:

CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB;