Questions tagged [postgresql]

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.

Filter by
Sorted by
Tagged with
45
votes
2answers
4k 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 ...
15
votes
4answers
891 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 ...
15
votes
1answer
1k 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 ...
15
votes
1answer
291 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 ...
12
votes
1answer
1k views

Cross-language constants

I'm developing a web application that leverages multiple programming languages. The data flow resembles: Browser » PHP » PL/SQL » XML » XSLT » XHTML + JavaScript » Browser Using different languages ...
11
votes
3answers
2k 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 ...
11
votes
3answers
177 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) ...
11
votes
2answers
10k views

Node.js password salting/hashing

In view of the recent LinkedIn breach, would anyone care to review my data access routines relating to user access? This is a standard node.js module, accessing a Postgres database. ...
10
votes
1answer
247 views

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

We all know that in the last period cryptocurrencies have ushered the era of the next financial revolution. Having that in mind, I thought it'd be useful to know (using Python 3.6) when a certain ...
8
votes
3answers
10k views

SQL: Search for a keyword in several columns of a table

I want to perform a search in several columns of a table. I use the following query: ...
8
votes
2answers
103 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 ...
8
votes
2answers
827 views

Extracting nodes from a graph database

I'm new to Postgres and PostGIS, but not to geospatial applications. I have a table loaded up with graph data in the form of links (edges). The link database has about 60,000,000 rows. I am trying to ...
8
votes
1answer
67 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 ...
8
votes
1answer
1k 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, ...
7
votes
3answers
23k views

SQL query where id=… or id=… or id=… etc [closed]

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. ...
7
votes
3answers
2k 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 ...
7
votes
1answer
12k 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 ...
7
votes
1answer
574 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). <...
7
votes
3answers
135 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 ...
7
votes
2answers
105 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
176 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 ...
7
votes
1answer
4k views

Run query and insert the result to another table

...
7
votes
1answer
8k 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 ...
6
votes
4answers
177 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. ...
6
votes
2answers
175 views

What are the latest Odds?

The main problem the following query is solving, is the following: Each Offer has multiple Odds (...
6
votes
2answers
288 views

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

This snippet works and does as desired, but I just wonder if I am being too repetitive. What I mean by this is, eventually I will have all 12 months in there. So.... 12 Update Queries, 12 Commands, ...
6
votes
1answer
523 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 ...
6
votes
1answer
63 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? ...
6
votes
1answer
193 views

OOP Python Blackjack game with player accounts in PostgreSQL

This is an OOP version of Lastest version of my Blackjack game. Also, it now uses PostgreSQL as database. ...
6
votes
1answer
97 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 ...
6
votes
1answer
1k views

Schema for Google Docs-like sharing

I want to allow users to share documents (and other stuff) in a database-driven application. I have designed the following schema to allow for this (PostgreSQL). Some of the tables like ...
6
votes
1answer
2k 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 ...
6
votes
1answer
546 views

Univariate analysis with SQL stored procedure

I wrote a SQL univariate analysis stored procedure taking as input a schemaname and a tablename on PostgreSQL. It is exactly like a PROC SAS UNIVARIATE. As output, you get less than 20 usable ...
6
votes
0answers
1k 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 ...
5
votes
1answer
10k views

Insert data to PostgreSQL

Im inserting data to a PostgreSQL database using the below function. Im not very happy with the solution for the IF statements to handle the adaptation of Python <...
5
votes
1answer
875 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 ...
5
votes
2answers
1k 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 ...
5
votes
1answer
147 views

Optimize postgres function

This function code works very slowly. How can I speed it up? ...
5
votes
1answer
97 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, ...
5
votes
2answers
3k 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 ...
5
votes
1answer
48 views

SQL Bulk status update

Context I'm a duck retailer. I rent ducks to farmers, and when they don't need them anymore, I get them back. In order to track which ducks I rented to whom, I have a PostGreSQL database that I keep ...
5
votes
1answer
6k views

Importing database of 4 million rows into Pandas DataFrame

I am using the following code to import database table into a DataFrame: ...
5
votes
1answer
432 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 ...
5
votes
1answer
299 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 ...
5
votes
1answer
93 views

Selecting and copying event log entries for some machine

I have a PL/pgSQL function with erratic performance: ...
5
votes
1answer
98 views

HackerNews news fetcher

The following code is part of a big application. I am new to Go, and appreciate feedback on the best practices and improvements. Please consider styling and comments as well. Every 30 minutes, this ...
5
votes
1answer
88 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. ...
4
votes
3answers
8k views

Splitting a list of numbers into multiple lists

I need split the list I get from the select into multiple list so I can use Parallel.For() to generate the next query in different threads. I create the main list ...
4
votes
2answers
336 views

Consume etherscan.io API, store in DB, serve from web app

I didn't make it to the next stage in this interview process, because another applicant did better than me, which wasn't surprising given that this was my first foray into Node.js. I'd like some tips ...
4
votes
1answer
26 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 ...