Subject: Re: Partitioning on the date part of a timestamp & PK issues
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <13f09d24-a255-ac33-7745-6a2f59d49679@gmail.com>
Date: Thu, 15 Jul 2021 17:06:26 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/13f09d24-a255-ac33-7745-6a2f59d49679%40gmail.com>
Precedence: bulk

On 7/15/21 3:10 PM, Wells Oliver wrote:
> I have a table like so:
>
> CREATE TABLE t (
>     guid uuid not null,
>     seq smallint not null,
>     tid smallint not null,
>     ts timestamp without time zone not null,
>     x real,
>     y real,
>     z real,
>     primary key (guid, tid, seq)
> ) partition by range (extract(date from ts));
>
> Which results in the error:
>
> ERROR:  unsupported PRIMARY KEY constraint with partition key definition
> DETAIL:  PRIMARY KEY constraints cannot be used when partition keys 
> include expressions.
>
> Is there a suitable way to get around this? I can make an additional date 
> col in the table to use for the partition range, but given the amount of 
> data we will have, I am trying to be cognizant of storage concerns.
>
> The PK is the PK, so I'm not sure what options I have there.

Why do you feel the need to "extract(date from ts)" instead of just 
partitioning by "ts"?

-- 
Angular momentum makes the world go 'round.