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

11–20 of 67 posts

Re: PostgreSQL Magic

#11

Post by rosser »

Hakeashar wrote: post #5
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…

Isn't a CTE in Postgres (unlike in MS SQL, AFAIR) also an optimization fence? Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)

Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.

Re: PostgreSQL Magic

#12

Post by cleansy »

rosser wrote: post #3

Quibble: the "now()" function doesn't return the time of statement start; it returns the time of transaction start. $ psql -q rosser=# begin; rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row) rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row)

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 same for the entire statement as well. clock_timestamp() doesn't.

Re: PostgreSQL Magic

#13

Post by fgutmann »

cleansy wrote: post #12
rosser wrote: post #3

Quibble: the "now()" function doesn't return the time of statement start; it returns the time of transaction start. $ psql -q rosser=# begin; rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row) rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row)

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.

Re: PostgreSQL Magic

#14

Post by [deleted] »

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.

[deleted]

Re: PostgreSQL Magic

#17

Post by ahachete »

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 practically unbounded number of double values coming from a sensor. Plus, PostgreSQL has a limit in the number of columns (1600) of a table, of which you could run out soon if representing this kind of data as regular columns rather than array values.

Re: PostgreSQL Magic

#18

Post by Jgrubb »

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…

IANADBA, but "normalizing something more" without taking into account the performance impact or the overall usability for the end user sounds like a design mistake.

Everything is a nail and all that...

Re: PostgreSQL Magic

#19

Post by mhd »

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…

That's a nice justification for their inclusion, but I don't really see that as the common actual use case. More often it seems that you'll get arrays where normally a join table would be used (e.g. tags for a blog post, authors for a book).

Re: PostgreSQL Magic

#20

Post by gnud »

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've not actually used arrays in relational databases, but often wished I had them when doing grouping and aggregates. It would often be nice to do the aggregate on the server side, but also return all the IDs in each group in a clean way.
Post reply on HN