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
1
vote
0answers
118 views

Node.js pg-pool db connection acquisition, use and release

I am trying to use pg-pool to connect to my DB and perform queries using a connection pool. Here is how I do it, but to be sure I'm not doing it wrong, or to see if there is a better way, please ...
1
vote
1answer
29 views

Combination aggregate/greatest-n-per-group SQL query on simplified data set

I've been reading the top 'greatest-n-per-group' answers on StackOverflow but haven't yet found any answers that fit my specific case. The following code works, but it seems overly-complicated for ...
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 <...
2
votes
1answer
1k views

Flatten JSON to get relevant data in Postgresql

Following is my table: ...
3
votes
1answer
29 views

Creating a join table entry by looking up name

I wrote a stored procedure that allows a join entity to be created by name for my fictional characters database. I would like some feedback on it with regard to exception handling, and conditions I ...
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: ...
-1
votes
1answer
36 views

Count buffered streets that intersect buffered points using PostGIS

PostgreSQL 9.5.1 with PostGIS 2.0 extension This first section creates an intersection (clip) of the streets from another polygon (MTC15), then it buffers the intersection: ...
4
votes
1answer
139 views

Flask Database Model Design

I am developing my first website in Flask using PostgreSQL and I want my database to have professors from all universities and courses in the USA. As I am not very familiar with databases I have ...
0
votes
2answers
2k views

Best way to get the best performance of a large CSV upload in PHP

I am trying to upload a CSV file to a database which has around 800,000 rows. I am able to do this on my server with the following specs: RAM: 4GB Rows: 800,000 Time taken: 305 seconds Usage: 15% RAM ...
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 ...
4
votes
1answer
3k views

Database design for school attendance and scheduling

I work at a small English school in Japan and I'm trying to create a database to handle student attendance and class scheduling. I'm using PostgreSQL for the database and I'll use PHP for interacting ...
2
votes
1answer
2k views

go-pg efficient multi table/row insert

What is an efficient way to write this raw PostgreSQL query using in go? I'm using go-pg but something with just the pg library ...
1
vote
0answers
71 views

Distance between 2 points using Haversine Formula

This is an implementation of the Haversine formula in SQL. How can I simplify and improve the function? ...
3
votes
2answers
40 views

Get the total counted stock take lines for each hour between two dates

I have the following table structure: Tables stocktakebinline stocktakebinlinebatch A stocktakebinline has a one to many ...
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 ...
2
votes
1answer
56 views

Translating code numbers to item descriptions in PostgreSQL query

For each itemId I need to select a value in my PostgreSQL query. I am doing it like this: ...
2
votes
1answer
209 views

Listing employees, categorized by license expiration date

I am working on a personal project and I have hit a wall. I know I am writing bad code and I really want to refactor the code below. The application has three tables on the same page. Each table ...
3
votes
1answer
28 views

Selecting attributes corresponding to the most frequent entries in a junction table

I currently am writing a query which retrieves the max amount of rows for a junction table. I'll use the following example in SQL Fiddle: MyTable ...
3
votes
1answer
165 views

Imported data from CSV successfully, but slow

Currently I am using the gem activerecord-import to seed more than 55,000 records into my database, and it works fine. However, it takes too much time. I am ...
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 ...
3
votes
1answer
216 views

Getting timeslots using a single query

I want to get latest time slots for 2 cases based on doorman true for false based on logged in user. Cases are: If doorman is true, the query will be the same if doorman is false, then I need to ...
1
vote
0answers
123 views

AWS Redshift wrapper class that automates similar types of loads from S3

I have a AWS Redshift wrapper class that automates similar types of loads from S3 for me, and I have recently adapted it to work for Spark jobs, which don't require a manifest, and instead need a ...
2
votes
1answer
676 views

Managing PostgreSQL transactions for concurrency

I have a situation where I need to use PostgreSQL's serializable isolation level for transactions. This is for a table shared among multiple concurrent PHP processes. If the database runs into any ...
2
votes
1answer
86 views

Checking whether a store is open or not by querying over its business hours

I currently have a method that checks if the store is open. It's working nicely, however on the main page I have quite a few stores. Whom again have different locations. Each of these locations again ...
4
votes
1answer
125 views

Migrate files from MySQL BLOBs to PostgreSQL largeobjects

As title says, this piece of code migrates files (binary and metadata) from a database to another one with different structure. Currently my problem is that when I have to deal with a big database (...
1
vote
2answers
234 views

Argument parser for a PostgreSQL backup utility

I'm sure it's still not yet perfect and tips will be greatly appreciated! I just re-read the script and I realised I'm not handling exceptions! The script is unfinished but I'm looking for WIP (work ...
1
vote
0answers
58 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 it....
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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
756 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. ...
3
votes
1answer
453 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 ...
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. ...
7
votes
1answer
4k views

Run query and insert the result to another table

...
3
votes
1answer
84 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
44 views

DRYing two very similar SQL conditional inserts

I have two very similar SQL statements ...
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, ...
2
votes
2answers
2k 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, ...
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 ...
1
vote
1answer
1k 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
339 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
59 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
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 ...
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? ...
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 ...
4
votes
1answer
73 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
225 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
44 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 ...
3
votes
0answers
1k 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
53 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? <...