I wrote a summary of lesser-known Postgres type system information

At PostgresConf 2024, I gave a presentation about PostgreSQL types (thanks to folks on this list for the assist with that). I wrote up a summary of it here: https://lydb.xyz/postgres-types/

Re: Thoughts on user-defined types for talk at Postgres conference?

Thanks for that! It seems as though a Composite Type having only fixed-length fields should be able to be regarded as a fixed-length value. On 6 Mar 2024 at 09:45 -0800, Stephen Frost , wrote: > Greetings, > > * Guyren Howe (guy...@gmail.com) wrote: > > But what *really* sets Post

Thoughts on user-defined types for talk at Postgres conference?

I am doing a talk at Postgres Conf about Postgres’s type system. I already asked about this and got some great responses: https://www.postgresql.org/message-id/flat/b82f8886db61a3395e6eab94981258274cdbacd3.camel%40cybertec.at#f13288b72bd2d564ee758c1a273652ad Those responses discussed mostly

Re: Content for talk on Postgres Type System at PostgresConf

On 29 Feb 2024, at 14:51, Tom Lane wrote: > >>> - time with time zone *does* store the time zone, but this isn’t >>> actually useful and should be avoided (I’m not entirely sure why and the >>> docs only gesture at the problems without stating them, IIRC) > >> No it doesn't store the time zone.

Re: Content for talk on Postgres Type System at PostgresConf

On 28 Feb 2024, at 17:08, Guyren Howe wrote: > > I am to talk about Postgres’s type system at PGConf: > > https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system > > I picked the issue because I think it’s poorly understood, greatly

Re: Content for talk on Postgres Type System at PostgresConf

them, IIRC) • money is a fixed-point decimal value, the number of decimal places is locale determined. I’m not aware of any particular problems with that On 29 Feb 2024 at 01:11 -0800, Laurenz Albe , wrote: > On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote: > > I am to talk about P

Content for talk on Postgres Type System at PostgresConf

I am to talk about Postgres’s type system at PGConf: https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system I picked the issue because I think it’s poorly understood, greatly under-discussed, and an excellent way to empower postgres users. I am reasonably conversant

Good overview of programming IN Postgres?

Is there a good overview of programming WITHIN Postgres? Not writing code in client languages that communicates WITH Postgres, but how to take full advantage of Postgres’ unique programming features — the advanced type system, the various PL languages, triggers, stored procedures, functions, …

Re: Converting sql anywhere to postgres

For some reason, I was thinking the rule could see just the fields from the command, but you’re right; a rule won’t work. Sorry. Guyren G Howe On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems , wrote: > I have just had a quick look at rules and I am not sure how it can be

Re: Converting sql anywhere to postgres

I’m fairly confident you can do this using a Rule. It would even be fairly simple. Be careful, though: Rules are Postgres’ biggest potential foot gun. Guyren G Howe On Aug 15, 2023 at 08:05 -0700, Russell Rose | Passfield Data Systems , wrote: > Hi there > > I am trying to convert a SQL

Re: Large scale reliable software system

w, if it's because they're used to using MySQL, well maybe that's > not so hard to understand. :-) > > On Mon, Jun 26, 2023 at 8:05 PM Guyren Howe wrote: >> >> This is a reasonable answer, but I want to offer a caveat. >> >> Likely because of the influence

Re: Large scale reliable software system

This is a reasonable answer, but I want to offer a caveat. Likely because of the influence of the originator of Ruby on Rails, it is close to holy writ in the web development community that the database must be treated as a dumb data bucket and all business logic must be implemented in the Ruby

Re: Question about where to deploy the business logics for data processing

People change applications and programming languages all the time. But change the database? Particularly away from Postgres, which is for nearly any purpose clearly the best SQL database available? You have to pick one. Heck, write your triggers and stored procedures in Python and you can

Re: massive update on gin index

You might consider defining a phone type that includes your “type” information, and just having an array of those, if you really want to do something like this. But a related table instead would be the obvious answer. > On Sep 14, 2022, at 12:33 , Marcos Pegoraro >

Are stored procedures/triggers common in your industry

I’ve really only ever worked in web development. 90+% of web developers regard doing anything at all clever in the database with suspicion. I’m considering working on a book about implementing business logic in Postgres, and I’m curious about how common that actually is.

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

> > The MySQL autocomplete is designed without context filtering. Maybe we can > > have this implementation too (as alternative) > > > > so using all column names + all table names + aliases.column names (when we > > know defined alias) > > > > Another idea about column excluding. Any

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

I get all this. Give me a couple million bucks, and I’ll hire some of the Postgres devs to build a new database. We could crib some of the low-level code from Postgres, but everything above the low level would need to be rewritten. I was proposing more that we at least provide higher-level,

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

is pretty much a variant of Prolog. https://www.datomic.com I don’t use it because it’s closed source. On Feb 10, 2022, 21:15 -0800, Raymond Brinzer , wrote: > > On Thu, Feb 10, 2022 at 11:56 PM Guyren Howe wrote: > > > I feel like anyone who is defending SQL here isn’t aware of how muc

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

On Feb 10, 2022, at 17:06 , Mladen Gogala wrote: > >> But SQL is a terrible, no good, very bad language. > > I cannot accept such a religious persecution of SQL without a detailed > explanation. > I feel like anyone who is defending SQL here isn’t aware of how much better the alternatives

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

gt; On Thu, Feb 10, 2022, 4:13 PM Bruce Momjian <mailto:br...@momjian.us>> wrote: > On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote: > > On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote: > > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: > >

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

by just implementing good fp features like this. On Feb 10, 2022, 11:09 -0800, Merlin Moncure , wrote: > On Thu, Feb 10, 2022 at 10:54 AM Merlin Moncure wrote: > > > On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe wrote: > > > > > > > > > > > > > >

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

Postgres has since the outset gone beyond the SQL standard in many ways : types, inheritance, programmability, generality are all well beyond what SQL used to mandate and still well beyond the current standard. There are huge developer benefits available to focusing more on making a great

Re: Match 2 words and more

On Nov 27, 2021, at 16:27 , Shaozhong SHI wrote: > > select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$’; The simplest thing that does what you says is I think: select name FROM a_table where "STREET_NAME" ~ ‘^([[:alpha:]]+\s)+[[:alpha:]]+$’;

Best options for tracking history of data?

I’m interested in tracking our data over time. The basic requirement is for debugging and disaster recovery, but I’m passing familiar enough with the issue that I can imagine being able to set things up so I get that but much more. I’ll need something that I can use on eg AWS, probably RDS,

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

I’m a bit confused by this conversation. Open- and closed-ended ranges behave as I would expect. select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[)') && tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]') false select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[]') &&

Re: The tragedy of SQL

On Sep 16, 2021, at 7:31 , Merlin Moncure wrote: > > On Wed, Sep 15, 2021 at 7:31 PM FWS Neil wrote: >> On Sep 15, 2021, at 2:44 PM, Merlin Moncure wrote: >>> I think you ought to recognize that many people on this list make >>> money directly from managing that complexity :-). >> >> I did

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

:49:07 -0700 (PDT) > > > On Mon, 13 Sep 2021, 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. > > > > > > SQL is not the problem.

Re: The tragedy of SQL

Exactly. SQL is the roman numerals of relational databases. On Sep 14, 2021, 13:08 -0700, Raymond Brinzer , wrote: > On Tue, Sep 14, 2021 at 3:58 PM Guyren Howe wrote: > > You’re confusing SQL with the relational model. Datalog and Quel and > > Tutorial D and other database langua

Re: The tragedy of SQL

On Sep 14, 2021, 12:51 -0700, Mladen Gogala , wrote: > Replies in-line > On 9/14/21 01:51, 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. > Based on what criteria? Verbos

Re: The tragedy of SQL

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, pleasurable thing to use in any

The tragedy of SQL

A fun philosophical discussion. I am no fan of “worse is better”, and particularly its poster child, SQL. The world’s economic output would be substantially higher (5%?) if our industry had settled on almost anything other than SQL for relational databases. So much of the design of *almost

Re: Sort question - Fractions, Metric etc

You might define new types for temperature, length, whatever, with suitable conversion, operation and creation functions. You’d be able to define how the new types participate in indexes, support directly sorting on them, so you package up this complexity and forget about it. Either normalize

Re: syntax question

I know it would be non-standard, but I would love to see Postgres support the likes of nested functions. I know that would be non-standard, but Postgres has lots of non-standard features that make it more like a real programming language and considerably more productive. On Jun 3, 2021, 12:34

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

I think that’s burying the lede a bit. Still, unless the “Extending SQL Section” acquired an Overview section, I’m not sure where else you’d put it. On Mar 8, 2021, 20:41 -0800, Tom Lane , wrote: > Guyren Howe writes: > > This seems like an important consideration. I’ve spent 1

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

This seems like an important consideration. I’ve spent 10 minutes searching the documentation for PG 11 and can’t find where it is documented. Perhaps it should be made more prominent? On Mar 8, 2021, 16:53 -0800, raf , wrote: > On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane wrote: > > >

How to determine server's own IP address? inet_server_addr not working

The documentation says that inet_server_addr() does this, but on our servers it is returning nothing.

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

An interesting option is to make your tenants work through views, and have the views work through a variable that contains the tenant’s id. There would be a bit of coding, but it would be the same for every table, so you could automate it easy enough. When you’re done, client software just

Re: Is there a good discussion of optimizations?

out when the database is the appropriate place for some bit of logic. > On Thu, Jan 7, 2021 at 11:21 PM Guyren Howe wrote: > > On Jan 7, 2021, 13:07 -0800, Kevin Brannen , wrote: > > > From: Guyren Howe > > > > > > >Most folks, in my experience, who us

RE: Is there a good discussion of optimizations?

On Jan 7, 2021, 13:07 -0800, Kevin Brannen , wrote: > From: Guyren Howe > > >Most folks, in my experience, who use relational databases don’t really > >understand the basic theory or even more important the why - the philosophy > >- of what a relational database is and

Re: Multi-column index vs index on individual columns

In a typical web storefront or catalog app, there are a fairly small number of queries that are run often that are more complex and slower. It is perfectly appropriate to consider creating compound/partial indexes to speed up those queries. Just be aware that indexes trade off slower

Is there a good discussion of optimizations?

I’d like to put together a good video and writeup about what the… philosophy behind relational databases is. Most folks, in my experience, who use relational databases don’t really understand the basic theory or even more important the why — the philosophy — of what a relational database is

Any interest in adding match_recognize?

I just became aware of the SQL 2016 standard match_recognize feature, which allows for rich pattern matching across groups of rows. It’s a great feature, currently only supported in Oracle. I can find no evidence it’s ever been discussed here and there’s no mention of it on the PG website.

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

Regarding indexes, I recommend the website use-the-index-luke.com. That guy’s other website about modern SQL is also great. Regarding self-tuning, it was actually part of the original vision for relational databases that they would do that, but I’m not aware of any modern SQL database that

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

On Sep 14, 2020, 13:22 -0700, tutilu...@tutanota.com, wrote: > 3. The ability to embed PG to run in an automatic, quiet manner as part of > something else. I know about SQLite, but it's extremely limited to the point > of being virtually useless IMO, which is why I cannot use that for anything

Can we get SQL Server-like cross database queries

One of the few really useful features of SQL Server that Postgres doesn’t have is straightforward cross-database queries. You can reference any table in any database on the same server you’re on as database.schema.table. With Postgres, it is necessary to set up a FDW connection between every

Re: Oracle vs. PostgreSQL - a comment

On Jun 2, 2020, at 14:16 , Stephen Frost wrote: > > Greetings, > I'm sure there's things we can do to improve the performance of the FDW. > Not sure we'll get to a point where we are actually cacheing information > from the far side... but who knows, maybe if we arrange to have a > notification

Re: Oracle vs. PostgreSQL - a comment

On Jun 2, 2020, at 12:45 , Ravi Krishna wrote: > >> >> >> Generally speaking, I discourage having lots of databases under one PG >> cluster for exactly these kinds of reasons. PG's individual clusters >> are relatively lightweight, after all. >> > > Plus PG does not directly support cross

Re: How to unnest nested arrays

> On Apr 6, 2020, at 19:44 , David G. Johnston > wrote: > > On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <mailto:guy...@gmail.com>> wrote: > Consider this: > > select (array[array[1, 2], array[3, 4]])[i:i] > from generate_subscripts(array[array[1, 2], array

How to unnest nested arrays

Consider this: select (array[array[1, 2], array[3, 4]])[i:i] from generate_subscripts(array[array[1, 2], array[3, 4]], 1) i which produces: {{1,2}} {{3,4}} I expect and want, from that source: {1, 2} {3, 4} These don’t work: select (array[array[1, 2], array[3, 4]])[i:i][:] {{1,2}} {{3,4}}

Re: Backing out of privilege grants rabbit hole

More fully: REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; -- repeat the above commands in each database of the cluster DROP ROLE doomed_role; > On Apr 2, 2020, at 20:37 , Guyren Howe wrote: > > https://www.postgresql.org/docs/12/sql-drop-owned.html

Re: Backing out of privilege grants rabbit hole

https://www.postgresql.org/docs/12/sql-drop-owned.html > On Apr 2, 2020, at 20:34 , AC Gomez wrote: > > Do I understand correctly that if a role was assigned countless object > privileges and you want to delete that role you have to

Re: Join help, please

The three types of thing (permitted_work; employee; work_type) don’t stand in a 1:1 relationship with each other. You might have multiple work_types or permitted_work for each employee, I’m guessing. Each existing combination produces one row in the result. So an employee with three

Re: Advice request : simultaneous function/data updates on many databases

On Mar 4, 2020, at 14:33 , Rory Campbell-Lange wrote: > > Essentially we wish to reduce the window where the frontend and backend > aren't synchronised. > > If we have (for example) 200 databases which each take 2 seconds to > update, a client could be on the wrong frontend code for over 6

table name "unnest" specified more than once

# select * from unnest(array[array['a', 'b'], array['c', 'c']]), unnest(array[array['1', '2'], array['3', '4']]); ERROR: 42712: table name "unnest" specified more than once I’m trying to cross-join multiple two-dimensional arrays, expecting to retain the inner arrays. I’ve

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

> On Feb 25, 2020, at 11:28 , Paul A Jungwirth > wrote: > > On Tue, Feb 25, 2020 at 2:18 AM Stanislav Motyčka > mailto:stanislav.moty...@gmail.com>> wrote: >> Sometimes (for tables with many columns) it would be better and easier to >> write "SELECT" statement with clause "EXCEPT": >>

Re: Returning a table from a function, that requires multiple selects?

On Aug 30, 2019, at 13:03 , stan wrote: > I need to encapsulate, what are basically 2 related function calls into a > single > function. The result of each of th calls is a date type. > > y current thinking is to return a 2 row table with the 2 dates in it. But, I > seem to > be having issues

Re: Reproducing query plans in empty database: can I just copy stats and settings?

On Jul 29, 2019, at 12:25 , Adrian Klaver wrote: > > On 7/29/19 12:07 PM, Guyren Howe wrote: >> I work with Protected Health Information so have restricted access to an >> important database. >> I would like to look into index and query changes for that database. It &

Reproducing query plans in empty database: can I just copy stats and settings?

I work with Protected Health Information so have restricted access to an important database. I would like to look into index and query changes for that database. It occurs to me that if I was able to reproduce the statistics and cost settings for the database, then Explain would produce

Re: Request for resolution || Support

Another option would be an app that is constantly connected to Postgres using LISTEN/NOTIFY. On Jul 24, 2019, 22:34 -0700, jay chauhan , wrote: > Hi Thomas, David/Team, > > Thanks you for your response. However we need your confirmation whether my > Error/issue as mentioned below will be

Elastic Search much faster at statistics?

I find this… surprising. Caching? http://blog.nrowegt.com/database-vs-elasticsearch-speed-column-statistics/

Re: Rules and decision logic triggered by / acting on JSONB nodes in Postgres

On Sep 1, 2018, at 7:14 , Amit Kothari wrote: > > Hi all, > > I’m sorry if this question is off-topic for postgres-core, but didn’t know > where else to ask. > > We’ve built a workflow and BPM (business process management) tool with pg as > the database, and we’d like to explore conditional

Re: Immutable function WAY slower than Stable function?

On Aug 7, 2018, at 9:10 , Tom Lane wrote: > > I've had a to-do item to rewrite and improve the SQL function cache > mechanism for a long time, but I hadn't thought it was high priority. > Maybe it should be. Personally, I would love this. Far too few developers dig into doing things in the

Re: Code of Conduct plan

On Jun 3, 2018, at 16:08 , Gavin Flower wrote: > > Be very careful in attempting to codify 'correct' behaviour! +1 this is a distraction.

Re: Whither 1:1?

On Jun 1, 2018, at 10:16 , Olivier Gautherot wrote: > > You will get a benefit in terms of space only if the optional fields in the > second table exist in a reduced number of instances - and the second table is > significantly wider. This can make a difference on big tables but this gain >

Re: Whither 1:1?

On Jun 1, 2018, at 10:00 , James Keener wrote: > > I don't think I fully understand. Do you mean all pk using a single sequence? > I’m not sure how this would avoid nulls or grouping fields. Presumably, you would create the multiple tables together or after some main one or something where

Whither 1:1?

It’s come to my attention that what seems an obvious and useful database design pattern — 1:1 relations between tables by having a shared primary key — is hardly discussed or used. It would seem to be a very simple pattern, and useful to avoid storing nulls or for groups of fields that tend to

Re: Rationale for aversion to the central database?

On Apr 27, 2018, at 8:45 , Basques, Bob (CI-StPaul) wrote: > > Just chiming in . . . we’ve taken a somewhat different approach and actually > encourage our programmers to build out thier own DBs. We’re using Postgres > to aggregate many varied datasources into

Rationale for aversion to the central database?

I am a Rails developer at a medium-large size company. I’ve mostly worked at smaller companies. I’ve some exposure to other web development communities. When it comes to databases, I have universally encountered the attitude that one should treat the database as a dumb data bucket. There is a

Re: single sql, multiple schemas, one result set

Make a view that joins all the things, with a column providing the name of the schema that they came from. > On Apr 3, 2018, at 10:47 , PegoraroF10 wrote: > > Suppose a DB with dozens of schemas with same structure. > DB > Schema1 >Table1 >Table2 > Schema2 >