ERROR: operator does not exist: timestamp without time zone + integer
Hi team,
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.
Regards,
Daulat
Amazon Linux Support?
Hi Guys, With the repo changes associated with the April 17 changes, https://pgstef.github.io/2019/04/17/one_rpm_to_rule_them_all.html It is evident that support for amazon linux has been dropped. While you can try to use redhat pgdp packages, they are not installable on Amazon Linux, giving the following error... /etc/redhat-release is needed by pgdg-redhat-repo-42.0-4.noarch I confident that the rest rpms available in the repo can be used by Amazon linux, but the redhat-release dependency is painful for automation with existing recipes such as postgresql in chef. Before I invest the effort to produce a pull request to address this, I wanted to confirm that amazon linux has been dropped and will no longer, nor likely ever be supported for the rpms repo's, or is it still on the road map? Cheers Lewis Shobbrook Team Lead - DevOps base2Services | The Cloud Services People T 1300 713 559 E l.shobbr...@base2services.com Lvl 21, 303 Collins St, Melbourne VIC 3000 base2services.com.au
Re: 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
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 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.kla...@aklaver.com
Re: Amazon Linux Support?
On 4/30/19 2:02 AM, Lewis Shobbrook wrote: Hi Guys, With the repo changes associated with the April 17 changes, https://pgstef.github.io/2019/04/17/one_rpm_to_rule_them_all.html It is evident that support for amazon linux has been dropped. While you can try to use redhat pgdp packages, they are not installable on Amazon Linux, giving the following error... /etc/redhat-release is needed by pgdg-redhat-repo-42.0-4.noarch I confident that the rest rpms available in the repo can be used by Amazon linux, but the redhat-release dependency is painful for automation with existing recipes such as postgresql in chef. Before I invest the effort to produce a pull request to address this, I wanted to confirm that amazon linux has been dropped and will no longer, nor likely ever be supported for the rpms repo's, or is it still on the road map? You will need a community account to see the issue: https://redmine.postgresql.org/issues/4205 The relevant part: Updated by Devrim Gündüz 4 days ago " Hi, We dropped Amazon Linux support years ago, there were lots of compatibility issues. The new repo RPMs just reflect that. Please switch to a supported distro, or rebuild RPMs from SRPMS. Regards, Devrim " Cheers Lewis Shobbrook Team Lead - DevOps base2Services | The Cloud Services People T 1300 713 559 E l.shobbr...@base2services.com Lvl 21, 303 Collins St, Melbourne VIC 3000 base2services.com.au -- Adrian Klaver adrian.kla...@aklaver.com
Re: ERROR: operator does not exist: timestamp without time zone + integer
> "Adrian" == Adrian Klaver 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)
Re: ERROR: operator does not exist: timestamp without time zone + integer
On 30/04/2019 16:00, Andrew Gierth wrote:
"Adrian" == Adrian Klaver 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
r...@rodonnell.ie
Re: Amazon Linux Support?
On 4/30/19 8:40 AM, Michael Nolan wrote: Updated by Devrim Gündüz 4 days ago " Hi, We dropped Amazon Linux support years ago, there were lots of compatibility issues. The new repo RPMs just reflect that. Please switch to a supported distro, or rebuild RPMs from SRPMS. Regards, Devrim I"m not sure exactly what this means, we got a notice from Amazon recently about EOL issues for PG 9.3 on an RDS server, is that related? (I don't handle AWS administration issues.) I would say not. Pretty sure the notice you got is about: https://www.postgresql.org/support/versioning/ Version Current minor Supported First Release Final Release 9.3 9.3.25 NoSeptember 9, 2013 November 8, 2018 In other words about Postgres 9.3 being past EOL as of past November. What the OP was referring to was Amazon's version of Linux: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/amazon-linux-ami-basics.html which is based off RH. The community RH repos no longer support that distro. -- Mike Nolan -- Adrian Klaver adrian.kla...@aklaver.com
Re: ERROR: operator does not exist: timestamp without time zone + integer
On 4/30/19 8:00 AM, Andrew Gierth wrote:
"Adrian" == Adrian Klaver 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.kla...@aklaver.com
Re: ERROR: operator does not exist: timestamp without time zone + integer
> "Adrian" == Adrian Klaver 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)
Re: ERROR: operator does not exist: timestamp without time zone + integer
On 4/30/19 12:11 PM, Andrew Gierth wrote:
"Adrian" == Adrian Klaver 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.kla...@aklaver.com
PostgreSQL Asian language support for full text search using ICU (and also updating pg_trgm)
Hello, Currently PostgreSQL doesn't support full text search natively for many Asian languages such as Chinese, Japanese and others. These languages are used by a large portion of the population of the world. The two key modules that could be modified to support Asian languages are the full text search module (including tsvector) and pg_trgm. I would like to propose that this support be added to PostgreSQL. For full text search, PostgreSQL could add a new parser ( https://www.postgresql.org/docs/9.2/textsearch-parsers.html) that implements ICU word tokenization. This should be a lot more easier than before now that PostgreSQL itself already includes ICU dependencies for other things. Then allow the ICU parser to be chosen at run-time (via a run-time config or an option to to_tsvector). That is all that is needed to support full text search for many more Asian languages natively in PostgreSQL such as Chinese, Japanese and Thai. For example Elastic Search implements this using its ICU Tokenizer plugin: https://www.elastic.co/guide/en/elasticsearch/guide/current/icu-tokenizer.html Some information about the related APIs in ICU for this are at: http://userguide.icu-project.org/boundaryanalysis Another simple improvement that would give another option for searching for Asian languages is to add a run-time setting for pg_trgm that would tell it to not drop non-ascii characters, as currently it only indexes ascii characters and thus all Asian language characters are dropped. I emphasize 'run-time setting' because when using PostgreSQL via a Database-As-A-Service service provider, most of the time it is not possible to change the config files, recompile sources, or add any new extensions. PostgreSQL is an awesome project and probably the best RDBMS right now. I hope the maintainers consider this suggestion. Best Regards, Chanon
