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.

learn more… | top users | synonyms (1)

1
vote
0answers
27 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 ...
3
votes
1answer
16 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
45 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 ...
1
vote
1answer
35 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. ...
2
votes
1answer
78 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
44 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
111 views
3
votes
1answer
51 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
35 views

DRYing two very similar SQL conditional inserts

I have two very similar SQL statements ...
1
vote
0answers
38 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
27 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, ...
0
votes
0answers
21 views

Form Builder for RESTFUL API

I have a web app which requires a team leader to be able to add additional text fields of their choice whenever they add a new user. So for example, by default my app would have the following fields ...
2
votes
2answers
80 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
209 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
85 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
119 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
38 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
56 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
83 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
85 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
56 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
50 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
41 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
203 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
45 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
45 views

Bookmaker odds and offers query

The following query returns the latest Odds for each Offer based on the timestamp on the ...
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
145 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
239 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 ...
14
votes
1answer
265 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
187 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 ...
3
votes
2answers
1k 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
122 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
140 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
82 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
916 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
37 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
138 views

What are the latest Odds?

The main problem the following query is solving, is the following: Each Offer has multiple Odds (...
7
votes
3answers
129 views

Step 2: Creating functions the business tool will use

I've been working on this project and here is my first function for step 2. I feel this is the most important one, as I will carry over recommendations from this ...
8
votes
2answers
81 views

Benchmarking of SQL data crunching using a common datum

@nhgrif and I were curious about a couple different ways to aggregate a number of records based on a duplicate/common datum. Two different ideas will be shown. Any advice on all code but especially ...
5
votes
0answers
424 views

PostgreSQL datatypes for libpq in c++

Following on from my earlier question: Very simple PostgreSQL ORM in C++ using libpq (tldr; I'm creating a set of helper classes for working with libpq) I've decided to have another attack at the ...
8
votes
1answer
732 views

Very simple PostgreSQL ORM in C++ using libpq

I'm working on a set of helper classes for working with libpq in C++ and I want to be able to pass in objects to the helper classes and have them internally represented as strings when sent to the db, ...
11
votes
3answers
164 views

Revision 2 - Step 1: PsychoProductions management tool project

This is another revision of my Psycho Productions database, this time refactored from MySQL to PostgreSQL, after I found out about some of the shortcomings of MySQL. The following is a (simplified) ...
35
votes
2answers
3k views

Tired of FizzBuzz yet?

There have been many FizzBuzz questions lately, and here is one more! Granted, it is pretty straightforward to do FizzBuzz with SQL using calculations. So instead, I'm going to do a FizzBuzz which ...
6
votes
1answer
89 views

Is there a way to optimize this aggregate postgreSQL statement by using CASE?

I have a working SQL statement which shows the following: Player Champion Role Total Kill/Death Ratio Total Kill+Assist/Death Ratio Total Win % Difference between Ratio from one month ago Difference ...
3
votes
1answer
71 views
12
votes
1answer
327 views

Tic-tac-toe in SQL with optimal AI

The simplest introduction to this code is to play it! Here's an SQL Fiddle. However, to enjoy it fully, you'll need a more interactive environment, like the psql ...
3
votes
1answer
205 views

Taking data from staging table and inserting into target tables

I have the below mentioned function written in plpgsql which is working fine. I want someone to review it and let me know how can I improve its performance. This function takes data from staging ...
14
votes
4answers
550 views

Finding person with most medals in Olympics database

I have an Olympics database from each Olympic year and I want to find the person that has won the most medals. The main problem is that I'm basically querying the same sub-query twice in ...