ERROR: operator does not exist: timestamp without time zone + integer

Daulat Ram schrieb am 30.04.2019 um 05:46:

We are getting an ERROR: “operator does not exist: timestamp without
time zone + integer “ while creating table in postgres. The same
script is working fine in Oracle, I know there are some changes in
postgres but I am unable to identify . Please suggest how we can
create it successfully in postgres.

kbdb=# CREATE TABLE motif_site (
kbdb(#         topic_match_conf_threshold bigint DEFAULT 3,
kbdb(#         retention_period bigint DEFAULT 3,
kbdb(#         site_mode_date timestamp NOT NULL DEFAULT date_trunc('day', LOCALTIMESTAMP)+7,
kbdb(#         reload_submission_date timestamp,
kbdb(#         socket_time_out bigint DEFAULT 2500,
kbdb(#         reload_date timestamp,
kbdb(#         marked_content_tag varchar(1024) DEFAULT 'pagecontent konabody intellitxt echotopic contentpaneopen postbody realtext newscontent content contentbody posttext##post_message_.*',
kbdb(#         crawl_batch_size_lower_limit numeric(38) NOT NULL DEFAULT 20,
kbdb(#         site_name varchar(512) NOT NULL,
kbdb(#         crawl_batch_size_upper_limit numeric(38) NOT NULL DEFAULT 40,
kbdb(#         mtg numeric(38) DEFAULT 2000,
kbdb(#         enabled numeric(38) NOT NULL DEFAULT 0,
kbdb(#         root_url varchar(1024),
kbdb(#         blocked_content_tag varchar(1024) DEFAULT 'nointellitxt noechotopic',
kbdb(#         match_params varchar(1024),
kbdb(#         tf_data_source varchar(256) DEFAULT 'Web',
kbdb(#         site_id numeric(38) NOT NULL
kbdb(# ) ;
ERROR:  operator does not exist: timestamp without time zone + integer
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

You can only add integers to DATEs, not to timestamps.

To add a number of days to a timestamp, you need to use an interval:

date_trunc('day', LOCALTIMESTAMP) + interval '7 day'

Thomas

On 4/30/19 2:24 AM, Thomas Kellerer wrote:

Daulat Ram schrieb am 30.04.2019 um 05:46:

We are getting an ERROR: “operator does not exist: timestamp without
time zone + integer “ while creating table in postgres. The same
script is working fine in Oracle, I know there are some changes in
postgres but I am unable to identify . Please suggest how we can
create it successfully in postgres.

kbdb=# CREATE TABLE motif_site (
kbdb(#         topic_match_conf_threshold bigint DEFAULT 3,
kbdb(#         retention_period bigint DEFAULT 3,
kbdb(#         site_mode_date timestamp NOT NULL DEFAULT date_trunc('day', LOCALTIMESTAMP)+7,
kbdb(#         reload_submission_date timestamp,
kbdb(#         socket_time_out bigint DEFAULT 2500,
kbdb(#         reload_date timestamp,
kbdb(#         marked_content_tag varchar(1024) DEFAULT 'pagecontent konabody intellitxt echotopic contentpaneopen postbody realtext newscontent content contentbody posttext##post_message_.*',
kbdb(#         crawl_batch_size_lower_limit numeric(38) NOT NULL DEFAULT 20,
kbdb(#         site_name varchar(512) NOT NULL,
kbdb(#         crawl_batch_size_upper_limit numeric(38) NOT NULL DEFAULT 40,
kbdb(#         mtg numeric(38) DEFAULT 2000,
kbdb(#         enabled numeric(38) NOT NULL DEFAULT 0,
kbdb(#         root_url varchar(1024),
kbdb(#         blocked_content_tag varchar(1024) DEFAULT 'nointellitxt noechotopic',
kbdb(#         match_params varchar(1024),
kbdb(#         tf_data_source varchar(256) DEFAULT 'Web',
kbdb(#         site_id numeric(38) NOT NULL
kbdb(# ) ;
ERROR:  operator does not exist: timestamp without time zone + integer
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

You can only add integers to DATEs, not to timestamps.

To add a number of days to a timestamp, you need to use an interval:

date_trunc('day', LOCALTIMESTAMP) + interval '7 day'

Or cast to a date:

test=> select date_trunc('day', localtimestamp)::date + 7;

?column?

------------

2019-05-07

(1 row)

Thomas

--
Adrian Klaver
adrian.klaver@aklaver.com

"Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

Adrian> Or cast to a date:

Adrian> test=> select date_trunc('day', localtimestamp)::date + 7;

yeesh. that's a very long-winded way to write current_date + 7

--
Andrew (irc:RhodiumToad)

On 30/04/2019 16:00, Andrew Gierth wrote:

"Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

Adrian> Or cast to a date:

Adrian> test=> select date_trunc('day', localtimestamp)::date + 7;

yeesh. that's a very long-winded way to write current_date + 7

Well, current_date is different: current_date returns a date, so you
only have to do:

select current_date + 7;

The original question (if I remember correctly; have zapped it now) was
about adding an integer to a timestamp, hence the need to truncate it to
a date first as in Adrian's example above.

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 4/30/19 8:00 AM, Andrew Gierth wrote:

"Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

Adrian> Or cast to a date:

Adrian> test=> select date_trunc('day', localtimestamp)::date + 7;

yeesh. that's a very long-winded way to write current_date + 7

Yeah, I was just working of the OP's original DEFAULT:

site_mode_date timestamp NOT NULL DEFAULT date_trunc('day',
LOCALTIMESTAMP)+7,

--
Adrian Klaver
adrian.klaver@aklaver.com

"Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

yeesh. that's a very long-winded way to write current_date + 7

Adrian> Yeah, I was just working of the OP's original DEFAULT:

Adrian> site_mode_date timestamp NOT NULL DEFAULT date_trunc('day',
Adrian> LOCALTIMESTAMP)+7,

Right, but since all these are exactly equivalent:

CURRENT_DATE
LOCALTIMESTAMP::date
date_trunc('day',LOCALTIMESTAMP)::date

and since date can be cast to timestamp, then DEFAULT current_date+7
would seem to be the simplest answer.

--
Andrew (irc:RhodiumToad)

On 4/30/19 12:11 PM, Andrew Gierth wrote:

"Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

yeesh. that's a very long-winded way to write current_date + 7

Adrian> Yeah, I was just working of the OP's original DEFAULT:

Adrian> site_mode_date timestamp NOT NULL DEFAULT date_trunc('day',
Adrian> LOCALTIMESTAMP)+7,

Right, but since all these are exactly equivalent:

CURRENT_DATE
LOCALTIMESTAMP::date
date_trunc('day',LOCALTIMESTAMP)::date

and since date can be cast to timestamp, then DEFAULT current_date+7
would seem to be the simplest answer.

I would agree. Put it down to a case of tunnel vision.

--
Adrian Klaver
adrian.klaver@aklaver.com