PostgreSQL DDL Operations

Internal

• PostgreSQL Operations

Providing a Password to psql in Command Line

If a database is protected by a password, psql will challenge for password interactively. That can be avoided in scripts as follows:

export PGPASSWORD=password; psql -l -h some.host -U some-user ...

Specifying SQL Commands with psql

psql ... -c "sql-command"

Example:

psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"

Version

Server version:

pg_config --version

Client version:

psql --version

Also, execute:

SELECT version():

Database Cluster

initdb

https://www.postgresql.org/docs/9.5/static/app-initdb.html

initdb will create a new database cluster.

Database

List Databases

psql -l [-h ... -U ... -d ...]

or

> \l

or

SELECT datname FROM pg_database;

Create a Database

createdb <dbname>

createdb -h localhost -U admin playground

or

CREATE DATABASE <dbname>;

Also see:

Database Name Case Sensitivity

Connect to a Database

psql <database-name>

Drop a Database

DROP DATABASE <dbname>;

This command will not work if there are users connected to the database:

ERROR:  database "..." is being accessed by other users
DETAIL:  There are 10 other sessions using the database.

The database can though be forcefully dropped as follows (enclose the database name in single quotes):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';

This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone.

If there is a lot of activity on the database, you may optionally disallow new connections:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>'; 
ALTER DATABASE <db-name> CONNECTION LIMIT 1;

When all backends are gone, the database can be dropped.

Users

The following user operations are supported.

List Users

psql
\du

or

SELECT usename FROM pg_user;

Create User

Command line:

createuser <user_name>

or SQL client:

CREATE USER <user_name>;

Change Password

In psql, for the current user:

\password <user_name>

or

ALTER USER user_name WITH PASSWORD 'new_password';

Granting Privileges on a Database

GRANT ALL PRIVILEGES ON DATABASE <dbname> TO  <user>;

Alternative from command line:

psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"

Delete a User

DROP USER <username>

Tablespace

List Tablespaces

psql
\db

Tables

List Tables

psql
\dt

SELECT * FROM pg_catalog.pg_tables;

Describe Table Structure

\d <table_name>

Create a Table

CREATE TABLE poc_library_component
(
  "id" integer NOT NULL,
  "name" text,
  "desc" text,
   CONSTRAINT poc_library_component_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE poc_library_component OWNER TO is3_as;

Note: apparently, PostgreSQL does not like uppercases in column names.

Records

Delete Records

DELETE FROM <table-name>;