How to create a LIST-RANGE sub-partitioned table in PostgreSQL

Create the table:

CREATE TABLE orders(
  id            integer,
  country_code  VARCHAR(5),
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2)
)
PARTITION BY LIST (country_code);

Create partition for your table. I have defined two. Define it so that it is ready to be further diveded into sub-partitions:

CREATE TABLE orders_NO
 PARTITION OF orders 
 FOR VALUES IN ('no') 
 PARTITION BY RANGE (order_total);

CREATE TABLE orders_SE
 PARTITION OF orders 
 FOR VALUES IN ('se') 
 PARTITION BY RANGE (order_total);

Finally, create your sub-partitions and set their boundaries:

CREATE TABLE small_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (4001) to (maxvalue);

CREATE TABLE small_orders_se
PARTITION OF orders_SE FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (4001) to (maxvalue);

How to avoid ERROR: no partition of relation ... found for row in PostgreSQL

Building on my previous post, I would now like to insert the following rows into my LIST partitioned table:

INSERT INTO ORDERS values (1,'NO',100,'01.02.2018',1000);
INSERT INTO ORDERS values (2,'SE',101,'01.03.2018',1200);

So one rows that I expect to go into the partition for Norway, and another row for the partition for Sweden.

I execute the following:

INSERT INTO ORDERS values (1,'NO',100,'01.02.2018',1000);

which results in an error:

Error: ERROR: no partition of relation "orders" found for row
  Detail: Partition key of the failing row contains (country_code) = (NO).

Contrary to Oracle, with PostgreSQL you need to explicitly create your partition first, like this:

CREATE TABLE orders_NO
 PARTITION OF orders FOR VALUES IN ('NO');

After this is done, PostgreSQL will accept your INSERT-statement.

You can also enforce contraints at the same time, if desirable:

CREATE TABLE orders_NO
 PARTITION OF orders (
 CONSTRAINT orders_id_nn CHECK (id is not null)
) FOR VALUES IN ('NO');

How to create a LIST-partitioned table with a primary key in PostgreSQL

In my experiements with a new RDBMS, I have tried to replicate what I am familiar with from Oracle, to Postgres.

Postgres supports partitioning: RANGE, LIST and HASH.

Here are some findings regarding LIST partitioning, which is extensively used in Oracle by my customers:

To create a list partitioned table in Postgres:

CREATE TABLE orders(
  order_id      integer,
  country_code  VARCHAR(5),
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2)
)
PARTITION BY LIST (country_code);

Add the desired partitions:

CREATE TABLE orders_NO
 PARTITION OF orders FOR VALUES IN ('NO');

CREATE TABLE orders_SE
 PARTITION OF orders FOR VALUES IN ('SE');

When working with Oracle, the equivalent table in Oracle could have been defined with a primary key on the column order_id:

CREATE TABLE orders(
  order_id      number primary key,
  country_code  varchar2(5),
  customer_id   number,
  order_date    date,
  order_total   number(8,2)
)
PARTITION BY LIST (country_code);

With PostgreSQL however, doing so will result in an error:

Error: ERROR: insufficient columns in PRIMARY KEY constraint definition Detail: PRIMARY KEY constraint on table "orders" lacks column "country_code" which is part of the partition key

If the primary key is changed to use the partitioning column, you'll get a valid primary key:

CREATE TABLE orders(
  id            integer,
  country_code  VARCHAR(5) primary key,
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2)
)
PARTITION BY LIST (country_code);

But this is not what I want, since my table is designed to store multiple values for each country.

To create a partitioned table including a valid primary key:

CREATE TABLE orders(
  id            integer,
  country_code  VARCHAR(5),
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2),
PRIMARY KEY (id,country_code)
)
PARTITION BY LIST (country_code);

As long as the primary key includes the partition key, the syntax is accepted by the postgres server.
This is different from Oracle, where you define the primary key independently of the partition key.

As with Oracle, a primary key constraint will automatically create a unique B-tree index.

Note that

PRIMARY KEY (id,country_code)

can alternatively be replaced by

UNIQUE (id,country_code)

The result is the same, either way.

How to connect to a postgres database using jdbc

Using SquirrelSQL I connected to a postgres database as can be seen below:




The entire connection string is

jdbc:postgresql://mypostgresserver01:5432/vegdb01

How to create a multicolumn list-partitioned table in Oracle 12.2

From Oracle 12.2, you can LIST partition a table using multiple columns:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) 
(
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         )
);

Incoming data that has any of the combinations below, will enter the partition NORWAY_SOUTH_WEST

'NOR','AUST-AGDER'
'NOR','VEST-AGDER'
'NOR','ROGALAND'

This functionality removes the need for LIST-LIST subpartitioning, as far as I can see.


Some queries to verify that the partitions exist:

select  partition_name,partition_position,tablespace_name
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME
NORWAY_SOUTH_WEST	1	USERS
NORWAY_WEST	2	USERS
NORWAY_CENTRAL	3	USERS
NORWAY_EAST	4	USERS

SELECT PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT 
FROM USER_PART_TABLES 
where table_name='REGIONAL_ORDERS';

PARTITIONING_TYPE	PARTITION_COUNT	PARTITIONING_KEY_COUNT
LIST	4	2

SELECT column_name,column_position 
FROM USER_PART_KEY_COLUMNS
where name='REGIONAL_ORDERS';

COLUMN_NAME	COLUMN_POSITION
COUNTRY_CODE	1
REGION_CODE	2

Let's insert a row:

insert into regional_orders
values (1,'NOR','ROGALAND',3344,'04.01.2019', 1000);
commit;

Analyze the table:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');

Verify:

select partition_name, num_rows
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	NUM_ROWS
NORWAY_CENTRAL	0
NORWAY_EAST	0
NORWAY_SOUTH_WEST	1
NORWAY_WEST	0

Insert with a different value:

insert into regional_orders
values (2,'NOR','BUSKERUD',3345,'04.01.2019', 1200);

COMMIT;

Analyze again:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');

Verify:

select partition_name, num_rows
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	NUM_ROWS
NORWAY_CENTRAL	0
NORWAY_EAST	1
NORWAY_SOUTH_WEST	1
NORWAY_WEST	0

If you send in a value that is not covered in your pre-specified list, your statement will fail:

insert into regional_orders
values (3,'NOR','NORDLAND',3346,'04.01.2019', 1100);

ORA-14400: inserted partition key does not map to any partition

The solution to this is to either create your table with a default partition or to use the Oracle 12.2 new feature of automatic list partitioning.

Using a default partition will be as follows:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) 
                (       
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         ),
                PARTITION NORWAY_REST VALUES (DEFAULT) 
                )
;

Using the AUTOMATIC keyword will be as easy as adding the keyword AUTOMATIC to your partitioning-clause and to remove the specification for your overflow partition:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) AUTOMATIC
                (       
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         )
                )
;

New values will be added automatically and given system-generated partition names.

There is an obvious trade-off here: if you use automation, you will not be able to give your partition logical names and place values logically belong together, in the same partition.

For example, the values

'NOR','NORDLAND'

may logically belong to the same partition as the values

'NOR','TROMS'
and
'NOR','FINNMARK'

If I knew such a value may be sent at the time of table creation, I would of course create another partition, like this:

PARTITION NORWAY_NORTH VALUES (
                                        ( 'NOR','NORDLAND'),
                                        ( 'NOR','TROMS'),
                                        ( 'NOR','FINNMARK')
                                         )

If you use a default partition, you will always be able to save your incoming rows, and you have full controll over your partition names and their values.
But new rows end up in an overflow partition together with any other record that cannot find its way into a properly named and configured partition.
You may need to split your default partition later, to satisify your business needs or to reduce the size of your default partition.

How to solve the problem with ORA-00997: illegal use of LONG datatype when quering the dba_tab_partitions

If you need to selectively find partitions based on their upper-bound value, like this:

select partition_name,high_value
from dba_tab_partitions
where table_name='MYTABLE'
and high_value='201805';

you will run into the following error:

ORA-00997: illegal use of LONG datatype

One solution I found was to create a temporary table, like this:

CREATE GLOBAL TEMPORARY TABLE PARTITION_HIGH_VAL
ON COMMIT PRESERVE ROWS
AS
select 
     PARTITION_NAME,
     to_lob(high_value) as text_clob
     from dba_tab_partitions 
     where TABLE_OWNER='SH'
     and table_name='SALES';

Then, use the table PARTITION_HIGH_VAL to view the upper bounds of each partition:

select PARTITION_NAME,TO_CHAR(TEXT_CLOB) "high value" 
from partition_high_Val;

PARTITION_NAME	high value
SYS_P79451	201806
SYS_P71617	201805
SYS_P70243	201803
SYS_P70231	201804
SYS_P70112	201802

Since this is a temporary table, your rows will be deleted upon your session exit because of the directive ON COMMIT PRESERVE ROWS.
Obviously, you could create a heap-organized table to store these results in, too, if you need to store your data for a longer period.

How to setup Oracle Network Integrity

Encryption will protect your data from being read in clear text, when in transition.
However, data can still be tampered with and arrive modified at the receiver's end.

To protect against modification, you need to ensure the integrity of the data being sent over the network.

Data integrity protection from Oracle works independently from the encryption process. In other words, you can use both encryption and checksuming together or either one separately.

To implement network integrity, add the following to your server’s sqlnet.ora file:

SQLNET.CRYPTO_CHECKSUM_SERVER = requested
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA256, SHA384, SHA512, SHA1, MD5)

All of the hash algorithms in the list create a checksum that changes if the data is altered in any way.

The value "requested" in the CRYPTO_CHECKSUM_SERVER instructs the Oracle server that calculating checksums when sending network packets to the client is desirable, but not required. Other valid settings are "required", "accepted" and "rejected". See the documentation for details.

The list of available encryption algorithms are stated in the CRYPTO_CHECKSUM_TYPE_SERVER, and the first one common to both client and server in the list will be selected. During my tests, the SHA256 was selected.

More information is to be found in the official documentation