Re: pg12 - partition by column that might have null values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Mariel Cherkassky wrote:
> In PG12 I'm trying to create partitions by range on a date column
> that might be null (indicates it is the most recent version of the
> object). My PK has to include the partition column, therefore I'm
> getting an error that I cant create a primary key with the specific
> column because it has null values.
> 
> For example : 
> \d object_revision
>                                           Table
> "public.object_revision"
>    Column    |            Type             | Collation | Nullable |  
>                  Default
> -------------+-----------------------------+-----------+----------+
> -----------------------------------------------
>  id          | integer                     |           | not null |
> nextval('mariel_dates_test_id_seq'::regclass)
>  end_time    | timestamp without time zone |           |          |
>  object_hash | text                        |           |          |
> Indexes:
>     "id_pk" PRIMARY KEY, btree (id)
> 
> Lets say that the same object (object_hash) can have many revisions,
> the end_time is the time it was last updated. I'm trying to create
> this table as a range partition on the end_time. However, when I try
> to add the pk I'm getting an error : 
> ALTER TABLE object_revision ADD CONSTRAINT
> object_revision_id_end_time PRIMARY KEY (id,end_time);
> ERROR:  column "end_time" contains null values
> 
> does someone familiar with a workaround ? I know that in postgresql
> as part of the primary key definition unique and not null constraints
> are enforced on each column and not on both of them. However, this
> might be problematic with pg12 partitions..

Can "end_time" ever be modified?

If yes, it is a bad choice for a partitioning column.

If the NULL in "end_time" is meant to signify "no end", use the
value "infinity" instead of NULL.  Then you can define the column
as NOT NULL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux