How to create a new database in PostgreSQL

postgres=# create role music;
CREATE ROLE
postgres=# create database musicdb with owner = music;
CREATE DATABASE
postgres=# 

Documented here

Use can also use the wrapper "createdb" for convenience:

$ createdb -O music musicdb
CREATE DATABASE musicdb OWNER music;

A complete DDL for creating a database may look like this:

CREATE DATABASE memo
    WITH 
    OWNER = mysuperuser
    TEMPLATE = template0
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE memo
    IS 'my comment here';

How to display Linux kernel and version information on debian platforms

Use the lsb (Linux Standard Base) utility:

lsb_release -a

Output:

No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.1 LTS
Release: 18.04
Codename: bionic

Replace the -a switch with the -d switch to limit the output to the version only.

or view the file /etc/os-release:

cat /etc/os-release:

NAME="Ubuntu"
VERSION="18.04.1 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.1 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

Deprecated security parameter in Oracle 12c: SEC_CASE_SENSITIVE_LOGON

Oracle states:

"The use of the Oracle instance initialization parameter SEC_CASE_SENSITIVE_LOGON is deprecated in favor of setting the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter to 12 to ensure that passwords are treated in a case-sensitive fashion.
Disabling password case sensitivity is not supported in Exclusive mode (when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a.)

See this post for an example used during implementation

Documentation here

How to lookup ip addresses from mac addresses on Windows

Use the arp utility (Adress Resolution Protocol).

arp -a

it will print the arp table for you, where you can map ip addresses to mac addresses.

How to compress tables on import

Oracle 12c introduces a way to compress tables on the fly, during import using impdp.

Use the directive

transform=table_compression_clause:"row store compress advanced"

in your impdp parameter file.

Documentation here

How to unlock users with expired password in postgreSQL

select now()+'90 days' as expiry;
 expiry
-------------------------------
 2019-08-07 10:07:01.172082+02
\gset
alter user jim valid until :'expiry';
ALTER ROLE

The command "\gset" sends the current query buffer to the server and stores the query's output into a psql variable.

After the change, use \du to verify that the password has the correct expiry time:

mydb01=# \du
                                            List of roles
     Role name     |                         Attributes                         |      Member of
-------------------+------------------------------------------------------------+---------------------
 postgres          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until 2019-01-16 00:00:00+01                |
 superuser         | Superuser                                                 +| {}
                   | Password valid until 2019-08-04 00:00:00+02                |
 jim               | Password valid until 2019-08-07 10:07:03.237862+02         | {}

Use the "\list" command to list databases and access privileges, if desirable:

mydb01=# \list
                                    List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges
-----------+----------+----------+------------+------------+------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 mydb01    | jim      | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/jim                     +
           |          |          |            |            | jim=CTc/jim                 +

To give the user a new password, use this syntax:

alter role jim password 'mynewpassword';

Consult the documentation for more information

How to explain a query in PostgreSQL

If you are using partitioned tables, make sure you have enabled partition pruning:

SET enable_partition_pruning to on;

The explain statement is simple enough:

explain select * from documents where dokumenttype='SUBPOENA';

                            QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.00..1.02 rows=1 width=774)
   ->  Seq Scan on P_SUBPOENA  (cost=0.00..1.01 rows=1 width=774)
         Filter: ((documenttype)::text = 'SUBPOENA'::text)
(3 rows)

Since this is the LIST-partitioned table outlined in this post, I know the optimizer picked the correct partition for my predicate.

For the LIST-range subpartitioned table outlined in this post, I get the following query plan:

explain select * from orders where country_code='se' and order_total between 4000 and 4999;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..28.21 rows=1 width=50)
   ->  Seq Scan on large_orders_se  (cost=0.00..28.20 rows=1 width=50)
         Filter: ((order_total >= '4000'::numeric) AND (order_total <= '4999'::numeric) AND ((country_code)::text = 'se'::text))
(3 rows)

Regarding parallelism, the documentation tells you to watch for the terms Gather or Gather Merge Node in the query plan:
An example:

"Subquery Scan on andre  (cost=1000.00..10024950.78 rows=294885 width=84)"
"  ->  Gather  (cost=1000.00..10013892.60 rows=294885 width=64)"
"        Workers Planned: 2"
"        ->  ProjectSet  (cost=0.00..9983404.10 rows=29488500 width=64)"
"              ->  ProjectSet  (cost=0.00..9830063.90 rows=12286900 width=128)"

How to create a LIST partitioned table in PostgreSQL

Create the table:

CREATE TABLE DOCUMENTS(
  DOC_ID                      INTEGER  NOT NULL,
  LEGAL_ENTITY                INTEGER       NULL,
  CREATED_DT                  DATE      NOT NULL,
  REGION                      VARCHAR(30)   NULL,
  DOCUMENTTYPE                VARCHAR(100)  NULL,
  DOCUMENTNAME                VARCHAR(1000) NULL
)
PARTITION BY LIST (DOCUMENTTYPE);

Create a couple of partitions, including a default partition:

CREATE TABLE P_SUBPOENAS PARTITION OF  DOCUMENTS FOR VALUES IN  ('SUBPOENA');
CREATE TABLE P_AFFIDAVITS PARTITION OF DOCUMENTS FOR VALUES IN  ('AFFIDAVIT');
CREATE TABLE P_MEMORANDOMS PARTITION OF DOCUMENTS FOR VALUES IN ('MEMORANDOM');
CREATE TABLE P_DEFAULT PARTITION OF DOCUMENTS DEFAULT;

To add a primary key to a partitioned table, read this post

If your LIST-partitioned table would benefit from sub-partitioning, read this post

What exactly is meant by "global statistics" on a partitioned table?

From the whitepaper "Understanding Optimizer Statistics":

"When dealing with partitioned tables the Optimizer relies on both the statistics for the entire table
(global statistics) as well as the statistics for the individual partitions (partition statistics) to select a
good execution plan for a SQL statement. If the query needs to access only a single partition, the
Optimizer uses only the statistics of the accessed partition. If the query access more than one partition,
it uses a combination of global and partition statistics."