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

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
postbodyrealtext 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