Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Magic

goto.project-a.com

Post reply on HN
Print view View original

31–40 of 67 posts

Re: PostgreSQL Magic

#31

Post by greggyb »

yread wrote: post #28
greggyb wrote: post #22

Earlier quoted context omitted.

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better. I'm curious, as I do almost 100% of my production work in MS S…

There are window functions in sql server, too https://www.simple-talk.com/sql/learn-sql-server/window-func...

I am well aware. My confusion is, therefore, why do window functions get called out specifically, fairly commonly, as a Postgres-specific piece of awesomeness.

Window functions are awesome, and they are a part of the SQL standard, thus my confusion. As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.

Re: PostgreSQL Magic

#32

Post by asolove »

greggyb wrote: post #22
timonv wrote: post #2

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless you actually need map-reduce, ofcourse. You probably don't. /trollface) What I miss though in this article, and where I think postgres shines majorly compared to other rel dbs, are window functions. It allows you to apply a partition to a set. You can do some…

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better. I'm curious, as I do almost 100% of my production work in MS S…

I think it's largely a question of community.

Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well.

Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only know small bits of SQL, have only a basic understanding of why any company would need a DBA, and only learn things like this when they absolutely need them for a task. And, for that group of people, MS and Oracle probably aren't serious choices, so the fact that a free database has these cool features seems exciting to them.

(Note that I don't say this with distain. I'm a former-mysql user who now uses postgres on Heroku and am constantly learning things like this. I wouldn't even have understood your perspective until I started working with people who knew Oracle and MS SQL so thoroughly.)

Re: PostgreSQL Magic

#35

Post by dsugarman »

The best part of this for me was the batch updates. I am a little confused at the need for a user defined type that is a copy of the schema of the table. Wouldn't it be better to do use:

::table_name%ROWTYPE;

That way you wouldn't need to maintain a table and a type with the exact same schema, no?

Re: PostgreSQL Magic

#36

Post by AlterEgo20 »

fgutmann wrote: post #13
cleansy wrote: post #12

Earlier quoted context omitted.

That's also not completely acurate: postgres=> SELECT now(), now(), clock_timestamp(), clock_timestamp(); now | now | clock_timestamp | clock_timestamp -------------------------------+-------------------------------+-------------------------------+------------------------------ 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.419087+00 | 2015-09-11 09:57:00.41909+00 Now() stays the…

If you ever manage to have multiple transactions in one statement please email me.

Easy. Just use dblink or foreign table to execute part of the statement inside other transaction. dblink to the same server/db is often used as way to create an "AUTONOMOUS_TRANSACTION"

Re: PostgreSQL Magic

#37

Post by jeltz »

greggyb wrote: post #29
mistermann wrote: post #24

Earlier quoted context omitted.

Whenever I read articles like this, Postgres seems to have so many more small niceties and syntaxes than MSSQL though (I don't have a list of things, it just seems "easier" and more flexible).

I don't disagree in the general case, but I'm particularly interested in window functions here, as they are part of the SQL standard and seem equivalent between MS SQL Server and Postgres (I don't have near enough experience in other RDBMS's to speak to their implementations), despite being called out with some regularity as a Postgres-specific nicety.

The only one I know of is that PostgreSQL has some nice ordinary aggregate functions which can be used well with window functions, like bool_and() and array_agg(). I can't think of an example on top of my head where it is useful but I know I have used them both.

Re: PostgreSQL Magic

#38

Post by allenwlee »

mhd wrote: post #4

I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and…

i use arrays as a substitute for app-side caching via materialized views

Re: PostgreSQL Magic

#40

Post by greggyb »

asolove wrote: post #32
greggyb wrote: post #22

Earlier quoted context omitted.

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better. I'm curious, as I do almost 100% of my production work in MS S…

I think it's largely a question of community. Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well. Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only k…

Thanks very much. It is helpful to understand more of the background from someone who's seen multiple sides of the situation.

For my work especially (BI consulting), fluency in SQL is incredibly useful, and specifically I don't think there's a single person at my company who doesn't use window functions regularly in all their development work. Other advanced constructs are very common as well.

Again, I appreciate understanding the world outside my own little bubble. Thanks.

Post reply on HN