Re: Ghost data from failed FDW transactions?

> On Aug 28, 2024, at 10:18 AM, Jacob Biesinger > wrote: > > But to go deeper, we use the javascript knex adapter and some > application-level transaction management that automatically retries a > transaction N times when it encounters serialization errors. On this > particular endpoint, th

Re: Manual query vs trigger during data load

> On Sep 13, 2024, at 10:57 AM, Adrian Klaver wrote: > > On 9/13/24 07:50, Adrian Klaver wrote: >>> On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: >>> Hello, I find it unlikely that the trigger will work properly, since the >>> reserved fields of the OLD subset have no value in

Re: unexpected character used as group separator by to_char

On 3/9/21 2:22 PM, Tom Lane wrote: Vincent Veyron writes: Having a database with the proper lc_numeric setting for each country, and using to_char/to_number to manipulate numbers is much more appealing than writing my own parser in my front end. But this weird space is getting in my way. Th

Re: unexpected character used as group separator by to_char

On 3/9/21 4:03 PM, Vincent Veyron wrote: No dice. 'G' formatting looks like a whitespace, but is different (it appears to be slightly narrower when displayed in html, too) : An "n-space", no?

Re: Compare with default value?

> On Mar 13, 2021, at 1:51 PM, David G. Johnston > wrote: > >  >> On Sat, Mar 13, 2021 at 1:41 PM Ulrich Goebel wrote: >> But it could help to get a reference to the default value. > > There is no such thing as a "default value". There is a "default expression" > though. It should be ava

Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

> On Mar 25, 2021, at 10:32 PM, Jagmohan Kaintura > wrote: > >  > Hi All, > > PostgreSQL version : 12.5 > > When we are compiling View definition into database , the where clause on the > Character Columns is getting internally typecasted to text. > Example : Source Code being Applied : >

Re: accessing cross-schema materialized views

On 3/31/21 4:31 PM, Tim Clarke wrote: We have:     create materialized view schema1.matview.     grant select on table schema1.matview to mygroup     create view schema2.usingview as select ... from schema1.matview     grant select on table schema2.using to mygroup Is that schema2.using

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time and makes code way cleaner.   It's very

Re: Primary keys and composite unique keys(basic question)

On 4/1/21 8:28 PM, Merlin Moncure wrote: This is one of the great debates in computer science and it is not settled. There are various tradeoffs around using a composite key derived from the data (aka natural key) vs generated identifiers. It's a complex topic with many facets: performance, org

Re: Primary keys and composite unique keys(basic question)

> > It's a small thing, but UUIDs are absolutely not memorizable by > humans; they have zero semantic value. Sequential numeric identifiers > are generally easier to transpose and the value gives some clues to > its age (of course, in security contexts this can be a downside). > I take the abo

Re: Primary keys and composite unique keys(basic question)

> On Apr 7, 2021, at 10:17 AM, Ron wrote: > >  On 4/5/21 9:37 PM, Rob Sargent wrote: >>> It's a small thing, but UUIDs are absolutely not memorizable by >>> humans; they have zero semantic value. Sequential numeric identifiers >>> are generally eas

Re: Primary keys and composite unique keys(basic question)

On 4/7/21 11:59 AM, Ron wrote: On 4/7/21 11:35 AM, Rob Sargent wrote: On Apr 7, 2021, at 10:17 AM, Ron wrote:  On 4/5/21 9:37 PM, Rob Sargent wrote: It's a small thing, but UUIDs are absolutely not memorizable by humans; they have zero semantic value.  Sequential numeric identifier

Re: How can I insert the image as a blob in the table

> > On Apr 12, 2021, at 7:29 PM, Igor Korot wrote: > > Hi, guys, > > On Sun, Apr 11, 2021 at 7:08 PM Igor Korot > wrote: >> >> Hi, David, >> >> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston >> wrote: >>> >>> On Sunday, April 11, 2021, Igor Korot wrote:

Re: How can I insert the image as a blob in the table

> On Apr 12, 2021, at 8:31 PM, Abdul Qoyyuum wrote: > > Have you tried > > insert into table(picture) values(lo_import('D:\image.jpg')); > ? > No, I’m doing some non-standard compression on the way down.

Re: Sequence gaps after restart

On 5/11/21 9:54 AM, Christopher Sumner wrote: Hello. I'm using PostgreSQL v. 10.14 via AWS Aurora Serverless. Our users recently noticed gaps in the primary ids for many of our tables.  As I understand it, this is due to PostgreSQL effectively caching 32 additional sequence values (hardcoded

How different is AWS-RDS postgres?

I have what purports to be Postgres 12 ( PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit ) running on RDS.  I testing a new release of my service with an admittedly large data set (which may be my swamping AWS bandwidth).  But my app logs

Re: How different is AWS-RDS postgres?

My question is: Should I be surprised that the initial bulk loaded table is nowhere to be seen, given the thumbs up from the logs?  Is this frailty inherent in AWS/RDS infrastructure? Since this is an academic exorcise, I have minimal AWS support, which has yet to chime in on this matter. M

Re: How different is AWS-RDS postgres?

> On May 26, 2021, at 4:37 PM, Ian Harding wrote: > >  > There is an option to send the logs to cloudwatch which makes it less awful > to look at them. I have that but precious little of interest there. Lots of autovac, a smattering of hints to increase wal size!? I have yet to spot anythi

Re: How different is AWS-RDS postgres?

> We used Aurora (AWS hosted Postgres) and I agree that Cloudwatch search is > pretty limited. I wrote a Python script to download cloudwatch logs to my > laptop where I can use proper tools like grep to search them. It’s attached > to this email. It’s hacky but not too terrible. I hope you fi

Re: How different is AWS-RDS postgres?

On 5/27/21 7:45 AM, Philip Semanchuk wrote: On May 26, 2021, at 10:04 PM, Rob Sargent wrote: On May 26, 2021, at 4:37 PM, Ian Harding wrote:  There is an option to send the logs to cloudwatch which makes it less awful to look at them. I have that but precious little of interest there

Re: How different is AWS-RDS postgres?

On 5/27/21 3:08 PM, Sam Gendler wrote: The same JDBC connection that is resulting in lost data?  Sounds to me like you aren't connecting to the DB you think you are connecting to. I almost wish that were true. However, looking at AWS "Performance Insights" is see the sql statements generate

Re: How different is AWS-RDS postgres?

On 5/27/21 4:10 PM, Ron wrote: On 5/27/21 4:58 PM, Rob Sargent wrote: On 5/27/21 3:08 PM, Sam Gendler wrote: The same JDBC connection that is resulting in lost data?  Sounds to me like you aren't connecting to the DB you think you are connecting to. I almost wish that were true. Ho

Re: How different is AWS-RDS postgres?

On 5/27/21 4:25 PM, Sam Gendler wrote: That sure looks like something is causing your connection to have a transaction rollback.  I haven't worked in Java in far too long, but it seems like your connection pool is under the impression your connection was abandoned so it reclaims it and rollback

Re: How different is AWS-RDS postgres?

Found it.  Though there are indeed separate transactions for the bulk copy  v. target update, I had missed that there is an over-arching transaction in play and of course when things go south, /all/ things go south. I get away with this, for now by bumping the tomcat idle time to an hour, whi

Re: Internationalisation (i18n) with Postgres as backend

On 6/1/21 2:09 PM, Laura Smith wrote: Hi, I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS. So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgr

Re: Internationalisation (i18n) with Postgres as backend

On 6/1/21 5:00 PM, Laura Smith wrote: What is your notion of "object".  I first assumed it was akin to "document" but then pages have objects. I think my terminology is a bit off. A document/page has object(s) on it. Or, perhaps better expressed, think of document/page as the template and obj

Re: Database issues when adding GUI

On 6/7/21 9:02 AM, Rich Shepard wrote: This is an unusual message and I hope someone(s) here can offer insights into the cause of the problem I've been wrestling with for the past couple of weeks. Context: For the past decade I've run my business tracking database from the psql shell (current

Re: Database issues when adding GUI

On 6/7/21 9:02 AM, Rich Shepard wrote: This is an unusual message and I hope someone(s) here can offer insights into the cause of the problem I've been wrestling with for the past couple of weeks. Context: For the past decade I've run my business tracking database from the psql shell (current

Re: Database issues when adding GUI

can you show a psql session which accesses these tables, including connection string? Not sure about the 'connection string' part but: bustrac=# select * from activitytypes; psql --host machine --user role --dbname something For your app it might be a config file with corresponding entries,

Re: Even more OT: Ditto machines [was: bottom / top posting]

Indeed I "ditto" remember them! And the smell was the prize for running an errand for the teacher to pick up copies from the Mimeograph room. -Tom Hand-cranked Gestetner, anyone?  Blotchy ink, indelible mess but made one feel a brother of Gutenberg.

Re: When to REINDEX a serial key?

> > There is no such thing as a lopsided B-tree, because a B-tree is by > definition self-balancing. Perhaps that answers your original question. > You do incur the cost of rebalancing often and the cost/frequency/extent is related to fill factor. > >

Re: Undocumented array_val[generate_series(...)] functionality?

> On Jul 11, 2021, at 5:52 PM, David G. Johnston > wrote: > >  > Hey, > > A post over in Reddit had an expression form I've never seen before: > > select (array[1,2,3,4]::integer[])[generate_series(1, 3)]; > === > 1 > 2 > 3 > > Looking at subscripting in the SQL syntax this example doesn't

Re: Use of '&' as table prefix in query

On 7/12/21 11:25 AM, Rich Shepard wrote: Long ago I wrote a query which was greatly improved (i.e., it actually worked as intended) by help here): /* This query selects all activity information for a named person */ SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,   

Re: Use of '&' as table prefix in query

On 7/12/21 11:47 AM, Rich Shepard wrote: On Mon, 12 Jul 2021, Rob Sargent wrote: These look like value substitutions, usually done on the client at it sends the sql. How is this sql getting to the server (presumably after substitution). Rob, I was running queries from the psql back then

Re: Formating psql query output

> On Jul 19, 2021, at 11:49 AM, Adrian Klaver wrote: > > On 7/19/21 10:33 AM, Rich Shepard wrote: >> Until I finish building the python/tkinter/psycopg2 front end to my business >> tracking tool I continue to work using the psql shell.' >> I have a working .sql script that reports my contacts

Re: Formating psql query output

> On Jul 19, 2021, at 12:53 PM, Rich Shepard wrote: > > On Mon, 19 Jul 2021, Basques, Bob (CI-StPaul) wrote: > >> -F separator >> --field-separator=separator >> Use separator as the field separator for unaligned output. This is >> equivalent to \pset fieldsep or \f. > > Bobb, > > I should

Re: Formating psql query output

> On Jul 19, 2021, at 1:07 PM, Rich Shepard wrote: > > On Mon, 19 Jul 2021, Rob Sargent wrote: > >> Can we see on line of the csv output? The field with commas should be in >> quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” >> might

Re: More records after sort

> Query: > INSERT INTO invl_aml_mapping_temp(invl_id, aml_id, cond) >SELECT il.id, ml.id, 48 >FROM account_invoice_line il >JOIN account_invoice i ON i.id = il.invoice_id >JOIN account_move m ON m.id = i.move_id >

Re: PostgreSQL reference coffee mug

> On Jul 26, 2021, at 1:06 AM, Pavel Stehule wrote: > >  > >> Thanks in advance for any hints. > > I have this in czech language - maybe google translator > https://translate.google.com/ helps > > https://postgres.cz/files/tahak_postgresql-13.pdf > > Regards > > Pavel >> >> I could def

Re: Recursive Parent-Child Function Bottom Up

On 7/26/21 9:19 AM, Avi Weinberg wrote: Hi, I would like to populate the children_ids column with all the ids of the children recursively (+ grandchildren etc.) If I do it top-bottom I will end up doing extra work since there is no need to go all levels down if I can just compute my IMMEDIA

Re: Recursive Parent-Child Function Bottom Up

On 7/26/21 9:19 AM, Avi Weinberg wrote: Hi, I would like to populate the children_ids column with all the ids of the children recursively (+ grandchildren etc.) If I do it top-bottom I will end up doing extra work since there is no need to go all levels down if I can just compute my IMMEDIA

Re: Recursive Parent-Child Function Bottom Up

On 7/26/21 9:55 AM, Alban Hertroys wrote: On 26 Jul 2021, at 17:52, Alban Hertroys wrote: Something like this: with recursive foo (id, parent, children_ids) as ( select id, parent, null::text from tree t where not exists ( select 1 from tree c where c.

Re: Recursive Parent-Child Function Bottom Up

On 7/26/21 9:55 AM, Alban Hertroys wrote: On 26 Jul 2021, at 17:52, Alban Hertroys wrote: Something like this: with recursive foo (id, parent, children_ids) as ( select id, parent, null::text from tree t where not exists ( select 1 from tree c where c.

Re: PostgreSQL reference coffee mug

I suggest a series: one per chapter! > On Aug 6, 2021, at 12:09 PM, Matthias Apitz wrote: > > > The prototype is ready. > > matthias > > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > Tear it down!

Re: Selecting table row with latest date

> On Aug 19, 2021, at 10:06 AM, Rich Shepard wrote: > > On Thu, 19 Aug 2021, Adrian Klaver wrote: > >> Alright now I am confused. You keep referring to contact_date, yet the >> query is referring to next_contact. Are they the same thing, different >> things or other? > > Adrian, > > The tabl

Re: Selecting table row with latest date

> On Aug 19, 2021, at 10:31 AM, Rich Shepard wrote: > > On Thu, 19 Aug 2021, Rob Sargent wrote: > >> Did you try David J’s suggestion? or maybe > > Rob, > > Yes. > >> select person_nbr, max(next_contact) group by person_nbr where >> next_conta

Re: Selecting table row with latest date

> On Aug 19, 2021, at 10:59 AM, Rich Shepard wrote: > > On Thu, 19 Aug 2021, Rob Sargent wrote: > >> Yeah, but my quibble is the the table you described up-thread. Your >> contact table contains next_contact? I think that column should be >> normalized out. >

Re: Selecting table row with latest date

> On Aug 19, 2021, at 11:20 AM, Rich Shepard wrote: > > On Thu, 19 Aug 2021, Rob Sargent wrote: > >> sorry, wasn’t clear: person_nbr, next_contact >> On the premise that there can only be one next date. > > Rob, > > I wasn't sufficiently cle

Re: Idempotent DDL Updates

> On Aug 27, 2021, at 1:32 PM, Adrian Klaver wrote: > > On 8/27/21 11:19 AM, Miles Elam wrote: >> What is the general consensus within the community on idempotent DDL >> scripts, ie. consistent usage of IF EXISTS/IF NOT EXISTS/OR REPLACE for SQL >> init files that get checked into source co

Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent

> On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès wrote: > > Hello, > > I have a strange issue in a production database on a customer and need to > help to know how to solve the problem. > > Any select on the table other than pg_catalog table throws the error FATAL: > catalog is missing 1 attr

Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent

> On Sep 1, 2021, at 8:14 AM, FOUTE K. Jaurès wrote: > > No, I am using PostgreSQL 12 (Server and Client) > > Le mer. 1 sept. 2021 à 16:12, Rob Sargent <mailto:robjsarg...@gmail.com>> a écrit : > > >> On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès >

Re: The tragedy of SQL

On 9/13/21 11:51 PM, Guyren Howe wrote: They are making a decent decision. SQL is a *fucking terrible* language, which I don’t blame them for not wanting to learn. The whole industry, programming languages, infrastructure, everything would have developed differently if relations were a natural

Re: The tragedy of SQL

> ORMs a function of poor development culture and vendor advocacy, not > the fault of SQL. If developers don't understand or are unwilling to > use joins in language A, they won't in language B either. > > merlin Back in the day, within IBM there were two separate relational databases.  Sy

Re: The tragedy of SQL

On 9/14/21 10:10 AM, Michael Nolan wrote: I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to list.  I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory space, so you had to wri

Re: The tragedy of SQL

On 9/14/21 1:53 PM, Mladen Gogala wrote: On 9/14/21 02:18, Rob Sargent wrote: All languages are fucking terrible. I like English. It's not very complex and it allows me to express myself very well. You should see my native tongue, Croatian language, from the group of Slavic lang

Re: Alter and move corresponding: was The tragedy of SQL

As far as alter, in 1981, before I became a programmer, I asked my Cobol Programmer friend if there was anything you could put in a program that would get you fired. He said yes, the alter statement :-). In my 3 semesters of Cobol, I never once used the Alter statement. [...] I was very proud

Re: The tragedy of SQL

Missing my original point here. The set theory is the _point_. SQL is a gargantuan distraction from using it efficiently. Imagine if COBOL was the only widely-available programming language with functions. You might use it, because functions are really great abstraction for programming. That

Re: Timestamp with vs without time zone.

On 9/22/21 9:56 AM, Michael Lewis wrote: On Wed, Sep 22, 2021 at 12:44 AM cen > wrote: On 21. 09. 21 23:34, Michael Lewis wrote: Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as t

Re: Faster distinct query?

On 9/23/21 10:16 AM, Israel Brewster wrote: On Sep 23, 2021, at 4:34 AM, Ryan Booz > wrote: Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions. This is what depesz is referring to: https://wiki.pos

Re: Faster distinct query?

I would look into pre-loading the lookup table (and pre-emptive maintenance).  Add the foreign key, but not the trigger. That makes sense. Thanks! Yeah, then I got to wondering: Do you care?  Are these stations likely to be spoofed?  You have the station id and type in you data table and esse

Re: Using XMLNAMESPACES with XMLEMENT

> On Sep 24, 2021, at 3:44 PM, Garfield Lewis wrote: > >  > Hi All, > > I am attempting to port the following statement from DB2z to Postgres: > > SELECT e.empno, e.firstnme, e.lastname, > XMLELEMENT ( NAME "foo:Emp", > XMLNAMESPACES('http://www.foo.com' AS "foo"), >

Re: Using XMLNAMESPACES with XMLEMENT

> On Sep 24, 2021, at 4:54 PM, Garfield Lewis wrote: > >  > I’m using PG v12.6 and no I haven’t tried the path function. Do you have an > example? > > -- > Regards, > Garfield A. Lewis > > From: Rob Sargent > Date: Friday, September 24, 2021 a

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

> On Oct 4, 2021, at 10:20 AM, Shaozhong SHI wrote: > > Hello, Adrian Klaver, > > Pandas version is 0.23.0. > > I used the following code: > > def psql_insert_copy(table, conn, keys, data_iter): > # gets a DBAPI connection that can provide a cursor > dbapi_conn = conn.connection >

Re: Growth planning

> On Oct 4, 2021, at 10:22 AM, Israel Brewster wrote: > > > - and the data table definition: > > Column | Type | Collation | Nullable | > Default > +--+---+--+--

Re: Growth planning

On 10/4/21 11:09 AM, Israel Brewster wrote: On Oct 4, 2021, at 8:46 AM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: On Oct 4, 2021, at 10:22 AM, Israel Brewster <mailto:ijbrews...@alaska.edu>> wrote: Guessing the “sd” is "standard deviation”?  Any chance th

Re: Growth planning

On 10/4/21 3:09 PM, Israel Brewster wrote: On Oct 4, 2021, at 12:46 PM, Ron > wrote: On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.

Re: Growth planning

On 10/4/21 3:37 PM, Israel Brewster wrote: On Oct 4, 2021, at 1:21 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: My "strict" table per station suggestion was meant as an option to avoid the partitioning pain point entirely if it wasn't going to buy you anythi

Re: "two time periods with only an endpoint in common do not overlap" ???

I was afraid you were going to say that.  It's completely bizarre, but seems to be a "thing" in computer science. Or maybe it's a "math thing".  But an overlap implies some length. A point has no length.  Maybe think of them as abutting one another? One ends /there/, the other starts /there

Re: Looking for a doc section that presents the overload selection rules

You can, of course, trivially make that so in your own database. =# create function to_char(date, text) returns text language sql stable strict parallel safe as 'select pg_catalog.to_char($1::timestamp without time zone, $2)'; =# select to_char(current_date, 'dd-Mon- TZH:TZM'); to_c

Re: Need help understanding error message

> On Oct 26, 2021, at 9:58 AM, Rich Shepard wrote: > > In a database table I have these rows: > # select * from contacts where person_nbr=6; > person_nbr | contact_date | contact_type | > not > es

Re: Need help understanding error message

> On Oct 26, 2021, at 10:07 AM, Rob Sargent wrote: > > > >> > What do you get when you select * where person_nbr = 6 and contact_date = > ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? > > > Whoa. Not sure why I put the “from” last but

Re: Need help understanding error message

> On Oct 26, 2021, at 11:05 AM, Rich Shepard wrote: > > On Tue, 26 Oct 2021, Rob Sargent wrote: > >> What do you get when you select * where person_nbr = 6 and contact_date = >> ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? > > Rob, > >

Re: Need help understanding error message

> On Oct 26, 2021, at 11:06 AM, Rich Shepard wrote: > > On Tue, 26 Oct 2021, Ron wrote: > >> Show us the actual UPDATE statement. > > Ron, > > insert into contacts > (person_nbr,contact_date,contact_type,notes,next_contact) values > (1,'2021-10-26','Email','message 3','2012-11-16'), > (4,'

Re: Design database schemas around a common subset of objects

> On Nov 7, 2021, at 6:10 PM, Rich Shepard for people are a natural key as is the > VIN (vehicle identification number) for ... vehicles. > > Rich Be very careful with SSN. Not as unique as you might think. As well as a stretch to call natural. > >

Re: Replace anonymized data in string

On 11/12/21 7:12 AM, Patrick FICHE wrote: Hi Team, I have some data that has been anonymized and I would like to generate some test data from this. In some way, I would like to deanonymize this data with random data. For example, phone numbers have been anonymized with changing the 5 right

Re: any default columns for tracking / auditing purpose

> On Nov 20, 2021, at 6:50 AM, Sanjay Minni wrote: > > > In Postgres 12 and up, are there any default columns available which could be > useful for tracking / auditing purposes ? > e.g. > - real user who entered / updated the data > - timestamp when data was updated / entered > where can i g

Re: insert column monetary type ver 2

> On Nov 21, 2021, at 8:21 PM, Ron wrote: > > Why then does the money type exist? At the very least, why isn't it > deprecated? Some of us like it as an easy formatter trick for reports > > -- > Angular momentum makes the world go 'round.

Re: Best examples of cardinality check and associated functions

> On Nov 25, 2021, at 3:16 PM, Shaozhong SHI wrote: > >  > I wonder whether the Postgres community has got the best examples of > cardinality check and associated functions. > Are you looking for examples or opinions on the examples? > Regards, > > David

Re: Best examples of cardinality check and associated functions

gt; >> On Thu, 25 Nov 2021 at 22:24, Rob Sargent wrote: >> >> >> > On Nov 25, 2021, at 3:16 PM, Shaozhong SHI wrote: >> > >> >  >> > I wonder whether the Postgres community has got the best examples of >> > cardinality check

Re: Packaging pgAdmin 4

> On Nov 27, 2021, at 1:09 PM, Chuck Davis wrote: > >  > Have you checked your distro repositories? The distro I use ships pgAdmin > together with the latest and previous versions of postgresql. > >> On Sat, Nov 27, 2021 at 10:52 AM Blake McBride wrote: >> Greetings, >> >> I've had ongoin

Re: Match 2 words and more

> On Nov 27, 2021, at 5:27 PM, Shaozhong SHI wrote: > >  > this is supposed to find those to have 2 words and more. > > select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$'; > > But, it finds only one word as well. > > It appears that regex is not robust. > > Can an

Re: How to reveal the codes of functions properly?

On 11/30/21 9:30 AM, Shaozhong SHI wrote: Any one can shed the light on this? Regards, David \sf+ function_name

Re: How to reveal the codes of functions properly?

On 11/30/21 1:17 PM, Shaozhong SHI wrote: In what environment, that did not work. On Tuesday, 30 November 2021, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: On 11/30/21 9:30 AM, Shaozhong SHI wrote: Any one can shed the light on this? Regards, David

Re: Max connections reached without max connections reached

On 12/1/21 3:29 PM, James Sewell wrote: Looking again into the back trace[1], it appeared that the backend is getting stuck while getting the subtransaction's parent information, and that can only happen if the snapshot is getting marked as overflowed.  So it seems that some of t

Re: Max connections reached without max connections reached

On 12/1/21 7:08 PM, Michael Lewis wrote: On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: Also be on the look-out for begin trans; begin trans; etc  I read Kumar's report as nested transactions.  If your gang is doing a transaction per

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 10:03 AM, Alan Hodgson wrote: I keep running into problems like these: Devs are using an ORM. It really likes to produce queries like: SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

On 12/6/21 11:02 AM, Alan Hodgson wrote: On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote: To be clear, is it the devs or the ORM that's adding the ORDER  and the LIMIT?  I'm betting on devs.  Do they need the smallest id (first occurrance?) or do they need data common to all 50

Re: Best Strategy for Large Number of Images

On 12/15/21 10:45, Estevan Rech wrote: Hi, I don't currently use PostgreSQL, but I plan to migrate and I have a question about the best way/strategy for storing images. I have about 2 million images with a growth trend of around 1 million images per year. I plan to store in bytea format in an

Re: Best Strategy for Large Number of Images

On 12/15/21 12:22, Estevan Rech wrote: The possibilities are known, but does anyone have experience with this scenario? My previous employer dealt with radiology images.  These were in the file system with an image server as suggested up thread.

Re: Find missing data in a column

> On Dec 29, 2021, at 10:07 AM, Adrian Klaver wrote: > > On 12/29/21 09:43, john polo wrote: >> Hi, >> I have a database in PostgreSQL 12 on Windows. It has > 8,000,000 rows. I >> want to copy this database to PostgreSQL 10 on Slackware Linux. I used this >> command to get the data out of the

Re: Create and access a dictionary type

>> >> Well, yes - you can always decompose the dict and store it in an EAV, but >> sometimes it's more convenient to just use "nested" dictionary at the row >> level. Which is what a JSONB column does. > > Edgar Codd is rolling in his grave. > > Contributing mightily to the global angular m

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

> > Any help would be appreciated in diagnosing this vexing issue. Thanks, --DD Have you captured all the DDL generated by your C++ code?

Re: java.lang.ClassNotFoundException: batchprint.Batch

> '1103003', > '1020') > > and the ERROR I am getting is: > ERROR: java.lang.ClassNotFoundException: batchprint.Batch > SQL state: XX000 > > My postgres version is 11.14. > > > Could anyone suggest

Re: psql does not provide proper response

On 1/20/22 10:54, Shaozhong SHI wrote: I do not know what happened. psql does not provide proper response anymore. I typed the following and see nothing. user=# select * from boundaryline.scotland_and_wales_const_region user-# Can anyone enlighten me? Regards, David Add semi-colon return?

Re: Undetected Deadlock

> On Jan 24, 2022, at 10:02 PM, Michael Harris wrote: > > My apologies, > > After posting this and looking at how it appears I realised that line > wrapping makes the tables totally illegible. > > Here they are again with all unnecessary columns removed and others shortened. > > locktype |

Re: Robust ways for checking allowed values in a column

On 1/25/22 09:35, Shaozhong SHI wrote: How about adding null as an alteration. Would this be robust? Regards, David On Tue, 25 Jan 2022 at 14:25, David G. Johnston wrote: On Tue, Jan 25, 2022 at 6:56 AM Shaozhong SHI wrote:  select form from mytable  where form ~

Re: Counting the number of repeated phrases in a column

On 1/26/22 13:35, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of oc

Re: PostgreSQL Management and monitoring tool

On 1/26/22 20:24, Lucas wrote: On 27/01/2022, at 3:00 PM, Bruce Momjian wrote: On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: Hi guys. I’m migrating a few databases to PG 14 and was wondering that it would be very nice to have a tool to help me monitor and manage my databases. I f

Re: Counting the number of repeated phrases in a column

On 1/27/22 10:03, Merlin Moncure wrote: On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: with s as (select 'Hello World Hello World' as sentence) select phrase, array_upper(string_to_array((select sentence from s), phrase), 1) - 1 as occurrances from ( select array_to_string(x, '

Re: Can we go beyond the standard to make Postgres radically better?

On 2/11/22 09:12, Mladen Gogala wrote: On 2/11/22 09:48, Benedict Holland wrote: So to summarize, people are bad programmers who refuse to learn SQL So SQL is the problem? Common. You cannot bring that to a postgres list serve. Look. It's not perfect. It's a pain. It is hard to generate queri

<    1   2   3   4   5   6   7   >