Hacker News Reader

Comment by ThrustVectoring | Hacker News Reader

I looked into this recently, and views cover like 98% of the functionality that the client app needs from Postgres. One issue I ran into was that Postgres forgets that the primary key is a primary key when pulling from a view, which breaks some queries that rely on grouping by the primary key.

https://dba.stackexchange.com/questions/195104/postgres-grou... has some more info on this

View on HN

I recently transitioned a number of tables over to views as part of a data model rearrangement and I absolutely loathed my previous self that leveraged that primary key trick. I don't think there is anything unsafe about them choosing to transition to allowing any column singularly defined as a unique index for the table to serve this role and it'd help make things a fair bit more logical.

That all said, until that happens, I'd strongly suggest avoiding that functionality since it can lay down some real landmines.

I've made a library that generates Typescript types from a PG database and I see a variation of this problem: since the "reflection" capabilities on views don't tell me about references in their source tables, I lose the ability to see where a foreign key points to. I normally use this to create nominal ID types, but in views I can just create strings or numbers.

I am trying to work around this but so far my only solution is to parse the SQL of the view definition and extract the information from there. I have it somewhat working but it's a bit complex for my liking..

Interesting link, thanks! It would be really nice if Postgres were to close that gap and make them fully equivalent (if that is even possible).

Until then, you just have to list the other columns in the GROUP BY... You should usually really be listing which columns you need explicitly, anyway.

Doesn't DISTINCT work in this case?

  SELECT DISTINCT ON (vt.id)
  row_to_json(vt.*) FROM vt
  JOIN vy ON vt.id = vy.tid
  WHERE vt.id = 1
  AND vy.amount > 1;

The SQL in question which was problematic for me (tables renamed):

SELECT posts.*, MAX(COALESCE(comments.created_at, posts.created_at)) AS latest_timestamp FROM posts LEFT OUTER JOINS comments ON posts.id = comments.post_id GROUP BY posts.id ORDER BY latest_timestamp desc

In short, this is sorting posts by the most recent comment, with a fallback to the post date if the post has no comments on it. Hard to get rid of the grouping here and get the same data back.

I see, that makes more sense.

I'm the OP of the DBA StackExchange post, and while yes, it does technically work in this case, you do lose some abilities. For one, its much harder to `count(*)` rows with `DISTINCT`. Also, `DISTINCT` uses a totally different mode in planning that requires first retrieving all the rows and then finally filtering them. This is _much_ slower and generally not very fun to deal with!

Reply on news.ycombinator.com