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
0
votes
1answer
53 views

Port the data from a TEXT file to Postgres table using Python

I have written the below code to read a huge text file of size 20+ MB and insert the values to a PostgreSQL table. The code is working fine, but I'm facing below issues in the code. 1) The code is ...
5
votes
1answer
41 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 ...
1
vote
0answers
25 views

Golang and Gin framework: best practices to connect to Postgresql

I come from this question. Project A very simple project with Gin framework with an endpoint to return a list of users based on their score. Structure ...
2
votes
1answer
46 views

Python script to find employees who are in one PostgreSQL table but not in another

I'm writing a python script and do database query to get the ids of employees who are in one table but are not in another. I'm using psycopg2 module for python 2 and PostgreSQL as database. After ...
0
votes
0answers
52 views

Is there any better approach on this rows to columns table?

I pass a function into the variable ($monthly_receivable) to get the value of each months to pass and get the result of a function.. but the process is dumb slow. ...
3
votes
0answers
58 views

PL/pgSQL function to test if a word is contained within a phrase

I am using PostgreSQL 11.0 and I wrote a PL/pgSQL function to test if a word or phrase is contained within another phrase. This function is called several times within another function to extract some ...
1
vote
1answer
32 views

slow JavaFx PostgreSQL CRUD app with complex object

I've created a JavaFX app to the data about Vessels, Employees and Jobs. The current version works but it is quite slow which I need to fix. I understand the root of my problems but I do not know how ...
1
vote
1answer
58 views

Turning simple Node.JS Postgres query and logging into most-functionally-pure-possible code

After reading "How to deal with dirty side effects in your pure functional JavaScript," I've decided to take something I do on a regular basis ("Original approach" below)—connect to a Postgres ...
3
votes
1answer
33 views

Iterate between dates and INSERT values in a performant way

I have created a query that populates a datapoint with random values. The logic is simple: Iterate between the START and END dates and INSERT random values. I want from this query to be very ...
3
votes
0answers
23 views

Context manager to temporarily drop indexes and constraints on a table

I'm importing millions of rows into a PostgreSQL database using a foreign data wrapper. To speed this up I've written this context manager to DROP all the indexes ...
2
votes
1answer
142 views

Offset vs Cursor Pagination

I am trying to compare any performance gain(s) from moving a current offset-based pagination endpoint to a cursor based one. I have the following ruby code: ...
2
votes
0answers
78 views

Upsert query using CTE in PostgreSQL 9.4

I have a generic pipeline in SnapLogic that logs the start time of the executions of a group of incremental jobs. Suppose, the log table is as follows. ...
4
votes
0answers
189 views

Automatically generate TypeScript classes from database tables

Made a project to generate TypeScript classes from database tables, and I'm looking for input if the code is reasonable. pg and ...
2
votes
0answers
33 views

SQL query for selecting count from grouped columns of latest records

I have this table on SQL: ...
2
votes
0answers
359 views

Design database for OHLC

Requirements: I have to design a database to store tick data of 350 stocks. Inside this database queries will be executed to transform tick data and retrieve OHLC candles of 1 minute, 1 hour, 1 day. ...
2
votes
0answers
92 views

PostgreSQL: maintaining non-overlapping temporal ranges

I have to store some temporal meta data used primarily in bulk processing of a time-series (sensor measurements). Each value of this meta data is valid for a potentially half-open range of time. ...
4
votes
2answers
313 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 ...
3
votes
1answer
2k views

Fetch, parse, and store JSON in PostgreSQL

I was given a task to read JSON from a URL and then parse it in python. Each field will be assigned to a variable to store the value. The value will then be stored into postgres table. Example of ...
0
votes
1answer
152 views

Filtering DbContext data dynamically by user input in WPF applications

I need to filter database data based on filters available to the end user in the form of search term text box, select boxes etc. I have put together this code and need feedback if this is a good way ...
1
vote
0answers
78 views

KNN search by distinct category in postgis/postgres

I have a postgres table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). I'...
1
vote
4answers
3k views

Correct using of try/catch clause on database execution

When a statement to a DB returns null, I am catching it this way: ...
2
votes
1answer
88 views

Exporting documents from PostgreSQL into a second database

This is my first try with a database. I read some articles/books and now i tried it myself and I want to know if I can write it better or what are my mistakes. All in all I am using try/catch/finally, ...
10
votes
1answer
223 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 ...
6
votes
2answers
280 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
421 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 ...
2
votes
4answers
1k views

C# SQL query builder to fill DataTable

I'm trying to optimize my code and in the process of doing that I made a class Query_builder that handles SQL queries. That class has couple of methods: ...
1
vote
0answers
107 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 ...
4
votes
1answer
8k 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 <...
1
vote
1answer
568 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
5k 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
129 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
96 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
2k 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
69 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 ...
3
votes
3answers
6k 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
54 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: ...
1
vote
1answer
205 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
26 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
160 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
416 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
160 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
113 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
587 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 ...