On Mon, May 06, 2024 at 05:56:54PM +0200, Alvaro Herrera wrote: > On 2024-May-04, Alvaro Herrera wrote: > > On 2024-May-03, Justin Pryzby wrote: > > > > > But if it's created with LIKE: > > > postgres=# CREATE TABLE t1 (LIKE t); > > > postgres=# ALTER TABLE t ATTACH PARTITION t1 DEFAULT ; > > > > > > ..one also sees: > > > > > > Not-null constraints: > > > "t1_i_not_null" NOT NULL "i" > > > > Hmm, I think the problem here is not ATTACH; the not-null constraint is > > there immediately after CREATE. I think this is all right actually, > > because we derive a not-null constraint from the primary key and this is > > definitely intentional. But I also think that if you do CREATE TABLE t1 > > (LIKE t INCLUDING CONSTRAINTS) then you should get only the primary key > > and no separate not-null constraint. That will make the table more > > similar to the one being copied. > > I misspoke -- it's INCLUDING INDEXES that we need here, not INCLUDING > CONSTRAINTS ... and it turns out we already do it that way, so with this > script > > CREATE TABLE t (i int PRIMARY KEY) PARTITION BY RANGE (i); > CREATE TABLE t1 (LIKE t INCLUDING INDEXES); > ALTER TABLE t ATTACH PARTITION t1 DEFAULT ; > > you end up with this > > 55432 17devel 71313=# \d+ t > Partitioned table "public.t" > Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ > Stats target │ Description > ────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼───────────── > i │ integer │ │ not null │ │ plain │ │ > │ > Partition key: RANGE (i) > Indexes: > "t_pkey" PRIMARY KEY, btree (i) > Partitions: t1 DEFAULT > > 55432 17devel 71313=# \d+ t1 > Table "public.t1" > Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ > Stats target │ Description > ────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼───────────── > i │ integer │ │ not null │ │ plain │ │ > │ > Partition of: t DEFAULT > No partition constraint > Indexes: > "t1_pkey" PRIMARY KEY, btree (i) > Access method: heap > > which I think is what you want. (Do you really want the partition to be > created without the primary key already there?)
Why not ? The PK will be added when I attach it one moment later. CREATE TABLE part (LIKE parent); ALTER TABLE parent ATTACH PARTITION part ... Do you really think that after ATTACH, the constraints should be different depending on whether the child was created INCLUDING INDEXES ? I'll continue to think about this, but I still find that surprising. -- Justin