Foreign table "import_dest1.t4"

Column | Type | Collation | Nullable | Default | FDW Options

--------+---------+-----------+----------+---------+--------------------

c1 | integer | |not null | | (column_name 'c1')

c1 | integer | | | | (column_name 'c1')

Server: loopback

FDW Options: (schema_name 'import_source', table_name 't4')

Foreign table "import_dest2.t4"

Column | Type | Collation | Nullable | Default | FDW Options

--------+---------+-----------+----------+---------+--------------------

c1 | integer | |not null | | (column_name 'c1')

c1 | integer | | | | (column_name 'c1')

Server: loopback

FDW Options: (schema_name 'import_source', table_name 't4')

these constraints on individual partitions.

</para>

<para>

When using range partitioning, a <literal>NOT NULL</literal> constraint

is added to each non-expression column in the partition key.

</para>

</listitem>

</varlistentry>

num_or_arms=key->partnatts;

partexprs_item=list_head(key->partexprs);

for (i=0;i<key->partnatts;i++)

{

if (key->partattrs[i]==0)

{

Expr*keyCol;

Expr*keyCol;

if (key->partattrs[i]!=0)

{

keyCol= (Expr*)makeVar(1,

key->partattrs[i],

key->parttypid[i],

key->parttypmod[i],

key->parttypcoll[i],

0);

}

{

if (partexprs_item==NULL)

elog(ERROR,"wrong number of partition key expressions");

keyCol=lfirst(partexprs_item);

keyCol=copyObject(lfirst(partexprs_item));

partexprs_item=lnext(partexprs_item);

Assert(!IsA(keyCol,Var));

nulltest=makeNode(NullTest);

nulltest->arg=keyCol;

nulltest->nulltesttype=IS_NOT_NULL;

nulltest->argisrow= false;

nulltest->location=-1;

result=lappend(result,nulltest);

}

nulltest=makeNode(NullTest);

nulltest->arg=keyCol;

nulltest->nulltesttype=IS_NOT_NULL;

nulltest->argisrow= false;

nulltest->location=-1;

result=lappend(result,nulltest);

}

{

*failed_at=parent;

*failed_slot=slot;

return-1;

result=-1;

gotoerror_exit;

}

if (key->strategy==PARTITION_STRATEGY_RANGE)

{

for (i=0;i<key->partnatts;i++)

{

if (isnull[i])

ereport(ERROR,

(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),

errmsg("range partition key of row contains null")));

{

*failed_at=parent;

*failed_slot=slot;

result=-1;

gotoerror_exit;

}

}

}

parent=pd[-parent->indexes[cur_index]];

}

error_exit:

ecxt->ecxt_scantuple=ecxt_scantuple_old;

returnresult;

}

if (stmt->partspec)

{

charstrategy;

intpartnatts,

i;

intpartnatts;

AttrNumberpartattrs[PARTITION_MAX_KEYS];

Oidpartopclass[PARTITION_MAX_KEYS];

Oidpartcollation[PARTITION_MAX_KEYS];

List*partexprs=NIL;

List*cmds=NIL;

partnatts=list_length(stmt->partspec->partParams);

StorePartitionKey(rel,strategy,partnatts,partattrs,partexprs,

partopclass,partcollation);

if (strategy==PARTITION_STRATEGY_RANGE)

{

for (i=0;i<partnatts;i++)

{

AttrNumberpartattno=partattrs[i];

Form_pg_attributeattform=descriptor->attrs[partattno-1];

if (partattno!=0&& !attform->attnotnull)

{

AlterTableCmd*cmd=makeNode(AlterTableCmd);

cmd->subtype=AT_SetNotNull;

cmd->name=pstrdup(NameStr(attform->attname));

cmds=lappend(cmds,cmd);

}

}

if (cmds!=NIL)

AlterTableInternal(RelationGetRelid(rel),cmds, true);

}

}

heap_close(parent,AccessShareLock);

}

if (rel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE)

{

PartitionKeykey=RelationGetPartitionKey(rel);

if (get_partition_strategy(key)==PARTITION_STRATEGY_RANGE)

{

intpartnatts=get_partition_natts(key),

i;

for (i=0;i<partnatts;i++)

{

AttrNumberpartattnum=get_partition_col_attnum(key,i);

if (partattnum==attnum)

ereport(ERROR,

(errcode(ERRCODE_INVALID_TABLE_DEFINITION),

errmsg("column \"%s\" is in range partition key",

colName)));

}

}

}

ERROR: cannot drop column referenced in partition key expression

ALTER TABLE partitioned ALTER COLUMN b TYPE char(5);

ERROR: cannot alter type of column referenced in partition key expression

-- cannot drop NOT NULL on columns in the range partition key

ALTER TABLE partitioned ALTER COLUMN a DROP NOT NULL;

ERROR: column "a" is in range partition key

-- it's fine however to drop one on the list partition key column

CREATE TABLE list_partitioned (a int not null) partition by list (a);

ALTER TABLE list_partitioned ALTER a DROP NOT NULL;

DROP TABLE list_partitioned;

-- partitioned table cannot participate in regular inheritance

CREATE TABLE nonpartitioned (

a int,

p

(1 row)

-- check that range partition key columns are marked NOT NULL

SELECT attname, attnotnull FROM pg_attribute

WHERE attrelid = 'partitioned'::regclass AND attnum > 0

ORDER BY attnum;

attname | attnotnull

---------+------------

a | t

b | f

c | t

d | t

(4 rows)

-- prevent a function referenced in partition key from being dropped

DROP FUNCTION plusone(int);

ERROR: cannot drop function plusone(integer) because other objects depend on it

Table "public.partitioned"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

a | integer | |not null |

a | integer | | |

b | integer | | |

c | text | |not null |

d | text | |not null |

c | text | | |

d | text | | |

Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")

\d partitioned2

Table "public.part_forced_oids"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | |not null | | plain | |

a | integer | | | | plain | |

Partition of: oids_parted FOR VALUES FROM (1) TO (10)

Partition constraint: ((a >= 1) AND (a < 10))

Partition constraint: ((aIS NOT NULL) AND (a>= 1) AND (a < 10))

Has OIDs: yes

DROP TABLE oids_parted, part_forced_oids;

a | text | | | | extended | |

b | integer | | not null | 0 | plain | |

Partition of: part_c FOR VALUES FROM (1) TO (10)

Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10))

Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (bIS NOT NULL) AND (b>= 1) AND (b < 10))

Check constraints:

"check_a" CHECK (length(a) > 0)

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | | | | plain | |

b | integer | | | | plain | |

c | integer | |not null | | plain | |

c | integer | | | | plain | |

Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL))

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))

DROP TABLE unbounded_range_part;

CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | | | | plain | |

b | integer | | | | plain | |

c | integer | |not null | | plain | |

c | integer | | | | plain | |

Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1))

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))

CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);

\d+ range_parted4_2

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | | | | plain | |

b | integer | | | | plain | |

c | integer | |not null | | plain | |

c | integer | | | | plain | |

Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))

CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);

\d+ range_parted4_3

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | | | | plain | |

b | integer | | | | plain | |

c | integer | |not null | | plain | |

c | integer | | | | plain | |

Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))

Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))

DROP TABLE range_parted4;

-- cleanup

insert into part4 values ('b', 10);

-- fail (partition key a has a NOT NULL constraint)

insert into part1 values (null);

ERROR:null value in column "a" violatesnot-null constraint

ERROR:new row for relation "part1" violatespartition constraint

DETAIL: Failing row contains (null, null).

-- fail (expression key (b+0) cannot be null either)

insert into part1 values (1);

insert into range_parted values ('b', 10);

-- fail (partition key (b+0) is null)

insert into range_parted values ('a');

ERROR: range partition key of row contains null

ERROR: no partition of relation "range_parted" found for row

DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, null).

select tableoid::regclass, * from range_parted;

tableoid | a | b

----------+---+----

ALTERTABLE partitioned DROP COLUMN b;

ALTERTABLE partitioned ALTER COLUMN b TYPEchar(5);

ALTERTABLE partitioned ALTER COLUMN a DROPNOT NULL;

CREATETABLElist_partitioned (aintnot null) partition by list (a);

ALTERTABLE list_partitioned ALTER a DROPNOT NULL;

DROPTABLE list_partitioned;

CREATETABLEnonpartitioned (

aint,