|
https://discord.gg/fMXvTF
|
# ? May 16, 2019 14:13 |
|
|
# ? Jun 4, 2024 20:15 |
|
Say I have a relational table called ingredients_in_cocktails that have 2 primary/foreign keys: Cocktail ID and Ingredient ID so a row might look like: code:
code:
teen phone cutie fucked around with this message at 03:19 on May 26, 2019 |
# ? May 25, 2019 18:51 |
|
Grump posted:Say I have a relational table called ingredients_in_cocktails that have 2 primary/foreign keys: Cocktail ID and Ingredient ID can you insert duplicates now? if not, you already have a unique index on the table and adding a new column and making it a primary key won't help you. you can drop the existing unique index (and recreate it as non-unique if you need it for query performance) and create the new column as a primary key
|
# ? May 25, 2019 19:38 |
|
Yeah, just make ID the primary key and unique. Then you can query like: code:
EDIT-This assumes you know what the cocktail is. Upon review of your app I'm guessing you'll also want to query by ingredient but maybe not return the extra rows.
|
# ? May 26, 2019 00:36 |
|
Grump posted:Say I have a relational table called ingredients_in_cocktails that have 2 primary/foreign keys: Cocktail ID and Ingredient ID You don't really want to do that. Either ingredients are unique or recipes are ordered. In the first case, "lime juice" should be a different ingredient than "lime wedge". In the latter case, much like with baking, "sugar" appears multiple times and the actual distinguishing characteristic is the 'order' of construction. Your composite key in that case will be recipe=(cocktail,step number), and just the foreign key constraint recipe.ingredient references ingredient.id. As an aside, that table name is a tragedy. Try "cocktail", "ingredient", and "recipe".
|
# ? May 26, 2019 01:05 |
|
Ok so i need to create the ID column as a normal row, THEN remove the unique index, THEN give my ID column the unique index?PhantomOfTheCopier posted:You don't really want to do that. Either ingredients are unique or recipes are ordered. In the first case, "lime juice" should be a different ingredient than "lime wedge". I do have a "step" column in that table - I just didn't post it. I actually am solving for the problem where you need to wipe the rim of a cocktail glass with a lemon twist and then throw the lemon twist into the glass, similar to your sugar example. Scaramouche posted:Yeah, just make ID the primary key and unique. I'm assuming (and hope) my existing queries should be fine teen phone cutie fucked around with this message at 03:23 on May 26, 2019 |
# ? May 26, 2019 03:11 |
|
Frankly speaking you don’t need to do any of that. You don’t even need a new column. Just remove your PK and recreate it as a non-clustered non-unique index. Is this a good idea? Well... you probably want some unique way to identify your row, so probably not. Your new ID column should probably be a the only column in a unique clustered index and it should have an auto-incrementing identity value. Basically a default primary key.
|
# ? May 26, 2019 03:23 |
|
hmmm I seem to be running into some errors while doing this. Mainly because both cocktail_id and ingredient_id are foreign keys. When running something like: code:
And when trying code:
I am very much a SQL newbie, so I could use some hand-holding on this.
|
# ? May 26, 2019 06:37 |
|
Probably this: Drop the foreign keys Drop the indexes Add the pk Recreate the indexes Recreate the foreign keys
|
# ? May 26, 2019 14:42 |
|
Grump posted:I actually am solving for the problem where you need to wipe the rim of a cocktail glass with a lemon twist and then throw the lemon twist into the glass, similar to your sugar example. Consider: Do you really, really want to model connected steps between rows? What is the benefit? What queries do you hope to support with such complexity? (And it may not matter, but do you really think what you're trying to build is what all those recipe sites use?) "Place 1c crushed ice into the glass obtained in step 5". "Fill glass with water". ... "Taking the now chilled glass from step 11, dump the ice (from step 9, not the ice you put in the shaker!!) and the water (from step 10), into the sink located in step 3". And what happens when the step numbers are updated? I still say this approach leads to madness. But this is all a trick. I'll bet you're really doing a homework problem to write instructions for making a peanut butter and jelly sandwich.
|
# ? May 27, 2019 03:03 |
|
You don’t need the ordinal/step to have meaning or appear. The steps could just say “wipe the rim with the lemon and save the lemon for later” and “put the lemon you saved into the drink” or w/e.
|
# ? May 27, 2019 03:25 |
|
Ruggan posted:You don’t need the ordinal/step to have meaning or appear. The steps could just say “wipe the rim with the lemon and save the lemon for later” and “put the lemon you saved into the drink” or w/e. This is a good point. I'd probably be better off just adding a "step text" column or something like that. Thanks for that. I think I'm over-complicating this. PhantomOfTheCopier posted:But this is all a trick. I'll bet you're really doing a homework problem to write instructions for making a peanut butter and jelly sandwich. Or I'm a front-end developer trying to learn SQL and Python in my spare time jesus loving christ
|
# ? May 27, 2019 05:27 |
|
I realize this is arm-chair architecting so if it comes across as wrong-headed I apologize, but it sounds like you need a different class of "ingredients" that are more like "instructions"?
|
# ? May 27, 2019 18:40 |
|
If you want to over-engineer it, you should have Coctail ID | Ingredient ID | Step Number | Action as your table with a PK on Coctail ID | Ingredient ID | Step Number. That way you can have the same ingredient for multiple actions at different points of the recipe. If you want to really over-engineer it, have a FK to an action-table with a list of predefined actions. If you want to really really over-engineer it, add an amount column and a unit column, where the unit column is a FK to a unit-table.
|
# ? May 27, 2019 18:53 |
|
Grump posted:This is a good point. I'd probably be better off just adding a "step text" column or something like that. Thanks for that. I think I'm over-complicating this. Scaramouche posted:I realize this is arm-chair architecting so if it comes across as wrong-headed I apologize, but it sounds like you need a different class of "ingredients" that are more like "instructions"?
|
# ? May 28, 2019 15:30 |
|
I posted redacted a readacted version of my table. Here's what I actually have Cocktails: code:
code:
code:
|
# ? May 28, 2019 16:45 |
|
Grump posted:I posted redacted a readacted version of my table. Here's what I actually have What if you have a step that doesn't involve ingredients (Shake, Rest, Heat, Apply Smoke Infusion, etc.)? What if you need to prepare some of the ingredients in a separate recipient, and then add the mixture all at once? I've modelled food production systems in relational DBs a few times. For something straightforward (read: no variations or improvisation allowed) you could use something like this: code:
NihilCredo fucked around with this message at 17:43 on May 28, 2019 |
# ? May 28, 2019 17:40 |
|
Like I said, I would remove "step" information from CocktailIngredients and put it in a separate table. An ingredient can be used in multiple steps, and a step can involve multiple ingredients or no ingredients at all. E.G. when you stir a Martini, you're not stirring any particular ingredients (maybe you could say you're stirring all of them). E: F,b. E2: If you just want something simple, on the cocktail itself, instead of the "Finish" column, I'd have a varchar(4000) "Instructions" column Jethro fucked around with this message at 19:49 on May 28, 2019 |
# ? May 28, 2019 18:29 |
|
yeah these are all good points. tbh, I'm not really looking to over-engineer this thing. It's just a fun little portfolio project to prove I can work with backend tech. They're probably all stretch goals I can accomplish if I ever get bored.
|
# ? May 28, 2019 18:42 |
|
Disclaimer: When you see an emote like that, it's a joke. But like all jokes... have you ever tried writing instructions for peanut butter and jelly? Disclaimer: I've done the this exercise. Cocktails, recipes, etc. Back in the days when a negation query wasn't optimised in PostgreSQL and I hanged the poor thing looking for drinks with A and B and not C. Amazing how hard the problem can be made, eh? I still think the 'ingredient' table should be the aggregates (14 limes) even if the recipe uses them in different steps (13 lime mix, 1 for garnish). Disclaimer: I don't run a cocktail recipe website. Fun exercise though.
|
# ? May 28, 2019 22:53 |
|
What are some good places to learn how to use SSIS/AS/RS? Just had a phone interview where it won't be progressing because the company don't have time to train me to use them
|
# ? Jun 3, 2019 15:49 |
|
Jose posted:What are some good places to learn how to use SSIS/AS/RS? Just had a phone interview where it won't be progressing because the company don't have time to train me to use them I'm only familiar with SSRS, but it's pretty basic stuff if you already know SQL and are at all familiar with visual studio. I'd suggest just playing around with it if you have access to VS and can toy with queries and presentation The query syntax is almost identical to T-SQL, with a few notable differences, the most important one being variable names are case sensitive in SSRS Other than that I'd say youtube some tutorials on how to play with row/column grouping and go from there
|
# ? Jun 3, 2019 16:03 |
|
So I have less of an SQL question and more of a development approach question. I've moved from a Medicare Advantage company that used SAS to one that's using SQL - SSMS, SSRS, etc. My first task is to set up the Payment Year 2020 Model in SQL. To anyone who isn't familiar with risk adjustment models (aka everyone), the way the model works is that a SAS dataset of patient information and their diagnosis codes are fed into the model, the patients are grouped into demographic categories (i.e. "Male, institutionalized, 74-79"), the diagnoses grouped together condition categories (i.e. "Diabetes with Complications") that are assigned to the patients, and then a risk score for each member is calculated based on their demographics and conditions. There's some extra elements where some co-morbidity and the number of conditions increase the score, but that's the gist. My thought is that, well, I'll re-make the SAS program macros step-by-step using tables and queries. It'll take a bit of work to implement, but not particularly difficult to figure out, and it has the advantage that I can justify each step in my calculations by pointing to model in SAS. But is that the best way to approach this problem? It feels a bit crude to take a SAS block and force it through a SQL hole, if that makes any sense, but I also don't want to make this problem harder than it has to be.
|
# ? Jun 11, 2019 02:02 |
Tibalt posted:So I have less of an SQL question and more of a development approach question. I always prefer a natural language business logic reference provided by or worked out with the customer/PM and then starting from scratch on the actual implementation in cases like these. Your proposed approach is how we're currently paying all our technical debt at my current workplace, and in most cases it means there wasn't any real benefit to switching Joda fucked around with this message at 12:57 on Jun 15, 2019 |
|
# ? Jun 15, 2019 12:51 |
|
kumba posted:I'm only familiar with SSRS, but it's pretty basic stuff if you already know SQL and are at all familiar with visual studio. I'd suggest just playing around with it if you have access to VS and can toy with queries and presentation I don't have VS currently. is it possible to get trial/local versions like I did with SQL server?
|
# ? Jun 18, 2019 10:45 |
|
Jose posted:I don't have VS currently. is it possible to get trial/local versions like I did with SQL server? I'm not familiar with the free versions but it appears so https://visualstudio.microsoft.com/free-developer-offers/
|
# ? Jun 18, 2019 13:29 |
VS Community is basically VS Pro but for free.
|
|
# ? Jun 18, 2019 18:49 |
|
Shy posted:VS Community is basically VS Pro but for free. For teams (or companies?) less than 5 people.
|
# ? Jun 18, 2019 18:58 |
|
lol poo poo i forgot that the import/export wizard is part of SSIS so I've not been talking about it in that sense with jobs despite making extremely heavy use of it in my last role
|
# ? Jun 25, 2019 14:00 |
|
What options do I have to copy a few PostgreSQL tables to Redis and keep those schemas up-to-date in Redis whenever a change occurs in PostgreSQL? It's for keeping mail lookup tables in PostgreSQL in sync with Redis for rspamd to determine handling preferences. I see there's Apache Storm, but anything that's specific for this case?
|
# ? Jun 26, 2019 01:19 |
|
Maybe the Redis foreign data wrappers.
|
# ? Jun 26, 2019 03:38 |
|
PhantomOfTheCopier posted:Maybe the Redis foreign data wrappers. That's plan B. It'll allow me to push data from Postgres into Redis as a separate query, in which case I'd just run separate Postgres + Redis queries since it also has to work with 9.2 to 12 and beyond. Downside is it won't capture queries issued directly to the tables without a trigger. There's also no guarantee the host will be running rspamd + Redis, so it has its limitations. I'm looking for something that'll arbitrate data between Redis (read-only) and Postgres (read-write) and monitor Postgres for changes from a selection of tables. Almost like replication but concerned with the data not the queries that result in the data. It doesn't need to be real-time either, 30 second lag is fine. Postgres has a pub/sub facility that'll fit the bill in a flexible manner. Emit a pg_notify() whenever a record is updated then feed the changes to Redis through a separate subscriber. Desync is still possible if the subscriber goes down, which I'd like a clean approach.
|
# ? Jun 26, 2019 17:07 |
|
There’s also the logical decoding stuff in postgres as of a couple of releases ago. It’d be an efficient thing to use, but there’s probably nothing off the shelf that’ll do exactly what you want, so it’d be on you to write something that uses it and updates redis.
|
# ? Jun 26, 2019 19:38 |
|
nem posted:I'm looking for something that'll arbitrate data between Redis (read-only) and Postgres (read-write) and monitor Postgres for changes from a selection of tables. Almost like replication but concerned with the data not the queries that result in the data. It doesn't need to be real-time either, 30 second lag is fine.
|
# ? Jun 26, 2019 22:12 |
|
So I'm completely at a loss: Sometimes my PostgreSQL tables get into a state where a simple DELETE...WHERE query gives completely unexpected results.code:
Here's the structure of the table: code:
I cannot easily reproduce this by creating and populating a new table, or even by pg_dump'ing a bad table and restoring that. But it happens regularly after running some software that uses this table for a while.
|
# ? Jun 27, 2019 19:49 |
|
Olly the Otter posted:So I'm completely at a loss: Sometimes my PostgreSQL tables get into a state where a simple DELETE...WHERE query gives completely unexpected results. I haven't ever used PostgreSQL, but it looks as though it's evaluating the condition in the WHERE clause separately for each row of the table. If rows are evaluated for the outer query in order of increasing tid and the delete is actually carried out for a row before the WHERE clause for the next row is evaluated, that's going to result in your table being cleared. If it does this unpredictably then I guess it sometimes doesn't evaluate rows for the outer query in order of increasing tid. It sounds like what you want is for the set of rows to be deleted to be evaluated once, rather than for each row. Maybe you can make that happen either by (1) computing the tid to be deleted in a separate statement, (2) using a CTE, or (3) putting the subquery in the FROM clause rather than the WHERE clause?
|
# ? Jun 27, 2019 20:53 |
|
I'd be a little shocked if that was the case, since that sounds like the Halloween problem - and I wouldn't expect that in Postgres. Does EXPLAIN shed any light on it?
|
# ? Jun 27, 2019 21:39 |
|
I'd be astonished if it's actually running the subquery multiple times, with row deletion happening in between. Is there a query I could try that might confirm this behavior deterministically? I tried EXPLAIN, but I'm not very good at reading the output. I can tell that it's basically saying the query's inefficient due to the bad primary key, but not sure what else is helpful here. code:
|
# ? Jun 27, 2019 22:30 |
|
Olly the Otter posted:I'd be astonished if it's actually running the subquery multiple times, with row deletion happening in between. Is there a query I could try that might confirm this behavior deterministically? No idea. I created 2 SQL fiddles: http://sqlfiddle.com/#!17/7964c/1 <-- uses your form of query http://sqlfiddle.com/#!17/7964c/3 <-- uses PostgreSQL's "using" multi-table delete syntax Both give the same result but I suspect, given the behaviour you report, that for the first one this is purely down to the order in which the database engine evaluates the rows of the table. Have you tried something like that second delete query?
|
# ? Jun 27, 2019 22:43 |
|
|
# ? Jun 4, 2024 20:15 |
|
The second query form doesn't seem to make any difference:code:
code:
code:
Olly the Otter fucked around with this message at 22:56 on Jun 27, 2019 |
# ? Jun 27, 2019 22:48 |