Software >> Services >> RDBMS >> PostgreSQL >> How to get started with PostreSQL


## launch and login to the client

psql -U username -W
password: ********

or

psql -U username -d mydb -W


## connect to the database

\c mydb


## display tables

\dt

or

\dt+


## Create a database

Syntax:

CREATE DATABASE database_name
WITH
   [OWNER =  role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false ]


Example:

CREATE DATABASE hr
WITH
   ENCODING = 'UTF8'
   OWNER = hr
   CONNECTION LIMIT = 100;


## Alter a database

eg1

ALTER DATABASE database_name
RENAME TO new_name;

eg2

ALTER DATABASE database_name
OWNER TO new_owner|current_user|session_user;

eg3

ALTER DATABASE database_name
SET TABLESPACE new_tablespace;

eg4

ALTER DATABASE database_name
SET configuration_parameter = value;


## Drop a database

DROP DATABASE [IF EXISTS] database_name;


## Copy a database

CREATE DATABASE targetdb
WITH TEMPLATE sourcedb;


## Get table size

SELECT
    pg_size_pretty (pg_relation_size('table_name'));


## Get tablespace size

SELECT
    pg_size_pretty (
        pg_tablespace_size ('tablespace_name')
    );


## Create tablespace

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;


## Changing tablespace

ALTER TABLESPACE tablespace_name
action;

eg1

ALTER TABLESPACE tablespace_name
RENAME TO new_name;

eg2

ALTER TABLESPACE tablespace_name
OWNER TO new_owner;

eg3

ALTER TABLESPACE tablespace_name
SET parameter_name = value;


## Delete tablespace

DROP TABLESPACE [IF EXISTS] tablespace_name;

eg

CREATE TABLESPACE demo
LOCATION 'c:/data/demo';