Cheatsheet for Running SQL Against PostgreSQL from the Command Line

Assuming for this that I have a user named 'postgres' with a default password of 'postgres' (note: don't use these values for anything worthwhile only testing or demonstration purposes obvi) and a database called 'test'.

SELECT

PGPASSWORD=postgres psql -U postgres -d 'test' -c 'SELECT * FROM table1;' -A

-A will print to the console in a non aligned format so this:

1|2|3
1|11112|3

instead of this:

1|       2|3
1|11112|3

INSERT

PGPASSWORD=postgres  psql -U postgres -d 'TestDB' -c 
  "INSERT INTO test (\"Name\", created_at, \"UpdatedAt\", hired_at, hired, id) 
  VALUES ('Hello World', CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_DATE, true, 5);"

Using single quotes to insert values and double quotes for column names that require capitalisation (postgres transforms all column names to lowercase).

PGPASSWORD=postgres  psql -U postgres -d 'TestDB' -c 
  "PREPARE insertplan (\"Name\", created_at, \"UpdatedAt\", hired_at, hired, id) 
  AS INSERT INTO test 
  VALUES($1, $2, $3, $4, $5, $6); 
  EXECUTE insertplan('Hello World', CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_DATE, true, 6);"

SELECT
        "$Table"."id" as "ID" 
        "$Table"."Name" as "Name"
        "$Table"."created_at" as "CreatedA"
        "$Table"."UpdatedAt" as "UpdatedAt"
        "$Table"."hired_at" as "HiredAt"
        "$Table"."hired" as "Hire"
      FROM
        "test" as "$Table
      WHERE
       "$Table"."UpdatedAt" > '01/04/2017'

let
  SQL = 
    "
      SELECT 
        ""Name"",
        created_at,
        ""UpdatedAt"",
        hired_at,
        hired
      FROM 
        public.test;
    ",
  Source = PostgreSQL.Database("localhost:5432", "TestDB", [Query= SQL])
in
  Source