PostgreSQL

Installation

Installation - Fedora

Config

Environment Variables

JDBC

Cheatsheet

psql - PostgreSQL interactive terminal

psql - Connect to a database

# Default values can be omitted
PGPASSWORD=$database_password psql -h $database_host -p $database_port -U $database_user -d $database_name

or

Using PSQL_CONNECTION_STRING

psql $PSQL_CONNECTION_STRING

List all databases

\l

or

\list

Change the current database

\c $database_name

List all tables in the current database

\dt+

List all tables from a specific schema in the current database

\dt+ $schema_name.*

Show table schema

\d+ $table_name

or

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema';

Run a SQL script

psql $PSQL_CONNECTION_STRING -f script.sql

SQL

  • exists is a boolean operator like a function

View server settings

SELECT * FROM postgres.pg_catalog.pg_settings

Create a database if it does not exist

SELECT 'CREATE DATABASE db1'
WHERE NOT EXISTS(SELECT FROM pg_database WHERE datname = 'db1');

Add an identity column to a table

CREATE TABLE people (
    id bigint GENERATED ALWAYS AS IDENTITY,
    ...,
);

Drop a table if it exists

DROP TABLE IF EXISTS $schema.$table_name;

Delete all rows from a table only if the table exists

DELETE FROM $table_name
WHERE EXISTS (
  SELECT to_regclass('$schema.$table_name')
);

or

DELETE FROM $table_name
WHERE EXISTS (
  SELECT 1
  FROM information_schema.tables
  WHERE table_schema = '$schema'
  AND table_name = '$table_name'
);
Crystal LoverComputer Graphics