Re: Rationale for aversion to the central database?

I have done some big contracts for large financial companies, and for most of them, ANY changes to the DB structure required extensive 3rd party testing and a change control process that sometimes took weeks. But we did get a waiver for the use of DB 'code' like stored procedures and views, which

Time-based table design / index selection

I come form a DB background using Oracle (which is quite outdated) and more recently SQL Server, 2012 and 2016, so would like some ideas from the group. I have 2 tables which represent a time-based relationship between various object types, most of which also have a geographical component. There

Re: Function for Exception Logging

I'd be interested if there is an answer to this. The big issue with writing to a table is the interaction with rollbacks and commits. Even more difficult in PG functions as they have no commit / rollback capability. I haven't played with stored procedures in in PG11 yet. In Oracle, I wrote

Re: editable spreadsheet style interface

For a quick and dirty data editor, LibreOffice Base seems to work fine. On Tue, 30 Oct 2018 at 23:05, Tim Clarke wrote: > On 30/10/2018 20:32, Martin Mueller wrote: > > > > I have used Aqua Data Studio for several years. Jetbrains recently > > released a similar product. Academic licensing is

Re: Question about index on different tablespace and rebuild it

Did you check the documentation for alter index? https://www.postgresql.org/docs/10/sql-alterindex.html You could create a script file (plenty of examples on the internet on generating these) and then run through psql or whatever. Also, if you just have a few indexes to move, you could use (for

Re: the installation of pgadmin4 makes me weep in frustration

I know this has already been answered, but for those who are also python users, there is another and probably better option: If you already have a python 3.6+ installation, you can download the pgadmin4 wheel (python installable) from https://www.pgadmin.org/download/ Then do a pip install on it

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free and readily available? It also has a graphical table-from-file loader as well. On Fri, 7 Dec 2018 at 23:35, Adrian Klaver wrote: > On 12/7/18 9:04 AM, s4...@yahoo.co.jp wrote: > > I didn't specify any schema, so it was

Re: querying both text and non-text properties

I would suggest doing testing out btree_gin with a non-insignificant amount of data before going ahead with it. I did a test case last month, and the size of the generated index was _much_ bigger than the base table. The case involved a compound key if 1 int column and 1 timestamp range column.

Re: Pulling data from Postgres DB table for every 5 seconds.

I am not familiar with Aurora, but.. What something like https://github.com/subzerocloud/pg-amqp-bridge? Set up a message queue in Postgres, which calls into AMPQ (RabbitMQ) to send a message for consumption by one or more clients. This provides a function that can be called from a trigger to

Re: Benchmark of using JSON to transport query results in node.js

I'm fairly new to Postgres, but one question is how node.js implements the native driver when fetching the data: fetchall, fetchmany or fetch.single? Also which native driver is it using? Does the native driver do a round trip for each record fetched, or can it batch them into multiples? For

Re: the installation of pgadmin4 makes me weep in frustration

Just over a year ago, I started to look for free / open source clients for Postgres, and went through most of that list. Unless you are willing to pay for a commercial license, most of them are not close to PG or have some limitations that made them unusable for me. Several 'free' tools were

Data entry / data editing tools (more end-user focus).

Looking for a good tool that I can give to users to enter data (for example, products, categories, brands, price tables and so on). Preferably it should also allow images to be copied into a bytea field but I know I can't have everything. Been battling with a few open source 'headless' content

Re: Key encryption and relational integrity

Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers. The primary key of the user is _should_ be system generated, and this is meaningless from a user identity standpoint. If you encrypt user name and other significant personal data on

Re: When to store data that could be derived

Not the answer you are looking for, but... I'd suggest trying to create a non-trivial set of dummy data to test your assumptions before deciding on a route. It's saved my (professional) life a few times over the years when dealing with untested designs and new (to us) technology. Some years ago

Re: Tools to migrate data from Json files to PostgreSQL DB.

You may want to assess how you want to store and access the data in Postgres before deciding on an import strategy. I have a system with a mix of relational and JSON data. The data was originally sourced in flat file format. I wrote a few Python programs to take the data, then format to JSON,

Re: Forks of pgadmin3?

Or just persevere with pgadmin4 for a few months? Pretty common for people to hate any major changes to a tool that they are very comfortable with. This year I've invested the time to learn a few new toolsets (not on Postgresql necessarily) and found it to be well worth while. At least

Re: Forks of pgadmin3?

but it’s not handy with many columns. > > For that reason we currently stay with pgadminIII (and this is for us also > one of several reasons to delay any move from 9.6 to a more recent version). > > > > Klaus > > > > *Von:* Tony Shelver > *Gesendet:* Freitag, 22.

Re: Help : Update and insert record based on several value in the parameter

I don't know what you are using as a front end to call this update, but I have been finding json/jsonb parameters passed into a function / procedure and then using postgres json functions to process the data as a good solution. If your data fits postgres array datatype, as others have mentioned,

Re: Data entry / data editing tools (more end-user focus).

lia.io/ (JS) or CUBA https://www.cuba-platform.com/ > (Java) > > :Stefan > > Am Do., 28. März 2019 um 15:39 Uhr schrieb Adrian Klaver > : > > > > On 3/27/19 11:49 PM, Tony Shelver wrote: > > > > Please reply to list also, more eyes on the the problem. > >

Re: bigint out of range

Adding to what David said, I don't see much point of having a bigint status. Usually status attributes are a fixed set of values that can be checked programmatically. >

Re: Postgres for SQL Server users

I have to agree on the geospatial (GIS) features. I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS. The PostGIS ecosystem of open source / 3rd party

Re: Postgres for SQL Server users

list of extensions for datatypes, languages, specialized functions and so on. Things like PostGIS (already mentioned), PipelineDB and so on, plus Postgres-based things like Timescale, Citusdata and so on. On Tue, 7 May 2019 at 07:35, Tony Shelver wrote: > I have to agree on the geospat

Re: DRY up GUI wiki pages

> I created the first one because the second one was full of old, stale, > useless things. I believe that everything valid on the second one was added > to the first one at the time. > > Also look at https://wiki.postgresql.org/wiki/Design_Tools and >

Re: How to set up PostGIS to support ArcGIS, MapInfo and QGIS desktop users?

This is probably the wrong place to ask. Have a look at the POSTGis support options page. Stack Exchange has a lot of info. Also go to the particular products that you are interested in, they should have their requirements listed. You will likely also want to set

Re: SQL equivalint of #incude directive ?

Similar to what Francisco said. Not exactly sure what your use case is though.. In the past I have usually used a DB modeling / design front end tool to design my database, and then maintain and generate most of the build scripts. Datanamic Dezign used to be my go-to for SQL Server when i still

Re: pgmodeler ?

<< On Sun, 1 Sep 2019 at 13:45, Thiemo Kellner wrote: > Quoting Olivier Gautherot : > > This is the specific error message: > >> >> Could not execute the SQL command. >> Message returned: ERROR: column pr.proisagg does not exist >> LINE 1: ...namespace AS ns ON

Re: PGAdmin4.11.1 on Ubuntu 18.04

> >> What repo are you getting the packages from? > > Repo looks like PostgreSQL apt repository https://apt.postgresql.org. .../bionic-pgdg.main > -- > Adrian Klaver > adrian.kla...@aklaver.com >

Re: Does pgadmin4 work with postgresql 8.4?

8.4's final release was 5 years ago, so unlikely anyone would know the answer to your question. The only way to really know is to try it. I just had an issue with the current version (4.11) against PG11.4, where the version of psycopg2 was out of date by a release (2.7x instead of 2.8). Most

Re: Recomended front ends?

On Wed, 7 Aug 2019 at 20:57, stan wrote: > I am in the process of defining an application for a very small company > that uses Postgresql for the backend DB. This DB will eventually run on a > hosted machine. As you imagine all of the employees have Windows machines > for their normal work asks.

PGAdmin4.11.1 on Ubuntu 18.04

I am getting an 'able oid' when querying any tables using the Query tool. When using the view / edit data option, everything works fine. I came across this <<< The problem is due to python3-psycopg2. The latest pgadmin4 version requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt

Re: PGAdmin4.11.1 on Ubuntu 18.04

AM, Tony Shelver wrote: > > I am getting an 'able oid' when querying any tables using the Query tool. > > When using the view / edit data option, everything works fine. > > Should have asked in previous post: > > What Postgres version(s)? > > > > > I came acro

Re: PGAdmin4.11.1 on Ubuntu 18.04

The article at stackoverflow is here <https://stackoverflow.com/questions/57226520/select-does-not-return-values-postgres-11-4> On Thu, 1 Aug 2019 at 17:28, Tony Shelver wrote: > Thanks for the replies: the version is PG11.4. > > As for needing to upgrade: I just want to

Re:

Research Postgraphile, it provides an easy-to-use GraphQL interface to Postgres. Postgraphile.org On Wed, 20 Nov 2019 at 17:00, Soukaina Lahchiouach wrote: > Hello , > does postgresql with react native allow us to create chat applications? >

Re: Return Table in StoredProceure/Function

Well then SQL Server breaks that rule big time :) Most people coming from a SQL Server background expect procedures to return a result set that can be queried, and in-out or out parameters to return variables for further information. On Wed, 20 Nov 2019 at 17:20, Thomas Kellerer wrote: >

Re: What's your experience with using Postgres in IoT-contexts?

On Mon, 5 Oct 2020 at 16:34, Thorsten Schöning wrote: > Guten Tag Tony Shelver, > am Montag, 5. Oktober 2020 um 15:44 schrieben Sie: > > > Not sure about PG in that environment. Have you thought about something > > like H2 java database? https://www.h2database.com/html/ma

Re: What's your experience with using Postgres in IoT-contexts?

requirements, everything is needed at the cloud / server level for analysis. On Wed, 7 Oct 2020 at 08:28, Thorsten Schöning wrote: > Guten Tag Tony Shelver, > am Dienstag, 6. Oktober 2020 um 16:33 schrieben Sie: > > > Each vehicle reports position to the database at least every 30 seconds

Re: What's your experience with using Postgres in IoT-contexts?

Not sure about PG in that environment. Have you thought about something like H2 java database? https://www.h2database.com/html/main.html That is included as the standarDB in a vehicle tracking system we use, although we have re[placed with PG. On Mon, 5 Oct 2020 at 11:20, Thorsten Schöning

Fwd: "Go" (lang) standard driver

-- Forwarded message - From: Tony Shelver Date: Tue, 18 Aug 2020 at 09:33 Subject: Re: "Go" (lang) standard driver To: Edson Richter A quick Google search found https://github.com/lib/pq. Has 6.1K stars so I would guess fairly well used. On Tue, 18 Aug 2020 at 05

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

> > > On 9/23/20 11:51 AM, tutilu...@tutanota.com wrote: > > Huh? A schema is just a name space, why does it matter how the > extension chooses to define it? I mean you could have number of > permutations of postgis. > > I'm saying that PostGIS has a bug due to incorrectly constructed

Re: PostgreSQL on Windows' state

A quick Google search came up with this: https://www.npgsql.org. No experience with it however. We moved to Python just before we moved to Postgresql. The key motivator for C# at the time is now more of a React / JS proponent Everything in our stack apart from 1 proprietary Java / Oracle

Re: Querying PostgreSQL / PostGIS Databases in Python

We are using python on top of Postgresql / PostGIS, for a vehicle tracking system. THis is quite data intensive, and we have some 'interesting' GIS queries where we see where a vehicle or fleet has stopped within specific areas, where it has traveled, any incidents along the way and much more.

Re: Querying PostgreSQL / PostGIS Databases in Python

system written in java on top of Postgres, where the queries are done via the java ORM system and the GIS processing is done in Java. This is even more true where the python client is situated on physically separate servers to the database. On Tue, 4 Aug 2020 at 14:09, Tony Shelver wrote: >

Re: simple reporting tools for postgres in aws

LibreOffice Base? I have used LibreOffice Base to spin up a quick data entry front end to Postgresql. Has some basic reporting functionality as well, and can integrate to other LIber\Office products. Poor man's version of MS Access. As for other tools, https://querytreeapp.com seems to have

Re: New "function tables" in V13 documentation

On Mon, 9 Nov 2020 at 02:54, Adrian Klaver wrote: > On 11/8/20 1:57 PM, Thomas Kellerer wrote: > > In case someone is interested: there is a little discussion going on on > > Reddit whether the new format of presenting functions in V13 is a step > > backwards: > > > > > > >

JSONB order?

ot;No", "Nausea, vomiting, diarrhea, abdominal pain": "No", "Runny nose/stuffy nose or nasal congestion": "No", "Difficulty breathing or shortness of breath": "No", "Not feeling well, extreme tiredness, sore muscles": "No", "Have you travelled outside of Canada in the past 14 days?": "No", "Have you had close contact with a confirmed or probable case of COVID-19?": "No" } If the order had remained the same, it's child's play to pull the data out and present it in a report, even if the data elements change. But... seen above, the order gets mixed up. Any ideas? Thanks Tony Shelver

Fwd: JSONB order?

-- Forwarded message - From: Tony Shelver Date: Thu, 5 Nov 2020 at 17:45 Subject: Re: JSONB order? To: Christophe Pettus Thanks Christophe, that's what I thought. Just seemed weird that they were 'disordered' in exactly the same way every time. FYI, as of Python 3.7, dicts

Re: JSONB order?

On Thu, 5 Nov 2020 at 18:27, Rob Sargent wrote: > > > On Nov 5, 2020, at 8:45 AM, Tony Shelver wrote: > > > > -- Forwarded message ----- > From: Tony Shelver > Date: Thu, 5 Nov 2020 at 17:45 > Subject: Re: JSONB order? > To: Christophe Pettus >

Fwd: Modelling a web CMS in Postgres ... a little advice needed

From: Tony Shelver Date: Thu, 11 Aug 2022 at 11:47 Subject: Re: Modelling a web CMS in Postgres ... a little advice needed To: Laura Smith On Thu, 11 Aug 2022 at 09:35, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > I'm looking at using pgsql as a backe

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

On Thu, 11 Aug 2022 at 12:00, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi Tony > > The reason I'm looking to do it from scratch is that its a case of "once > bitten, twice shy". > > This CMS will be replacing a Joomla based CMS. > > I can't quite say I'm enamoured by the option

Re: Is there a guide to use PostgresSQL as alternative to MariaDB/MySQL in OpenStack?

On Sat, 5 Nov 2022 at 01:15, Larry Sevilla wrote: > Hi, > > I'm studying OpenStack Zed using Ubuntu 22.04 with the following guides: > > https://docs.openstack.org/install-guide/ > > And I have successfully installed OpenStack services and tested Horizon. > > > But the guide uses MariaDB/mysql.

Re: Best Open Source OS for Postgresql

Copied to the list On Wed, 1 Feb 2023 at 08:18, Tony Shelver wrote: > > > On Wed, 1 Feb 2023 at 08:04, Tony Shelver wrote: > >> >> On Tue, 31 Jan 2023 at 15:10, Marc Millas wrote: >> >>> Sorry for inappropriate "reply". >>> >>

Re: Large scale reliable software system

involves processing through large amounts of data for reports / queries. Shipping all that back through the ORM / db interface (ODBC / JDBC / psycopg2 / whatever for resolution / filtering on the front end application where SQL / procedures / views could do that in the DB and just ship back the required data seems counterproductive. Tony Shelver >