8 votes
Accepted

MySQL-to-PostgreSQL migration script

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 ...
  • 18.4k
7 votes
Accepted

Insert data to PostgreSQL

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 ...
  • 17.2k
7 votes
Accepted

Cryptocurrency notifier - let's get rich (or at least let's try)

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 ...
  • 8,673
6 votes

PostgreSQL multiple processes and queries or nested query

Style Your style of writing SQL makes it very difficult to read... for example: ...
  • 20.3k
5 votes

SQL report of subscribers and their associated revenue

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

Gathering distinct patient diagnosis records

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 ...
5 votes
Accepted

Supervisors and employees query, with a subselect and inner join

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 ...
  • 11k
5 votes

MS Access to PostgreSQL converter

First off, if you're not using Python 3.x, you need to explicitly inherit from object like this: class MyClass(object). If you ...
5 votes
Accepted

Searching text, starring, flagging and joins

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: ...
  • 25.2k
5 votes
Accepted

Correct using of try/catch clause on database execution

Some quick remarks: Avoid writing ADO.NET code by hand; instead use an ORM like Dapper. Why not use MAX() -- which I think is supported by PostgreSQL -- instead of ...
  • 11.1k
5 votes

Correct using of try/catch clause on database execution

Just one short note... catch (NullReferenceException e) You never want to catch this exception explicitly. 99.999% of the time its occurance means there is a ...
  • 44.1k
5 votes

Postgres query to extract a JSON sub-object from a JSON field

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/...
4 votes
Accepted

Downloading 2 tables from db, does calculation and uploads resulting table

I know, that you know, that your code is messy, but I'm still going to give some tips on your coding style. Here are a few tips I have on what can be improved there. Don't use ...
4 votes

Fixing logs that overlap

There's really only rather small things I'd change / adapt CREATE OR REPLACE FUNCTION normalize_moderation_log_overflow() RETURNS INTEGER AS Since you always <...
  • 25.2k
4 votes
Accepted

DRYing two very similar SQL conditional inserts

You'll want to create a stored procedure that takes your setting ID ...
  • 663
4 votes
Accepted

Translating code numbers to item descriptions in PostgreSQL query

You should put all the mapping between itemId values and whatever constant strings in a separate table (2 columns) and do a join with it. This allows you to update the mapping (by changing the table ...
4 votes
Accepted

Update SQL database, similar-but-different queries for each month

There are two main problems with this: Firstly, as you've identified, your approach will involve a lot of repeated code. The only thing that changes for each of your SQL commands is the monthid field ...
4 votes

Iterate between dates and INSERT values in a performant way

Instead of a loop which inserts one row after the other you should use generate_series to create all timestamps at once. Untested: ...
  • 1,172
4 votes
Accepted

Historical Funding Rate Miner for ftx.com

Code should be correct, maintainable, robust, reasonably efficient, and, most importantly, readable. Code should be useful and have real value. I found your code hard to read. Your code does not ...
  • 3,386
4 votes
Accepted

Postgres DB design

What probably stands out most is the foreign key in sh_staff_members which also functions as a PK. While inheritance seems like a better tool to represent the is-a relationship between sh_staff_member ...
  • 55.5k
3 votes
Accepted

Database migration script

There's no reason why you can't modify the data retrived from MySQL and still insert multiple rows at once into PostgreSQL, for example something like this: ...
  • 49.4k
3 votes

MySQL-to-PostgreSQL migration script

Yes, you'd be better off doing a function. There's no real need for a class, but a function allows you to just pass in the string command that you need for each part, it could look like this: ...
3 votes
Accepted

Append user input to a database

This drops the table every time you start the application, so previous data is removed, DROP TABLE IF EXISTS test; Are you sure this is what you really want? I ...
3 votes
Accepted

Do you Connect with my Groove?

Defining database connection parameters It is not recommended to hard-code your connection parameters within the script. You could for example parse the database properties from an external Json ...
3 votes
Accepted

Loading 40GB twitter JSON TAR file from archive.org and load into PostgreSQL

Improving throughput If you want to get the best possible throughput, you need to decompose this into a few processes. The decompression can be handled with bzcat...
3 votes

Supervisors and employees query, with a subselect and inner join

You could greatly improve the readability of your query by using a Common Table Expression instead of a sub-query. Then just refer to the CTE like you would a table. Like so: ...
  • 20.3k
3 votes

Argument parser for a PostgreSQL backup utility

You raise many exceptions, but you are no more specific than Exception. You should use a more specific class and give a message of some kind. You give the message ...
  • 3,787
3 votes

Migrate files from MySQL BLOBs to PostgreSQL largeobjects

You don't have any performance metrics, so there is no telling whether this will have a significant effect, but one of the things you could do is combine your queries. Take this one: ...
  • 22.3k
3 votes
Accepted

Asynchronous database query system using futures-rs in Rust

Here are a few shallow comments on the code. This code ...
  • 9,563
3 votes

Splitting a list of numbers into multiple lists

Pretty minor but you don't need the if (row_id == 0) ...
  • 5,946

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