pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

PostgreSQL-11 partition creation issue
2+ messages / 2 participants
[nested] [flat]

* PostgreSQL-11 partition creation issue
@ 2020-08-07 14:49 Ravi Tammineni <RTammineni@jackhenry.com>
  2020-08-07 15:49 ` Re: PostgreSQL-11 partition creation issue Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Ravi Tammineni @ 2020-08-07 14:49 UTC (permalink / raw)
  To: pgsql-admin

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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PostgreSQL-11 partition creation issue
  2020-08-07 14:49 PostgreSQL-11 partition creation issue Ravi Tammineni <RTammineni@jackhenry.com>
@ 2020-08-07 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2020-08-07 15:49 UTC (permalink / raw)
  To: Ravi Tammineni <RTammineni@jackhenry.com>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2020-08-07 15:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-07 14:49 PostgreSQL-11 partition creation issue Ravi Tammineni <RTammineni@jackhenry.com>
2020-08-07 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>

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