|
Mind_Taker posted:A few design/best practices questions: DELETE CASCADE posted:in postgres it is common to use snake_case instead of camelCase, because postgres does automatic case folding of all identifiers to lowercase. you declare the column fooBar, the actual column created is foobar. feel free to call it fooBar in all your queries, postgres will just silently convert that to foobar and it works. if you really want to name the column fooBar with the caps, you can use double quotes to disable case folding: "fooBar". great, you've created a camelCase column. but now you must refer to it as "fooBar" each time, with the quotes, because plain fooBar gets case folded to foobar, and that's not the name of the column. and since in postgres we don't want to sprinkle quotes all over the place, we use snake_case. (microsofties seem to have no problem with the [mssql].[nonsense]...) i guess i can give my views on your other questions too! don't we just love opinions singular or plural makes absolutely no difference to me, just pick one and be consistent with it, i agree with hammerite here id vs tablename_id does have an actual consequence if you like the "using" clause. i tend to name the primary key of a table "id", and then any foreign keys in other tables are foreigntablename_id. but this means you have to write, x join customer c on x.customer_id = c.id. if you had consistently used customer_id in both places, you could write, x join customer c using (customer_id). this can be convenient but also confusing (you only get one customer_id column from this join, and it gets moved to the leftmost position) so i stick with my convention, but it does seem like the existence of a "using" clause suggests that using the same column name is preferred by at least some people
|
# ? Oct 26, 2023 02:52 |
|
|
# ? May 25, 2024 14:56 |
|
If your going into an existing codebase/deploy with an existing style guide, that is *always* going to be the one you want to use. snake_case and pluralized tables might be more logical, but in the scheme of things, its just a preference, and bucking the teams preference isn't a good way to remain employed. And yeah, I prefer just using id for the index too, because it means theres always a predictable pk, but again, its a preference, and mob rules when it comes to teamwork.
|
# ? Oct 26, 2023 06:30 |
|
Mind_Taker posted:A few design/best practices questions: I agree with everyone else, it's irrelevant as long as it's consistent. quote:2. Do you use snake case, camel case, or another case to name columns? E.g. first_name or FirstName? I think snake case is the safest choice. Regardless of what your DB does, there are a million different tools that will interface with it over time (reporting tools, API generators, ORM/query generators, etc. etc.). I'm not aware of any language or tool that doesn´t like snake_case as identifiers, while a lot of tools will mangle, change, or remove casing when generating their DB code; likewise for kebab case or *shudders* spaces in names. quote:3. Do you prefix a table's id column name with the table name or just leave it as id? E.g. id or customer_id Prefix. It makes things explicit by default, makes queries more readable, makes it hard to write wrong joins, and if you are writing a query where that doesn't help and you use the column a lot, it's easy to just add "as id" to the select. One variant I've seen that I find tempting is "id_customer". Yes, it's not grammatically correct English, but it means that when you are typing a query you can just type '.id' and autocomplete will show you what's available in the table. Furthermore, all id columns will stand out in the list, and in long queries they will align vertically. I haven't adopted it yet but if I'd like to give it a try in a side project. NihilCredo fucked around with this message at 11:25 on Oct 26, 2023 |
# ? Oct 26, 2023 11:22 |
|
Personally I don't care about plural/singular but every shop I've worked in has used plural. I prefer snake case and it's the most common style in my experience. I'm neutral on id vs tablename_id, used to be a big proponent of id but my current workplace uses tablename_id and that's OK too (and join using(tablename_id) is convenient when you're writing one-off queries by hand). That said though, it seems OP is in a Microsoft shop and in that case the singular/Pascal case/TablenameID preference makes perfect sense because that's the standard at Microsoft and so that's the pattern used in all the example code in the MSSQL documentation, as well as in most MSSQL books and such.
|
# ? Oct 26, 2023 17:30 |
|
For me, singular vs plural and all that stuff... just use whatever style is most used in the documentation for a given server type. Unlike in the JavaScript-land I'm most used to you actually have docs for things with examples, so use it.
|
# ? Oct 26, 2023 20:51 |
|
i'd definitely name id columns differently if my db supported USING (columns). this database is multitenanted with tables having a clustered index on (TenantId, Id)*, so i'm real sick of writing out JOIN Foos f ON d.TenantId = a.TenantId AND f.Id = a.FooId ad nauseum * this probably isn't optimal but it tends to work well enough that no one cares
|
# ? Oct 27, 2023 00:13 |
|
I'm far from a seasoned DBA but so far as working in a client onboarding/ETL capacity goes I sure appreciate it when id columns are named thing_id because it makes mapping data from legacy systems more intuitive (ask me about staring into the horrors of a raiser's edge backup file)
|
# ? Oct 27, 2023 00:58 |
|
lmao the database I inherited would give you all nightmares
|
# ? Oct 27, 2023 01:10 |
|
Just-In-Timeberlake posted:lmao the database I inherited would give you all nightmares [laughs in democratic party fundraising and engagement database]
|
# ? Oct 27, 2023 01:15 |
|
Point of sale db in a product with ongoing updates for 30 years, on an SAP db server.
|
# ? Oct 27, 2023 01:56 |
|
Lib and let die posted:[laughs in democratic party fundraising and engagement database] oh poo poo I think I'm one of your users
|
# ? Oct 27, 2023 02:04 |
|
shame on an IGA posted:oh poo poo I think I'm one of your users was. have a backup plan ready.
|
# ? Oct 27, 2023 02:06 |
|
Y'all fools aint seen nothing till you've worked with a database designed by scientists. Horrors without end. "I just copied the field names from excel but for some reason it wont let me use spaces" "I just put the code in a text field and then use pythons exec command to run it" "Ever Their praises, and abundance to the Black Goat of the Woods. Iä! Shub-Niggurath! Iä! Shub-Niggurath! The Black Goat of the Woods with a Thousand Young"
|
# ? Oct 27, 2023 02:57 |
|
duck monster posted:"I just copied the field names from excel but for some reason it wont let me use spaces" SELECT [A], [B], [Adjusted Value (Fixed)-FORMULA-DO NOT EDIT] FROM dbo.[Sheet4];
|
# ? Oct 28, 2023 20:21 |
|
speaking of Excel I was really excited to hear that they were fixing a decades old “feature” and thought it would be no longer automatically converting large numbers into scientific notation when you paste it in. it wasn’t that
|
# ? Oct 28, 2023 20:31 |
|
What's the correct way to convert an interval to a number of days in postgres? I'm generating an interval by doing an "age" difference between two dates:SQL code:
It works, but I'd really like an integer number of days as the "days_to_expiration". Whenever I try to use the date functions I'm just truncating/extracting the date, so I end up with "0 days, 27 days, ..." in that column, which is not what I want.
|
# ? Oct 30, 2023 14:14 |
https://www.postgresql.org/docs/current/functions-datetime.htmlquote:date - date → integer You can't use the AGE function for this since it calculates a calendar difference, including months which can have varying numbers of days, and you lose that information at the age calculation. Just use the regular minus operator. nielsm fucked around with this message at 14:45 on Oct 30, 2023 |
|
# ? Oct 30, 2023 14:43 |
|
Thank you! I figured I was making it too complicated. Works great now. I really appreciate this thread--coming from Django where there was quite a bit of magic going on in the ORM to now knowing when to drop down to Raw SQL has been a game changer. Now I just need to learn it better and not skim the docs so much.
|
# ? Oct 30, 2023 15:51 |
|
Also, if you have two timestamps instead, subtracting them gives you an interval. Once you have an interval you can use e.g. date_trunc to truncate it to whatever precision you want (days, seconds, whatever) or use extract to get at specific fields. Postgres' date and time stuff is really convenient, you can just do stuff like now() + interval '1 month 20 minutes' if you like.
|
# ? Oct 30, 2023 16:02 |
|
yes it is excellent, the only thing i don't like about postgres datetime handling is "timestamp with time zone" (it isn't!) but i think that's the sql standard's fault
|
# ? Oct 31, 2023 05:40 |
|
Back again with a sorting question for postgres. I have the following tables: The options contracts have data for them every day, sometimes from multiple sources. Here's what I'm trying to do:
What I tried so far: SQL code:
where the first two rows I would expect to only see the row with 0.75 moneyness and again between rows 3 and 4 I would expect to see only 1 of those (two different sources have the same IV). I thought based on reading ORDER BY and SELECT DISTINCT ON() only the first row based on (reference_date, moneyness) would survive that but that's not the case. Maybe that's getting caught in the lateral cross join? In my DB both of those exist (it's not just an outer product thing), I just want the one with smallest possible moneyness returned. Adding another SELECT DISTINCT() in the outer table just returns the higher moneyness, the 1.05, so I assume it's not doing the thing I want and I'm clearly missing something. Hed fucked around with this message at 20:54 on Nov 1, 2023 |
# ? Nov 1, 2023 16:58 |
|
I've never seen DISTINCT ON before, but a little research leads me to believe you are selecting the first row for each combination of reference_date and moneyness with no particular order. If you want to get one row per reference_date then only put reference_date in the DISTINCT ON clause. Then do your ORDER BY as you currently are.
|
# ? Nov 1, 2023 21:12 |
|
Hed posted:The options contracts have data for them every day, sometimes from multiple sources. Window functions are almost always the answer for these types of things rather than cross joins. I have a cold rn so I'm not braining good, but something like SQL code:
|
# ? Nov 2, 2023 20:06 |
|
With halloween just been and gone, what's your spookiest database experience?
|
# ? Nov 6, 2023 12:36 |
|
I would blow Dane Cook posted:With halloween just been and gone, what's your spookiest database experience? I mentioned NGP VAN, right? e: "a client needs the disclosure report updated for this batch of thousands of donations." "ok, where do those values live?" "no one knows. make the change in the front-end and see what updates." "I can't change the front end. The batch is closed." "Yeah." Lib and let die fucked around with this message at 14:28 on Nov 6, 2023 |
# ? Nov 6, 2023 14:19 |
|
I would blow Dane Cook posted:With halloween just been and gone, what's your spookiest database experience? "can you hop on a call? a client is having problems connecting to the api" they were doing everything in the database - building json via string concatenation and calling our api by calling curl using xp_ cmdshell. the only reason i heard about it was because they couldn't get any error response codes back from curl because xp_cmds hell doesn't give you the exit code from the thing you're calling (lmao at the something awful WAF blocking posts with xp_c mdshell in)
|
# ? Nov 6, 2023 18:44 |
|
redleader posted:"can you hop on a call? a client is having problems connecting to the api" only the best code for SA
|
# ? Nov 6, 2023 19:28 |
|
cmds hell sounds like an accurate description
|
# ? Nov 7, 2023 02:55 |
|
xp_cmdshell
|
# ? Nov 7, 2023 10:22 |
|
Postgresql. Is there a way to query the total data size queried for a particular user in a given day?
|
# ? Nov 8, 2023 15:24 |
|
you can do that with https://www.postgresql.org/docs/current/pgstatstatements.html
|
# ? Nov 9, 2023 06:48 |
|
DELETE CASCADE posted:you can do that with https://www.postgresql.org/docs/current/pgstatstatements.html That's where I landed but still can't figure out how to do a query that returns the size in bytes of the queries that have been performed. Do you know which parameter would be leveraged for that? The parameters I see all appear to only relate to the time taken for the query to complete and number of rows returned.
|
# ? Nov 9, 2023 16:42 |
Scrapez posted:That's where I landed but still can't figure out how to do a query that returns the size in bytes of the queries that have been performed. Do you know which parameter would be leveraged for that? The parameters I see all appear to only relate to the time taken for the query to complete and number of rows returned. My best guess would be a sum of shared_blks_read + local_blks_read + temp_blks_read , multiplied by the block size, whatever that is. E: Of course that's going to report how much storage the user has touched, not how much data the user has retrieved. Complex queries might touch a lot of indexes and thus a lot of pages, but not return a whole lot of data. For example, a single count(*) of a big join. nielsm fucked around with this message at 17:59 on Nov 9, 2023 |
|
# ? Nov 9, 2023 17:56 |
|
nielsm posted:My best guess would be a sum of shared_blks_read + local_blks_read + temp_blks_read , multiplied by the block size, whatever that is. Thank you for the info. These are fairly simple queries and static so it may be possible.
|
# ? Nov 9, 2023 20:52 |
|
gently caress. I got a task to refactor this query to use NATURAL JOIN without subuqeries..SQL code:
SQL code:
SQL code:
Ihmemies fucked around with this message at 15:13 on Nov 11, 2023 |
# ? Nov 11, 2023 14:43 |
|
Actually what the gently caress these tasks are..Course assignment posted:The task is asking for a non-recursive SQL query that retrieves all pairs of movie IDs and their predecessor movie IDs. For each movie, the query should fetch both its immediate predecessor (if any) and any indirect predecessors. The value in the "level" column indicates the "distance" between the predecessor and the movie in the predecessor-movie hierarchy, i.e., how many levels up the hierarchy the predecessor is found. For example, an immediate predecessor would have a level value of 1. So how I'm going to write an infinitely deep SQL query without recursion? Like I just can't use a "for" loop or anything Hnngh.. poo poo..
|
# ? Nov 11, 2023 15:07 |
|
Ihmemies posted:gently caress. I got a task to refactor this query to use NATURAL JOIN without subuqeries.. Why the altering? Couldn't you just do... SQL code:
No clue what the other task description is about though. Kuule hain nussivan fucked around with this message at 15:19 on Nov 11, 2023 |
# ? Nov 11, 2023 15:15 |
|
Kuule hain nussivan posted:Why the altering? Couldn't you just do... The joke was we must use "NATURAL JOIN". So it's problematic if the tables have a "name" column with completely different contents. Renaming all the columns of the 2nd table helped..
|
# ? Nov 11, 2023 15:18 |
|
Ihmemies posted:The joke was we must use "NATURAL JOIN". So it's problematic if the tables have a "name" column with completely different contents. Renaming all the columns of the 2nd table helped.. Ah, what a piece of poo poo. Had a look at the oracle documentation for it, and it doesn't mention anything about it taking into account aliases in the SELECT statement, but can you check the query plan for it?
|
# ? Nov 11, 2023 15:22 |
|
|
# ? May 25, 2024 14:56 |
|
Heh, mini-Vinnie
|
# ? Nov 11, 2023 17:01 |