Postgres

Resources

• Top Postgres Links of 2019
• Don't Do These Things In Postgres
• Improve Performance of COUNT(*)

Frequently Used Queries

http://www.postgresqltutorial.com

# change user's password
ALTER USER user_name WITH PASSWORD 'new_password';

# list databases - shows name and owner
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
ORDER BY 1;

# list database users 
SELECT usename FROM pg_user;

# show what database user can do
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

# Show all tables with schema name and table owner.
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

# List databases (names)
SELECT datname FROM pg_database;

# Show database owners
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
ORDER BY 1;

# Show table details (max, min, numeric precision, data_type, etc.
SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'users'

# list schemas for database
SELECT nspname FROM pg_catalog.pg_namespace;

# list tables in database schema
SELECT table_name FROM information_schema.tables WHERE table_schema='public';

# list schema, table, and table owner.
select * from pg_tables where schemaname = 'schema';

# ** show what current user can do to a table 
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='abuses'

Order

Nulls first/Nulls Last

https://www.postgresql.org/docs/9.6/queries-order.html

The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

# Sort so null values come last
mytable.score IS NULL, mytable.score DESC

Case Sensitivity

Strings are case sensitive. Workarounds:

• Use the citext extension. It requires superuser to install and you have to install per database: CREATE EXTENSION IF NOT EXISTS citext; Or install once by installing the extension in template1. Use if applying a unique index on a field with unknown character case (i.e., API Foobar, CSS, Hammer).
• Use ILIKE instead of LIKE
• Add an index on lower(email) and use rails downcast() or Postgres lower() to convert input to lowercase.
• Don't use Postgres lower() on the column itself, bad performance! i.e, where lower(title) = 'foo'

# Rails migrations with citest
class CreateUsers < ActiveRecord::Migration[5.1]
  def change
    enable_extension 'citext'

    create_table :users do |t|
      t.citext :first_name, null: false
      t.citext :last_name, null: false
      t.citext :email, null: false
      t.timestamps
    end
    add_index :users, [:first_name, :last_name, :email], unique: true
  end
end