5 ms·

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 forge

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-group-by-id-works-on-table-directly-but-not-on-identical-view https://dba.stackexchange.com/questions/195104/postgres-grou... has some more info on this

fabianlindfors 5y ago
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).
- jfrunyon 5y ago
  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.
munk-a 5y ago
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.
e12e 5y ago
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;
- ThrustVectoring 5y ago
  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.
  - e12e 5y ago
    I see, that makes more sense.
- biggerfisch 5y ago
  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!
kristiandupont 5y ago
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..

fabianlindfors 5y ago

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

jfrunyon 5y ago
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.

munk-a 5y ago

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.

e12e 5y ago

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;

ThrustVectoring 5y ago
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.
- e12e 5y ago
  I see, that makes more sense.
biggerfisch 5y ago
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!

kristiandupont 5y ago

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