PostgreSQL is an open-source, Relational Database Management System (RDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions. Always include the "sql" tag and possibly the "plpgsql" tag if applicable.
1
vote
0answers
9 views
Alternative of too many case in postgres
My requirment is according to itemId value I need to select a value in postgresssql query. So I am doing like this .
...
0
votes
0answers
35 views
Listing employees, categorized by license expiration date
I am working on a personal project and I have hit a wall. I know I am writing bad code and I really want to refactor the code below. The application has three tables on the same page. Each table ...
3
votes
1answer
22 views
Selecting attributes corresponding to the most frequent entries in a junction table
I currently am writing a query which retrieves the max amount of rows for a junction table. I'll use the following example in SQL Fiddle:
MyTable
...
3
votes
1answer
56 views
Imported data from CSV successfully, but slow
Currently I am using the gem activerecord-import to seed more than 55,000 records into my database, and it works fine. However, it takes too much time. I am ...
4
votes
1answer
115 views
Asynchronous database query system using futures-rs in Rust
Background
I've been working on an algorithmic trading platform in Rust and utilizing the newly released futures-rs library to keep everything asynchronous and non-blocking.
One issue I've come ...
3
votes
1answer
60 views
Getting timeslots using a single query
I want to get latest time slots for 2 cases based on doorman true for false based on logged in user.
Cases are:
If doorman is true, the query will be the same
if doorman is false, then I need to ...
1
vote
0answers
46 views
AWS Redshift wrapper class that automates similar types of loads from S3
I have a AWS Redshift wrapper class that automates similar types of loads from S3 for me, and I have recently adapted it to work for Spark jobs, which don't require a manifest, and instead need a ...
2
votes
1answer
55 views
Managing PostgreSQL transactions for concurrency
I have a situation where I need to use PostgreSQL's serializable isolation level for transactions. This is for a table shared among multiple concurrent PHP processes. If the database runs into any ...
2
votes
1answer
61 views
Checking whether a store is open or not by querying over its business hours
I currently have a method that checks if the store is open. It's working nicely, however on the main page I have quite a few stores. Whom again have different locations. Each of these locations again ...
4
votes
1answer
99 views
Migrate files from MySQL BLOBs to PostgreSQL largeobjects
As title says, this piece of code migrates files (binary and metadata) from a database to another one with different structure.
Currently my problem is that when I have to deal with a big database (...
1
vote
2answers
116 views
Argument parser for a PostgreSQL backup utility
I'm sure it's still not yet perfect and tips will be greatly appreciated!
I just re-read the script and I realised I'm not handling exceptions! The script is unfinished but I'm looking for WIP (work ...
0
votes
0answers
14 views
psql event store
Below are a couple of different files that comprise of example code for a postgres event store, where rows for one main table events are created, as well as a ...
1
vote
0answers
34 views
Generating a Postgres query dealing with creating a time series
I want to sum the number of my published publications model in a given interval and return a time series. (related SO)
The main challenge is that I want the code to respect the time zone passed to it....
4
votes
1answer
22 views
SQL report of subscribers and their associated revenue
A user can have many subscriptions, and a subscription belongs to one plan. The plans table tells us a subscription's cost. Is the query below the correct one for ...
2
votes
1answer
293 views
Redshift Load and Unload Wrapper class
We do constant traffic with our Redshift tables, and so I created a wrapper class that will allow for custom sql to be ran (or a default generic stmt), and can run a ...
2
votes
1answer
142 views
Parsing data from big json field and geocode services then store to PostgreSQL using psycopg2 instead django-orm
This code works, but my boss told me that it's very bad code, and don't want to deploy it. It is my first time working as a programmer and I have at least 3 months of experience.
...
3
votes
1answer
96 views
Find all the countries I can visit by just crossing direct borders
I was helping on this question. There you have a table border to indicate countryA and countryB have a common border. And want ...
4
votes
1answer
54 views
Appending unique data to a postgres table
I receive daily files (filename_%m_%d_%Y.csv) from a client and I read those in pandas, process them, and store them in Postgres. Sometimes there are delays and we do not get the data for a few days. ...
6
votes
1answer
417 views
3
votes
1answer
56 views
Ruby on rails instance loops
I am trying to use a custom sql query to display different attributes for a product i.e. Size and Price. The query I have when running in console displays as it should
...
0
votes
1answer
42 views
1
vote
0answers
39 views
Delivering groups of online reports
As part of my present project, I revived the task to deliver group of online reports. As per a report's complexity and best performance on my radar, I decided to write SQL view on PostgreSQL and next ...
5
votes
1answer
38 views
Gathering distinct patient diagnosis records
This code correctly gathers diagnosis records for a patient to show distinct records based on the AMA ICD9 code, diagnosis description, and date of posting. The output is distinct on (ICD code, ...
2
votes
2answers
189 views
Database migration script
I'm working on a database migration script written in python, which will take data from a MySQL database and insert them into a PostgreSQL database with a different Schema (different table structures, ...
6
votes
3answers
366 views
MySQL-to-PostgreSQL migration script
I'm working on a Python script to migrate a MySQL database into a PostgreSQL database with a different Schema (different table structures, different datatypes and so on).
I'm a sysadmin and ...
1
vote
1answer
220 views
Summarizing income and expense information by month
I made myself a simple budgeting application. Each transaction is stored in the transactions table:
...
3
votes
0answers
199 views
C++ PostgreSQL database driver wrapper
I am writing my own wrapper for PostgreSQL (but it can be used for other engines too).
I am using this desgin:
...
3
votes
0answers
40 views
PL/pgSQL autologin token generation
Here's an excerpt from my migration script for my database for the new "remember me" login option for my web application.
Logging in with a valid username/password combination (authenticate_user) ...
8
votes
1answer
62 views
Searching text, starring, flagging and joins
I have an interesting performance concern that I would like to address before it becomes a serious issue. I created a SQL Fiddle to demonstrate the query, and the explain statement can be seen on ...
6
votes
1answer
61 views
Fixing logs that overlap
Here we trying to fix logs that have overlaps with each other. Am I doing it right? Is there any way to improve/refactor it?
...
7
votes
2answers
94 views
Append user input to a database
This is my first time writing a Swing+Groovy application that successfully inserts data to SQL tables. It may look like example code, but it is more test code, as I will eventually expand this to a ...
7
votes
1answer
114 views
Do you Connect with my Groove?
I'm new at groovy, and this is my first time writing a database connection script. It may seem like example code, but it is more like test code. It works as posted, and I'm planning to use this as a ...
4
votes
1answer
62 views
Downloading 2 tables from db, does calculation and uploads resulting table
I have written this script that downloads two tables from the db, preforms an intersection on them and adds 2 new columns to the resulting table, and uploads the resulting table to the db. It's a bit ...
4
votes
1answer
73 views
Monitoring and re-establishing a PostgreSQL connection
Within a complicated Ruby project, I need to monitor the connection to a PostgreSQL database, as answered here. I use a thread for this purpose. Every few milliseconds, I invoke "consume_input" which ...
3
votes
0answers
43 views
Updating results when an opinion poll is answered
Let's say I am trying to build an opinion poll app, such that I can create a template of an opinion poll, give it multiple sections/questions, assign multiple people to different copies of a given ...
1
vote
0answers
322 views
Save file from request to database
I am saving (image) files to PostgreSQL database in my Node.js and Express server application. The code is used to upload user images to server (at the moment it is not restricted to image files in ...
3
votes
2answers
50 views
Supervisors and employees query, with a subselect and inner join
I want to replace or improve the SELECT command of tblVisor to make it faster.
Is there any way to improve this SQL command?
<...
1
vote
1answer
51 views
Bookmaker odds and offers query
The following query returns the latest Odds for each Offer based on the timestamp on the Odds...
2
votes
0answers
75 views
Fetching photos and tweets related to selected publications
1) Postgresql
2) User has_many :reports
3) Report belongs_to :user
3) Report belongs_to :publication
4) Publication has_many :reports
4) Publication scope :photos, -> ...
7
votes
1answer
190 views
MS Access to PostgreSQL converter
I was hoping to get some feedback on the implementation of this class for programmatically converting an access file into a PostgreSQL schema (works by obtaining schema data from the cursor object).
<...
5
votes
1answer
296 views
PostgreSQL multiple processes and queries or nested query
I have a series of tables and queries I run a process against and at times this process can take hours - if not multiple days - depending on the date range I select. I am running PostgreSQL 9.4.
In ...
15
votes
1answer
274 views
Complicated article sort
In short I have a bunch of articles (~100k) which shall be ranked ("mixed", hence "newsmix") based on their newsValue (how important they are, between 1 and 10) and newsLifetime (how long they stay ...
2
votes
0answers
240 views
Function to split a given string according to predefined patterns
Here's a function that splits given string according to predefined patterns. It's part of a trigger function used to populate one table from another. I want to be sure that I'm not making any mistakes....
3
votes
2answers
2k views
Loading 40GB twitter JSON TAR file from archive.org and load into PostgreSQL
I wrote the script below to load data obtained from the twitter JSON archive on archive.org into a PostgreSQL database.
I'm looking for optimizations in the code. It currently runs at ~1.7 seconds ...
6
votes
4answers
124 views
Using PostgreSQL effectively with a banking app
I'm kind of newbie for these things but I did something and I want to know how I can do this effectively.
...
2
votes
1answer
252 views
Simple SQL query to get phrase pairs from a translations table
This is probably the noobiest SQL question ever, and I'm almost positive there's a JOIN or something in PostgreSQL for this, but I can't find it so I'm using a ...
2
votes
1answer
100 views
Building query for search engine
I'm writing a basic search engine for my website.
It works but I'm really unhappy with the code. I know it can't be improved but I'm lacking some experiences here.
In my params from my search I get:
...
12
votes
3answers
1k views
Trading Card Game database schema for statistics
This database schema will be used in conjunction with various systems as related to an online Trading Card Game, mostly for permanent storage and statistics. Some example cases would include:
JDBC ...
2
votes
1answer
42 views
Sorting of nested categories inside a Common Table Expression
I have a PostgreSQL database which contains (among other things) the following table definition:
...
6
votes
2answers
143 views
What are the latest Odds?
The main problem the following query is solving, is the following:
Each Offer has multiple Odds (...
