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
1answer
29 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
32 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:
...
2
votes
0answers
37 views
Multi-Table Query Gotchas and Standards [closed]
Does my SQL statement contain any gotcha's or can you see any problems where it could fail?
Below is my first endevour into multi-table querying in PostgreSQL and I have a feeling that I may not be ...
3
votes
0answers
25 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
56 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
54 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
60 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
45 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 ...
3
votes
1answer
47 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
36 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
37 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 ...
0
votes
0answers
90 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
41 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
42 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
74 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
74 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
140 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
258 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
151 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
532 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
109 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
65 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
66 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:
...
11
votes
3answers
635 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
30 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
131 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
127 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
72 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
276 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
526 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
150 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) ...
33
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
82 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
67 views
11
votes
1answer
211 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
141 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
521 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 ...
5
votes
2answers
478 views
How to properly call an “upsert” using parameterized raw SQL to Postgresql in ActiveRecord?
I need to call an upsert to my Category table in PostgreSQL. My current solution uses the exec_query API in ActiveRecord, using ...
2
votes
2answers
79 views
5
votes
2answers
3k views
SQL query where id=… or id=… or id=… etc
I haven't had much experience with postgresql (none) and I am wondering/hoping that there is a better way for me to do this query.
...
6
votes
1answer
254 views
Help optimizing this query with multiple where exists
In the application I'm building, the user is able to define 'types' where each 'type' has a set of 'attributes'.
The user is able to create instances of products by defining a value for each ...
2
votes
1answer
2k views
Faster way to update/create of a large amount of records?
In our Rails 3.2.13 app (Ruby 2.0.0 + Postgres on Heroku), we are often retreiving a large amount of Order data from an API, and then we need to update or create each order in our database, as well as ...
1
vote
1answer
212 views
Importing Markdown Files - Code Efficiency?
I'm building a rails app that, among other things, imports text markdown files as blog posts. The idea is that the truth is in the markdown files, so any time those are edited, created, or deleted, ...
1
vote
1answer
275 views
Automating the install of PostgreSQL from Shell
When my Debian server deploys it can run a shell script so I wanted to make one to install postgreSQL, create a role, create two databases and then import a schema into one.
Can anyone please look ...
1
vote
1answer
128 views
Adding new or existing tags to a blog post
I'm working on a simple blogsystem (for learning purposes, not to reinvent the wheel). In this system the user can add existing or new tags to a blogpost. I wrote a method to achieve this, but it's ...
5
votes
1answer
119 views
2
votes
1answer
215 views
Summing different tables values limiting the results
I have two tables (a and b) which have both one date field of type ...
5
votes
1answer
1k views
Generic method for database calls
Background
Breaking from MVC, I've implemented the following architecture:
POST/GET ➤ PHP ➤ Database Calls ➤ XML ➤ XSLT ➤ HTML
All database interactions are ...
5
votes
1answer
249 views
HasPostgres instance for IO with Snap framework
I want to write my database access code for a Snap application in a way that makes the queries easy to test from the ghci repl, while also working within the Snap ...
6
votes
1answer
3k views
Get all recursive dependencies of a single database object
I've written a SELECT statement that creates a List of all objects that depend on a single object, so that if I wanted to DROP that object I could DROP all referenced objects first without using ...