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)

6
votes
2answers
86 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
58 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
76 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
130 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, ...
9
votes
3answers
76 views

Revision 2 - Step 1: PsychoProductions management tool project

I have been told by DBA.SE people that this was off-topic for DBA.SE and should be on CR.SE instead. So... here I submit this database schema design for my trusted fellow CRitters to review! This is ...
24
votes
2answers
2k 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
65 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
56 views
11
votes
1answer
90 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
50 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
443 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
186 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
69 views

Does this query affect perfomance?

I have this query: ...
4
votes
2answers
593 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
180 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
376 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
155 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
96 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 ...
0
votes
1answer
92 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
91 views

Optimize postgres function

This function code works very slowly. How can I speed it up? ...
2
votes
1answer
124 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
702 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 ...
3
votes
1answer
1k 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 ...
1
vote
2answers
178 views

First Database Schema - How did I do?

I would really like some advice from any DB gurus who have a few minutes free. After doing some reading and playing with sqlfiddle over the weekend I have constructed this postgresql schema and it is ...
3
votes
2answers
810 views

PostgreSQL simple query with repeated function calls

I have PostgreSQL 9.1.1 running in a shared hosting environment and I'm running this query: ...
1
vote
2answers
123 views

simplify and improve performance of getting data

I am working on a little browsergame project written in PHP and using PostgreSQL as DBMS. Now I'm not really lucky with the process started after a userlogin was succesful. Some info: there are 3 ...
10
votes
1answer
300 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 ...
6
votes
1answer
78 views

Low performance of PL/pgSQL function

I have an unstable PL/pgSQL function: ...
10
votes
2answers
4k 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. ...
6
votes
3answers
2k 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: ...
6
votes
2answers
779 views

SQL: Extracting nodes from a graph database

I guess I'm one of these people who are more at home in C#,C++, Python, etc; and only use SQL to do simple INSERTs and SELECTs, so apologies if this is too simple. I'm also new to Postgres and ...