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
1answer
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 ...
2
votes
1answer
38 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 ...
2
votes
1answer
60 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: ...
1
vote
0answers
17 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 ...
2
votes
0answers
18 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 ...
1
vote
0answers
59 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 ...
4
votes
1answer
163 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://...
6
votes
1answer
195 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 ...
3
votes
1answer
103 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: ...
2
votes
0answers
25 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 ...
4
votes
1answer
75 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 ...
2
votes
1answer
46 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 ...
3
votes
1answer
26 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 ...
3
votes
2answers
47 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 ...
1
vote
1answer
20 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 ...
2
votes
0answers
29 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 ...
4
votes
1answer
84 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 ...
1
vote
0answers
30 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) ...
3
votes
1answer
38 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 ...
3
votes
0answers
22 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. ...
1
vote
0answers
13 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 ...
2
votes
1answer
49 views

My Django custom user model

I followed a video on YouTube to create a custom user model, this is my attempt: ...
1
vote
0answers
76 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 ...
4
votes
1answer
30 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 *: ...
2
votes
2answers
102 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: ...
5
votes
0answers
66 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 ...
1
vote
0answers
49 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 <...
2
votes
1answer
30 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. ...
1
vote
1answer
191 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 ...
3
votes
0answers
59 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 ...
1
vote
0answers
27 views

Finding total values for each day - Postgresql

I wrote an SQL query to find how many leases were "active" for each day of 2018. So if a contract began on 27/12/18 and ended on 27/12/18 and another one began on 27/12/18 and ended on 28/12/18, the ...
1
vote
0answers
22 views

Postgres Schema Design for School Classes with Cancellations and Substitute Instructors

I have modelled a series of school classes as follows. The part I am not sure about is having a separate table for cancellations and substitute instructors. With this setup every time you looked up ...
1
vote
0answers
26 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
266 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
26 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
35 views

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

I have the following table in a PostgreSQL database: ...
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
264 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
226 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
491 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
635 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
49 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
717 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 ...
3
votes
1answer
57 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
67 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
219 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
80 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
77 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
36 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
24 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 ...