PostgreSQL

PostgreSQL#

Table of Contents#

Resources#

freeCodeCamp

  • [ y ] 04-04-2019. freeCodeCamp. “Learn PostgreSQL Tutorial - Full Course for Beginners”.

Installation#

macOS

Documentation#

[D][W] PostgreSQL

  • 8 Data Types

    • [D] 8.5.1 Date/Time Input

  • 20 Server Configuration

    • [D] 20.1 Setting Parameters

    • [D] 20.2 File Locations

    • [D] 20.3 Connections and Authentication

    • [D] 20.11 Client Connection Defaults

  • [D] PostgreSQL Client Applications

[D] SQL Commands

  • [D] ALTER DATABASE

  • [D] ALTER ROLE

  • [D] ALTER TABLE

  • [D] CREATE DATABASE

  • [D] CREATE ROLE

  • [D] CREATE TABLE

  • [D] DROP DATABASE

  • [D] DROP ROLE

  • [D] DROP TABLE

  • [D] EXPLAIN

  • [D] INSERT

  • [D] SELECT INTO

  • [D] SET

  • [D] SHOW

  • [D] UPDATE

[D] pgAdmin

EDB

  • [D] Connecting PostgreSQL using psql and pgAdmin

Commands#

macOS#

brew info postgresql

psql myDatabase -c "show config_file;"
psql myDatabase -c "show data_directory;"
psql myDatabase -c "show hba_file;"
psql myDatabase -c "show listen_addresses;"
psql myDatabase -c "show port;"

pg_ctl status -D '/opt/homebrew/var/postgresql@15'

pg_isready

pg_config
pg_config --bindir
pg_config --sysconfdir

Windows#

pg_ctl status  -D "C:\Program Files\PostgreSQL\15\data"
pg_ctl restart -D "C:\Program Files\PostgreSQL\15\data"

commands#

-- config
select version();
select current_user;
select session_user;
\conninfo

-- data type, schema
\d+ myTableName
select pg_typeof(myColumnName) from myTableName limit 1;

-- explain

-- arithmetic
select 2 + 2;

-- date style (input, output)
show datestyle;
alter database myDatabase set datestyle to 'iso, dmy';
alter database myDatabase set datestyle to 'iso, mdy';
alter database myDatabase set datestyle to 'iso, ymd';
alter database myDatabase reset datestyle;
alter role myRole in database myDatabase set datestyle to 'iso, dmy';

-- dates and times
select current_date;
select current_time;
select current_timestamp;
select now();
select now()::date;
select to_char(now()::date, 'mm-dd-yyyy');
select to_char(now()::date, 'dd-mm-yyyy');
select to_char(now()::date, 'day month dd-mm-yyyy');
select to_char(now()::date,   'd dd-mm-yyyy'); -- day of the week
select to_char(now()::date,  'dd dd-mm-yyyy'); -- day of the month
select to_char(now()::date, 'ddd dd-mm-yyyy'); -- day of the year
select extract(year from now()) as year;
select * from pg_timezone_names;
show timezone;
select now()::timestamp at time zone 'America/New_York';
select age('2023-02-04', '1992-02-04');

-- locale, localization
update pg_database set datctype  ='en_US.UTF-8' where datname='myDatabaseName';
update pg_database set datctype  ='C'           where datname='myDatabaseName';
update pg_database set datcollate='en_US.UTF-8' where datname='myDatabaseName';
update pg_database set datcollate='C'           where datname='myDatabaseName';

-- more config
table information_schema.columns; -- select * from information_schema.columns
table information_schema.tables;  -- select * from information_schema.tables
table pg_attribute;               -- select * from pg_attribute;
table pg_config;                  -- select * from pg_config;
table pg_database;                -- select * from pg_catalog.pg_database;
table pg_db_role_setting;         -- select * from pg_catalog.pg_db_role_setting;
table pg_file_settings;           -- select * from pg_catalog.pg_file_settings;
table pg_hba_file_rules;          -- select * from pg_catalog.pg_hba_file_rules;
table pg_roles;                   -- select * from pg_roles;
table pg_settings;                -- select * from pg_catalog.pg_settings;
table pg_shadow;                  -- select * from pg_catalog.pg_shadow;
table pg_stat_activity;           -- select * from pg_catalog.pg_stat_activity;
table pg_tablespace;              -- select * from pg_tablespace;
table pg_timezone_names;          -- select * from pg_timezone_names;
table pg_user;                    -- select * from pg_user;

show all;                       -- select current_setting('');
show config_file;               -- select current_setting('');
show default_tablespace;        -- select current_setting('');
show effective_cache_size;      -- select current_setting('');
show effective_io_concurrency;  -- select current_setting('');
show lc_collate;                -- select current_setting('');
show lc_ctype;                  -- select current_setting('');
show lc_messages;               -- select current_setting('');
show lc_monetary;               -- select current_setting('');
show lc_numeric;                -- select current_setting('');
show lc_time;                   -- select current_setting('');
show log_destination;           -- select current_setting('');
show maintenance_work_mem;      -- select current_setting('');
show max_connections;           -- select current_setting('');
show max_parallel_workers;      -- select current_setting('');
show max_prepared_transactions; -- select current_setting('');
show max_wal_size;              -- select current_setting('');
show max_worker_processes;      -- select current_setting('');
show min_wal_size;              -- select current_setting('');
show random_page_cost;          -- select current_setting('');
show search_path;               -- select current_setting('');
show shared_buffers;            -- select current_setting('');
show synchronous_commit;        -- select current_setting('');
show temp_tablespaces;          -- select current_setting('');
show timezone;                  -- select current_setting('timezone');
show wal_buffers;               -- select current_setting('');
show work_mem;                  -- select current_setting('');

Command line utilities

  • createdb

  • createuser

  • dropdb

  • dropuser

  • init_db

  • pg_config

  • pg_ctl

  • pg_isready

  • postgres

  • psql

    • psql --help

    • psql -V

    • psql -l

    • psql myDatabase -c 'select 2 + 2;'

Meta commands

  • \? List internal commands.

  • \h List help.

  • \c Connect to a database.

  • \conninfo Current connection information.

  • \l+ List databases.

  • \d+ table_name List tables, views, and sequences.

  • \dt+ List tables.

  • \du+ List roles.

  • \pset pager off

  • \q Exit psql.

  • \x

Create a new database called myDatabase.

createdb myDatabase -e

Delete database myDatabase.

dropdb myDatabase -e

drop database if exists myDatabase;

Switch to database myDatabase.

\c myDatabase

Initialize a database called myDatabase with a schema file.

psql -f schema.sql -d myDatabase -e

Create a new user with a password.

createuser postgres -P -s -d -r -e

Check the current database; user; socket; port.

\conninfo

Switch the current user to user postgres.

\c - postgres

List databases.

\l+

List databases from the command line.

psql -l

List users.

\du+

select current_user;

Change the password for user postgres.

alter user postgres password 'myPassword';

create role dave with login password 'myPassword';

Delete user postgres.

dropuser postgres -e

drop role if exists postgres;

Create default database cluster (Homebrew).

initdb --locale=C -E UTF-8 /opt/homebrew/var/postgresql@15

Check user permissions.

select * from pg_user;

Change role/user postgres’s password to myPassword.

\password

alter role postgres with password 'myPassword';

Remove role/user postgres’s password.

alter role postgres with password null;

Texts#

  • Obe, Regina O. & Leo S. Hsu. (2017). PostgreSQL Up & Running: A Practical Guide to the Advanced Open Source Database. O’Reilly.

Contents