GitHub

@@ -37,13 +37,6 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>

3737

ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }

3838

ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>

3939

DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]

40-

ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>

41-

SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO

42-

(PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT },

43-

PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])

44-

ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>

45-

MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...])

46-

INTO <replaceable class="parameter">partition_name</replaceable>

47404841

<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>

4942

@@ -1124,140 +1117,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM

11241117

</listitem>

11251118

</varlistentry>

112611191127-

<varlistentry id="sql-altertable-split-partition">

1128-

<term><literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])</literal></term>

1129-1130-

<listitem>

1131-

<para>

1132-

This form splits a single partition of the target table. Hash-partitioning

1133-

is not supported. Bounds of new partitions should not overlap with new and

1134-

existing partitions (except <replaceable class="parameter">partition_name</replaceable>).

1135-

If the split partition is a DEFAULT partition, one of the new partitions must be DEFAULT.

1136-

In case one of the new partitions or one of existing partitions is DEFAULT,

1137-

new partitions <replaceable class="parameter">partition_name1</replaceable>,

1138-

<replaceable class="parameter">partition_name2</replaceable>, ... can have spaces

1139-

between partitions bounds. If the partitioned table does not have a DEFAULT

1140-

partition, the DEFAULT partition can be defined as one of the new partitions.

1141-

</para>

1142-

<para>

1143-

In case new partitions do not contain a DEFAULT partition and the partitioned table

1144-

does not have a DEFAULT partition, the following must be true: sum bounds of

1145-

new partitions <replaceable class="parameter">partition_name1</replaceable>,

1146-

<replaceable class="parameter">partition_name2</replaceable>, ... should be

1147-

equal to bound of split partition <replaceable class="parameter">partition_name</replaceable>.

1148-

One of the new partitions <replaceable class="parameter">partition_name1</replaceable>,

1149-

<replaceable class="parameter">partition_name2</replaceable>, ... can have

1150-

the same name as split partition <replaceable class="parameter">partition_name</replaceable>

1151-

(this is suitable in case of splitting a DEFAULT partition: we split it, but after

1152-

splitting we have a partition with the same name).

1153-

Only simple, non-partitioned partition can be split.

1154-

</para>

1155-

<para>

1156-

The new partitions will be created the same as tables created with the

1157-

SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_nameN</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>.

1158-

The indexes and identity are created later, after moving the data

1159-

into the new partitions.

1160-

Extended statistics aren't copied from the parent table, for consistency with

1161-

<command>CREATE TABLE PARTITION OF</command>.

1162-1163-

New partitions will have the same table access method as the parent.

1164-

If the parent table is persistent then new partitions are created

1165-

persistent. If the parent table is temporary then new partitions

1166-

are also created temporary. New partitions will also be created in

1167-

the same tablespace as the parent.

1168-

</para>

1169-

<note>

1170-

<para>

1171-

This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock.

1172-

This is a significant limitation, which limits the usage of this

1173-

command with large partitioned tables under a high load.

1174-

</para>

1175-

</note>

1176-

</listitem>

1177-

</varlistentry>

1178-1179-

<varlistentry id="sql-altertable-merge-partitions">

1180-

<term><literal>MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable></literal></term>

1181-1182-

<listitem>

1183-

<para>

1184-

This form merges several partitions into the one partition of the target table.

1185-

Hash-partitioning is not supported. If DEFAULT partition is not in the

1186-

list of partitions <replaceable class="parameter">partition_name1</replaceable>,

1187-

<replaceable class="parameter">partition_name2</replaceable> [, ...]:

1188-

<itemizedlist>

1189-

<listitem>

1190-

<para>

1191-

For range-partitioned tables it is necessary that the ranges

1192-

of the partitions <replaceable class="parameter">partition_name1</replaceable>,

1193-

<replaceable class="parameter">partition_name2</replaceable> [, ...] can

1194-

be merged into one range without spaces and overlaps (otherwise an error

1195-

will be generated). The combined range will be the range for the partition

1196-

<replaceable class="parameter">partition_name</replaceable>.

1197-

</para>

1198-

</listitem>

1199-

<listitem>

1200-

<para>

1201-

For list-partitioned tables the value lists of all partitions

1202-

<replaceable class="parameter">partition_name1</replaceable>,

1203-

<replaceable class="parameter">partition_name2</replaceable> [, ...] are

1204-

combined and form the list of values of partition

1205-

<replaceable class="parameter">partition_name</replaceable>.

1206-

</para>

1207-

</listitem>

1208-

</itemizedlist>

1209-

If DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>,

1210-

<replaceable class="parameter">partition_name2</replaceable> [, ...]:

1211-

<itemizedlist>

1212-

<listitem>

1213-

<para>

1214-

The partition <replaceable class="parameter">partition_name</replaceable>

1215-

will be the DEFAULT partition.

1216-

</para>

1217-

</listitem>

1218-

<listitem>

1219-

<para>

1220-

For range- and list-partitioned tables the ranges and lists of values

1221-

of the merged partitions can be any.

1222-

</para>

1223-

</listitem>

1224-

</itemizedlist>

1225-

The new partition <replaceable class="parameter">partition_name</replaceable>

1226-

can have the same name as one of the merged partitions. Only simple,

1227-

non-partitioned partitions can be merged.

1228-

</para>

1229-

<para>

1230-

The new partition will be created the same as a table created with the

1231-

SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_name</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>.

1232-

The indexes and identity are created later, after moving the data

1233-

into the new partition.

1234-

Extended statistics aren't copied from the parent table, for consistency with

1235-

<command>CREATE TABLE PARTITION OF</command>.

1236-

The new partition will have the same table access method as the parent.

1237-

If the parent table is persistent then the new partition is created

1238-

persistent. If the parent table is temporary then the new partition

1239-

is also created temporary. The new partition will also be created in

1240-

the same tablespace as the parent.

1241-

</para>

1242-

<note>

1243-

<para>

1244-

This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock.

1245-

This is a significant limitation, which limits the usage of this

1246-

command with large partitioned tables under a high load.

1247-

</para>

1248-

</note>

1249-

</listitem>

1250-

</varlistentry>

1251-12521120

</variablelist>

12531121

</para>

1254112212551123

<para>

12561124

All the forms of ALTER TABLE that act on a single table, except

12571125

<literal>RENAME</literal>, <literal>SET SCHEMA</literal>,

1258-

<literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>,

1259-

<literal>SPLIT PARTITION</literal>, and <literal>MERGE PARTITIONS</literal>

1260-

can be combined into

1126+

<literal>ATTACH PARTITION</literal>, and

1127+

<literal>DETACH PARTITION</literal> can be combined into

12611128

a list of multiple alterations to be applied together. For example, it

12621129

is possible to add several columns and/or alter the type of several

12631130

columns in a single command. This is particularly useful with large

@@ -1500,8 +1367,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM

15001367

<term><replaceable class="parameter">partition_name</replaceable></term>

15011368

<listitem>

15021369

<para>

1503-

The name of the table to attach as a new partition or to detach from this table,

1504-

or the name of split partition, or the name of the new merged partition.

1370+

The name of the table to attach as a new partition or to detach from this table.

15051371

</para>

15061372

</listitem>

15071373

</varlistentry>

@@ -1917,31 +1783,6 @@ ALTER TABLE measurement

19171783

DETACH PARTITION measurement_y2015m12;

19181784

</programlisting></para>

191917851920-

<para>

1921-

To split a single partition of the range-partitioned table:

1922-

<programlisting>

1923-

ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2023 INTO

1924-

(PARTITION sales_feb2023 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),

1925-

PARTITION sales_mar2023 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'),

1926-

PARTITION sales_apr2023 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01'));

1927-

</programlisting></para>

1928-1929-

<para>

1930-

To split a single partition of the list-partitioned table:

1931-

<programlisting>

1932-

ALTER TABLE sales_list SPLIT PARTITION sales_all INTO

1933-

(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),

1934-

PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'),

1935-

PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));

1936-

</programlisting></para>

1937-1938-

<para>

1939-

To merge several partitions into one partition of the target table:

1940-

<programlisting>

1941-

ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central)

1942-

INTO sales_all;

1943-

</programlisting></para>

1944-19451786

</refsect1>

1946178719471788

<refsect1>

Read the original on github.com ↗