Re: Selecting strict, immutable text for a composite type.

On Thursday, May 10, 2018, Steven Lembark wrote: > > Q: Why does it work with enums? Guessing because enums are not composites; they are scalar and most scalar types in core seem to be covered by the extension. > e.g., If I create a type foo_t as enum (...) and install the function > foo_text

Re: Selecting strict, immutable text for a composite type.

On Thu, 10 May 2018 17:38:48 -0400 Tom Lane wrote: > Yeah, but that's not what you did. > > I think you could make that work with > > exclude using gist ( > lat_lng_text(location) with =, > effective with && > ) > > but it's not going to apply the function without you telling it to. Q: Wh

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 01:37 PM, Ben Hood wrote: On 10 May 2018, at 16:33, Francisco Olarte > wrote: For what you want to do I think you'll have to parse the text value, maybe by definig a view with a text columns and using some rule/trigger magic for insert / updates.

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 02:17 PM, Peter J. Holzer wrote: On 2018-05-10 21:37:26 +0100, Ben Hood wrote: On 10 May 2018, at 16:33, Francisco Olarte wrote: For what you want to do I think you'll have to parse the text value, maybe by definig a view with a text columns and using some rul

Re: Selecting strict, immutable text for a composite type.

On Thu, May 10, 2018 at 2:16 PM, Steven Lembark wrote: > exclude using gist > ( > locationusing =, > effective using && > ) > Have you installed the btree-​gist extension? https://www.postgresql.org/docs/10/static/btree-gist.html Not sure about composites but

Re: Selecting strict, immutable text for a composite type.

Steven Lembark writes: > On Thu, 10 May 2018 14:41:26 -0400 > Tom Lane wrote: >> Steven Lembark writes: >>> The problem is with gists telling me that they cannot index >>> the type. This works for enums, just not the composite type. >> Oh, well, they can't. There's no GiST opclass covering a

Re: Selecting strict, immutable text for a composite type.

On Thu, 10 May 2018 14:41:26 -0400 Tom Lane wrote: > [ please keep the list cc'd ] > > Steven Lembark writes: > > On Thu, 10 May 2018 11:52:48 -0400 > > Tom Lane wrote: > >> Maybe you should show a more concrete example of what's not > >> working. > > > The problem is with gists telling m

Re: Domain based on TIMEZONE WITH TIME ZONE

On 2018-05-10 21:37:26 +0100, Ben Hood wrote: > On 10 May 2018, at 16:33, Francisco Olarte wrote: > > For what you want to do I think you'll have to parse the text value, > maybe by definig a view with a text columns and using some > rule/trigger magic for insert / updates. > >

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 16:33, Francisco Olarte wrote: > > For what you want to do I think you'll have to parse the text value, > maybe by definig a view with a text columns and using some > rule/trigger magic for insert / updates. Sorry for being unclear - the solution I have in production appea

Re: Domain based on TIMEZONE WITH TIME ZONE

I think for the use case you describe, and given the fact that it does work in production, your solution simply shows The Power Of PostgreSQL. Java tries to be cross-platform, JDBC tries to be cross-database -- these goals don't seem to lend themselves to advocating or understanding what one parti

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 17:38, Adrian Klaver wrote: > > Well if you are using a timestamp with timezone field the value is always > going to be stored as UTC. The TimeZone setting just determines the rotation > from the input value to the stored value and the reverse. My previous point > was j

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 17:35, David G. Johnston > wrote: > > ​'2018-05-10T15:23:00-07:00​'::timestamptz is unambiguous That is true. Mandating UTC is not the only way to eliminate ambiguity. Apologies for appearing to suggest that this is case. > > Allowing client applications to represent

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 18:29, Adrian Klaver wrote: > > Per my previous post a timestamp with timezone is going to be stored as UTC, > so there is no ambiguity there. On reflection I realized your concern maybe > with determining the original input timezone. That information is not stored > by

Re: Selecting strict, immutable text for a composite type.

[ please keep the list cc'd ] Steven Lembark writes: > On Thu, 10 May 2018 11:52:48 -0400 > Tom Lane wrote: >> Maybe you should show a more concrete example of what's not working. > The problem is with gists telling me that they cannot index > the type. This works for enums, just not the compos

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 09:13 AM, Ben Hood wrote: On 10 May 2018, at 15:12, Vick Khera > wrote: On Thu, May 10, 2018 at 7:31 AM, Ben Hood > wrote: Or are we saying that domains are one way of achieving the timestamp hygiene, but equally, you can

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 09:09 AM, Ben Hood wrote: On 10 May 2018, at 14:41, Adrian Klaver wrote: OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit timezone qualified timestamps in what language they are written in. Not really: https://www.postgresql.org/docs/10/static/datatype-

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:33, Tom Lane wrote: > > Ben Hood writes: >> So the question is not how does the timestamp get stored, rather, is it an >> anti-pattern to use Postgres as a linter for apps that forget to use UTC >> exclusively? > > Well, using a domain to enforce additional constraint

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 9:13 AM, Ben Hood wrote: > On 10 May 2018, at 15:12, Vick Khera wrote: > > On Thu, May 10, 2018 at 7:31 AM, Ben Hood wrote: > >> Or are we saying that domains are one way of achieving the timestamp >> hygiene, but equally, you can get the same result as described above?

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:17, Karsten Hilbert wrote: > >> Not really: >> >> https://www.postgresql.org/docs/10/static/datatype-datetime.html >> >> "For timestamp with time zone, the internally stored value is always in UTC >> (Universal Coordinated Time, traditionally known as Greenwich Mean Ti

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:12, Vick Khera wrote: > > On Thu, May 10, 2018 at 7:31 AM, Ben Hood > wrote: > Or are we saying that domains are one way of achieving the timestamp hygiene, > but equally, you can get the same result as described above? > > The *only* way to hav

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 14:41, Adrian Klaver wrote: >> OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit >> timezone qualified timestamps in what language they are written in. > > Not really: > > https://www.postgresql.org/docs/10/static/datatype-datetime.html > > "For times

Re: Selecting strict, immutable text for a composite type.

Steven Lembark writes: > Q: What is the syntax for a strict, immutable function in >SQL that returns text suitable for use with either >creating a "select *" view or a GIST index? Your example works just fine for me ... regression=# select lat_lng_text('(42,54.5)'); lat_lng_text

Re: Enhancement to psql command, feedback.

On Thu, May 10, 2018 at 4:46 PM, Steven Lembark wrote: > The whole point of "#!" working in shell is that the two-bytes > (a) mark the file as executable by a specific shell command and > (b) are a shell comment. Shebang is an unix-ism. It is not part of the shell. The shell just execs whatever y

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 12:19 PM, Ben Hood wrote: >> On 10 May 2018, at 09:59, Francisco Olarte wrote: >> Maybe I'm confussing you more, its not too easy to explain. >> The point is TIMEZONE is not stored in either of them. > > Many thanks for clarification, very much appreciated. > > Your p

Selecting strict, immutable text for a composite type.

Trying to find specific doc's. Lots of people asking similar quesitons told to work around it different ways. Is there any specific doc on how to create a default operator class for a given composite type with any examples of minimal working code to access the pieces and convert them to text? I ha

Re: Enhancement to psql command, feedback.

The whole point of "#!" working in shell is that the two-bytes (a) mark the file as executable by a specific shell command and (b) are a shell comment. One fairly simple fix that would make annotating here scripts and the like simpler for shell(ish) execution would be simply ignoring all text fr

Re: Is there any C functions that convert the entry to string?

"=?ISO-8859-1?B?YQ==?=" <372660...@qq.com> writes: > Saying that I am writing a C function that may facing varies of types. Is > there a postgresql function that will automatically call the relative > to_string function and transform them to string?? You might look at the format() function (a/k/

Re: Domain based on TIMEZONE WITH TIME ZONE

Ben Hood writes: > So the question is not how does the timestamp get stored, rather, is it an > anti-pattern to use Postgres as a linter for apps that forget to use UTC > exclusively? Well, using a domain to enforce additional constraints on a field's value is certainly not an anti-pattern in i

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 06:41:04AM -0700, Adrian Klaver wrote: >> OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit >> timezone qualified timestamps in what language they are written in. > > Not really: > > https://www.postgresql.org/docs/10/static/datatype-datetime.html >

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 7:31 AM, Ben Hood wrote: > Or are we saying that domains are one way of achieving the timestamp > hygiene, but equally, you can get the same result as described above? > The *only* way to have timestamp hygiene is to require them to have time zones at all times, even if t

Re: Why is my Postgre server went in recovery mode all in sudden

On 05/10/2018 04:42 AM, nikhil raj wrote: 1) What OS and version? ans: windows 2012R2 2) System memory size is ? ans: 32GB 3) What is session_start_timestamp tracking? In other words what does it match up to here: ans: This is the format to timestamp=%m,user=%u,db=%d,app=%a,client=%h,transa

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 04:31 AM, Ben Hood wrote: On 10 May 2018, at 11:36, Karsten Hilbert wrote: On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote: I dare say it is one of PG's strengths' to be usable as a "linter”. Interesting that you share this view, because after thinking about why I w

Re: ON CONFLICT DO UPDATE

On 05/09/2018 09:50 PM, tango ward wrote: Ccing list. Hi, this is the my ON CONFLICT CODE ON CONFLICT (school_system_id,   student_id,   campus_name   ) DO UPDATE   

Re: Why is my Postgre server went in recovery mode all in sudden

1) What OS and version? ans: windows 2012R2 2) System memory size is ? ans: 32GB 3) What is session_start_timestamp tracking? In other words what does it match up to here: ans: This is the format to timestamp=%m,user=%u,db=%d,app=%a,client=%h,transaction- ID=%x,session_start_timestamp=%s,SQL_s

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 11:36, Karsten Hilbert wrote: > > On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote: > > I dare say it is one of PG's strengths' to be usable as a > "linter”. Interesting that you share this view, because after thinking about why I was doing this, using UTC domain

Is there any C functions that convert the entry to string?

Saying that I am writing a C function that may facing varies of types. Is there a postgresql function that will automatically call the relative to_string function and transform them to string??

Re: issues when installing postgres

Dear Tim and Adrian Thanks for your attention. Finally I solved the problem. In addition to deleting files as suggested it seems to be important to *remove postgres user and group* (userdel -r postgres, groupdel postgres) before the new install. https://stackoverflow.com/questions/2748607/how-to

Re: How do I get the SQL statement in a trigger?

2018-05-10 12:50 GMT+02:00 Fabio Ugo Venchiarutti : > Querying over pg_stat_activity with clauses in a trigger can be quite > expensive if your write transaction rate is high. > > > You may want to look into the current_query() function documented at > https://www.postgresql.org/docs/current/stat

Re: How do I get the SQL statement in a trigger?

Querying over pg_stat_activity with clauses in a trigger can be quite expensive if your write transaction rate is high. You may want to look into the current_query() function documented at https://www.postgresql.org/docs/current/static/functions-info.html On 10/05/18 11:44, Pavel Steh

Re: How do I get the SQL statement in a trigger?

2018-05-10 12:42 GMT+02:00 a <372660...@qq.com>: > Thanks a lot, would you please be so kind to tell me more about what is top > level query?? > for example - if you run some queries from PLpgSQL functions (triggers), then these queries are not top queries. Regards Pavel > > > ---

Re: How do I get the SQL statement in a trigger?

Thanks a lot, would you please be so kind to tell me more about what is top level query?? -- Original message -- From: "Pavel Stehule"; Sendtime: Thursday, May 10, 2018 6:38 PM To: "a"<372660...@qq.com>; Cc: "pgsql-general"; Subject: Re: How do I get the SQL

Re: How do I get the SQL statement in a trigger?

Hi 2018-05-10 12:23 GMT+02:00 a <372660...@qq.com>: > Hi I would like to write a trigger that recorded every sql statement under > the effected entry. So if it is possible to retrieve the sql statement > within a trigger?? > You can read a tom command from pg_stat_activity table postgres=# sel

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote: > The question should not be “how does Postgres store the timestamp internally”. > > Rather it should read “is enforcing the submission of UTC denominated > timestamps in the server by using a domain a sensible way to enforce a policy >

How do I get the SQL statement in a trigger?

Hi I would like to write a trigger that recorded every sql statement under the effected entry. So if it is possible to retrieve the sql statement within a trigger??

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 09:59, Francisco Olarte wrote: > > On Thu, May 10, 2018 at 10:03 AM, Ben Hood wrote: > ... >> Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE >> internally? > > After reading in the follow up TIMEZONE is a typo for TIMESTAMP, yes, you are. > > Aproxi

Re: ON CONFLICT DO UPDATE

Yes, that's what I figured out eventually. I thought, only the columns that I declared inside the ON CONFLICT() parenthesis can be called in SET. My bad. On Thu, May 10, 2018 at 5:57 PM, Alban Hertroys wrote: > > > On 10 May 2018, at 7:13, tango ward wrote: > > > ON CO

Re: ON CONFLICT DO UPDATE

> On 10 May 2018, at 7:13, tango ward wrote: > ON CONFLICT (school_system_id, > student_id, > campus_name > ) DO UPDATE > SET s

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 10:03 AM, Ben Hood wrote: ... > Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE > internally? After reading in the follow up TIMEZONE is a typo for TIMESTAMP, yes, you are. Aproximately, postgres stores ( virtually ) a point in the time line, for bot

Re: Domain based on TIMEZONE WITH TIME ZONE

Sorry about the bug in the subject: the data type is TIMESTAMP WITH TIME ZONE, not TIMEZONE WITH TIME ZONE > On 10 May 2018, at 09:03, Ben Hood wrote: > > Hi, > > I'm using a domain to specialize the built in TIMEZONE WITH TIME ZONE type. I > want to sanity check this approach before continu

Domain based on TIMEZONE WITH TIME ZONE

Hi, I'm using a domain to specialize the built in TIMEZONE WITH TIME ZONE type. I want to sanity check this approach before continuing to use this. I want to prevent timestamps with non-UTC offsets from getting inserted into the database. Having a UTC-only database at the schema level means no