Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
1 answer
36 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 ...
user avatar
  • 229
2 votes
0 answers
17 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. ...
user avatar
2 votes
0 answers
68 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 ...
user avatar
  • 121
2 votes
0 answers
26 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 ...
user avatar
2 votes
1 answer
59 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 ...
user avatar
4 votes
2 answers
126 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 ...
user avatar
  • 223
0 votes
1 answer
20 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 ...
user avatar
  • 1
2 votes
1 answer
33 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: ...
user avatar
  • 121
5 votes
1 answer
76 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) ...
user avatar
  • 53
1 vote
1 answer
60 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. ...
user avatar
  • 163
1 vote
2 answers
55 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 ...
user avatar
  • 107
1 vote
1 answer
56 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 ...
user avatar
1 vote
0 answers
24 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 ...
user avatar
1 vote
0 answers
30 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 ...
user avatar
  • 111
1 vote
2 answers
494 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 ...
user avatar
1 vote
0 answers
214 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 ...
user avatar
  • 11
0 votes
0 answers
30 views

MTG Metagame Schema

So there's another question about storing information about a trading card game, but in my situation I have to store not only raw tournament information but a lot of statistics I've extracted from it: ...
user avatar
2 votes
1 answer
36 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 ...
user avatar
2 votes
0 answers
429 views

Simple Connection Pooling with psycopg2

I am currently using a SimpleConnectionPool from psycopg2 to lease transactions to a PostgreSQL database and would like a review ...
user avatar
  • 45
1 vote
1 answer
20 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 ...
user avatar
  • 401
1 vote
1 answer
85 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 ...
user avatar
2 votes
1 answer
63 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: ...
user avatar
1 vote
0 answers
61 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 ...
user avatar
1 vote
0 answers
20 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 ...
user avatar
  • 111
1 vote
0 answers
235 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 ...
user avatar
  • 967
4 votes
1 answer
700 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://...
user avatar
  • 389
3 votes
0 answers
109 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: ...
user avatar
  • 131
6 votes
1 answer
531 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 ...
user avatar
2 votes
1 answer
110 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: ...
user avatar
  • 137
2 votes
0 answers
26 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 ...
user avatar
  • 195
4 votes
1 answer
227 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 ...
user avatar
2 votes
1 answer
71 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 ...
user avatar
3 votes
1 answer
36 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 ...
user avatar
  • 291
3 votes
2 answers
52 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 ...
user avatar
1 vote
1 answer
24 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 ...
user avatar
  • 113
2 votes
0 answers
32 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 ...
user avatar
  • 121
4 votes
1 answer
101 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 ...
user avatar
1 vote
0 answers
135 views

querying users from table if user.id is found in another table

I am using flask with sqlAlchemy to connect with a postgresql database with two tables Users table (id, name) ...
user avatar
  • 111
3 votes
1 answer
48 views

Modelling user preference system with categories

I am modelling a user preference system with the following requirements a user can have attributes (foods they eat) a user can have dynamic categories of preferences users which have all their ...
user avatar
  • 43
3 votes
0 answers
28 views

Django migration script to add metadata

I made a simple django migration that adds two field to our Invoices model : first_payment and first_paying_month. ...
user avatar
  • 131
1 vote
0 answers
14 views

Moving hierarchy with the most revisions with more performance (SQL)

I have hierarchical table for my features with a parent-child relation. There was an unique constraint missing, why I have branches in the data, where multiple children relate to one parent. To add ...
user avatar
  • 61
2 votes
1 answer
59 views

My Django custom user model

I followed a video on YouTube to create a custom user model, this is my attempt: ...
user avatar
1 vote
0 answers
312 views

Database design for Instagram-like post/comments

I'd like a review of my design for an Instagram-like application where users can post and comment on pictures. I just finished a freecodecamp crash course on database design and this is the first ...
user avatar
  • 11
4 votes
1 answer
34 views

Select latest phase of objects not marked "remove"

I have the following table in a PostgreSQL DB (simplified for the question), where I want to select only the rows marked with *: ...
user avatar
  • 151
2 votes
2 answers
320 views

Postgres query to extract a JSON sub-object from a JSON field

I have a json field in a table that I want to select certain fields from into a new json field. So far I have this, which works, but I feel may not be optimal: ...
user avatar
4 votes
0 answers
79 views

Interacting with PostgreSQL attachment and the file system

I have just migrated from Python 2 to Python 3. I have written the following script to fetch all records of a table, ir_attachment, from PostgreSQL. After fetching all the records I check following ...
user avatar
1 vote
0 answers
52 views

Reading attachment data from a PostgreSQL table with python

I have developed this script to find the counts of attachment records stored in a table in PostgreSQL for different other tables. I check if the res_id field of <...
user avatar
2 votes
1 answer
33 views

Assigning book ids to certain students on registration of a new student

I have a function where I am trying to assign book ids to certain students on registration of a new student. ...
user avatar
1 vote
1 answer
193 views

Reading employee data from a PostgreSQL database

I have developed this script to read employee_language table records of a PostgreSQL database using Python 2 (due to some OS limitation). I want to find duplicate ...
user avatar
3 votes
0 answers
89 views

Assigning raffle numbers to users in Rails

Background One part of our Rails app is running a Raffle, usually twice a month, with around 600k entrants each round. Each raffle ticket record will have at least a unique raffle number (consisting ...
user avatar