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
22 views

Storing multiple translations for retrieval

I've designed a database schema to store multiple translations with the objective of it being easy to retrieve data and add more translations if ever needed in the future without needing to make any ...
0
votes
1answer
49 views

Discord.py Pokemon Bot performance problems [closed]

I have currently problem with discord bot, what is in Python programming language. This commands loading take so long, and I need help with optimization of this code. ...
3
votes
1answer
24 views

SQL query on percentage share of different groups in total [closed]

I could barely get this to work... It seems there is redundancy as the dates are repeated twice, for example. Is there a way to simplify it? ...
3
votes
1answer
31 views

Enforcing a constraint that a user may vote on each post at most once

I have the following table in a PostgreSQL database: ...
0
votes
0answers
28 views

Server Design with GraphQL.Js

To learn graphql I'm creating a server for queries to a local postgres cluster. The pg cluster is populated with sample data from postgresqltutorial.com and I wanted to use GraphQL.Js instead of ...
1
vote
0answers
18 views

Merge logic for customer access

Here is a stored procedure which we use to port the data from a remote server to our data warehouse server. The SP uses a generic style where all the tables along with its schema and data will be ...
0
votes
0answers
43 views

Python script to import MySQL tables to PostgreSQL

I'm using the below Python script to port few table data from a remote server (MySQL database) to my data warehouse server (PostgreSQL DB), The script is working fine (I'm using foreign data wrapper), ...
6
votes
1answer
182 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. ...
3
votes
2answers
98 views

Importing data into PostgreSQL from remote text file using ftplib class

I need to read a remote file using FTP and process it and insert the processed data to a PostgreSQL database in another server. The below code is working fine, but i want some advice to modify few ...
0
votes
1answer
72 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
44 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
201 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
48 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
60 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
91 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
41 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
60 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
34 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
309 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
137 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
295 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
34 views

SQL query for selecting count from grouped columns of latest records

I have this table on SQL: ...
2
votes
0answers
454 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
99 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
327 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
3k 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
193 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
80 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
4k 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
89 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
240 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
287 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
506 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
112 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
9k 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
867 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
133 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
97 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
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 ...
3
votes
3answers
7k 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 ...