### Create Time-Partitioned Table and Data

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Example of creating a time-partitioned table with several child partitions and inserting sample data. This setup is typical for time-series data.

```sql
CREATE TABLE tracking.hits_time (id int GENERATED BY DEFAULT AS IDENTITY NOT NULL, start timestamptz DEFAULT now() NOT NULL) PARTITION BY RANGE (start);
CREATE INDEX ON tracking.hits_time (start);
CREATE TABLE tracking.hits_time2023_02_26 partition of tracking.hits_time FOR VALUES FROM ('2023-02-26'::timestamptz) TO ('2023-03-05'::timestamptz);
CREATE TABLE tracking.hits_time2023_03_05 partition of tracking.hits_time FOR VALUES FROM ('2023-03-05'::timestamptz) TO ('2023-03-12'::timestamptz);
CREATE TABLE tracking.hits_time2023_03_12 partition of tracking.hits_time FOR VALUES FROM ('2023-03-12'::timestamptz) TO ('2023-03-19'::timestamptz);

INSERT INTO tracking.hits_time VALUES (1, generate_series('2023-02-27 01:00:00'::timestamptz, '2023-03-04 23:00:00'::timestamptz, '1 hour'::interval));
INSERT INTO tracking.hits_time VALUES (2, generate_series('2023-03-06 01:00:00'::timestamptz, '2023-03-11 23:00:00'::timestamptz, '1 hour'::interval));
INSERT INTO tracking.hits_time VALUES (3, generate_series('2023-03-12 01:00:00'::timestamptz, '2023-03-18 23:00:00'::timestamptz, '1 hour'::interval));
```

```sql
\d+ tracking.hits_time
                                                      Partitioned table "tracking.hits_time"
 Column |           Type           | Collation | Nullable |             Default              | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+---------+-------------+--------------+-------------
 id     | integer                  |           | not null | generated by default as identity | plain   |             |              |
 start  | timestamp with time zone |           | not null | now()                            | plain   |             |              |
Partition key: RANGE (start)
Indexes:
    "hits_time_start_idx" btree (start)
Partitions: tracking.hits_time20230226 FOR VALUES FROM ('2023-02-26 00:00:00-05') TO ('2023-03-05 00:00:00-05'),
            tracking.hits_time20230305 FOR VALUES FROM ('2023-03-05 00:00:00-05') TO ('2023-03-12 00:00:00-05'),
            tracking.hits_time20230312 FOR VALUES FROM ('2023-03-12 00:00:00-05') TO ('2023-03-19 00:00:00-04')
```

--------------------------------

### Install pg_partman from Source

Source: https://github.com/pgpartman/pg_partman/blob/development/README.md

Installs the pg_partman extension from source. Ensure you are in the directory where pg_partman was downloaded.

```sh
make install
```

--------------------------------

### Create Time-Partitioned Table and Partitions

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Example of creating a parent table partitioned by time and its initial child partitions.

```sql
CREATE TABLE tracking.hits_stufftimeaa partition of tracking.hits_stufftime FOR VALUES FROM ('2023-01-01'::timestamptz) TO ('2023-01-08'::timestamptz);
CREATE TABLE tracking.hits_stufftimebb partition of tracking.hits_stufftime FOR VALUES FROM ('2023-01-08'::timestamptz) TO ('2023-01-15'::timestamptz);
CREATE TABLE tracking.hits_stufftimecc partition of tracking.hits_stufftime FOR VALUES FROM ('2023-01-15'::timestamptz) TO ('2023-01-22'::timestamptz);
```

--------------------------------

### Create ID-Partitioned Table and Data

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Example of creating an ID-partitioned table with several child partitions and inserting sample data. This setup is useful for tables where partitioning by a sequential ID is appropriate.

```sql
CREATE TABLE tracking.hits_id (id int GENERATED BY DEFAULT AS IDENTITY NOT NULL, start timestamptz DEFAULT now() NOT NULL) PARTITION BY RANGE (id);
CREATE INDEX ON tracking.hits_id (id);
CREATE TABLE tracking.hits_id1000 partition of tracking.hits_id FOR VALUES FROM (1000) TO (2000);
CREATE TABLE tracking.hits_id2000 partition of tracking.hits_id FOR VALUES FROM (2000) TO (3000);
CREATE TABLE tracking.hits_id3000 partition of tracking.hits_id FOR VALUES FROM (3000) TO (4000);

INSERT INTO tracking.hits_id VALUES (generate_series(1000,1999), now());
INSERT INTO tracking.hits_id VALUES (generate_series(2000,2999), now());
INSERT INTO tracking.hits_id VALUES (generate_series(3000,3999), now());
```

```sql
\d+ tracking.hits_id
                                                       Partitioned table "tracking.hits_id"
 Column |           Type           | Collation | Nullable |             Default              | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+---------+-------------+--------------+-------------
 id     | integer                  |           | not null | generated by default as identity | plain   |             |              |
 start  | timestamp with time zone |           | not null | now()                            | plain   |             |              |
Partition key: RANGE (id)
Indexes:
    "hits_id_id_idx" btree (id)
Partitions: tracking.hits_id1000 FOR VALUES FROM (1000) TO (2000),
            tracking.hits_id2000 FOR VALUES FROM (2000) TO (3000),
            tracking.hits_id3000 FOR VALUES FROM (3000) TO (4000)
```

--------------------------------

### Create Serial-Partitioned Table and Partitions

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Example of creating a parent table partitioned by a serial integer and its initial child partitions.

```sql
CREATE TABLE tracking.hits_stuffid (id int GENERATED BY DEFAULT AS IDENTITY NOT NULL, start timestamptz DEFAULT now() NOT NULL) PARTITION BY RANGE (id);
CREATE INDEX ON tracking.hits_stuffid (id);
CREATE TABLE tracking.hits_stuffidaa partition of tracking.hits_stuffid FOR VALUES FROM (1000) TO (2000);
CREATE TABLE tracking.hits_stuffidbb partition of tracking.hits_stuffid FOR VALUES FROM (2000) TO (3000);
CREATE TABLE tracking.hits_stuffidcc partition of tracking.hits_stuffid FOR VALUES FROM (3000) TO (4000);
```

--------------------------------

### Example of create_partition() with named arguments

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Demonstrates how to use named arguments for the `create_partition()` function to improve clarity and avoid confusion, especially when many arguments are available. Default values do not require explicit passing.

```sql
SELECT create_partition('schema.table', 'col1',  '1 day', p_start_partition := '2023-03-20');
```

--------------------------------

### Define Daily Time-Based Partitions

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

This example shows how to define daily partitions for a time-based partitioned table. It includes partitions for specific dates and a default partition.

```sql
partman_test.time_taptest_table_p20230326 FOR VALUES FROM ('2023-03-26 00:00:00-07') TO ('2023-03-27 00:00:00-07'),
            partman_test.time_taptest_table_p20230327 FOR VALUES FROM ('2023-03-27 00:00:00-07') TO ('2023-03-28 00:00:00-07'),
            partman_test.time_taptest_table_p20230328 FOR VALUES FROM ('2023-03-28 00:00:00-07') TO ('2023-03-29 00:00:00-07'),
            partman_test.time_taptest_table_p20230329 FOR VALUES FROM ('2023-03-29 00:00:00-07') TO ('2023-03-30 00:00:00-07'),
            partman_test.time_taptest_table_p20230330 FOR VALUES FROM ('2023-03-30 00:00:00-07') TO ('2023-03-31 00:00:00-07'),
            partman_test.time_taptest_table_p20230331 FOR VALUES FROM ('2023-03-31 00:00:00-07') TO ('2023-04-01 00:00:00-07'),
            partman_test.time_taptest_table_p20230401 FOR VALUES FROM ('2023-04-01 00:00:00-07') TO ('2023-04-02 00:00:00-07'),
            partman_test.time_taptest_table_default DEFAULT
```

--------------------------------

### Example of Weekly Partitioning

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_5.0.1_upgrade.md

Illustrates the structure of weekly partitioned tables created by pg_partman, showing partitions for specific date ranges and a default partition.

```sql
partman_test.time_taptest_table_p20230403 FOR VALUES FROM ('2023-04-03 00:00:00-04') TO ('2023-04-10 00:00:00-04'),
            partman_test.time_taptest_table_p20230410 FOR VALUES FROM ('2023-04-10 00:00:00-04') TO ('2023-04-17 00:00:00-04'),
            partman_test.time_taptest_table_p20230417 FOR VALUES FROM ('2023-04-17 00:00:00-04') TO ('2023-04-24 00:00:00-04'),
            partman_test.time_taptest_table_p20230424 FOR VALUES FROM ('2023-04-24 00:00:00-04') TO ('2023-05-01 00:00:00-04'),
            partman_test.time_taptest_table_p20230501 FOR VALUES FROM ('2023-05-01 00:00:00-04') TO ('2023-05-08 00:00:00-04'),
            partman_test.time_taptest_table_p20230508 FOR VALUES FROM ('2023-05-08 00:00:00-04') TO ('2023-05-15 00:00:00-04'),
            partman_test.time_taptest_table_default DEFAULT
```

--------------------------------

### Determine Partition Range Start

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Queries the original table to find the minimum and maximum values of the 'col3' column. This is used to determine an appropriate start date for partitioning to avoid conflicts with existing data when creating the default partition.

```sql
SELECT min(col3), max(col3) FROM original_table;
```

--------------------------------

### Create Partition with Specific Start Date

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_5.0.1_upgrade.md

Creates a new partition for a time-based table, ensuring the partition starts on a Monday by using `date_trunc` and formatting the output. This is useful for maintaining consistent weekly partition start dates.

```sql
SELECT partman.create_partition('public.time_table', 'col3', '1 week', p_start_partition := to_char(date_trunc('week',CURRENT_TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS'));
```

--------------------------------

### Create Initial Partition with pg_partman

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Uses the `create_partition` function from pg_partman to create the first partition for a parent table. The interval is set to '10' for this example.

```sql
SELECT partman.create_partition(
    p_parent_table := 'partman_test.id_taptest_table'
    , p_control := 'col1'
    , p_interval := '10'
);
```

--------------------------------

### Create Weekly Partitions Anchored to Monday

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Sets up weekly partitions for a timestamp column, anchored to the start of each week (Monday). The `p_start_partition` parameter specifies the anchor point for the first partition.

```sql
CREATE TABLE public.events (
    id   bigint,
    ts   timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (ts);

SELECT partman.create_partition(
    p_parent_table        := 'public.events',
    p_control             := 'ts',
    p_interval            := '1 week',
    p_date_trunc_interval := 'week',
    p_start_partition     := to_char(date_trunc('week', CURRENT_TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')
);
```

--------------------------------

### Create Initial Partition with pg_partman

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Use this function to add an existing partition set to pg_partman's configuration and potentially create new child tables. The `p_start_partition` parameter is crucial for defining the starting point, especially when dealing with historical data or specific interval requirements. Test thoroughly before production.

```sql
SELECT partman.create_partition('tracking.hits_time', 'start', '1 week', p_start_partition := '2023-03-26 00:00:00');
COMMIT;
```

--------------------------------

### Describe Partitioned Table Structure

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

This command displays the structure of a partitioned table, including its column definitions, partition key, and existing partitions. It is useful for verifying the setup after using `create_partition` and understanding how pg_partman has organized the data.

```sql
\d+ tracking.hits_time
```

--------------------------------

### Insert Data into Time-Partitioned Table

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

Example of inserting data into a time-partitioned table using generate_series.

```sql
INSERT INTO tracking.hits_stufftime VALUES (1, generate_series('2023-01-02 01:00:00'::timestamptz, '2023-01-06 23:00:00'::timestamptz, '1 hour'::interval));
INSERT INTO tracking.hits_stufftime VALUES (2, generate_series('2023-01-09 01:00:00'::timestamptz, '2023-01-13 23:00:00'::timestamptz, '1 hour'::interval));
INSERT INTO tracking.hits_stufftime VALUES (3, generate_series('2023-01-15 01:00:00'::timestamptz, '2023-01-20 23:00:00'::timestamptz, '1 hour'::interval));
```

--------------------------------

### View Managed Partition Sets

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Query the `part_config` table to see all tables managed by pg_partman and their partitioning intervals. Useful for an overview of your partitioning setup.

```sql
SELECT parent_table, control, partition_interval, premake, retention, automatic_maintenance
FROM partman.part_config
ORDER BY parent_table;
```

--------------------------------

### Partition Data by Time (SQL)

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Use this function to partition existing data into time-based partitions, especially for data that existed before the parent table was set up. It also handles data inserted into the default table. For subpartitioned sets, start at the highest level and work down. Be cautious with subpartitioning as it can be a destructive operation.

```sql
partition_data_time(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval interval DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
    , p_override_system_value boolean DEFAULT false
    , p_ignore_infinity boolean DEFAULT false
)
RETURNS bigint
```

--------------------------------

### Create New Partition with pg_partman

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Use this function to create a new partition for a parent table. Specify the parent table, control column, interval, template table, premake count, and the start of the new partition.

```sql
SELECT partman.create_partition(
    p_parent_table := 'public.new_partitioned_table'
    , p_control := 'col3'
    , p_interval := '1 day'
    , p_template_table:= 'public.original_table_template'
    , p_premake := 1
    , p_start_partition := (CURRENT_TIMESTAMP+'2 days'::interval)::text
    , p_default_table := false
);
```

--------------------------------

### Configure Partition Retention

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Update the `part_config` table to set a retention policy for old partitions. This example enables dropping partitions older than 6 months, keeping only the table structure and indexes.

```sql
UPDATE partman.part_config
SET retention            = '6 months',
    retention_keep_table = false,
    retention_keep_index = false
WHERE parent_table = 'partman_test.time_taptest_table';
```

--------------------------------

### Get Partition Boundary Information

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Retrieves boundary details (start and end times/IDs) for a specific child table of a partition set. This function helps in understanding the data range covered by individual partitions.

```sql
SELECT * FROM partman.show_partition_info(
    p_child_table := 'partman_test.time_taptest_table_p20240315'
);
-- child_start_time             | child_end_time              | child_start_id | child_end_id | suffix
-- ----------------------------+-----------------------------+----------------+--------------+--------
-- 2024-03-15 00:00:00+00      | 2024-03-16 00:00:00+00      |                |              | 20240315
```

--------------------------------

### Install pg_partman without Background Worker

Source: https://github.com/pgpartman/pg_partman/blob/development/README.md

Installs only the PL/pgSQL functions of pg_partman, excluding the background worker process. Use this if you do not require the BGW functionality.

```sh
make NO_BGW=1 install
```

--------------------------------

### Update pg_partman Extension Version

Source: https://github.com/pgpartman/pg_partman/blob/development/README.md

Run this SQL command within PostgreSQL to update the pg_partman extension to the latest installed version. Ensure 'make install' or package updates are done first.

```sql
ALTER EXTENSION pg_partman UPDATE TO '<latest version>';
```

--------------------------------

### Create Partitioned Parent Table and Initial Partition

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Sets up the new partitioned parent table and creates the first partition using pg_partman's create_partition function. Ensure all original table properties like indexes and defaults are reapplied.

```sql
CREATE TABLE public.original_table (
    col1 bigint not null
    , col2 text not null
    , col3 timestamptz DEFAULT now()
    , col4 text)
PARTITION BY RANGE (col1);

CREATE INDEX ON public.original_table (col1);

SELECT partman.create_partition(
    p_parent_table := 'public.original_table'
    , p_control := 'col1'
    , p_interval := '10000'
);
```

--------------------------------

### show_partition_info

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Retrieves boundary details (start and end times/IDs) for a specific child table of a partition set.

```APIDOC
## `show_partition_info` — boundary details for a specific child table

### Usage
```sql
SELECT * FROM partman.show_partition_info(
    p_child_table := 'partman_test.time_taptest_table_p20240315'
);
-- Example Output:
-- child_start_time             | child_end_time              | child_start_id | child_end_id | suffix
-- ----------------------------+-----------------------------+----------------+--------------+--------
-- 2024-03-15 00:00:00+00      | 2024-03-16 00:00:00+00      |                |              | 20240315
```
```

--------------------------------

### Get Current Timestamp

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_5.0.1_upgrade.md

Retrieves the current timestamp from the database. This is often used as a reference point for partition creation.

```sql
SELECT CURRENT_TIMESTAMP;
```

--------------------------------

### Run Maintenance and Verify Partitions

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_5.0.1_upgrade.md

Increments the premake setting and enables infinite time partitions, then runs the maintenance function to create new partitions. This step verifies that the configuration changes are applied correctly and new partitions are generated.

```sql
UPDATE partman.part_config SET premake = premake+1, infinite_time_partitions = true;

SELECT partman.run_maintenance('partman_test.time_taptest_table');
```

--------------------------------

### Drop pg_partman Extension

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/fix_missing_procedures.md

This command drops the pg_partman extension from the database. If it was installed in a specific schema, ensure you note it for reinstallation.

```sql
DROP EXTENSION pg_partman;
```

--------------------------------

### Create Default Partition Table

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_declarative.md

Use this command to create a new table that will serve as the default partition. It should be a copy of the structure of the main partitioned table.

```sql
CREATE TABLE partman_test.time_taptest_table_default (LIKE partman_test.time_taptest_table INCLUDING ALL);
```

--------------------------------

### Create Initial Partition with Template

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Uses the `create_partition` function from pg_partman to create the first child partition for a parent table. It specifies the control column, interval, and the template table to ensure properties like primary keys are applied.

```sql
SELECT partman.create_partition(
    p_parent_table := 'partman_test.time_taptest_table'
    , p_control := 'col3'
    , p_interval := '1 day'
    , p_template_table := 'partman_test.time_taptest_table_template'
);
 create_parent
---------------
 t
(1 row)
```

--------------------------------

### Reinstall pg_partman Extension

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/fix_missing_procedures.md

Reinstall the pg_partman extension, specifying the same schema it was previously installed in. This ensures the extension is set up correctly with all its procedures.

```sql
CREATE EXTENSION pg_partman SCHEMA partman;
```

--------------------------------

### Insert Data into Partitioned Table

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Demonstrates how to insert data into a partitioned table. The `now()` function ensures the data is placed into the correct partition based on the current timestamp.

```sql
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
```

```sql
SELECT * FROM original_table ORDER BY col1 DESC limit 5;
```

--------------------------------

### Rename Weekly Partitions SQL

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_5.0.1_upgrade.md

This SQL query renames existing weekly partitions to a new format, aligning with the ISO week start day.

```sql
SELECT c.relname,
       'ALTER TABLE ' || c.relname || ' RENAME TO ' || replace(c.relname, '_w', '') || ';
' AS statement
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname ~ '_w[0-9]+$' AND n.nspname = 'partman_test'
AND c.relname NOT LIKE '%_default'
ORDER BY c.relname;
```

--------------------------------

### Inspect Partitioned Table with Newly Created Partitions

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

After creating partitions, use \d+ to view the updated table structure, which now lists the automatically generated partitions and their value ranges.

```sql
                              Partitioned table "partman_test.time_taptest_table"
 Column |  Type   | Collation | Nullable |    Default    | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------------+----------+-------------+--------------+-------------
 col1   | integer |           |          |               | plain    |             |              |
 col2   | text    |           |          | 'stuff'::text | extended |             |              |
 col3   | text    |           | not null |               | extended |             |              |
Partition key: RANGE (col3)
Indexes:
    "time_taptest_table_pkey" PRIMARY KEY, btree (col3)
Partitions: time_taptest_table_p20240815 FOR VALUES FROM ('INV20240815') TO ('INV20240816'),
            time_taptest_table_p20240816 FOR VALUES FROM ('INV20240816') TO ('INV20240817'),
            time_taptest_table_p20240817 FOR VALUES FROM ('INV20240817') TO ('INV20240818'),
            time_taptest_table_p20240818 FOR VALUES FROM ('INV20240818') TO ('INV20240819'),
            time_taptest_table_p20240819 FOR VALUES FROM ('INV20240819') TO ('INV20240820'),
            time_taptest_table_p20240820 FOR VALUES FROM ('INV20240820') TO ('INV20240821'),
            time_taptest_table_p20240821 FOR VALUES FROM ('INV20240821') TO ('INV20240822'),
            time_taptest_table_p20240822 FOR VALUES FROM ('INV20240822') TO ('INV20240823'),
            time_taptest_table_p20240823 FOR VALUES FROM ('INV20240823') TO ('INV20240824'),
            time_taptest_table_default DEFAULT
```

--------------------------------

### Create and Populate Original Table

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Defines and inserts data into the initial non-partitioned table before conversion.

```sql
CREATE TABLE public.original_table (
    col1 bigint not null
    , col2 text not null
    , col3 timestamptz DEFAULT now()
    , col4 text);

CREATE INDEX ON public.original_table (col1);

INSERT INTO public.original_table (col1, col2, col3, col4) VALUES (generate_series(1,100000), 'stuff'||generate_series(1,100000), now(), 'stuff');
```

--------------------------------

### Create Daily Time-Based Partition Set with Template

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Initializes a daily time-based partition set for a parent table. Uses a template table to propagate non-partition-key primary keys to child tables. Ensure the parent table and its indexes are created before calling.

```sql
CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.time_taptest_table (
    col1 int,
    col2 text DEFAULT 'stuff',
    col3 timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (col3);

CREATE INDEX ON partman_test.time_taptest_table (col3);

-- Template table lets pg_partman propagate non-partition-key PKs to children
CREATE TABLE partman_test.time_taptest_table_template
    (LIKE partman_test.time_taptest_table);
ALTER TABLE partman_test.time_taptest_table_template ADD PRIMARY KEY (col1);

SELECT partman.create_partition(
    p_parent_table   := 'partman_test.time_taptest_table',
    p_control        := 'col3',
    p_interval       := '1 day',
    p_template_table := 'partman_test.time_taptest_table_template'
);
-- Returns: t
```

--------------------------------

### Dump pg_partman Configuration Tables

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/fix_missing_procedures.md

Perform a plaintext dump of the pg_partman configuration tables to back up existing settings before dropping the extension. This command assumes pg_partman is installed in the 'partman' schema.

```sh
pg_dump -d mydbname -Fp -a -f partman_update_procedures.sql -t partman.part_config -t partman.part_config_sub
```

--------------------------------

### Verify Partitioned Table Structure

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Displays the structure of the newly created partitioned table, including its columns, partition key, and existing partitions.

```sql
\d+ original_table;
                                        Partitioned table "public.original_table"
 Column |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 col1   | bigint                   |           | not null |         | plain    |             |              |
 col2   | text                     |           | not null |         | extended |             |              |
 col3   | timestamp with time zone |           |          | now()   | plain    |             |              |
 col4   | text                     |           |          |         | extended |             |              |
Partition key: RANGE (col1)
Indexes:
    "original_table_col1_idx1" btree (col1)
    "original_table_col1_idx2" btree (col1)
Partitions: original_table_p0 FOR VALUES FROM ('0') TO ('10000'),
            original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'),
            original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'),
            original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'),
            original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'),
            original_table_default DEFAULT
```

--------------------------------

### Begin Transaction and Lock Table

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_declarative.md

To ensure data consistency during migration, start a transaction and acquire an exclusive lock on the original table. This prevents concurrent modifications and allows for rollback if issues arise.

```sql
BEGIN;
LOCK TABLE partman_test.time_taptest_table IN ACCESS EXCLUSIVE MODE NOWAIT;
```

--------------------------------

### Create ID/Serial Partition Set (10-Row Buckets)

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Initializes a partition set based on a numeric ID column, creating child tables in 10-row increments. No manual template table is required for this configuration.

```sql
CREATE TABLE partman_test.id_taptest_table (
    col1 bigint NOT NULL,
    col2 text,
    col3 timestamptz DEFAULT now() NOT NULL
) PARTITION BY RANGE (col1);

CREATE INDEX ON partman_test.id_taptest_table (col1);

SELECT partman.create_partition(
    p_parent_table := 'partman_test.id_taptest_table',
    p_control      := 'col1',
    p_interval     := '10'
);
-- Returns: t  — creates p0,p10,p20,p30,p40 + default
```

--------------------------------

### Create Partitioned Table with Text Control Column

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Define a partitioned table using a text column as the partition key. This setup is for time-based partitioning where the text column contains a timestamp-like identifier.

```sql
CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.time_taptest_table
    (col1 int,
    col2 text default 'stuff',
    col3 text PRIMARY KEY)
PARTITION BY RANGE (col3);
```

--------------------------------

### Insert Sample Data for Serial/ID Partitioning

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

This SQL snippet inserts sample data into a table using generate_series, simulating partitions with serial IDs. This data is used to demonstrate partitioning by modulus arithmetic.

```sql
INSERT INTO tracking.hits_stuffid VALUES (generate_series(1100,1294), now());
INSERT INTO tracking.hits_stuffid VALUES (generate_series(2400,2991), now());
INSERT INTO tracking.hits_stuffid VALUES (generate_series(3602,3843), now());
```

--------------------------------

### Show Existing Trigger-based Partitioned Table Schema

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_declarative.md

Displays the schema of a table managed by trigger-based partitioning, including its primary key, indexes, and associated triggers. This is useful for understanding the current setup before migration.

```sql
\d+ partman_test.time_taptest_table
```

--------------------------------

### create_partition Function

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Main function to create a partition set with one parent table and inherited children. Parent table must already exist and be declared as partitioned before calling this function. All options passed to this function must match that definition. Please apply all defaults, indexes, constraints, privileges & ownership to parent table so they will propagate to children.

```APIDOC
## create_partition

### Description
Creates a partition set with a parent table and inherited child partitions. The parent table must exist and be pre-declared as partitioned. All options passed to this function must match the parent table's definition. Indexes, constraints, privileges, and ownership should be applied to the parent table to ensure they propagate to children.

An ACCESS EXCLUSIVE lock is taken on the parent table during the running of this function. No data is moved when running this function, so lock should be brief.

A default partition and template table are created by default unless otherwise configured.

### Parameters

* **p_parent_table** (text) - Required - The existing parent table. MUST be schema qualified, even if in public schema.
* **p_control** (text) - Required - The column that the partitioning will be based on. Must be a time, integer, text or uuid based column. When control is of type text/uuid, p_time_encoder and p_time_decoder must be set.
* **p_interval** (text) - Required - The time or integer range interval for each partition. No matter the partitioning type, value must be given as text.
    * *\<interval\>* - Any valid value for the interval data type. Do not type cast the parameter value, just leave as text.
    * *\<integer\>* - For ID based partitions, the integer value range of the ID that should be set per partition. Enter this as an integer in text format ('100' not 100). If the interval is >=2, then the `p_type` must be `range`. If the interval equals 1, then the `p_type` must be `list`. Also note that while numeric values are supported for id-based partitioning, the interval must still be a whole number integer.
* **p_type** (text) - Optional (default: 'range') - The type of partitioning to be done. Currently only **range** and **list** are supported. See `p_interval` parameter for special conditions concerning type.
* **p_epoch** (text) - Optional (default: 'none') - Tells `pg_partman` that the control column is an integer type, but actually represents an epoch time value. Valid values for this option are: 'seconds', 'milliseconds', 'microseconds', 'nanoseconds', and 'none'. All table names will be time-based. In addition to a normal index on the control column, be sure you create a functional, time-based index on the control column (to_timestamp(controlcolumn)) as well so this works efficiently.
* **p_premake** (int) - Optional (default: 4) - How many additional partitions to always stay ahead of the current partition. This will keep at minimum 5 partitions made, including the current one. For example, if today was Sept 6th, and `premake` was set to 4 for a daily partition, then partitions would be made for the 6th as well as the 7th, 8th, 9th and 10th. Note some intervals may occasionally cause an extra partition to be premade or one to be missed due to leap years, differing month lengths, etc. This usually won't hurt anything and should self-correct (see **About** section concerning timezones and non-UTC). If partitioning ever falls behind the `premake` value, normal running of `run_maintenance()` and data insertion should automatically catch things up.
* **p_start_partition** (text) - Optional (default: NULL) - Allows the first partition of a set to be specified instead of it being automatically determined. Must be a valid timestamp (for time-based) or positive integer (for id-based) value. Be aware, though, the actual parameter data type is text. For time-based partitioning, all partitions starting with the given timestamp up to CURRENT_TIMESTAMP (plus `premake`) will be created. For id-based partitioning, only the partition starting at the given value (plus `premake`) will be made. Note that for subpartitioning, this only applies during initial setup and not during ongoing maintenance.
* **p_default_table** (boolean) - Optional (default: true) - Boolean flag to determine whether a default table is created.
* **p_automatic_maintenance** (text) - Optional (default: 'on') - Parameter to set whether maintenance is managed automatically when `run_maintenance()` is called without a table parameter or by the background worker process. Current valid values are "on" and "off". When set to off, `run_maintenance()` can still be called on an individual partition set by passing it as a parameter to the function. See **run_maintenance** in Maintenance Functions section below for more info.
* **p_constraint_cols** (text[]) - Optional (default: NULL) - Columns to be used for constraints.
* **p_template_table** (text) - Optional (default: NULL) - The template table to use for creating new partitions.
* **p_jobmon** (boolean) - Optional (default: true) - Whether to use jobmon for tracking maintenance jobs.
* **p_date_trunc_interval** (text) - Optional (default: NULL) - Interval for date truncation. Used for time-based partitioning.
* **p_control_not_null** (boolean) - Optional (default: true) - Whether the control column should be NOT NULL.
* **p_time_encoder** (text) - Optional (default: NULL) - Function to encode time values for partitioning. Required for text/uuid control columns.
* **p_time_decoder** (text) - Optional (default: NULL) - Function to decode time values for partitioning. Required for text/uuid control columns.

### Returns
* **boolean** - True if successful, false otherwise.
```

--------------------------------

### Insert Data and Run Maintenance

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Inserts sample data into the partitioned table and then calls `run_maintenance_proc` to create new child tables as needed based on the data and partitioning configuration.

```sql
INSERT INTO partman_test.id_taptest_table (col1, col2) VALUES (generate_series(1,20), generate_series(1,20)::text||'stuff'::text);

CALL partman.run_maintenance_proc();
```

--------------------------------

### Verify Partitioned Table Data and Structure

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Confirm that the parent partitioned table now contains all the migrated data and that child tables have been created as expected.

```sql
SELECT count(*) FROM original_table;
 count  
--------
 100000
(1 row)


 \d+ public.original_table
                                        Partitioned table "public.original_table"
 Column |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 col1   | bigint                   |           | not null |         | plain    |             |              |
 col2   | text                     |           | not null |         | extended |             |              |
 col3   | timestamp with time zone |           |          | now()   | plain    |             |              |
 col4   | text                     |           |          |         | extended |             |              |
Partition key: RANGE (col1)
Indexes:
    "original_table_col1_idx" btree (col1)
Partitions: original_table_p0 FOR VALUES FROM ('0') TO ('10000'),
            original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'),
            original_table_p100000 FOR VALUES FROM ('100000') TO ('110000'),
            original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'),
            original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'),
            original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'),
            original_table_p50000 FOR VALUES FROM ('50000') TO ('60000'),
            original_table_p60000 FOR VALUES FROM ('60000') TO ('70000'),
            original_table_p70000 FOR VALUES FROM ('70000') TO ('80000'),
            original_table_p80000 FOR VALUES FROM ('80000') TO ('90000'),
            original_table_p90000 FOR VALUES FROM ('90000') TO ('100000'),
            original_table_default DEFAULT

SELECT count(*) FROM original_table_p10000;
 count
-------
 10000
(1 row)
```

--------------------------------

### Get Partition Info - show_partition_info

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Retrieves boundary values (start/end times or IDs) and the suffix for a given child table. The partition interval can be specified or will be looked up from the part_config table. This function only works for existing child tables.

```sql
show_partition_info(p_child_table text
    , p_partition_interval text DEFAULT NULL
    , p_parent_table text DEFAULT NULL
    , OUT child_start_time timestamptz
    , OUT child_end_time timestamptz
    , OUT child_start_id bigint
    , OUT child_end_id bigint
    , OUT suffix text
)
RETURNS record
```

--------------------------------

### Create New Partitioned Table Structure

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Sets up the parent table for partitioning by range on the 'col3' column. Note that the IDENTITY column 'col1' is initially not set as a primary key due to partitioning constraints.

```sql
CREATE TABLE public.new_partitioned_table (
    col1 bigint not null GENERATED BY DEFAULT AS IDENTITY
    , col2 text not null
    , col3 timestamptz DEFAULT now() not null
    , col4 text) PARTITION BY RANGE (col3);

CREATE INDEX ON public.new_partitioned_table (col3);
```

--------------------------------

### Create pg_partman Schema and Extension

Source: https://github.com/pgpartman/pg_partman/blob/development/README.md

Create a dedicated schema for pg_partman and then create the extension within that schema. This is a prerequisite for using pg_partman's partitioning features.

```sql
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
```

--------------------------------

### Rename Tables and Reset Identity Sequence

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md

Rename the old partitioned table and the new regular table to their desired final names. Reset the identity sequence of the new table to the correct starting value and change its generation method to GENERATED ALWAYS if required.

```sql
SELECT max(col1)+1 FROM public.new_regular_table;

ALTER TABLE original_table RENAME TO old_partitioned_table;
ALTER SEQUENCE original_table_col1_seq RENAME TO old_partitioned_table_col1_seq;

ALTER TABLE new_regular_table RENAME TO original_table;
ALTER SEQUENCE new_regular_table_col1_seq RENAME TO original_table_col1_seq;
ALTER TABLE public.original_table ALTER col1 RESTART WITH <<<VALUE OBTAINED ABOVE>>>;
ALTER TABLE public.original_table ALTER col1 SET GENERATED ALWAYS;
```

--------------------------------

### Get Partition Name by Value - show_partition_name

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Given a parent table and a value (time or ID in text form), this function returns the name of the child partition that value would fall into. It also provides the raw suffix value and a boolean indicating if the table exists.

```sql
show_partition_name(
    p_parent_table text
    , p_value text
    , OUT partition_schema text
    , OUT partition_table text
    , OUT suffix_timestamp timestamptz
    , OUT suffix_id bigint
    , OUT table_exists boolean
)
RETURNS record
```

--------------------------------

### Display Table Ownership and Privileges

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_declarative.md

Use \dt and \dp+ to inspect the ownership and access privileges of your existing table. Ensure these are replicated on the new native partitioned table.

```sql
\dt partman_test.time_taptest_table
                     List of relations
    Schema    |        Name        | Type  |     Owner  
--------------+--------------------+-------+--------------- 
 partman_test | time_taptest_table | table | partman_owner 
(1 row)

\dp+ partman_test.time_taptest_table
                                               Access privileges
    Schema    |        Name        | Type  |          Access privileges          | Column privileges | Policies 
--------------+--------------------+-------+-------------------------------------+-------------------+---------- 
 partman_test | time_taptest_table | table | partman_owner=arwdDxt/partman_owner+ |                   | 
              |                    |       | partman_basic=arwd/partman_owner   + |                   | 
              |                    |       | testing=r/partman_owner             |                   | 
(1 row)
```

--------------------------------

### partition_gap_fill

Source: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

Fills in any gaps in the series of child tables for a given parent table. It starts from the current minimum child table and fills gaps up to the current maximum child table based on the partition interval. Returns the number of child tables created, or 0 if none were created.

```APIDOC
## partition_gap_fill(p_parent_table text)

### Description
Function to fill in any gaps that may exist in the series of child tables for a given parent table.

### Parameters
#### Path Parameters
- **p_parent_table** (text) - The parent table of the partition set.

### Returns
- **integer** - The number of child tables created. Returns 0 if none are created.
```

--------------------------------

### create_partition

Source: https://context7.com/pgpartman/pg_partman/llms.txt

Initializes a new partition set for a given parent table. This is the primary entry point for creating managed partition sets.

```APIDOC
## create_partition — initialize a new partition set

### Description
The primary entry point for initializing a new partition set. The parent table must already exist and be declared `PARTITION BY RANGE` (or `LIST`). All indexes, defaults, and privileges should be applied to the parent before calling this function; pg_partman propagates them (and template-table properties) to every new child.

### Method
`SELECT partman.create_partition(...)`

### Parameters
- **p_parent_table** (text) - Required - The name of the parent table.
- **p_control** (text) - Required - The column to partition on.
- **p_interval** (text) - Required - The interval for creating new partitions (e.g., '1 day', '10').
- **p_template_table** (text) - Optional - The name of a template table to propagate properties from.
- **p_date_trunc_interval** (text) - Optional - Specifies a date truncation interval for time-based partitioning (e.g., 'week').
- **p_start_partition** (text) - Optional - Defines the starting point for partition creation, especially when using `p_date_trunc_interval`.
- **p_time_encoder** (text) - Optional - A function to encode time values for non-standard column types.
- **p_time_decoder** (text) - Optional - A function to decode time values for non-standard column types.

### Request Example
```sql
-- Example 1: Daily time-based partition set with a template table
SELECT partman.create_partition(
    p_parent_table   := 'partman_test.time_taptest_table',
    p_control        := 'col3',
    p_interval       := '1 day',
    p_template_table := 'partman_test.time_taptest_table_template'
);

-- Example 2: ID/serial partition set with 10-row buckets
SELECT partman.create_partition(
    p_parent_table := 'partman_test.id_taptest_table',
    p_control      := 'col1',
    p_interval     := '10'
);

-- Example 3: Weekly partitions anchored to Monday using date_trunc
SELECT partman.create_partition(
    p_parent_table        := 'public.events',
    p_control             := 'ts',
    p_interval            := '1 week',
    p_date_trunc_interval := 'week',
    p_start_partition     := to_char(date_trunc('week', CURRENT_TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')
);

-- Example 4: UUIDv7 time-based partitioning
SELECT partman.create_partition(
    p_parent_table := 'partman_test.uuid_table',
    p_control      := 'col3',
    p_interval     := '1 day',
    p_time_encoder := 'partman.uuid7_time_encoder',
    p_time_decoder := 'partman.uuid7_time_decoder'
);

-- Example 5: Custom text-column time partitioning
SELECT partman.create_partition(
    p_parent_table := 'partman_test.inv_table',
    p_control      := 'col3',
    p_interval     := '1 day',
    p_time_encoder := 'public.encode_timestamp',
    p_time_decoder := 'public.decode_timestamp'
);
```

### Response
- **Returns**: boolean - `t` on success.
```