PostgreSQL

Installation / Setup

Install via Brew

$ brew install postgresql

Start Postgres Service (via Brew)x

$ brew services start postgresql

Creating / Dropping Databases

Create a new database

$ createdb <db_name>      # Creates a new database with the given name
$ createdb `whoami`       # Creates a new database for the current logged in user, and allow connection via psql

Drop Database

$ dropdb --help               # Drop database
$ dropdb -i <database_name>   # With confirmation
$ dropdb <database_name>      # Without confirmation

PSQL

Help

$ psql --help             # Command line options
$ psql --help=commands    # Backslash commands
$ psql --help=variables   # Special variables

Connect to a specific database

$ psql -d <db_name>
$ psql <db_name>          # No need to use -d option

List available databases

$ psql -l

Export DB Schema

$ pg_dump --help
$ pg_dump -s <database_name> > filename    # To file
$ pg_dump -s <database_name> | pbcopy      # To clipboard

Output PostgreSQL History (Shows all previously run SQL commands)

$ cat ~/.psql_history

Load a SQL File into a DB

$ psql -d <database_name> -f <path_and_filename>
$ psql -h <host> -p <port> -u <database>

# Example (will prompt for password)
$ psql -h somehost-012345.db.elephantsql.com -p 5432 -d mydatabase -U username -f ./sql/initial_schema.ddl

psql Console Commands

\h                     # Show help (does not show backslash commands)
\h CREATE              # Show help for the CREATE command

\l                     # Show all databases

\c <database_name>     # Connect to database

\d                     # Show tables/views/sequences
\dt                    # Show all tables
\d <tablename>         # Describe table

\i <filename>          # Load SQL file

\q                     # Quit

show config_file;      # Output the location of the postgresql.conf file
show time zone;        # Output Time Zone (current session only)

Postgresql Time Zone Setting

Defaults to the system time zone at the time of install if not specified.

Location of postgresql.conf file: /usr/local/var/postgres/postgresql.conf
Timezone setting: timezone = "UTC"

Sample SQL Statements

Create Database

create database studioreservations_dev;

Create User + Grant Access

create user admin with encrypted password 'my-secret-password';

grant all privileges on database <database_name> to admin;

Create Table Using various data types

CREATE TABLE users (
  id integer NOT NULL,
  email character varying,
  password_digest character varying,
  created_at timestamp without time zone default current_timestamp NOT NULL,
  updated_at timestamp without time zone default current_timestamp NOT NULL,
  name character varying
);

Insert data into table

insert into users (email, password) values ('name@domain.com', 'my-secret-Password-123');

Delete all rows from table

truncate table <tablename>

Drop Database

drop database <database_name>

Rename Table

alter table <current_table_name> rename to <new_table_name>

Notes on SQL Queries

• Need to use single quotes when inserting data via the console.
• Matching on wildcard characters:
  • _ matches on a single character (as compared to ? in Oracle SQL)
  • % matches on any number of characters (as compared to * in Oracle SQL)

Configuration

Default Postgres configuration file location: /usr/local/var/postgres/postgresql.conf

Useful settings:

• max_connections = 100 (requires restart)

Troubleshooting

Server was running but client could not connect

Error Message:

"psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"

Solved by: Removing postmaster.pid file: rm /usr/local/var/postgres/postmaster.pid

Reference: Stackoverflow post

Error: psql: error: could not connect to server: No such file or directory

Try to start the server manually: $ pg_ctl -D /usr/local/var/postgres/ start

A different error explains what is wrong:

waiting for server to start....2021-05-04 09:24:12.203 JST [56328] FATAL: database files are incompatible with server

2021-05-04 09:24:12.203 JST [56328] DETAIL: The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 13.2.

stopped waiting
pg_ctl: could not start server
Examine the log output.

Resolution:

• Upgrade the database using brew: $ brew postgresql-upgrade-database
• Can now connect to the DB via psql

Research Topics

• Differences between Postgresql and MySql as it relates to TIMESTAMP fields
• The Postgresql “JSON” column type

References

• http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands/