Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL-11 partition creation issue

PostgreSQL-11 partition creation issue

From
Ravi Tammineni
Date:

Hi,

 

I am trying to create a partition table for UNIX epoch column.  Here are the details of the table.  Our application is converting the date and storing the date in UNIX epoch time format.

 

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 to_timestamp(osn_timestamp/1000)::date;

 

CREATE TABLE measurement_y2016 PARTITION OF oauth_server_nonce_new FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');

CREATE TABLE measurement_y2017 PARTITION OF oauth_server_nonce_new FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');

 

Getting the following error.

 

ERROR:  syntax error at or near "/"

LINE 7: ) partition by to_timestamp(osn_timestamp/1000)::date;

 

Any help is appreciated.

 

Regards,

Ravi

NOTICE: This electronic mail message and any files transmitted with it are intended
exclusively for the individual or entity to which it is addressed. The message,
together with any attachment, may contain confidential and/or privileged information.
Any unauthorized review, use, printing, saving, copying, disclosure or distribution
is strictly prohibited. If you have received this message in error, please
immediately advise the sender by reply email and delete all copies.

Re: PostgreSQL-11 partition creation issue

From
Tom Lane
Date:
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