From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ravi Tammineni <RTammineni@jackhenry.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PostgreSQL-11 partition creation issue
Date: Fri, 07 Aug 2020 11:49:54 -0400
Message-ID: <2755423.1596815394@sss.pgh.pa.us> (raw)
In-Reply-To: <F88AD378-F50D-4FEC-BEF0-581D3E60FDDE@jackhenry.com>
References: <A78FBB99-51B6-4B12-B5F8-9073EF872BB0@jackhenry.com>
<F88AD378-F50D-4FEC-BEF0-581D3E60FDDE@jackhenry.com>
Ravi Tammineni <RTammineni@jackhenry.com> writes:
> Getting the following error.
> ERROR: syntax error at or near "/"
> LINE 7: ) partition by to_timestamp(osn_timestamp/1000)::date;
You have the syntax wrong, and once you get past that, you'll also
find that you're trying to use a non-immutable function in a partition
expression (because casting from timestamptz to date depends on the
prevailing timezone). Something like this would work, perhaps:
=# CREATE TABLE public.oauth_server_nonce_new (
osn_id integer NOT NULL,
osn_consumer_key character varying(64) NOT NULL,
osn_token character varying(64) NOT NULL,
osn_timestamp bigint NOT NULL,
osn_nonce character varying(80) NOT NULL
) partition by range (((to_timestamp(osn_timestamp/1000) at time zone 'UTC')::date));
CREATE TABLE
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, RTammineni@jackhenry.com
Subject: Re: PostgreSQL-11 partition creation issue
In-Reply-To: <2755423.1596815394@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox