RE: How to watch for schema changes

-Original Message- From: Igor Korot [mailto:ikoro...@gmail.com] Sent: Monday, December 03, 2018 8:29 AM To: Adrian Klaver Cc: pgsql-general Subject: Re: How to watch for schema changes ... And executing LISTEN will also work for ODBC connection, right? Thank you. ___

Re: How to watch for schema changes

Igor, On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman wrote: > > > -Original Message- > From: Igor Korot [mailto:ikoro...@gmail.com] > Sent: Monday, December 03, 2018 8:29 AM > To: Adrian Klaver > Cc: pgsql-general > Subject: Re: How to watch for schema changes > > ... > > An

RE: How to watch for schema changes

-Original Message- From: Igor Korot [mailto:ikoro...@gmail.com] Sent: Tuesday, December 04, 2018 11:07 AM To: Igor Neyman Cc: Adrian Klaver ; pgsql-general Subject: Re: How to watch for schema changes Igor, On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman wrote: > > > -Original Messag

Re: postgis after pg_upgrade

On 11/30/18 05:11, Slavcho Trnkovski wrote: > I have postgresql 9.4 with postgis extension installed (latest version, > 2.4.5). I upgraded postgresql from 9.4 to 9.6.  > After upgrading to 9.6 I get the following result > from PostGIS_full_version() >  select PostGIS_full_version(); > >  ... (proc

simple division

I have asked this question before and apologize for not remembering it. How do you do simple division in postgres and get 10/4 with decimals? This involves cast and numeric in odd ways that are not well explained in the documentation. For instance, you’d expect an example in the Mathematical F

Geographic coordinate values format conversion to DD (Decimal Degrees) format

I have data that contains geographic coordinate values in various geographic coordinate system formats such as "degrees minutes seconds" and "degrees decimal minutes". I would like to convert these geographic coordinate values into decimal degrees format. Does PostgreSQL (more specifically PostGIS)

Re: simple division

On 12/4/18 12:29 PM, Martin Mueller wrote: I have asked this question before and apologize for not remembering it. How do you do simple division in postgres and get 10/4 with decimals? This involves cast and numeric in odd ways that are not well explained in the documentation. For instance,

RE: simple division

From: Martin Mueller Sent: Tuesday, December 4, 2018 3:30 PM To: pgsql-general Subject: simple division I have asked this question before and apologize for not remembering it. How do you do simple division in postgres and get 10/4 with decimals? This involves cast and numeric in odd ways th

Re: simple division

Martin Mueller schrieb am 04.12.2018 um 21:29: I have asked this question before and apologize for not remembering it. How do you do simple division in postgres and get 10/4 with decimals? In the expression 10/4 both numbers are integers. And an integer divsion does not yield decimals (that's

Re: simple division

On Tue, Dec 4, 2018 at 1:29 PM Martin Mueller wrote: > I have asked this question before and apologize for not remembering it. How > do you do simple division in postgres and get 10/4 with decimals? > This involves cast and numeric in odd ways that are not well explained in the > documentation.

Re: simple division

On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake wrote: > I may be misunderstanding the question but: Indeed... > select cast(x/y as numeric(10,4)); Your answer is 2.0 instead of the correct 2.5 - you need to cast before the division, not after. David J.

querying both text and non-text properties

Hi, I’ve got an application where I’d like to search a collection of objects based on various properties, some text and others non-text (bools, enums, ints, etc). I’ve used full text search before, following the PG docs to set up a index on a ts_vector. And of course I’ve used normal indexes b

Re: simple division

I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. But none of the different ways of putting the double colon seemed to work. The Postgres notation of this simple procedure is very unintuitive. I ha

Re: simple division

On Tue, Dec 4, 2018 at 1:57 PM Martin Mueller wrote: > > I didn't formulate my question properly, because the query went like > "select alldefects /wordcount" > where alldefects and wordcount are integers. But none of the different ways > of putting the double colon seemed to work. IDK...the

Re: simple division

Use CAST() instead of ::. SELECT CAST(alldefects AS NUMEREIC(10,4))/wordcount; On 12/04/2018 02:57 PM, Martin Mueller wrote: I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. But none of the diffe

Re: simple division

Am 04.12.18 21:57 schrieb(en) Martin Mueller: I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. test=# create table xxx(alldefects bigint, wordcount bigint); CREATE TABLE test=# insert into xxx value

Re: simple division

It worked, and I must have done something wrong. I'm probably not the only person who would find something like the following helpful: division (integer division truncates the result)10/33 division (with decimal results) 10/3::numeric 3. division (rounded) round(10/3::nume

Re: simple division

Martin Mueller schrieb am 04.12.2018 um 21:57: I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. But none of the different ways of putting the double colon seemed to work. One way is to make one

Re: simple division

On 12/4/18 2:36 PM, Martin Mueller wrote: It worked, and I must have done something wrong. I'm probably not the only person who would find something like the following helpful: division (integer division truncates the result)10/33 The math types might take offense here, with th

Re: querying both text and non-text properties

Rob Nikander wrote: > I’ve got an application where I’d like to search a collection of objects > based on various properties, some text and others non-text (bools, enums, > ints, etc). I’ve used full text search before, following the PG docs to > set up a index on a ts_vector. And of course I’ve us

Re: Geographic coordinate values format conversion to DD (Decimal Degrees) format

> On Dec 4, 2018, at 12:36 PM, Allan Kamau > wrote: > > Does PostgreSQL (more specifically PostGIS) have functions for these types of > conversions. > > Below are examples of the geographic coordinates values I have coupled with > the resulting decimal degrees val

Re: psql is hanging

On 11/30/2018 09:53 AM, John Smith wrote: We have a long script of sql that we run, several thousand lines of sql. If I execute the script from start to finish, somewhere in the middle of it, one sql command will hang and take 2 to 3 hours. During this time, "htop" shows 100% cpu usage with a p

Moving large table between servers: logical replication or postgres_fdw

Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which approach would be faster to move the data over: a). Use pg_fdw and do "create local_table as select *

Re: simple division

On 05/12/2018 10:51, Rob Sargent wrote: On 12/4/18 2:36 PM, Martin Mueller wrote: It worked, and I must have done something wrong. I'm probably not the only person who would find something like the following helpful: division (integer division truncates the result)    10/3    3 The math ty

Re: Moving large table between servers: logical replication or postgres_fdw

> On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart wrote: > > Greetings Folks, > > I have a relatively large table (100m rows) that I want to move to a > new box with more resources. The table isn't doing anything...i.e its > not being updated or read from. Which approach would be faster to move

Re: Moving large table between servers: logical replication or postgres_fdw

I tend to believe that a backup (pg_dump) in custom format (-F c) using multiple jobs (parallel) -> restore (pg_restore) also with multiple concurrent jobs would be better. Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb Rhys A.D. Stewart < rhys.stew...@gmail.com>: > Greetings Folks, > > I have a relat

Re: Moving large table between servers: logical replication or postgres_fdw

On 12/05/2018 12:05 AM, Rob Sargent wrote: On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart wrote: Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which appr

Re: Moving large table between servers: logical replication or postgres_fdw

On 12/04/2018 09:14 PM, Rhys A.D. Stewart wrote: Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which approach would be faster to move the data over: a

Re: Moving large table between servers: logical replication or postgres_fdw

pg_backup/pg_restore will work, but parallel is pointless on a single (unpartitioned) table. On 12/05/2018 12:13 AM, Rene Romero Benavides wrote: I tend to believe that a backup (pg_dump) in custom format (-F c) using multiple jobs (parallel) -> restore (pg_restore) also with multiple concurre

Re: simple division

> On Dec 4, 2018, at 9:33 PM, Gavin Flower > wrote: > >> On 05/12/2018 10:51, Rob Sargent wrote: >> >>> On 12/4/18 2:36 PM, Martin Mueller wrote: >>> It worked, and I must have done something wrong. I'm probably not the only >>> person who would find something like the following helpful: >>

Re: Moving large table between servers: logical replication or postgres_fdw

L > On Dec 4, 2018, at 11:13 PM, Rene Romero Benavides > wrote: > > I tend to believe that a backup (pg_dump) in custom format (-F c) using > multiple jobs (parallel) -> restore (pg_restore) also with multiple > concurrent jobs would be better. > >> Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb