Re: to_date() and to_timestamp() with negative years

[Adrian Klaver]

On 11/3/21 19:12, Bryn Llewellyn wrote:

/adrian.kla...@aklaver.com  wrote:/




Back to the point about separators, the "Current" doc has this bullet:

«
A separator (a space or non-letter/non-digit character) in the template 
string of to_timestamp and to_date matches any single separator in 
the input string or is skipped, unless the FX option is used...

»

(There's no such explanation in the Version 11 doc—but never mind that.) 
I read this to mean that a space IS a viable separator. And yet Tom's 
"nope, the space doesn't count [as a separator]" claims the opposite. 
The bullet's wording, by the way, says that the minus sign is a 
separator. But yet it can also be taken to confer the meaning "BC" to a 
year. No wonder I'm confused.


Elsewhere the "Current" doc says that runs of two or more spaces have 
the same effect as a single space (in the absence of FX or FM complexity).


No wonder that examples like I showed sometimes produce the wrong 
results, even after more than one round of tinkering with the C 
implementation. Try this test (the to-be-converted text has runs of five 
spaces, and the template has runs of ten spaces):


It comes down to determining what is a separator and what is the 
negative sign.




select to_date('     1950     02     14', '          MM          DD');
select to_date('     -1950     02     14', '          MM          DD');


The row above returns:

select to_date(' -1950 02 14', '  MM  DD');
to_date
---
 1950-02-14 BC

As you state below.

Change it to:

select to_date(' -1950 02 14', '   MM  DD');
  to_date

 1950-02-14

and you don't get the BC as -1950 is taken as separator(-)1950 not 
negative(-)1950.




select to_date('     14     02     1950', 'DD          MM          ');
select to_date('     14     02     -1950', 'DD          MM          ');

The above returns:

select to_date(' 14 02 -1950', 'DD  MM  ');
  to_date

 1950-02-14

Change it to:

select to_date(' 14 02 -1950', 'DD  MM');
to_date
---
 1950-02-14 BC

and you get BC as -1950 is taken as negative(-)1950 not separator(-)1950.


Not sure how this can be handled in a totally predictable way given the 
unpredictable ways in which datetime strings are formatted?


The only thing I can say it is it points out that when working with 
datetimes settling on a standard format is your best defense against 
unpredictable results.




"-1950" is taken as "BC" in the second to_date() but it is not so taken 
in the fourth to_date().





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: to_date() and to_timestamp() with negative years

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
> On 11/3/21 17:00, Adrian Klaver wrote:
>> On 11/3/21 15:56, Bryn Llewellyn wrote:
 t...@sss.pgh.pa.us wrote:
 
> 
>>> And you have to have some kind of separator between the years substring and 
>>> the adjacent one(s) even to succeed with years that have more than four 
>>> digits. Another usage note stresses that while this is OK:
>>> 
>>> select to_date('12340101', 'MMDD');
>>> 
>>> this isn't:
>>> 
>>> select to_date('123450101', 'MMDD');
>> It does with a tweak:
>> select to_date('12345 0101', 'FMMMDD');
>>to_date
>> -
>>  12345-01-01
> 
> Well that was just plain wrong. I was not paying attention.

I'm missing your point, Adrian. But I must confess that I'm guilty of not 
expressing myself clearly with my reference to years with more than four 
digits. I meant that reference only to make the point that, in that use-case, a 
space CAN act as a separator.

By the way, I'd been studying the "Usage notes for date/time formatting" 
section in the Version 11 PG docs because that's the PG version that YugabyteDB 
uses. It has eleven bullets. The corresponding section in the "Current" PG docs 
has just eight bullets and the overall wording of the section is quite 
different. However, the bullet, in the Version 11 docs, that started me on the 
testing that spurred my first email in this thread is identically worded in 
both versions. It says this:

«
In to_timestamp and to_date, negative years are treated as signifying BC. If 
you write both a negative year and an explicit BC field, you get AD again. An 
input of year zero is treated as 1 BC.
»

Back to the point about separators, the "Current" doc has this bullet:

«
A separator (a space or non-letter/non-digit character) in the template string 
of to_timestamp and to_date matches any single separator in the input string or 
is skipped, unless the FX option is used...
»

(There's no such explanation in the Version 11 doc—but never mind that.) I read 
this to mean that a space IS a viable separator. And yet Tom's "nope, the space 
doesn't count [as a separator]" claims the opposite. The bullet's wording, by 
the way, says that the minus sign is a separator. But yet it can also be taken 
to confer the meaning "BC" to a year. No wonder I'm confused.

Elsewhere the "Current" doc says that runs of two or more spaces have the same 
effect as a single space (in the absence of FX or FM complexity).

No wonder that examples like I showed sometimes produce the wrong results, even 
after more than one round of tinkering with the C implementation. Try this test 
(the to-be-converted text has runs of five spaces, and the template has runs of 
ten spaces):

select to_date(' 1950 02 14', '  MM  DD');
select to_date(' -1950 02 14', '  MM  DD');

select to_date(' 14 02 1950', 'DD  MM  ');
select to_date(' 14 02 -1950', 'DD  MM  ');

"-1950" is taken as "BC" in the second to_date() but it is not so taken in the 
fourth to_date().

Re: Error with Insert from View with ON Conflict

[Peter Geoghegan]

On Wed, Nov 3, 2021 at 2:18 PM Steve Baldwin  wrote:
> I'm pretty sure the 'alias' for the '.. on conflict do update ..' needs to be 
> 'excluded' (i.e. checks = excluded.checks, ...). Check the docs.

That's right . The excluded.* pseudo-table isn't exactly the same
thing as the target table -- it is a tuple that has the same "shape",
that represents what the implementation tried (and failed) to insert
into the table. I have to imagine that Alex wants to reference that,
because that's the standard idiomatic approach with ON CONFLICT. And
because the only alternative interpretation is that Alex intends to
update those columns using their current values (not new values),
which won't really change anything -- that seems unlikely to have been
the intent.

-- 
Peter Geoghegan

Re: to_date() and to_timestamp() with negative years

[Adrian Klaver]

On 11/3/21 17:00, Adrian Klaver wrote:

On 11/3/21 15:56, Bryn Llewellyn wrote:

t...@sss.pgh.pa.us wrote:



And you have to have some kind of separator between the years 
substring and the adjacent one(s) even to succeed with years that have 
more than four digits. Another usage note stresses that while this is OK:


select to_date('12340101', 'MMDD');

this isn't:

select to_date('123450101', 'MMDD');


It does with a tweak:

select to_date('12345 0101', 'FMMMDD');
    to_date
-
  12345-01-01


Well that was just plain wrong. I was not paying attention.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: to_date() and to_timestamp() with negative years

[Adrian Klaver]

On 11/3/21 15:56, Bryn Llewellyn wrote:

t...@sss.pgh.pa.us wrote:

Adrian Klaver  writes:

Haven't had time to work through what the above is actually doing.


I think the first two are explained by 489c9c340:

Also, arrange for the combination of a negative year and an
explicit "BC" marker to cancel out and produce AD.  This is how
the negative-century case works, so it seems sane to do likewise.

The last two look like a parsing issue: with no field separator (nope, the 
space doesn't count), the code is taking the dash as a field separator.


Thanks, Adrian, for trying those tests. So if PG is aiming for one consistent 
story for years that are less than one, in all three APIs (to_date() and its 
cousins, make_timestamp[tz](), and text-to-date-time typecasts), then work 
still remains.

The parsing problem seems to be a separable annoyance. I assume that Tom’s 
"nope, the space doesn't count” is a narrow comment on this corner of the 
implementation. It definitely counts here:

select to_date('12345 01 01', ' MM DD');

And you have to have some kind of separator between the years substring and the 
adjacent one(s) even to succeed with years that have more than four digits. 
Another usage note stresses that while this is OK:

select to_date('12340101', 'MMDD');

this isn't:

select to_date('123450101', 'MMDD');


It does with a tweak:

select to_date('12345 0101', 'FMMMDD');
   to_date
-
 12345-01-01




and nor is anything else that you might dream up that does not have a separator 
as mentioned above.

Tom asked about Oracle Database. I have a still-working Version 18.0 in a VM on my 
laptop. The query can't be so terse there because there's no implicit typecast from date 
to text. And there's the famous annoyance of "dual".


If you go here:

https://www.postgresql.org/docs/current/functions-formatting.html

and search on Oracle you will see that there are other exceptions. Like 
most things there is not complete agreement on how closely to follow 
someone else's code.




I tried this first:

select to_char(to_date('1234/01/01', '/MM/DD'), '/MM/DD') from dual;

It gets the same output back as the input you gave. So far so good. Then I 
changed the input to '-1234/01/01'. It caused this error:

ORA-01841: (full) year must be between -4713 and +, and not be 0

So it seems that Oracle Database has its own problems. But at least the wording 
"must… not be 0" is clear—and not what PG wants to support.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: to_date() and to_timestamp() with negative years

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
> Adrian Klaver  writes:
>> Haven't had time to work through what the above is actually doing.
> 
> I think the first two are explained by 489c9c340:
> 
>Also, arrange for the combination of a negative year and an
>explicit "BC" marker to cancel out and produce AD.  This is how
>the negative-century case works, so it seems sane to do likewise.
> 
> The last two look like a parsing issue: with no field separator (nope, the 
> space doesn't count), the code is taking the dash as a field separator.

Thanks, Adrian, for trying those tests. So if PG is aiming for one consistent 
story for years that are less than one, in all three APIs (to_date() and its 
cousins, make_timestamp[tz](), and text-to-date-time typecasts), then work 
still remains.

The parsing problem seems to be a separable annoyance. I assume that Tom’s 
"nope, the space doesn't count” is a narrow comment on this corner of the 
implementation. It definitely counts here:

select to_date('12345 01 01', ' MM DD');

And you have to have some kind of separator between the years substring and the 
adjacent one(s) even to succeed with years that have more than four digits. 
Another usage note stresses that while this is OK:

select to_date('12340101', 'MMDD');

this isn't:

select to_date('123450101', 'MMDD');

and nor is anything else that you might dream up that does not have a separator 
as mentioned above.

Tom asked about Oracle Database. I have a still-working Version 18.0 in a VM on 
my laptop. The query can't be so terse there because there's no implicit 
typecast from date to text. And there's the famous annoyance of "dual".

I tried this first:

select to_char(to_date('1234/01/01', '/MM/DD'), '/MM/DD') from dual;

It gets the same output back as the input you gave. So far so good. Then I 
changed the input to '-1234/01/01'. It caused this error:

ORA-01841: (full) year must be between -4713 and +, and not be 0

So it seems that Oracle Database has its own problems. But at least the wording 
"must… not be 0" is clear—and not what PG wants to support.

Re: to_date() and to_timestamp() with negative years

[Tom Lane]

Adrian Klaver  writes:
> Haven't had time to work through what the above is actually doing.

I think the first two are explained by 489c9c340:

Also, arrange for the combination of a negative year and an
explicit "BC" marker to cancel out and produce AD.  This is how
the negative-century case works, so it seems sane to do likewise.

The last two look like a parsing issue: with no field separator
(nope, the space doesn't count), the code is taking the dash
as a field separator.

regards, tom lane

Re: to_date() and to_timestamp() with negative years

[Adrian Klaver]

On 11/3/21 1:39 PM, Bryn Llewellyn wrote:

/adrian.kla...@aklaver.com  wrote:/


/Bryn wrote:/




In what PG version did you run your test?
I'm not surprised that grandfathered-in "solution" that I described 
will never go away—despite its questionable conceptual basis.


Thanks, Adrian. What results do these queries give in Version 14? 
(Forgive me, I'll make time to install v14 very soon.)


 select version();
  version 



 PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
7.5.0, 64-bit




select make_timestamp(0,1,1,0,0,0);

ERROR:  date field value out of range: 0-01-01


select make_timestamp(-0,1,1,0,0,0);

ERROR:  date field value out of range: 0-01-01



select '-0001-01-01'::date; -- and similar for typecasts to timestamp 
and timestamptz

ERROR:  invalid input syntax for type date: "-0001-01-01"
LINE 1: select '-0001-01-01'::date;



select '-01-01'::date;

ERROR:  date/time field value out of range: "-01-01"
LINE 1: select '-01-01'::date;



select '--01-01'::date;

ERROR:  invalid input syntax for type date: "--01-01"
LINE 1: select '--01-01'::date;



select
   to_date( '15/06/-2021',    'DD/MM/'    ) as a1,
   to_date( '15/06/-2021 BC', 'DD/MM//AD' ) as a2,
   ''                                           as "-",
   to_date( '15 06 -2021',    'DD MM '    ) as b1,
   to_date( '15 06 -2021 BC', 'DD MM  AD' ) as b2;



  a1   | a2 | - | b1 |  b2
---++---++---
 2021-06-15 BC | 2021-06-15 |   | 2021-06-15 | 2021-06-15 BC

Haven't had time to work through what the above is actually doing.

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Error with Insert from View with ON Conflict

[Steve Baldwin]

I'm pretty sure the 'alias' for the '.. on conflict do update ..' needs to
be 'excluded' (i.e. checks = excluded.checks, ...). Check the docs.

Steve

On Thu, Nov 4, 2021 at 8:05 AM Alex Magnum  wrote:

> Hi,
> I am trying to do an upsert using a view but for some reason get errors.
> All works fine without the ON CONFLICT
>
> INSERT INTO http_stats
> SELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())
> ON CONFLICT (url,ip,month) DO UPDATE
>   SET last_update = now(),
>   checks = V.checks,
>   uptime = V.uptime,
>   errors = V.errors;
>
> ERROR:  42P01: missing FROM-clause entry for table "v"
> LINE 5:   checks = V.checks,
>^
> LOCATION:  errorMissingRTE, parse_relation.c:3460
>
> Any help would be appreciated. Thanks in advance.
>
> A
>

Error with Insert from View with ON Conflict

[Alex Magnum]

Hi,
I am trying to do an upsert using a view but for some reason get errors.
All works fine without the ON CONFLICT

INSERT INTO http_stats
SELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())
ON CONFLICT (url,ip,month) DO UPDATE
  SET last_update = now(),
  checks = V.checks,
  uptime = V.uptime,
  errors = V.errors;

ERROR:  42P01: missing FROM-clause entry for table "v"
LINE 5:   checks = V.checks,
   ^
LOCATION:  errorMissingRTE, parse_relation.c:3460

Any help would be appreciated. Thanks in advance.

A

Re: to_date() and to_timestamp() with negative years

[Bryn Llewellyn]

adrian.kla...@aklaver.com wrote:

> Bryn wrote:
> 
>>> t...@sss.pgh.pa.us wrote:
>>> 
>>> Bryn Llewellyn  writes:
 Is there any chance that you might be bold and simply make negative "year" 
 values illegal in "to_date()" and "to_timestamp()" — just as they already 
 are in "make_timestamp()", "make_timestamptz()", and the "from text" 
 typecasts to date-time moment values?
>>> 
>>> Uh, what?
>>> 
>>> regression=# select make_timestamp(-44,3,15,0,0,0);
>>> make_timestamp
>>> 
>>> 0044-03-15 00:00:00 BC
>>> (1 row)
>>> 
>>> The other stuff you are talking about looks like confusion around which
>>> characters are minus signs in the data and which ones are field
>>> separators.  Given the very squishy definitions of to_date/to_timestamp,
>>> I'm not surprised if that works only with carefully chosen field
>>> layouts --- but it does work for me with all of these cases:
>>> 
>>> regression=# select to_date('-0044-03-15', '-MM-DD');
>>>to_date
>>> ---
>>> 0044-03-15 BC
>>> (1 row)
>>> 
>>> regression=# select to_date('03-15--0044', 'MM-DD-');
>>>to_date
>>> ---
>>> 0044-03-15 BC
>>> (1 row)
>>> 
>>> regression=# select to_date('03/15/-0044', 'MM/DD/');
>>>to_date
>>> ---
>>> 0044-03-15 BC
>>> (1 row)
>>> 
>>> I'd be the first to agree that that code is a mess and could stand to
>>> be rewritten --- but I seriously doubt that we'd take a patch that
>>> intentionally breaks cases that work fine today.  There's also the
>>> angle that these are supposed to be Oracle-compatible, so I wonder
>>> what Oracle does with such input.
>> I just ran your test:
>> select make_timestamp(-44,3,15,0,0,0);
>> in each of the three environments that I mentioned—but especially, 
>> therefore, in PG 13.4 (on macOS).
>> It cased the error that I mentioned:
>> ERROR:  22008: date field value out of range: -44-03-15
>> It's the same with "make_timestamp()".
> 
> https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.5.3
> 
> "
> Allow make_timestamp()/make_timestamptz() to accept negative years (Peter 
> Eisentraut)
> 
> Negative values are interpreted as BC years.
> "
> 
>> In what PG version did you run your test?
>> I'm not surprised that grandfathered-in "solution" that I described will 
>> never go away—despite its questionable conceptual basis.

Thanks, Adrian. What results do these queries give in Version 14? (Forgive me, 
I'll make time to install v14 very soon.)

select make_timestamp(0,1,1,0,0,0);

select make_timestamp(-0,1,1,0,0,0);

select '-0001-01-01'::date; -- and similar for typecasts to timestamp and 
timestamptz

select '-01-01'::date;

select '--01-01'::date;

select
  to_date( '15/06/-2021','DD/MM/') as a1,
  to_date( '15/06/-2021 BC', 'DD/MM//AD' ) as a2,
  ''   as "-",
  to_date( '15 06 -2021','DD MM ') as b1,
  to_date( '15 06 -2021 BC', 'DD MM  AD' ) as b2;

Re: Regex for (A) and (B) to find in Bus Stop (A) or (B)

[David G. Johnston]

On Wednesday, November 3, 2021, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, November 3, 2021, Shaozhong SHI 
> wrote:
>
>> What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?
>>
>
> Not tested…
>
> ^Bus\sStop\s\((\w)\)\sor\((\w)\)$
>
> The \s can just written as a space though the above seems clearer in email
> (though it allows for non-space whitespace too)
>

Sorry, the actual matcher probably should be something like:
[^)]+

So it matches everything except the closing paren.  If the value can
contain a paren its more complicated and I’d need to test and check to
figure it out.

David J.

Re: Regex for (A) and (B) to find in Bus Stop (A) or (B)

[David G. Johnston]

On Wednesday, November 3, 2021, Shaozhong SHI 
wrote:

> What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?
>

Not tested…

^Bus\sStop\s\((\w)\)\sor\((\w)\)$

The \s can just written as a space though the above seems clearer in email
(though it allows for non-space whitespace too)

David J.

Regex for (A) and (B) to find in Bus Stop (A) or (B)

[Shaozhong SHI]

What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?

Regards, David

Regex for (A) and (B) to find in Bus Stop (A) or (B)

[Shaozhong SHI]

What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?

Regards, David

Re: to_date() and to_timestamp() with negative years

[Adrian Klaver]

On 11/3/21 11:18, Bryn Llewellyn wrote:

t...@sss.pgh.pa.us wrote:

Bryn Llewellyn  writes:

Is there any chance that you might be bold and simply make negative "year" values illegal in "to_date()" and 
"to_timestamp()" — just as they already are in "make_timestamp()", "make_timestamptz()", and the "from 
text" typecasts to date-time moment values?


Uh, what?

regression=# select make_timestamp(-44,3,15,0,0,0);
 make_timestamp

0044-03-15 00:00:00 BC
(1 row)

The other stuff you are talking about looks like confusion around which
characters are minus signs in the data and which ones are field
separators.  Given the very squishy definitions of to_date/to_timestamp,
I'm not surprised if that works only with carefully chosen field
layouts --- but it does work for me with all of these cases:

regression=# select to_date('-0044-03-15', '-MM-DD');
to_date
---
0044-03-15 BC
(1 row)

regression=# select to_date('03-15--0044', 'MM-DD-');
to_date
---
0044-03-15 BC
(1 row)

regression=# select to_date('03/15/-0044', 'MM/DD/');
to_date
---
0044-03-15 BC
(1 row)

I'd be the first to agree that that code is a mess and could stand to
be rewritten --- but I seriously doubt that we'd take a patch that
intentionally breaks cases that work fine today.  There's also the
angle that these are supposed to be Oracle-compatible, so I wonder
what Oracle does with such input.


I just ran your test:

select make_timestamp(-44,3,15,0,0,0);

in each of the three environments that I mentioned—but especially, therefore, 
in PG 13.4 (on macOS).

It cased the error that I mentioned:

ERROR:  22008: date field value out of range: -44-03-15

It's the same with "make_timestamp()".


https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.5.3

"
Allow make_timestamp()/make_timestamptz() to accept negative years 
(Peter Eisentraut)


Negative values are interpreted as BC years.
"


In what PG version did you run your test?

I'm not surprised that grandfathered-in "solution" that I described will never 
go away—despite its questionable conceptual basis.







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Selectivity and row count estimates for JSONB columns

[Tom Lane]

Joel Perren  writes:
> - generic_restriction_selectivity() returns the default value (0.01) for
> data types that Postgres doesn't collect standard MCV and/or histogram
> statistics for. I think this is what happens with Table B which (quite
> correctly) does not have these statistics in pg_stats

There's nothing "correct" about that.  JSONB does have sorting support
(admittedly with a pretty arbitrary sort order), so I'd expect ANALYZE
to collect a histogram as well as MCV values for it.  Perhaps for your
Table B it's omitting the histogram because the MCV list captures the
entire contents of the column?  Or you've got auto-analyze disabled
for some reason?

Anyway, assuming you're using v14, the idea of matchingsel() is to
apply the given restriction clause to all the MCV and histogram
entries to see how many satisfy it [1].  In principle this'll produce
a far better estimate than any fixed default could provide.  If
you're coming out with a crappy estimate, you might be able to
improve matters by increasing the column's statistics target so
that more MCV and histogram entries are collected.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/12237.1582833074%40sss.pgh.pa.us

Re: to_date() and to_timestamp() with negative years

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
> Bryn Llewellyn  writes:
>> Is there any chance that you might be bold and simply make negative "year" 
>> values illegal in "to_date()" and "to_timestamp()" — just as they already 
>> are in "make_timestamp()", "make_timestamptz()", and the "from text" 
>> typecasts to date-time moment values?
> 
> Uh, what?
> 
> regression=# select make_timestamp(-44,3,15,0,0,0);
> make_timestamp 
> 
> 0044-03-15 00:00:00 BC
> (1 row)
> 
> The other stuff you are talking about looks like confusion around which
> characters are minus signs in the data and which ones are field
> separators.  Given the very squishy definitions of to_date/to_timestamp,
> I'm not surprised if that works only with carefully chosen field
> layouts --- but it does work for me with all of these cases:
> 
> regression=# select to_date('-0044-03-15', '-MM-DD');
>to_date
> ---
> 0044-03-15 BC
> (1 row)
> 
> regression=# select to_date('03-15--0044', 'MM-DD-');
>to_date
> ---
> 0044-03-15 BC
> (1 row)
> 
> regression=# select to_date('03/15/-0044', 'MM/DD/');
>to_date
> ---
> 0044-03-15 BC
> (1 row)
> 
> I'd be the first to agree that that code is a mess and could stand to
> be rewritten --- but I seriously doubt that we'd take a patch that
> intentionally breaks cases that work fine today.  There's also the
> angle that these are supposed to be Oracle-compatible, so I wonder
> what Oracle does with such input.

I just ran your test:

select make_timestamp(-44,3,15,0,0,0);

in each of the three environments that I mentioned—but especially, therefore, 
in PG 13.4 (on macOS).

It cased the error that I mentioned:

ERROR:  22008: date field value out of range: -44-03-15

It's the same with "make_timestamp()".

In what PG version did you run your test?

I'm not surprised that grandfathered-in "solution" that I described will never 
go away—despite its questionable conceptual basis.

Re: to_date() and to_timestamp() with negative years

[Tom Lane]

Bryn Llewellyn  writes:
> Is there any chance that you might be bold and simply make negative "year" 
> values illegal in "to_date()" and "to_timestamp()" — just as they already are 
> in "make_timestamp()", "make_timestamptz()", and the "from text" typecasts to 
> date-time moment values?

Uh, what?

regression=# select make_timestamp(-44,3,15,0,0,0);
 make_timestamp 

 0044-03-15 00:00:00 BC
(1 row)

The other stuff you are talking about looks like confusion around which
characters are minus signs in the data and which ones are field
separators.  Given the very squishy definitions of to_date/to_timestamp,
I'm not surprised if that works only with carefully chosen field
layouts --- but it does work for me with all of these cases:

regression=# select to_date('-0044-03-15', '-MM-DD');
to_date
---
 0044-03-15 BC
(1 row)

regression=# select to_date('03-15--0044', 'MM-DD-');
to_date
---
 0044-03-15 BC
(1 row)

regression=# select to_date('03/15/-0044', 'MM/DD/');
to_date
---
 0044-03-15 BC
(1 row)

I'd be the first to agree that that code is a mess and could stand to
be rewritten --- but I seriously doubt that we'd take a patch that
intentionally breaks cases that work fine today.  There's also the
angle that these are supposed to be Oracle-compatible, so I wonder
what Oracle does with such input.

regards, tom lane

to_date() and to_timestamp() with negative years

[Bryn Llewellyn]

I'm quoting here from "Usage notes for date/time formatting" just below "Table 
9.25. Template Pattern Modifiers for Date/Time Formatting" here:

https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE

on the page "9.8. Data Type Formatting Functions". Find this:

«
In to_timestamp and to_date, negative years are treated as signifying BC. If 
you write both a negative year and an explicit BC field, you get AD again. An 
input of year zero is treated as 1 BC.
»

This seems to be a suspect solution looking for a problem for these reasons:

1. Nobody ever talks about dates by saying "the year minus 42". It's always "42 
BC". Nor do they talk about "year zero" in the Gregorian calendar 'cos there's 
no such year.

2. If you invoke "make_timestamp()" or "make_timestamptz()" with a negative (or 
zero) argument for "year", then you get the "22008" error.

3. The text-to-date typecast « '-2021-06-15'::date » gets the "22009" error, as 
it does with "".

4. The unary "minus" operator is shorthand for "subtract the operand from zero" 
— i.e. "-x" means "0 - x". But there is no year zero. And anyway, the 
difference between two "date" values is an "integer" value; and the difference 
between two "timestamp[tz]" values is an "interval" value.

Finally, the implementation seems to be buggy (as I observed it in PG 13.4 on 
my MacBook with up-to-date macOS). Try this:

\x on
select
  to_date( '15/06/-2021','DD/MM/') as a1,
  to_date( '15/06/-2021 BC', 'DD/MM//AD' ) as a2,
  ''   as "-",
  to_date( '15 06 -2021','DD MM ') as b1,
  to_date( '15 06 -2021 BC', 'DD MM  AD' ) as b2;
\x off

Notice that the difference between the first two expressions (that produce the 
values "a1" and "a2") and the second two expressions (that produce the values 
"b1" and "b2") is how the to-be-converted substrings for "DD", "MM", and "" 
are separated. Otherwise, they express the same intention. So "b1" should be 
the same as "a1" and "b2" should be the same as "a2".

This is the result:

a3 | 2021-06-15 BC
a4 | 2021-06-15
-  | 
b3 | 2021-06-15
b4 | 2021-06-15 BC

The "a" values are right (by the rule in the PG doc)—and the "b" values are 
wrong.

If you express the same intention without the complication of using "-2021", 
then you get the right results throughout (and for any other way that you care 
to separate the to-be-interpreted substrings).

Moreover, I have an old PG 11.9 in a Ubuntu VM. The same test gets these 
results:

a1 | 2022-06-15 BC
a2 | 2022-06-15 BC
-  | 
b1 | 2022-06-15 BC
b2 | 2022-06-15 BC

Also buggy. But in a different way. And moreover (again) — I tride the test in 
our current latest YugabyteDB. This uses the PG 11.2 C code as is. (You'll have 
to trust me when I say that the test executes entirely here and goes nowhere 
near our own code,) This is the result (again on my MacBook).

a1 | 2022-06-15 BC
a2 | 2022-06-15 BC
-  | 
b1 | 2021-06-15
b2 | 2021-06-15 BC

Also buggy. But in YET a different way.

It looks like you have a tranche of baroque C code that wrongly implements a 
misguided idea. And that it's been tinkered around with between at least a 
couple of release boundaries without proper testing.

Is there any chance that you might be bold and simply make negative "year" 
values illegal in "to_date()" and "to_timestamp()" — just as they already are 
in "make_timestamp()", "make_timestamptz()", and the "from text" typecasts to 
date-time moment values?

Re: Doubt in pgbouncer

[Ninad Shah]

There is no equation to determine the best value. It depends on your
environment.


Regards,
Ninad Shah

On Wed, 3 Nov 2021 at 19:18, Rama Krishnan  wrote:

>
> Hi All,
>
> What is the limit value for fixing connection timeout issues?
>
> As i searched in google and verified pgbouncer document
> client_login_timeout
>
> If a client connects but does not manage to log in in this amount of time,
> it will be disconnected. Mainly needed to avoid dead connections stalling
> SUSPEND and thus online restart. [seconds]
>
> Default: 60.0
>
> Can you pls suggest the best value ? or how to decide this parameter value
>
>
>
> Regards
>
> A.Rama Krishnan
>

Doubt in pgbouncer

[Rama Krishnan]

Hi All,

What is the limit value for fixing connection timeout issues?

As i searched in google and verified pgbouncer document
client_login_timeout

If a client connects but does not manage to log in in this amount of time,
it will be disconnected. Mainly needed to avoid dead connections stalling
SUSPEND and thus online restart. [seconds]

Default: 60.0

Can you pls suggest the best value ? or how to decide this parameter value



Regards

A.Rama Krishnan

Re: develop a extension with cpp?

[huangning...@yahoo.com]

Thank you.  

On Wednesday, November 3, 2021, 06:20:18 AM GMT+8, Dmitry Igrishin 
 wrote:  
 
 вт, 2 нояб. 2021 г. в 20:12, huangning...@yahoo.com :
>
> Hi
>    if i can develop a extension with cpp language?
Sure, you can. Please, see example -- https://github.com/dmitigr/pgnso