They speed up qeuries in an SQL database They take up space - DB needs to be reindexed when new data is added.

Links:

  • upsert: update or insert of not there
  • Date/numerics formatting: https://www.postgresql.org/docs/current/functions-formatting.html
    • to_char(current_timestamp, 'HH12:MI:SS')
  • Enable logging for queries: https://tableplus.com/blog/2018/10/how-to-show-queries-log-in-postgresql.html
    • DISTINCT in postgres, implicitly sorts the array of data! Using DISTINCT implicitly sorts the array. If the relative order of the array elements needs to be preserved while removing duplicates, the function can be designed like the following: (should work from 9.4 onwards) Solution: select some_id from (select distinct on (some_id) * from "user-table@gmail.com".session_metadata ORDER BY some_id, some_column DESC) t ORDER by some_column DESC
    • \list
    • List all schemas: \dn / \dnS+
    • Connect to database: \c example
    • List all databases: select datname from pg_catalog.pg_database;
    • \q
    • List all roles (users + groups) -> \du OR \dg
    • Add a new role with superuser rights, e.g., berger -> CREATE USER berger WITH SUPERUSER;
    • Add a new attribute to a role, e.g., berger -> ALTER USER berger WITH SUPERUSER;
    • Remove an attribute from a role, e.g., berger -> ALTER USER berger WITH NOSUPERUSER;
    • Change password fora a role/user: ALTER USER <username> WITH PASSWORD '<passwd>';
    • Show all tables:
      • \d OR \dt OR \dtS
    • Show information about a table
      • \d <pg_am>
    • Create a table: 
          CREATE TABLE comics(
      name varchar(80),
      publisher varchar(80),
      date_published date
    );
    • Create table under a particular schema 
          create table myschema.company(
      ID   INT              NOT NULL,
      NAME VARCHAR (20)     NOT NULL,
      AGE  INT              NOT NULL,
      ADDRESS  CHAR (25),
      SALARY   DECIMAL (18, 2),
      PRIMARY KEY (ID)
    );
    • Find out if a table exists: 
         SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = '<schema-name-or-if-none == public>'
   AND    tablename  = '<some-table-name>'
   );
    • Find out if a database exists:
      • select datname from pg_catalog.pg_database WHERE lower(datname) = lower('<your-db-name>');
    • Find out if a schema exists:
      • select nspname from pg_catalog.pg_namespace WHERE nspname='someuser@gmail.com'
    • See all the tables of a schema:
      • \dt "schema_name".*
    • Show location of config file for postgres: SHOW config_file;
    • Convert an integer to string/varchar
    • Check that an integer is in array of integers:
    • Restore a pg dump file:
      • pg_restore -f <path/to/dump/file>
    • dropuser <user-name>
    • When installing, create a db for your user so that you can login with psql 
          createdb
    psql # this should now work from your user account
    • dropdb <dbname>
    • Connect as a specific user (other than your UNIX username): psql --host=localhost --dbname=example --username=grafanareader
    • Schema: A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. PostgreSQL statement CREATE SCHEMA creates a schema.
    • 4 Types of SQL Commands;
      • DDL: Data Definition Language
      • DML: Data Manipulation Language
      • DCL: Data Control Language
      • TCL: Transaction Control Language
      • What's the difference between DDL and DML?
      • DDL vs DML:

    • How to you set the instantclient/tnsnames path in SQL Developer:
      • Tools > Preferences Database > Advanced > Tnsnames Directory
    • sqlite databases only allow a single connection at the time. So for example, with software like, Anki you are only supposed to have a single connection open and a single tool accessing the DB, even if that's only for read operations.
    • Oracle Client comes with an installer and a lot of executable like sqlplus, tnsping, it's complete and huge.
    • Oracle Instant client is a basic lightweight client which can be unzipped in a location without any installation, it contains only the communication layer to be able to connect to oracle. It can be extended by adding for example sqlplus which ships as a separate executable. When using Instant Client, just unzip it to a location, set your ORACLE_HOME environment variable to this location and you are ready to go.