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.
6
votes
1answer
24 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 ...
17
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
62 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
54 views
9
votes
1answer
67 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 ...
2
votes
1answer
38 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 ...
11
votes
3answers
375 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
151 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
67 views
4
votes
2answers
342 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
176 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
279 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
147 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
79 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
80 views
please help to simplify my method (taxonomy) (propel ORM)
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
85 views
2
votes
1answer
91 views
Postgres 9.x Joining on Overlaping Date & Time Range
I have several tables that have a TIMESTAMP WITH TIME ZONE column along with some additional information. I need to be able to join these tables such that all of ...
2
votes
1answer
108 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
630 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
174 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
723 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 ...
5
votes
1answer
73 views
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
1k 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
770 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 ...
