List Partition in Postgres

Question

I'm using Enterprise Postgres with Oracle Compatibilty. Here is a table I have created in my database.

CREATE TABLE ALL_COUNTRIES 
(
  COUNTRY_ID                numeric(20,0),
  CHARACTERISTIC_NAME       character varying(255)
) 
PARTITION BY LIST (COUNTRY_ID) 
(
  PARTITION COUNTRY VALUES (484, 170, 76, 360, 710) TABLESPACE my_tbs
);

Two tables are created. One is main table and the other is partitioned table.

Main table:

CREATE TABLE cdar_panel.all_countries
(
    country_id numeric(20,0),
    characteristic_name character varying(255) 
)

Partitioned table:

CREATE TABLE cdar_panel.all_countries_country
(
    country_id ,
    characteristic_name ,
    CONSTRAINT all_countries_country_partition CHECK ((country_id = ANY (ARRAY['484'::numeric(20,0), '170'::numeric(20,0), '76'::numeric(20,0), '360'::numeric(20,0), '710'::numeric(20,0)])) AND country_id IS NOT NULL)
)
    INHERITS (cdar_panel.all_countries)

All I want to do is add two more fields in that CHECK constraint. May I know how to do that.

It's not allowing me to 1. alter constraint. 2. Can't drop "only partition". 3. Can't add one more constraint and delete the original.

Please help.

score 4 · Answer 1 · edited May 22 '20 at 11:23

The example that follows deletes a partition of the sales table. Use the following command to create the sales table:

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date    date,
  amount  number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

Querying the ALL_TAB_PARTITIONS view displays the partition names:

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name | server_name |     high_value      
----------------+-------------+---------------------
 europe         | seattle     | 'FRANCE', 'ITALY'
 asia           | chicago     | 'INDIA', 'PAKISTAN'
 americas       | boston      | 'US', 'CANADA'
(3 rows)

To delete the americas partition from the sales table, invoke the following command:

ALTER TABLE sales DROP PARTITION americas;

Querying the ALL_TAB_PARTITIONS view demonstrates that the partition has been successfully deleted:

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 asia           | 'INDIA', 'PAKISTAN'
 europe         | 'FRANCE', 'ITALY'
(2 rows)

i hope this will help regarding dropping a partition :)

List Partition in Postgres

1 Answers1