121–130 of 167 posts
I would hesitate to call this a useful feature. This error message doesn’t tell you about the actual problem at all.
How about something like:
Permission denied, cannot select other than (id, name) from table users
I want to stress the importance of not using id int SERIAL If you are on a somewhat recent version of postgres, please do yourself a favor and use: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY An "identity column", the part here: https://hakibenita.com/postgresql-unknown-features#prevent-s... You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so…
I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many…
Earlier quoted context omitted.
I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many…
Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have - Allows you to generate the key before the row is saved I think I default to auto-increment ID's due to: - Familiarity bias - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they…
Of your disadvantages... if I wanted to know when rows were created, I'd just add a created timestamp column.
But "easier to read" is for real -- it's easy when debugging something to have notes referencing rows 12455 and 98923823 or in some cases even keep them in your head. But UUIDs are right out.
And you definitely don't want to put UUIDs in a user-facing URL -- which is actually how you get around "avoids people being able to just iterate through records", really whether you have UUIDs or numeric pks I think putting internal PK's in URLs or anything else publicly exposed is a bad idea, just keep your pks purely internal. Once you commit to that, the comparison between UUIDs vs numeric as pks changes again.
Earlier quoted context omitted.
Yup this. Ulid is great, and implemented in many languages. For example there’s a crate named Ulid that you can use that gives you ulid’s in Rust.
Is there a PostgreSQL extension for ulid?
Avoid using lesser known features... They're the ones that will be hardest to migrate to a different database, most likely to be deprecated, and least likely to be understood by the next engineer to fill your shoes. While many of these are neat, good engineering practice is to make the simplest thing to get the job done.
How often does migrating databases ever actually happen? I'm not saying it doesn't happen, but I've never experienced it or know anyone who has (I've asked!). I certainly would shy away from using features that make my app code cleaner and improves the all-around performance of the app on the slim-to-none chance that one day I might one day have to migrate off of postgres.
I’ve since ported a few apps in early development or production between Microsoft SQL server, MySQL and Postgres (in various directions) but nothing in prod in over 10 years
Earlier quoted context omitted.
How often does migrating databases ever actually happen? I'm not saying it doesn't happen, but I've never experienced it or know anyone who has (I've asked!). I certainly would shy away from using features that make my app code cleaner and improves the all-around performance of the app on the slim-to-none chance that one day I might one day have to migrate off of postgres.
20-year career so far. Never seen a database swapped out. I've seen "apps" replaced on top of databases, or more programs added to access the same database. I've seen the app and database both get thrown out and replaced, because they were tightly coupled[0], as the parent advocates (Rails + ActiveRecord seems to be prime for this kind of "gotta throw it all out" situation). I've never seen the program stay the same…
One of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Postgres has a solution - `DISTINCT ON` - though personally I've always been a fan of how BigQuery does it: `ARRAY_AGG`. e.g. SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].* FROM posts GROUP BY user_id…
... ORDER BY date DESC LIMIT 1 BY user_idOne of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Postgres has a solution - `DISTINCT ON` - though personally I've always been a fan of how BigQuery does it: `ARRAY_AGG`. e.g. SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].* FROM posts GROUP BY user_id…
> I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`.
So, put ClickHouse in you list :)
Earlier quoted context omitted.
Yup this. Ulid is great, and implemented in many languages. For example there’s a crate named Ulid that you can use that gives you ulid’s in Rust.
Is there a PostgreSQL extension for ulid?
For the last point about "Find Overlapping Ranges", consider PostgreSQL range types [0] and their corresponding range operators [1]. [0]: https://www.postgresql.org/docs/current/rangetypes.html [1]: https://www.postgresql.org/docs/current/functions-range.html