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
-2
votes
0answers
13 views

Django Companies db design dilemma

Should the category, user model be attached to every single relation? Does my DB design is ok for having multiple stores and multiple products on top of a single company? Any performance-related ...
0
votes
0answers
12 views

Django model manager improvments

I'm trying to get a computed field at my Project model. It should calculate all foreign key data specific field. At first, I created a model property with a ...
-3
votes
0answers
15 views

My code is trying to pull data and present in a web page from 2 databases using php/pg_connect and postgresql [closed]

My code is trying to connect and use 2 different postgresql dbs (postgres and postgres1) on the same computer (localhost) and same php file. pg_connect is working for the default postgres db - no ...
0
votes
0answers
16 views

Is this the correct usage of a view in PostgreSQL?

I have two tables. One stores locations and another stores different ratings for those locations (where ratings can come from different users, so one location can have many ratings). I'd like a way to ...
2
votes
0answers
21 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
51 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: ...
0
votes
0answers
9 views

ExpressJS user creation in pg-database

I created a table within a postgres database ( https://www.postgresql.org/ ) by executing the following SQL ...
4
votes
0answers
58 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
48 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
27 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
189 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 ...
2
votes
0answers
34 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
25 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
21 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
23 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
67 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
25 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
31 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
60 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
187 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
178 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
110 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
46 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
351 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
51 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
63 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
107 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
52 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
61 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
369 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
158 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
374 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
35 views

SQL query for selecting count from grouped columns of latest records

I have this table on SQL: ...
2
votes
0answers
532 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
100 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
332 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
213 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
82 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
247 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
288 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
531 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
115 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 ...