SV: table returning function for each row in other resultset

2021-02-03 Thread Niels Jespersen
Fra: David G. Johnston Sendt: 3. februar 2021 16:08 >On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen wrote: >Hello all > >I have som data in a resultset. E.g: > >id date_begin date_end amount >1 2021-01-04 2021-02-06 100 >2 2021-03-17 2021-05-11 234 > >I have a table returning function

pg_trgm for address search

2021-02-03 Thread Sumit Raja
Hello, I am trying to get a functioning postgres address search capability for Australian addresses using tsearch or pg_trgm. pg_trgm is actually better suited in this case as it allows progressive entry e.g. ' 1, 20 Kle' gives a good set of results across Kelm Avenue, Kleins Av etc. Good

SV: SV: Npgsql and the Connection Service File

2021-02-03 Thread Niels Jespersen
Fra: Dave Cramer >>> It would be nice if Npgsql (and jdbc and others) emulated the libpq >>> behaviour. >>> Because in my mind, abstracting hostname, portnumber and databasename >>> away is a really useful feature. >>> >>> How do others manage this? >> >>Either they don't or they write their

Re: Database upgrade Pg11 to Pg12 issues

2021-02-03 Thread Bruce Momjian
On Wed, Feb 3, 2021 at 10:07:03PM -0500, Craig McIlwee wrote: > > (replying to the entire list instead of Bruce only this time...) > > > > This doesn't make sense to me.  Since we hard-linked, why would 12 be so > much smaller?  If it was symlinks, I could imaging that, but it doesn't

Re: Database upgrade Pg11 to Pg12 issues

2021-02-03 Thread Craig McIlwee
(replying to the entire list instead of Bruce only this time...) > This doesn't make sense to me. Since we hard-linked, why would 12 be so > much smaller? If it was symlinks, I could imaging that, but it doesn't > use symlinks, just hard links, so it should be similar. Please look at > the

Re: Database upgrade Pg11 to Pg12 issues

2021-02-03 Thread Bruce Momjian
On Wed, Feb 3, 2021 at 11:25:11PM +, Simon Windsor wrote: > Hi > > I have upgraded many Pg databases from 9 to 10 to 11 using the Ubuntu > pg_upgradecluster command (wrapper to pg_update) without issues, however today > I upgraded a DB from 11 to 12. > > Using the syntax > >

Database upgrade Pg11 to Pg12 issues

2021-02-03 Thread Simon Windsor
Hi I have upgraded many Pg databases from 9 to 10 to 11 using the Ubuntu pg_upgradecluster command (wrapper to pg_update) without issues, however today I upgraded a DB from 11 to 12. Using the syntax */pg_upgradecluser -k -m upgrade 11 main/* using the latest 12.5 binaries all appeared to

Re: SV: Npgsql and the Connection Service File

2021-02-03 Thread Dave Cramer
On Mon, 1 Feb 2021 at 04:51, Niels Jespersen wrote: > >On Sat, 2021-01-30 at 15:56 +, Niels Jespersen wrote: > >> It would be nice if Npgsql (and jdbc and others) emulated the libpq > behaviour. > >> Because in my mind, abstracting hostname, portnumber and databasename > >> away is a really

Tool for migrating Gupta SQLBase to PostgreSQL

2021-02-03 Thread Marco Lechner
Hi, we do have an old application that uses Gupta SQLBase as storage backend. While developing a new OpenSource-based application to substitute the old one we have to migrate the data in the Gupta SQLBase DB into PostgreSQL (because PostgreSQL/PostGIS has become our Standard DB-storage backend

Re: table returning function for each row in other resultset

2021-02-03 Thread David G. Johnston
On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen wrote: > Hello all > > I have som data in a resultset. E.g: > > id date_begin date_end amount > 1 2021-01-04 2021-02-06 100 > 2 2021-03-17 2021-05-11 234 > > I have a table returning function that can take one row and split it into >

table returning function for each row in other resultset

2021-02-03 Thread Niels Jespersen
Hello all I have som data in a resultset. E.g: id date_begin date_end amount 1 2021-01-04 2021-02-06 100 2 2021-03-17 2021-05-11 234 I have a table returning function that can take one row and split it into constituent monthpieces and distribute amount proportionally. select * from

Re: UPDATE Syntax - solved

2021-02-03 Thread Ulrich Goebel
Solved. Use update tbl set (col1, col2, ...) = ROW(val1, val2, ...) where id=xx Thank's to Laurenz Albe in the german mailinglist. Am 03.02.21 um 11:31 schrieb Ulrich Goebel: Hi, in a Python Script I build an UPDATE using the syntax: update tbl set (col1, col2, ...) = (val1, val2, ...)

UPDATE Syntax

2021-02-03 Thread Ulrich Goebel
Hi, in a Python Script I build an UPDATE using the syntax: update tbl set (col1, col2, ...) = (val1, val2, ...) where id = xx That works as long in the two lists are more then one column and values. If I have just one column and value, I get an error message:

Re: count(*) vs count(id)

2021-02-03 Thread Laurenz Albe
On Wed, 2021-02-03 at 10:54 +0100, Karsten Hilbert wrote: > > I just ran a few practical tests on large (~14mil rows) tables that have > > multiple indexes. > > SELECT COUNT(id) forces PostgreSQL to use the primary key index. > > SELECT COUNT(*) allows PostgreSQL to chose an index to use and it

Re: count(*) vs count(id)

2021-02-03 Thread Karsten Hilbert
Am Wed, Feb 03, 2021 at 01:43:14AM -0500 schrieb Cherio: > I just ran a few practical tests on large (~14mil rows) tables that have > multiple indexes. > > SELECT COUNT(id) forces PostgreSQL to use the primary key index. > SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to

Re: Foreign table performance issue / PostgreSQK vs. ORACLE

2021-02-03 Thread Sebastian Dressler
Hi Ingolf, On 2. Feb 2021, at 13:05, Markhof, Ingolf mailto:ingolf.mark...@de.verizon.com>> wrote: Hi! My PostgreSQL version is 11.8. The query I am running is referring to a number of foreign tables. The first one (table1) has to IDs, let's say ID_A and ID_B. While ID_A is unique, ID_B is