Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iiUd3-0006z4-7J for pgsql-admin@arkaria.postgresql.org; Sat, 21 Dec 2019 02:36:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iiUd2-0003FL-12 for pgsql-admin@arkaria.postgresql.org; Sat, 21 Dec 2019 02:36:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iiUd1-0003FE-K1 for pgsql-admin@lists.postgresql.org; Sat, 21 Dec 2019 02:36:27 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiUcz-0004bR-AX for pgsql-admin@lists.postgresql.org; Sat, 21 Dec 2019 02:36:26 +0000 Received: by mail-ot1-x32d.google.com with SMTP id p8so14289530oth.10 for ; Fri, 20 Dec 2019 18:36:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=U7oB68pN2sNGGl80dK1btKIIW7GFfxGW9G2rwOfoqAQ=; b=KAlOIjA7+XiiNbujM9RLDtbN1xYwW1PTL4IB+QXZ1248j4B9A4MHlKccLhkmiQ+b7d fPeQzyjpzggub2aw6N4hLVkLaOAKGOYH+mdgFhm8PEs8r2wxWxuxZkq9nM8KOZji78Ro Qk3+LQmleqbcjHtNU8ODY5PLny0ibHac29itwSrw371V9C4qjQXVSCHYwJITs6kSg8eX wC1zpldwXTuI8E1TrL51uYeFQ4/gv5WV8GDN/pMmRihaTTW6rt3xG0EpXmgpM2Csustn NmYaUOsTnakBrly79CFp+CVLh07wZtZhHXA5k/laGjQ6EQiLWJ4y0kOTdreVxtiVH18y Gbbw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=U7oB68pN2sNGGl80dK1btKIIW7GFfxGW9G2rwOfoqAQ=; b=Ak5TgZSQ+kJmhtMoayiZ6bqLxb0fAbUItxKXY7o5KzyTJQFssr/3naGBo9vu4gFvDI sM8lkHsGES4farKcA0kh2Pw2dTIThCQclM0Ans9/jFBfR4z/ZV3hRfO+Jmr5sTHDIB2I n4wkar+upsTSnulLaoMqsaGEyTek1i8ZlFCSeODvvYiw8lK/oa2+Rk04XM54Z42Pl0ml d+Dajh36L6ejy3cvesUS5IIa+wwwWF2SHKib3h36Y4qu2jcTFQFfXgkvvRxsd/WgmTed 0/5hpXhzrayKGS83voiPNnCRRWwmbQKxAlMm35rRmO20nbKMinG9e3R71xzJiVOlkqMq XXQw== X-Gm-Message-State: APjAAAX5Kt96dsnaz/zKUoHq9+zj3s3he3DANAYQk41WLm60XhuBjA2p zDjcTrcBgS48FRpjtrVA2nX975gEnMM= X-Google-Smtp-Source: APXvYqwDN6OpH2e/zFByvDe3ce10D80KUvqDpeNxhSRjWdSGgK5tjk1rlrft+S8zSBTNOtO3KsQq3w== X-Received: by 2002:a05:6830:128e:: with SMTP id z14mr17739627otp.184.1576895783721; Fri, 20 Dec 2019 18:36:23 -0800 (PST) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id e20sm2982738otr.32.2019.12.20.18.36.22 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 20 Dec 2019 18:36:23 -0800 (PST) Subject: Re: Partitioning by month causing an error? To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Fri, 20 Dec 2019 20:36:22 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6F15FCBDF2FABB5BC6430DC5" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6F15FCBDF2FABB5BC6430DC5 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit What's the: - Pg version number - exact error message - definition of mmd - example statement that fails On 12/20/19 8:32 PM, Wells Oliver wrote: > mmd has a PARTITION BY RANGE on a date column. > > On Fri, Dec 20, 2019 at 6:24 PM Ron > wrote: > > On 12/20/19 6:49 PM, Wells Oliver wrote: > > I have a partition per each month, like this: > > > > CREATE TABLE mmd_yr2019_3 PARTITION OF mmd FOR VALUES FROM > ('2019-03-01') > > TO ('2019-03-31'); > > CREATE TABLE mmd_yr2019_4 PARTITION OF mmd FOR VALUES FROM > ('2019-04-01') > > TO ('2019-04-30'); > > > > This breaks for dates at the end of the month. What's the better way to > > write the bounds? 3/1 to 4/1 and then 4/1 to 5/1? Does that cause some > > overlap issue? > > What is the mmd data type?  I ask that because '2019-03-31' is really > '2019-03-31 00:00:00.000', so there's nowhere for '2019-03-31 > 00:00:00.001' > through '2019-03-31 23:59:59.999' to go.  (And don't forget timezones.) > > -- Angular momentum makes the world go 'round. --------------6F15FCBDF2FABB5BC6430DC5 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit What's the:

- Pg version number
- exact error message
- definition of mmd
- example statement that fails

On 12/20/19 8:32 PM, Wells Oliver wrote:
mmd has a PARTITION BY RANGE on a date column.

On Fri, Dec 20, 2019 at 6:24 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 12/20/19 6:49 PM, Wells Oliver wrote:
> I have a partition per each month, like this:
>
> CREATE TABLE mmd_yr2019_3 PARTITION OF mmd FOR VALUES FROM ('2019-03-01')
> TO ('2019-03-31');
> CREATE TABLE mmd_yr2019_4 PARTITION OF mmd FOR VALUES FROM ('2019-04-01')
> TO ('2019-04-30');
>
> This breaks for dates at the end of the month. What's the better way to
> write the bounds? 3/1 to 4/1 and then 4/1 to 5/1? Does that cause some
> overlap issue?

What is the mmd data type?  I ask that because '2019-03-31' is really
'2019-03-31 00:00:00.000', so there's nowhere for '2019-03-31 00:00:00.001'
through '2019-03-31 23:59:59.999' to go.  (And don't forget timezones.)


--
Angular momentum makes the world go 'round.
--------------6F15FCBDF2FABB5BC6430DC5--