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

21–30 of 67 posts

Re: PostgreSQL Magic

#21

Post by hobs »

ahachete wrote: post #17
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 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. Why is that? There are many use cases for data (like vector data) which really needs an array. And it would be unwise to store it as columns. Think of, for example, matrix data or a prac…

Why wouldnt an unbounded set of values related to a sensor just fit in a one to many model of data? (Instead of a constantly updating row, simply inserting more rows)

It seems like you would be trading one fat row for something the database does well (unless you always want all of the sensor data every time)

Re: PostgreSQL Magic

#22

Post by greggyb »

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 SQL Server, where I am able to do everything that comes up when "Postgres window functions" are mentioned, whether there is extra functionality in Postgres compared to other RDBMS's window function implementations?

Is there a reason that Postgres seems to get special attention for window functions?

Thanks.

Re: PostgreSQL Magic

#23

Post by crc32 »

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 because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.

Re: PostgreSQL Magic

#24

Post by mistermann »

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…

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).

Re: PostgreSQL Magic

#25

Post by lsaferite »

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.

Thanks for the chuckle.

(Yes, I know this adds nothing to the discussion)

Re: PostgreSQL Magic

#27

Post by marcosdumay »

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…

They are incredibly useful in stored procedures. Also at indexes, pg's full text search is completely reliant on array indexes, ditto for the json storage.

I think I've never used them as column type, but I can imagine a few uses there too.

Re: PostgreSQL Magic

#28

Post by yread »

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…

There are window functions in sql server, too

https://www.simple-talk.com/sql/learn-sql-server/window-func...

Re: PostgreSQL Magic

#29

Post by greggyb »

mistermann wrote: post #24
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…

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.

Re: PostgreSQL Magic

#30

Post by greggyb »

crc32 wrote: post #23
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 because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.

This does make some sense. Sometimes I forget how abnormal my own frame of reference is in comparison with the typical (at least of the vocal subset) HN poster.
Post reply on HN