postgresql | list databases

\l

postgresql | list tables

\dt

postgresql | connect to db

\c DB_NAME

postgresql | date minus a couple of days

Using date and interval is weird. interval ‘2 days’ actually means yesterday. Today is the first day, yesterday is the second.

date > now() - interval '2 days'

postgresql | convert epoch to timestamp

SELECT timestamp 'epoch' + (hosts.firstseen) * interval '1 second' AS first  FROM hosts;

postgresql | create a list of database users

\du

postgresql | create user

CREATE ROLE ansibledb LOGIN;

postgresql | create database

CREATE DATABASE ansibledb WITH OWNER='ansibledb';

postgresql | timestamps as unix epoch

SELECT extract(epoch FROM timestamp) FROM sensors;

postgresql | change a user’s password

ALTER USER my_user_name with password 'my_secure_password';

postgresql | epoch to timestamp

select to_timestamp(field) from table;