Re: [DOCS] Requesting clarifying details on extract(epoch from timestamp)
ralph.h...@gmail.com writes:
> Specifically, for the case of 'extract epoch', the docs state:
> "for date and timestamp values, the number of seconds since 1970-01-01
> 00:00:00 local time"
> I ran an experiment, importing a timestamp '2016-06-26 20:01:38' with
> default time zone 'localtime', which is AEST in my case.
> SELECT id, extract(epoch FROM not_before) FROM bla;
> -> 1466971298
> Now I switch to timezone = 'UTC' in postgresql.conf. Confirming with SHOW
> TIMEZONE that I am now in the default UTC timezone in my session. Same
> query:
> -> 1466971298
> In other words, it seems that the function extract(epoch from timestamp)
> considered the timestamp field to be in UTC in both cases. I find it hard to
> reconcile that with the docs.
I don't see anything particularly wrong there; the misconception I think
you're harboring is not with extract(epoch), but with what "local time"
means. When you change TimeZone, you're changing the implied origin
for non-tz timestamps. Here's an example:
regression=# show timezone;
TimeZone
US/Eastern
(1 row)
regression=# create table foo (f1 timestamptz, f2 timestamp);
CREATE TABLE
regression=# insert into foo values('1970-01-01 00:00+00', '1970-01-01 00:00');
INSERT 0 1
regression=# select * from foo;
f1 | f2
+-
1969-12-31 19:00:00-05 | 1970-01-01 00:00:00
(1 row)
regression=# select extract(epoch from f1), extract(epoch from f2) from foo;
date_part | date_part
---+---
0 | 0
(1 row)
regression=# set timezone = utc;
SET
regression=# select * from foo;
f1 | f2
+-
1970-01-01 00:00:00+00 | 1970-01-01 00:00:00
(1 row)
regression=# select extract(epoch from f1), extract(epoch from f2) from foo;
date_part | date_part
---+---
0 | 0
(1 row)
Adjusting "timezone" changed the displayed form of the timestamptz value,
but not its underlying stored value, which was UTC anyway. It didn't
change the displayed form of the timestamp value, since that's implicitly
relative to the epoch for the current timezone. In the same way, both
entries were exactly 0 seconds past their respective epochs before
changing "timezone", and they're still exactly 0 seconds past their
respective epochs afterwards.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes
Erwin Brandstetter writes: > In Table 9-27. "Template Pattern Modifiers for Numeric Formatting" it says: > FM | prefix fill mode (suppress leading zeroes and padding blanks) | FM > In fact, 'FM' also suppresses *trailing* zeroes after the comma. To fix, > this might be changed to: >suppress insignificant zeroes and padding blanks Not necessarily. A bit of experimentation says that it also matters whether you use "0" or "9" as the format character: regression=# select to_char(0.1, '0.'); to_char - 0.1000 (1 row) regression=# select to_char(0.1, 'FM0.'); to_char - 0.1 (1 row) regression=# select to_char(0.1, '0.9900'); to_char - 0.1000 (1 row) regression=# select to_char(0.1, 'FM0.9900'); to_char - 0.1000 (1 row) regression=# select to_char(0.1, 'FM00.99009'); to_char - 00.1000 (1 row) It's also worth noting the existing examples regression=# select to_char(-0.1, '99.99'); to_char - -.10 (1 row) regression=# select to_char(-0.1, 'FM99.99'); to_char - -.1 (1 row) So it appears to me that the bit you point out is flat out backwards; what FM actually suppresses is trailing zeroes not leading zeroes. I'm tempted to propose that in table 9-26, we need to write 9 digit position (can be dropped if insignificant) 0 digit position (cannot be dropped, even if insignificant) and then in 9-27 say FM fill mode: suppress trailing zeroes and padding spaces Also, in between those two tables, I see * 9 results in a value with the same number of digits as there are 9s. If a digit is not available it outputs a space. This seems outright wrong per the above examples, and in any case is not very useful since it doesn't explain the difference from "0". Perhaps rewrite as * 0 specifies a digit position that will always be printed, even if it contains a leading/trailing zero. 9 also specifies a digit position, but if it is a leading zero then it will be replaced by a space, while if it is a trailing zero and fill mode is specified then it will be deleted. (I wonder how closely that agrees with Oracle's behavior ...) regards, tom lane -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] Failing example for to_number()
Erwin Brandstetter writes:
> On 21 August 2017 at 16:30, David G. Johnston
> wrote:
>> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter
>> wrote:
>>> The example fails for locales where the comma (',') does not happen to be
>>> the group separator and the dot ('.') is not the decimal point.
>> If one wants to try the example in a language other than in which the
>> example was written they should modify it so that the literal number being
>> parsed in written in conformance to the locale definition for the language
>> you are using.
> I guess there should be some more explanation.
Yeah. How about adding something like this to the "Usage notes for
numeric formatting" between tables 9-26 and 9-27:
* The pattern characters S, L, D, and G represent the sign,
currency symbol, decimal point, and thousands separator characters
defined by the current locale (see lc_monetary and lc_numeric).
The pattern characters period and comma represent those exact
characters, with the meanings of decimal point and thousands
separator, regardless of locale.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] Syntax for changing owner on sequence is not correct
I am just telling you that in your officiall documentation i didn't find that command. I founded oj stack owerflow and when i changed command I succesfully changed owner. Also one more hit: documentation will be much better with examples. I am DBA for a 7 years on ms sql and oracle and they have examples which can make life more easily if you are starting with some new tehnologies like postgres. Kind regards, Tarik Dolovac Sent from my iPhone > On 10 Aug 2017, at 16:04, David G. Johnston > wrote: > >> On Thu, Aug 10, 2017 at 5:37 AM, Peter Eisentraut >> wrote: > >> On 8/9/17 05:35, tarik.dolovac...@gmail.com wrote: >> > The following documentation comment has been logged on the website: >> > >> > Page: https://www.postgresql.org/docs/8.4/static/sql-altersequence.html >> > Description: >> > >> > Per documentation owner changing should use >> > owner to >> > this syntax doesn't work al 9.5 version >> > owned by >> > is working please correct because I spent over 3 hour until I figure out >> > what is the problem >> >> Are you saying that ALTER SEQUENCE ... OWNER TO doesn't work? It >> appears to work for everyone else. > > Sequences can have two owners - a role and, optionally, a table.column. > > The name following "OWNED BY" is a table.column (NONE to clear). > The name following "OWNER TO" is a role (mandatory). > > David J.
[DOCS] mistype
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/sql-expressions.html Description: cite "The restriction about appearing only in the result list or HAVING clause applies with respect to the query level that the aggregate belongs to." May be "the restriction above"? -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
[DOCS] Mistype
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/sql-createpolicy.html Description: "Note that INSERT with ON CONFLICT DO UPDATE checks INSERT policies' WITH CHECK expressions only for rows appended to the relation by the INSERT path." May be "by the INSERT part"? -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] mistype
On Fri, Aug 25, 2017 at 12:19 PM, wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.6/static/sql-expressions.html > Description: > > cite "The restriction about appearing only in the result list or > HAVING > clause applies with respect to the query level that the aggregate belongs > to." > > May be "the restriction above"? > Or, "The restriction that variables must appear only in the result list or HAVING clause applies to the query level to which the aggregate expression belongs." (I would consider emphasizing the word belongs). Really, just adding the word variables - "The restriction about variables appearing only" - would make it technically correct. Your proposal of only changing "about" to "above" makes it grammatically wrong and fails to fix the underlying problem. "about" it is right word but the sentence fails to provide "what", what must only appear in the result list or having clause? A meaningful example might help too - I'm having trouble imaging when one would choose to write a subquery whose effect would end up being treated as part of the main query. David J.
