Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

ive app in front of the database." This other db could be that app? Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Tom Lane Sent: Thursday, September 12, 2024 16:36 To: Andreas Joseph Krogh Cc: pgsql-general@lists.postgresql.o

Re: Overlapping values (?) in multi-column partitioned tables

estamp, minvalue) to ('2024-01-31'::timestamp, maxvalue); > CREATE TABLE > xof=# create table t2 partition of t for values from > ('2024-02-01'::timestamp, minvalue) to ('2024-02-29'::timestamp, maxvalue); Brent Wood Principal Technician, Fisheries NIWA D

Re: Foreign Data Wrappers

ery useful functionality. Brent Wood On Saturday, September 7, 2024 at 12:55:37 PM GMT+12, Gus Spier wrote: I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer to success through the documentation, youtube videos, and various google resources. But I'v

Re: Postgres limitation in user management

specially when the DBA role is contracted out. Sort of along this line, we have offloaded user management to AD, so our DB user management is now carried out via in-house IT, who are not DBA's and have no access to data. Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 386052

Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

stamp, and working in that. Even though we are ½ way around the world. The local times can easily be determined & applied if desired, but by standardising on the reference time zone at the start, things have "just worked", for around 15 years now. Brent Wood Principal Technician,

Re: Backup schema without data

Looking at the result of: pg_dump --help Usage:   pg_dump [OPTION]... [DBNAME] ...  -n, --schema=PATTERN dump the specified schema(s) only...  -s, --schema-only    dump only the schema, no data...  So: pg_dump -s -n It works for me... Cheers Brent Wood On Friday

Re: Re-2: New to PostgreSQL - looking for query writing tools

ttps://dbeaver.io/ Brent Wood From: Sacha Kerres Sent: Thursday, February 02, 2023 20:51 To: Giovanni Biscontini; pgsql-general@lists.postgresql.org Subject: Re-2: New to PostgreSQL - looking for query writing tools Hi, we use sqlmanager.net tools: Query, DBCompare,

Re: Best Open Source OS for Postgresql

I would suggest any major Ubuntu based distro, I have a personal preference for Mint. I'd also suggest you set up the official Postgres repo, rather than using a distro repo, so your installs come directly from there. https://wiki.postgresql.org/wiki/Apt Brent Wood Principal Techn

Re: FDW to postGIS Database

In line below... On Monday, March 7, 2022, 05:36:34 AM GMT+13, Susan Hurst wrote: Let say that I have a database named stp. I also have another database named geo that is a PostGIS database for storing geopolitical and geospatial data. I want to set up a foreign data wrapper in stp t

Re: DELETING then INSERTING record with same PK in the same TRANSACTION

without the commit, rollback instead to check this. I don't see how you can do this within a transaction, someone else might? Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Andrew Hardy Sent: Thursday, February 10, 2022 07:

Re: Zero Downtime Upgrade

You might try ogr2ogr - it can do postgres to postgres as easily as Postgres to something else and I think can do databases, schemas or individual tables in a single command Though your original stays up, I'd recommend not writing to it while being copied... Brent Wood On Tu

Re: help implementing OGR Postgres foreign data wrapper

how ESRI supports open standards. So it may be some network settings from NIWA causing the failure. At least I can kick that problem elsewhere!!! Much appreciated. Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Ben Madin Sent

help implementing OGR Postgres foreign data wrapper

arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer?SERVICE=WFS' with the following drivers. -> ESRIC ... -> OGR_PDS -> WFS -> OAPIF ... Can anyone suggest what might be the problem? Thanks. Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529

Re: SQL queries as sets: was The tragedy of SQL

any years ago. So from me, if no-one else, a heartfelt thank you to everyone who has contributed to the SQL/Postgres space which enables me to do so much!! In appreciation, Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Steve Li

Re: Choosing an index on partitioned tables.

tgres table, but very quickly. It also adds some SQL functions to add a bit of time based query functionality. Cheers Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Tim Uckun Sent: Tuesday, September 7, 2021 15:44 To: pgsql-general Su

RE: Multi-master replication

BDS Only using the free v1.0... Sent from Yahoo Mail on Android On Sat, 14 Aug 2021 at 2:07, Laura Smith wrote: ‐‐‐ Original Message ‐‐‐ On Friday, August 13th, 2021 at 12:35 PM, Zahir Lalani wrote: > Confidential > > Thx Laura > > So here is the dilemma - everything in the clo

Re: Timestamp/hstore query?

are timer (timestamp) & values (hstore). I used "between timestamps" rather than "= date_trunc()" in the where clause as this uses the index on timestamps, so is MUCH faster. FYI, we are using Timescaledb to manage the underlying data and data structures. Appreciated!!! B

Re: Timestamp/hstore query?

Thanks for your reply, The table is essentially:create table readings (timer    timestamp primary key,    readings  hstore); the hstore comprises ( ) key/value pairs for readings taken at the time specified in the timestamp. eg:  "67" "-45.67436", "68" "176.5424" could be key/

Timestamp/hstore query?

y in present in that 1 minute interval. Essentially this provides a snapshot saying "at this time (1 min interval) the latest (current) reading for each sensor. was this value" I'm OK with a user defined pl/pgsql function if this is the best approach. Thanks Brent Wood P

Re: hstore each() function - returned order??

Ahh, light dawns!!! Thanks guys, much appreciated!!!  (as always) Brent On Saturday, March 13, 2021, 3:52:33 AM GMT+13, Tom Lane wrote: "David G. Johnston" writes: > On Fri, Mar 12, 2021 at 3:18 AM Brent Wood wrote: >> Is this what you mean? >> >> s

Re: hstore each() function - returned order??

From: David G. Johnston Sent: Friday, March 12, 2021 21:19 To: Brent Wood Cc: pgsql-general@lists.postgresql.org Subject: Re: hstore each() function - returned order?? Thanks David, Is this what you mean? select measurement_key, value from t_reading_hstore, lateral ((EACH(value

hstore each() function - returned order??

east in my test cases, but I need to know if that is just luck, or if it is a safe assumption to make. Can anyone confirm this is a robust assumption for me? Thanks Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 [https://www.niwa.co.nz/static/niwa-2018-horizontal

Re: Need help with clarification on stored procedure support in PostGreSQL database

, Python. Perhaps add some detail from here to the official docs? Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Guyren Howe Sent: Tuesday, March 9, 2021 17:31 To: Tom Lane ; raf Cc: Adrian Klaver ; Bysani, Ram ; pgsql-general

Re: Need a DB layout gui

I use Valentina Studio these days... https://www.valentina-db.com/en/get-free-valentina-studio Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 [cid:image0a50e7.PNG@cb42a178.41b47e97]<http://www.niwa.co.nz> Brent Wood Principal Technician - G

Re: Postgres for SQL Server users

I have not used SS for spatial data, but I don't have a Postgres database without Postgis installed. The OSGEO ecosystem and synergies with other FOSS GIS tools is fantastic. And it does not stop with the Postgis extension. For time series data (anything from fleet management to sensor data) Po

Re: Postgres for SQL Server users

worth a few formal benchmarks: "It's so easy to connect to and sh*t it's fast!" Might be a useful comment for your presentation :-) A few years on & it is still working fine... Cheers...   Brent Wood On Tuesday, May 7, 2019, 7:21:40 AM GMT+12, Igal Sapir wrote:

Re: Subquery to select max(date) value

?? Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 [cid:imagee69a07.PNG@f4290eba.45ae534e]<http://www.niwa.co.nz> Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery T +64-4-38

Re: Can anyone please provide me list of customers using postgreSQL

to connect to, and sh** it's fast!" In general, you don't need to know much more... Brent Wood From: "Ramamoorthi, Meenakshi" To: pgsql-general Sent: Wednesday, January 16, 2019 8:42 AM Subject: Can anyone please provide me list of customers using postgreSQL

Re: Static PostgreSQL Binaries (Linux + Windows)

Does this support extensions such as Postgis & Timescale? Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 [cid:image419b22.PNG@6e8846bb.46b21a94]<http://www.niwa.co.nz> Brent Wood Principal Technician - GIS and Spatial Data Management

Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

psql-directory.html Cheers,   Brent Wood On 12/31/18 8:19 AM, chiru r wrote: > Hi All, > > I wanted to install the PostgreSQL-11 Software with Custom > installation/binary paths. > > Currently I am using EDB one click installer software with option file > like belo

Re: Ways to deal with large amount of columns;

You need to normalise your data model... the basic dataset you describe will probably fit in one table with three columns, but that might be simplistic... All your aggregate queries are pretty basic SQL statements, which could be represented as views or made simpler to invoke by turning them int

pgloader question - postgis support

e coordinate reference system as lat/lon degrees WGS84 (EPSG:4326) to match the column specification. How can I implement that in the pgloader command to load the CSV file? Thanks Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Prin

RE: pg_dump to a remote server

host1, which is sent over the wire to host2 where it is locally uncompressed & fed into psql... Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Informa

Re: Does PostgreSQL check database integrity at startup?

ordered by primary key could work? Brent Wood From: Edson Carlos Ericksson Richter To: pgsql-general@lists.postgresql.org Sent: Friday, December 29, 2017 6:47 AM Subject: Re: Does PostgreSQL check database integrity at startup? Em 28/12/2017 10:16, Stephen Frost escreveu: > Alv