The Standard stuff is pretty easy. There is also a good summeray. Here we go with a quick start to create a DB-file with a table in it and do some stuff.

# sqlite3 
sqlite> .table mydb.db
sqlite> CREATE TABLE IF NOT EXISTS T_names(name TEXT, birthyear INT);
sqlite> INSERT INTO T_names VALUES ('John Smith', 1999);
sqlite> INSERT INTO T_names VALUES ('Jack Doe', 1998);
sqlite> SELECT * FROM T_names;
sqlite> SELECT name FROM T_names WHERE birthyear=1998;
sqlite> UPDATE T_names SET birthyear=1997 WHERE name = 'Jack Doe';
sqlite> SELECT * FROM T_names LIMIT 1;

CREATE DATABASE IF NOT EXISTS meineDB;
CREATE USER 'benutzer'@'localhost' IDENTIFIED BY 'passwort';
GRANT ALL PRIVILEGES ON meineDB . * TO 'benutzer'@'localhost';

Grant Access from remote IP (10.11.12.13)

GRANT ALL ON meineDB.* to 'benutzer'@'10.11.12.13' IDENTIFIED BY 'passwort' WITH GRANT OPTION;

A useful tutorial is here.

Create, drop and list DB

CREATE DATABASE dbname;
DROP DATABASE dbname;
\l #List DBs

Connect to DB

\c dbname #Connect to DB

Select with multiple LIKE and NOT LIKE

SELECT something, anything FROM T_table WHERE something != 'thing' AND anything LIKE '%any%' AND anything NOT LIKE '%stuff%' AND anything NOT LIKE '%other%';
SELECT something, anything FROM T_table WHERE something LIKE ANY (ARRAY['%som%', '%thing', 'any%', 'stuff%'])

Create a new DB and User and change DB-Owner

CREATE DATABASE mynewdb;
CREATE USER myuser WITH PASSWORD 'Aw5s0m4Secur3';
ALTER DATABASE mynewdb OWNER TO myuser;

Copying PostgreSQL database to another server

pg_dump -C mynewdb | psql -h db.server.host -U myuser mynewdb

Original command from stackoverflow.

1. Find your postgresql.conf

   sudo -u postgres psql -c "SHOW config_file;" 

2. Open the postgresql.conf

   #listen_addresses = 'localhost'
   listen_addresses = '*' # OR a specific IP instead of the wildcard for all IPs

3. Find your pghba.conf (on RHEL based distros: /var/lib/pgsql/data/pghba.conf)
4. Add the following lines

   # Allow external
   host    all             all             10.11.12.0/24              scram-sha-256 # for a network
   host    all             all             192.168.1.111/32           scram-sha-256 # for a single IP

5. Restart the DBMS

   systemctl restart postgresql.service

From my point of view I've a good and a bad example

The good example:

SELECT something FROM table 
WHERE time_stamp = (SELECT MAX(time_stamp) FROM table);

The bad example:

SELECT something FROM table 
WHERE time_stamp = (SELECT time_stamp FROM table ORDER BY time_stamp DESC NULLS LAST LIMIT 1);

Switch off the pages from CLI(shell)

sudo -u postgres psql -P pager=off -d database -c "SELECT something FROM table;"

Copy a SELECT to a CSV from CLI(shell)

sudo -u postgres psql -d database -c "COPY (SELECT  something, other_stuff FROM table 
GROUP BY something) TO '/tmp/result.csv' WITH CSV HEADER;"