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.
187
questions
3
votes
1
answer
46
views
PostgreSQL Search Feature
The Problem
I am developing a search feature in PostgreSQL that involves a collection of JSONB documents stored in a table, which have been standardised. The goal is to enable clients to perform ...
- 179
0
votes
0
answers
24
views
Prisma models with various relations
The code is a bunch of Prisma models, starting from Move. This model stores data about pokémon moves as can be obtained from the PokéAPI. Further models are shown ...
- 43
0
votes
1
answer
39
views
Find and conditionally sort all venues matching a filter provided by an API client in PostgreSQL
I have an API where users can retrieve a list of "venues".
Every venue is a profile, but not every profile is a venue
Venues can be "parents" of other venues (think hotel - floor -...
- 103
1
vote
0
answers
69
views
Todo application using Spring Boot 3, Dockerized Postgres, Java 17, Maven
I'd like to have code review for backend of todo app.
It has 2 main functionalities:
Authentication and authorization using Spring Security and JWT token.
CRUD for tasks
In particular I'd like to ...
- 161
0
votes
0
answers
17
views
specify a policy for allowing access to a table through a foreign key
Consider the following two tables in a PostgreSQL database:
...
- 101
1
vote
2
answers
28
views
Creating single table vs creating multiple table
I have lots of services which I am integrating. (also I am new to sql)
For each service integration I get different response,
For example, for zoom, I would just need to store the access_Token, for ...
- 495
1
vote
0
answers
25
views
Database recording users' consent to be subject to commands of other users
I am adding a database to my application that fulfils these requirements:
There is a set of users and a set of commands.
Each user can consent to any number of other users to have commands done on ...
- 11
1
vote
0
answers
38
views
Update table with data from another table under certain criteria
I need to update a lot of data from one table based on criteria from another table.
My tables and records in those tables look like this:
This is Users table where i can have many records for single ...
- 341
1
vote
0
answers
38
views
Get the difference between two sets from database
Hello beautiful people!
I have created a script where I have two database functions (Feed and Product) where I have a list of Feeds in a store and a list of Product in the store. What I have done is ...
- 619
2
votes
1
answer
138
views
Extracting authors and books from XML and inserting them into PostgreSQL
There is a tree of start folder, it's subfolders, their subfolders, etc. In each folder, subfolder, etc. there are the same structured XML files stored.
books.xml
...
- 23
4
votes
1
answer
211
views
Postgres DB design
I need to develop a database to handle muting people in an online community. The DB needs to keep track of the currently muted people. When somebody gets unmuted, that entry gets moved to an archive ...
2
votes
0
answers
113
views
A Python base class for a family of Postgres server manager classes
I'm working on a repo the purpose of which is to create and manage a local PostGIS server. (PostGIS is a bit niche, but it boils down to a few extra spatial data types and built-in SQL function. With ...
- 441
0
votes
1
answer
180
views
Get Gzip file from endpoint, extract data and add to database
I currently have a script that fires a request to an API endpoint which returns a csv.gzip file - which roughly contains 75,000 rows and 15 columns. I download this ...
- 327
1
vote
0
answers
98
views
Postgresql: reduce the number of joins needed to get the information about followers and following [closed]
This query is part of a mobile application backend I am working on. It is hosted on Supabase (open source Firebase alternative): It's purpose is to retrieve the profile 'preview' - the most important ...
- 119
1
vote
1
answer
40
views
PostgreSQL query which return metrics joining different tables
I have a query that can be seen on this fiddle and I'm relatively new to PSQL, having a few months of experience.
DB Fiddle
As mentioned in the comment I should put the query here and the code is ...
- 271
2
votes
0
answers
22
views
Plpgsql function that redacts substrings wrapped with double pipes `||`
I'm using Postgres 14 and I've written a function (for use with Hasura GraphQL Computed Fields) that transforms a TEXT field, replacing all instances of spoiler-annotated text (e.g. ...
- 121
2
votes
0
answers
93
views
Go REST API feedback [closed]
My intention is to create an API as generic and DRY as possible using Go. To achieve this, I have made some more or less common decisions:
To use AJAX call to avoid reloading page when updating the ...
- 121
2
votes
0
answers
37
views
Implementing a tables inheritance mechanism with PostgreSQL which allows to always reference the parent table in a foreign key
Context
Postgresql has an INHERITS keyword which lets you inherit one table from another, but it is known to have several caveats, mainly because it is not a true ...
- 121
3
votes
1
answer
128
views
Django User Model to authenticate using email address and password
This is my second Django project for an actual client and I want some review on one of my important models.
This model will be responsible for authenticating users using their E-Mail Addresses and ...
- 31
4
votes
2
answers
167
views
Historical database design for school system
Context
We are a multi-academy trust designing a database system (more of a warehousing solution) to aggregate educational data from our 13 schools into a single source.
The data will be mostly ...
- 223
0
votes
1
answer
24
views
Comparing two columns across tables for a remaining subset
How can I find the unique phone numbers (and collapse them into a single column) from table_1 (while keeping the IDs and date fields), and remove phone numbers that appear in table_2?
table_1
id
...
- 1
2
votes
1
answer
38
views
Find coherent datetime ranges in postgresql
My Data consists of rows containing a Length/Duration in Minutes, a Begin & EndTime. The precision is 1min (or larger).
the table looks something like that:
...
- 121
5
votes
1
answer
128
views
PostgreSQL: Grouping and Aggregating on multiple columns
Problem Statement:
I am working on this simple dataset from Kaggle. I have provided a snippet of data with only required columns in below table. Dataset is quite simple, it has all IPL (cricket) ...
- 53
1
vote
1
answer
66
views
Simple website sql database
I'm getting into backend with postgresql and I would like to know how much of my example would fit for a real website database, just for storing and then displaying it on website.
...
- 173
1
vote
2
answers
67
views
Querying and rendering an HTML table of ISP performance statistics
I have multiples measurements and I want to render it into tables like this
Measurements
Operator
browsings
FTP DL
FTP UL
Location
Event
Date
Operator
avg
min
max
avg
min
max
avg
min
max
Verizon
...
- 107
1
vote
1
answer
76
views
Queries using ORM and PostgreSQL
After yesterday suggestions, I was told... recommended to move over to ORM and I did. I choosed to use PeeWee together with PostgreSQL. Queries for storing and get information from postgreSQL
This is ...
- 619
1
vote
0
answers
27
views
Scalable and Optimised group chat system
I have a simple chat system. I have built this to scale so I would appreciate optimisation tips or alternative table structures too. This does not include the pub/ sub queue system.
Users
...
1
vote
0
answers
44
views
Fetch the nearest city, optimised with Redis on a Lambda
I have a lambda function which returns the nearest city from a latitude and longitude.
My algorithm for this is resource heavy and time consuming. To optimise this, I hash the coordinates into a 6 ...
- 111
1
vote
2
answers
2k
views
A discord.py bot that logs every messages sent in a discord server
Hello Code Review community !
I'm making a bot with discord.py that listens to every message sent in a server and stores it in a database that could be retrieved by admin/mod later.
Here's the ...
- 103
1
vote
0
answers
613
views
Discord bot using SQLAlchemy
Right now I'm making a Discord Bot written in Python. I'm using SQLAlchemy to deal with the database.
My structure is:
One database.py containing a ...
- 11
2
votes
1
answer
143
views
Getting new vs. returning customers in Postgres
I have this work task, to write a SQL query that would show how many of the active customers are new (= do not have prior transactions) as opposed to returning per a given period.
There is nothing ...
- 301
4
votes
0
answers
1k
views
Simple Connection Pooling with psycopg2 and PostgreSQL
I am currently using a SimpleConnectionPool from psycopg2 to lease transactions to a PostgreSQL database and would like a review ...
- 179
1
vote
1
answer
23
views
Avoding CT and other sins?
I'm a SQL learner. In answering the question* For the region with the largest sales total_amt_usd, how many total orders were placed? for the following schema:
(I am unsure of the dialect is but I ...
- 401
1
vote
1
answer
874
views
Creating SQL Table from a Go Struct
I have been building an API in Go, which I want to link to a Postgres SQL table. I have always used NoSQL previously but thought ...
- 389
2
votes
1
answer
70
views
Managing a store in PostgreSQL
I have been trying to improve python combined with Postgres.
At this moment im only sending two values to database which are etc:
...
- 619
1
vote
0
answers
311
views
Creating a transaction with node-pg and Express
I'm trying to write a TRANSACTION with node-postgres in an Express API. [The code below works as it's supposed to, mostly ...
- 189
1
vote
0
answers
68
views
Postgresql - query returns different results on 2 copies of same database [closed]
I have the following postgresql query that was created for use in a production database to return a daily recap of counts. This query runs and returns correct results in the "staging"/test ...
- 111
1
vote
0
answers
379
views
Golang DB connection with AWS SSM Parameters and context
I'm working on setting up an app backend that's deployed automatically using infrastructure-as-code and a ci/cd pipeline. As a result of that I need the app server to gather DB credentials ...
- 1,066
4
votes
1
answer
785
views
Historical Funding Rate Miner for ftx.com
I recently started to take a look at the Go programming language and decided to write a small project without practical use.
The objectives were:
Regularly poll the latest funding rates from https://...
- 389
3
votes
0
answers
137
views
Updating user information in SQL
Newbie here. I have this gqlgen api with sqlc update code, which updates the user by given id with provided fields:
...
- 131
6
votes
1
answer
821
views
Air Quality/Environment Monitoring Web App using Plotly Dash
Overview & Motivation
This project manages and displays sensor data (temperature, humidity, Air Quality Index). The sensor data is displayed by the sensor manufacturer, PurpleAir, but their tool ...
- 61
2
votes
1
answer
119
views
Any ideas how do I prettify this SQL query?
This question is abut coding style. I'd like to see how would you improve this code look considering readability as well. Feel free to upvote others solutions you find good.
#1 Initial version:
...
- 137
2
votes
0
answers
29
views
A task to store user's favorited projects in postgres using python
I am not happy with two functions calling get_user() since while testing I mock get_user and to test with get_user_returns_null case, it returns Null for both ...
- 195
4
votes
1
answer
343
views
Discord bot with Psycopg
I've made a Discord Bot in Python that uses the Psycopg module to make calls to a PostgreSQL database in order to display information.
https://github.com/Chombler/cardbot
Specifically, I call this ...
- 143
2
votes
1
answer
90
views
Selecting changes from a temporal table
I'm using Temporal Tables on postgresql (https://github.com/arkhipov/temporal_tables) and C# with dapper.
I'm storing an entity together with its changes.
Here's an example entity, with an Id and two ...
- 647
3
votes
1
answer
67
views
Getting weather and cycling data (yr.no, oslobysykkel)
What I am trying to do, is to get weather data, data about bike trips and populate the postgresql database in order to be able to work with this data from Power BI/Tableau/any other tool.
I have two ...
- 291
3
votes
2
answers
87
views
Fetching all nodes of a directed graph by an id of any node
Task: Rowset of all reachable(in any direction) nodes from any node in a graph. In other words I need to extract all nodes of a particular graph from a table in the database where graph specified by ...
- 141
1
vote
1
answer
27
views
SQL Negation: Find X when X does not do Y
I'm learning The essence of SQL by Rozenshtein indirectly from the SQL Cookbook which has a section on it with modern SQL ...
- 113
2
votes
0
answers
36
views
Handling failures in DB transactions
This code is meant to retry the db operation when a DB connection breaks: OperationalError.
A transaction can fail for example when a db is being restarted and a commit fails. Or when a network error ...
- 121
4
votes
1
answer
129
views
Postgres Shopping Database using Python
So I have just started to work with SQL and I have tested my script and is working very well when it comes to my database.
The database is used for shopping. What I mean by that is that I scrape a ...
- 619