30

Proliferation of tables Database schemas should not be designed such that you need to create more tables to accommodate more data. The same principle applies here. Instead of separate tables Fizz, Buzz, Woof, Ping, and Plop, you should have a single NoiseDefs table. A classic unparameterized FizzBuzz should be much simpler… WITH NoiseDefs(multiple, ...


18

I think your scripts could use a bit more vertical whitespace; maybe it's the uppercasing, but I find this: BEGIN SET SEARCH_PATH = PhrancisFizzBuzz; -- call populate function with input parameters PERFORM fncPopulateFizzBuzz( prmMaxNumber, prmFizz, prmBuzz, prmWoof, prmPing, prmPlop ); DROP TABLE IF EXISTS tmpFizzBuzzResults; CREATE ...


12

The way to write that query is: SELECT * FROM member_copy WHERE id IN (17579, 17580, 17582); However, the real question is, where did that list of ids come from? If the list of ids is the result of another database query, then you should be doing either a subselect or a join instead.


11

This alternative to @rolfl's answer is more readable, in my opinion. It also has a more efficient execution plan. WITH medal_count AS ( SELECT athlete , SUM(total_medals) AS grand_total_medals , RANK() OVER (ORDER BY SUM(total_medals) DESC) AS rank FROM Olympics GROUP BY athlete ) SELECT athlete , ...


10

In PostgreSQL, you can use the rank() mechanism to help. It still requires a subselect, but consider the following query: Select o.athlete, sum(o.total_medals) as sumtotal_medals from Olympics o, ( select r.athlete as toprank, rank() over ( order by sum(r.total_medals) desc ) as rank from Olympics r group by r....


10

You can reduce the amount of queries by putting it into a transaction: ActiveRecord::Base.transaction do ... end This wont reduce the amount of queries but will do them all at once which will save it doing the commit step for each time it has to do the query. Note, that if one of them fails, the transaction will normally be rolled back. Bulk importing ...


10

I really don't see much to comment on here. The code was easy to read, well structured, and the code style was, for SQL relatively easy on the eyes, and consistent, which is good. One inconsistency I see is: create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), delete_date TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NULL, last_seen_date TIMESTAMP NULL yet ...


10

Games and Players I imagine that one of the most common statistics you would want to display is: "What are my win statistics against player X?". For such a query, the table game_master is really not optimized for it. Let's say that you want to make a query for "Find all game_ids that the user id 6354 has played in" SELECT game_id FROM game_master WHERE ...


8

I disagree with the recommendation to use an ENUM like 200_success suggests. Your first choice should always be to use a reference table (like the OP currently has). ENUM types are dangerous to modify (see: http://postgresql.1045698.n5.nabble.com/Problems-with-ENUM-type-manipulation-in-9-1-td4844778.html) and the information ends up stored in a table ...


8

There is a bug in your second version. Here is one row from the result: 1;0;2;0 That is, Serial_Number 1 has zero ones, two twos, and zero threes. But let's do a quick sanity check: select count(*) from seeded where serial_number = 1 and status = 'one'; This query returns 100000. There are also rows with the same Serial_Number, which I think is not ...


7

I do not see anything immediately wrong with your addUser() and getUser() implementations. Your hash() implementation could be improved in two areas though. Salt As already indicated by Bill Barry in the comments, your should use a better salt. Some rules to keep in mind when handling salts, Salts should be unique per password. Every password should have ...


7

This code is fine. As far as scripts go, I'm assuming this is something that is run once and then forgotten about. But of course, in the interest of code review... Don't Repeat Yourself You have seven blocks of code that look something like: cur_msql.execute("SELECT ...") for row in cur_msql: try: cur_psql.execute("INSERT INTO ...", row) except ...


7

Do not use string formatting to pass parameters to SQL queries. This way you are not only getting type conversion and quotes balancing/escaping issues, but also making your code vulnerable to SQL injections. Mandatory XKCD: (source) Instead, parameterize your query - put placeholders into the query and let your database driver worry about the Python to ...


7

I know I don't have docstrings and that my API keys shouldn't be stored in my program (the same for DB credentials), so try to avoid these aspects when reviewing my code. Just mentioning even though you've asked not to. Let the API keys and DB values be fetched from environment variables. For DB vars, I prefer creating a dict like: DB_DICT = { '...


6

I'm not familiar with PL/SQL so I'm unsure what exactly is being done with it. However, the commonly accepted solution for sharing between PHP and JavaScript is to use a JSON file. JSON is native to JS, its even in the name. PHP also supports JSON via its json_encode() and json_decode() functions. Here's an example of what the JSON file might look like: { ...


6

I will give you kudos on being very explicit in your programming. Here are my thoughts: Not performance related but still a factor, I would suggest that you remain consistent with your casing of key words. Sure SQL is not case-sensitive, but it makes the code easier to read especially in my opinion if SQL key words are all caps. Again likely not ...


6

A test with EXPLAIN ANALYZE VERBOSE will show you that the form with id IN (...) in the answer of @200_success is transformed internally into: SELECT * FROM member_copy WHERE id = ANY ('{17579, 17580, 17582}'); .. which therefore performs slightly faster to begin with (no conversion needed). Also, the form in your question will effectively perform very ...


6

I think that you are over thinking this, in SQL Server I would do something like this SELECT TOP (10) athlete FROM ( SELECT athlete, Sum(total_medals) AS total_medals FROM Olympics ORDER BY total_medals DESC GROUP BY athlete) And then I would use my Reporting Software to decide if there are 2 or more people at the top. This is ...


6

Style Your style of writing SQL makes it very difficult to read... for example: INSERT INTO tempresults( lmp_date, approved_lmp_name_a, approved_lmp_name_b, path, approved_a_pnode, approved_b_pnode, approved_a_sink, approved_b_sink, approved_a_source, approved_b_source, lmp_da_a, lmp_da_b, ...


5

I'm a little late to the party, but I think you can make it less complicated. Wouldn't this be what you need: SELECT athlete FROM Olympics GROUP BY athlete ORDER BY SUM(total_medals) DESC LIMIT 1 Here is the obligitory SQL Fiddle. EDIT: Previous version didn't account for multiple people with the same number of medals. SELECT athlete FROM Olympics ...


5

Your query doesn't do what you think it does. It discards the original ordering when it re-sorts. It might, depending on the sorting algorithm chosen, happen to come out how you want, but there's no guarantee. I am using sub query because i want sort created_on DESC and email ASC If that's what you want, just write: ORDER BY created_on DESC, email ASC ...


5

SQL allows you to join the same table multiple times, so what you need here is: SELECT p.id FROM products AS p JOIN attributes_products AS ap1 ON ap1.product_id = p.id AND ap1.attribute_id = 27 AND ap1.value = '...' JOIN attributes_products AS ap2 ON ap2.product_id = p.id AND ap2.attribute_id = 28 AND ap2.value = '...' JOIN attributes_products AS ap3 ...


5

Statement, formatted readably: WITH sel AS ( SELECT id FROM categories WHERE name=$1 AND source=$2 ), ins AS ( INSERT INTO categories (name, source, created_at, updated_at ) SELECT $1, $2, 'now', 'now' WHERE NOT EXISTS (SELECT * FROM sel) RETURNING id ) SELECT id FROM ins UNION ALL SELECT id FROM sel; Dummy table: CREATE TABLE categories ( ...


5

DROP SCHEMA IF EXISTS will fail if there's anything in the schema. Either use DROP SCHEMA IF EXISTS … CASCADE (a bit bold and risky), or don't include it in the script at all. A you have a lot of SERIAL–TEXT tables with a few fixed values. Those should just be enumerated types in PostgreSQL. Otherwise, you end up with a lot of annoying little joins. It ...


5

First off, if you're not using Python 3.x, you need to explicitly inherit from object like this: class MyClass(object). If you are using Python 3.x, then you're fine. If you're going to format your queries based on user input, then the following piece of code may have certain issues, like SQL injection. See this for more information. SQL = """ CREATE TABLE ...


5

Unless I'm missing something there doesn't seem to be a reason for the subselect, just join all three tables directly. Also, INNER is the default, so you could drop that prefix as well. The query would then become something like the following: SELECT cp_supervisor_properties.supervisor_id, persons.last_name, persons.first_names, ...


5

The way you join is the problem. Okay now that's out the way let me explain. These left joins you're doing are bloating your result set: LEFT JOIN favorite_translations FT ON T.translation_id = FT.translation_id The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL ...


5

As far as I can tell, CTE's defined before the LOOP do not transfer to the LOOP itself. However, a temporary table can be defined in the BEGIN block which is available in the LOOP block. The following solution runs 50 times faster then my original code. Anybody have a better approach? CREATE OR REPLACE FUNCTION test2 (patient_recid integer, tencounter ...


5

I think this looks very good, but I would make a few small changes: Instead of getting separate columns for month and day, use one column with date_trunc('day', subscriptions.created_at). Then you still have a timestamp that you can format, sort by, etc. Also you will avoid problems when someone reports on a range crossing New Years. Be aware that binning ...


5

I'd recommend (if it isn't already) converting to the type from json to jsonb and adding a GIN index. The documentation below lays it out pretty clear. Good luck! https://www.postgresql.org/docs/9.4/datatype-json.html For my recommendation, reason for using jsonb as opposed to vanilla json is that regular json is stored as an exact copy in postgres and you ...


Only top voted, non community-wiki answers of a minimum length are eligible