I have a database with PostgreSQL that includes a table called events. My goal is to enable a user to update any column or multiple columns in this table through input. The issue I am facing with the current code is that when a user updates, for instance ...
I am facing a challenge with a JSONB array in my postgres database. It is structured as follows: { "id" : 22323, "details" : [ { "status" : "stage1", "timestamp" : "201 ...
I am using PostgreSQL and have a table with a jsonb field defined as json JSONB NOT NULL. Within this jsonb field, I have an array of strings, such as: { "values": ["foo", "bar", "foobar"]} To search for rows that c ...
Currently, I am attempting to establish a connection between NODE.JS and Postgresql with PGAdmin V4.5. However, I am encountering the following error: (node:1184) UnhandledPromiseRejectionWarning: Error: connect ECONNREFUSED 127.0.0.1:5432 at TCPConnect ...
After setting up the server and installing various components like nodejs, nginx, postgres, ufw, and more, everything seems to be working fine. However, there is a persistent issue with connecting to postgres. Despite trying different SQL queries, the conn ...
I am currently working in PostgreSQL and I'm attempting to extract the key (r_dirigeant, r_actionnaire, r_beneficiaire) or generate a text containing all keys when "r_ppe" = "oui" and one of the keys (r_dirigeant, r_actionnaire, r_beneficiaire) is als ...
I have a prisma schema setup for Postgres database: model Member { id String @id @default(uuid()) token String @unique @default(uuid()) summary Summary[] } model Summary { id String @id @ ...
I am working on an application that requires users to upload a spreadsheet, which will then be inserted into one of our PostgreSQL database tables. The users uploading the file may not know how or want to save it as CSV or other delimited format, so I have ...
I have a task at hand: Iterate through an array of objects using the map function Execute multiple SQL queries based on each object Append the query results to the respective objects The array I'm looping through contains offers, which are objects with a ...
My goal is to extract arrays of integers from a table in this format: [1, 2, 3] I attempted the following query: (SELECT array_to_json(array_agg(row_to_json(s))) FROM( SELECT specialty FROM talent_specialty WHERE userid = 840 )s); This is the result r ...
I have been attempting to execute a select query from the database and display the results. However, although I can see the result in the console, it does not appear on the index page as expected. Additionally, there seems to be an issue with the way the r ...
I am currently tackling an exercise for a Bootcamp, and the function I am using is not yielding the desired outcome. The objective is to query the database with a first name or a part of a first name. While I can successfully execute the DB query and retri ...
Looking for a way to save a JSON object in PostgreSQL database using Hibernate with Java. Although PostgreSQL offers json and jsonb data types, Hibernate doesn't have built-in mapping for these data types. Seeking guidance on how to proceed, I came across ...
Currently, I am utilizing PostgreSQL 9.5 and have a table containing JSON arrays with JSON objects structured like so: [] [{animal:cat}, {plant:sunflower}, {car:mercedes}] [{animal:dog}] [{animal:dog}, {car:audi}] [] The goal is to present a table that m ...
Currently, I have two running EC2 instances. One is running a Postgres server and the other is hosting a NodeJS app that needs to connect to the Postgres database. However, it appears that the connection cannot be established as I am unable to ping the P ...
Upon realizing the lack of information on utilizing rails forms to store and retrieve JSON data, I encountered a specific issue: Within my form, I am creating a nested structure in JSON: = form_for(@car) do |cf| = cf.fields_for :locations do | locati ...
My current database structure consists of two tables that are roughly described in SQLAlchemy mapping as follows: class Parent(Base): __tablename__ = "parent" parent_id = Column(Integer, primary_key=True) class Child(Base): __tablename__ = " ...
In PostgreSQL version 9.3, the json_array_elements function extracts each string element from an array and returns them as JSON strings. select value from json_array_elements('["a", "b"]'); value ------- "a" "b" I am trying to convert these J ...
I am trying to use a for and foreach loop to display data based on a template table. However, I am running into an issue where the else conditions always display an index of their own. My expectation is as follows: https://i.stack.imgur.com/tqDSn.png Th ...
I am currently managing a local postgres database. The challenge I am facing involves an array that looks like this: [1,2,3,...2600] Because the array is extremely long, manually typing out each element for insertion is not a practical option. That' ...
After receiving information in the GET URL, I need to pass it into JSON format and save it with increasing IDs into a PostgreSQL database. However, the code I wrote does not seem to be saving anything without any errors: // Initializing Pg const { Client ...
I have researched extensively and found that the information available on this topic is inaccurate or incomplete. I am now seeking guidance on how to achieve the following tasks: Clean out all data in my postgresql database Remove all alembic revisions ...
My current task involves testing the post method on Postman. Strangely, every time I post the result it shows an empty array []. Upon further investigation by console logging on the node side, it also returns an empty array. CREATE TABLE users ( user_ ...
I've been grappling with this code snippet for a while now. It was originally written in JavaScript a few months back, but recently I decided to delve into TypeScript. However, I'm struggling to understand how data types are properly defined in T ...
I am currently utilizing a PostgreSQL database in conjunction with a GraphQL/NodeJS server. One particular mutation is causing me significant issues due to its inherent complexity. I have inserted console.log statements strategically in the code to track d ...
Having recently transitioned from MySQL to PostgreSQL, I installed PostgreSQL on my Fedora system and set up the postgres user following the instructions on the Fedora wiki. After creating the role using the psql shell while su-ing into the postgres user, ...
As a novice Laravel developer trying to export query results to a CSV file, I'm encountering confusing duplicate values in the output. Here is my code: $connection = fopen('/BSTablespace/someFile.txt', 'w'); $users = DB::table('users')->select('first_ ...
If my database table 'configurations' contains rows with a 'data' column in JSONB format like the example below: { "US": { "1234": { "id": "ABCD" } }, "CA": { ...
TypeOrm "typeorm": "^0.3.11" nodejs/typeorm postgresql doesn't return non-selected column test logs display: query: SELECT "Test"."name" AS "Test_name", "Test"."id" AS "Test_ ...
Assume I have a column1 made up of various objects in a json array. I need to achieve something similar to what is shown here: When I try to case over a set of elements with event types like ('Urban', 'Urban', 'Rural')... the ...
Imagine we have a basic table, id - user identification rooms - JSON type indicating the number of rooms the user is looking for. id rooms 1 ["3.0","4.0"] 2 ["3.0"] 3 ["1.0"] If I want to filter users searching for 3 rooms, how can I do that? I am aw ...
My objective is to create a temporary table in the postgres database and retrieve records from it to insert into another table. Here is the code I have implemented: The import psycopg2 import sys import pprint from __future__ import print_function from ...
I'm currently working on a script that inserts data into a database using a form. I'm trying to figure out how to trigger another action in a different section after the insertion. Any insights or tips would be greatly appreciated! ...
Let me provide some context. I have been working with the Mezzanine CMS for Django and created models that inherited from the Mezzanine models. This caused a problem in my Postgres database where one object was present in two tables, leading to search issu ...
Currently, I am using Sequelize, GraphQL, and Typescript for my coding. Within my database, I have two tables named RecordInformation and OtherDescription. The RecordInformation table contains a foreign key called "OtherID" which references the OtherDescri ...
Wondering if it's possible to retrieve groups as an associative array using SQL. I'm interested in understanding the capabilities of SQL and its potential for solving this particular issue. The challenge at hand involves a database containing a ...
I have a model called Location with a field named coordinates of type geometry. I'm looking to create a function that takes in latitude, longitude, and radius as parameters, and returns all locations within that radius (in meters). I attempted to foll ...
I've set up a database table to store information about people and their relationships, allowing for self-association of parents, children, cousins, etc. const People = sequelize.define('People', { gender: Sequelize.STRING, name: Sequelize.STRING, a ...
Encountering a very peculiar issue during updates where I don't receive any response - no errors, warnings, success messages - it's as if nothing is changing. The request seems to be stuck in an infinite loop, always loading. I am using knex.js f ...
When populating the database, I intentionally do it partially for certain tests. However, after completing all tests, I would like to reset the database in order to avoid any complications that may arise during the next populate process. How can I efficien ...
Currently, I am working on a project that involves renting houses and apartments. At this stage, I am looking to implement a feature that allows users to filter properties based on certain amenities such as wifi, security, etc. For this purpose, I decided ...
Looking for guidance on utilizing PostgreSQL stored procedures in Node.js applications using the pg package or without it in an Express.js environment. While I can execute inline queries, I'm unsure about how to implement stored procedures. ...
I'm a beginner with node-postgres and struggling to solve an error related to preventing SQL injection in my prepared statement. Take a look at a snippet of my code below // the prepared statement var preparedstatement = client.query({ ...
As I consider storing data in a postgres jsonb data type, the structure could look something like {"name": "Jhon Smith", "emails": ["johnsmith@example.com", "j.smith@example.com"], "phones": ["123456789", "987654321"]}. I am aware that I can search this ...
I recently set up an express server and I'm still fairly new to the world of databases. An interesting dilemma has come up - if my express server's code is hosted publicly on Github, and it contains this snippet: const pool = new Pool({ user: ...
Currently, I'm involved in an assignment that requires the management of a collection of PostgreSQL databases related to a school district. One of my tasks is to update the new field student_gpas, which is an array designed to store unique student GPAs. To ...
Context Users have the ability to select specific date ranges, which can be seen in the screenshot below: All combinations of starting month/day and ending month/day are valid, such as: Mar 22 to Jun 22 Dec 1 to Feb 28 The second combination presents ...
I'm working on a function that can insert a record into a table in PostgreSQL. The catch is that the table name needs to be a parameter for the function, and the column names are determined dynamically. To ensure protection against SQL Injection, I am cons ...
Is it possible to run text that is stored in a Postgresql database as Python code and have it update the database accordingly? ...
I've come across this issue several times on various forums but haven't been able to resolve it yet. My setup involves Express and PostgreSQL with Sequelize ORM. The problem lies in the one-to-one relationship between two models: Campus and Acad ...
What is the best approach to retrieve a list of users with their last two visited locations using SQL query? user-table id name xxx a xyx b zzz e visitedlocation-table id startDate userID location 1. 1/2/21 xxx USA 2. 1/3/21 ...
I have a Ruby on Rails application running on version 5.1 with PostgreSQL version 9.6 as the database. Within one of my tables, I am storing data in a jsonb field. My goal is to simply retrieve this string and send it to the client without having to conver ...
Within my express application, I utilize Sequelize for database management. Below is the bootstrap code that I use: db.sequelize .sync({ force: true}) .complete(function(err) { if (err) { throw err[0]; } else { //seed requi ...
Currently, I am running CentOS-6 with Python 3.8, PostgreSQL 12, and PyGreSQL 5.2.2. In previous versions, my code included a function that would insert a row of data if an update raised an exception due to the row not already existing. import pg db = pg. ...
Hi everyone, I'm new to React and I think I might have overlooked something. I've been trying to create a simple registration form, but it seems like I'm having trouble changing the state. ` import {useState} from 'react' export const [inputs, setInputs ...
There are two interconnected tables with a one-to-many relationship: CREATE TABLE user ( id bigserial PRIMARY KEY, name varchar(255) NOT NULL UNIQUE, ) CREATE TABLE user_pets ( id bigserial PRIMARY KEY, user_id int8 FOREIGN KEY REFERENCES user(id ...
Struggling to successfully retrieve the callback result and transfer it to a different page, although I've managed to establish the connection and can see the data in the console.log output. My query is: How can I access the callback result and send ...
Database Structure: class Team(Base): id = Column(Integer, primary_key=True) name = Column(String, nullable=False) players = relationship("Player", backref="team") class Player(Base): id = Column(Integer, primary_key=T ...
In this code snippet, each process is responsible for crawling a link, extracting data, and then storing it in a database simultaneously. def crawl_and_save_data(url): while True: res = requests.get(url) price_list = res.json() ...
I have a collection of JSON objects and I am trying to retrieve specific property values from them. My scenario is related to the following query: DO $$ DECLARE v_whatever character varying := 'a'; v_res character varying; BEGIN SELECT params->>v ...
After reading the documentation for Postgres in relation to Flask I learned that in order to run Postgres, it is necessary to include the following code snippet: app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = postgresql://localh ...
How can I effectively model a person's profile received in JSON so that every value is searchable? Additionally, how can I make it queryable for specific criteria such as finding all persons who like Tarantino movies? While a relational model with one-to ...
I am looking to enhance my select query by incorporating filter and search values. Users should have the flexibility to search, filter, or do both simultaneously. Here are some scenarios: Success: Search only. select name, id from master.contact where na ...
Currently, I am involved in a project which utilizes express as the server and postgres as the database to delve into dockerization. The server relies on the database being operational. Initially, when running docker-compose up everything functions correct ...
I am facing an issue with joining two tables in PostgreSQL for my quiz application. The data retrieved from the join table does not match my expectations. I am wondering if there is a way to group the data using a JavaScript function? Here are the table ...
I have a User model and Team model that I am looking to connect. User.js Model "use strict"; module.exports = sequelize => { const User = sequelize.define( "User", { id: { type: Sequelize.UUID, defaultValue: Sequelize. ...
I am working with two tables: mail and status. CREATE TABLE mail ( mail_id integer NOT NULL, mail_from character varying NOT NULL, mail_to character varying NOT NULL, subject character varying NOT NULL, text text, CONSTRAINT mail_pkey PRIMARY ...
Imagine a zoo with a postgresql database. To enable web access to this database, I am using php and javascript. Most of the web pages are working fine, but I am currently working on a page where clients can add or remove animals from existing exhibits. T ...
To start, run the following commands: npm install prisma --save-dev npx prisma init --datasource-provider postgresql Make sure to set up your .env file with the database URL from supabase. DATABASE_URL="postgres://postgres.pnfqkgzzcnikiuchyljz:[email ...
A problem arises when the Node Js (express) server responds to requests using cUrl, resulting in an infinite load. However, when requested via Postman or a browser, there are no errors but still faces issues. Despite searching for solutions, none of the s ...
How can I extract values from a json column in my database? The values in this particular column are structured like the example below: column1 --------------------------------------------- "[{'name': 'Kate', 'position': 'painter'}]" I ...
After creating an SQL script with a large amount of data and running it in a postgresql Database, I encountered an issue while using the Doctrine2 ORM within the application to manage the database. The specific problem I am facing is as follows: When in ...
When fetching data from an API, my approach looks like this: async function getApiData() { const promises = []; for (let i = 0; i < PAGE_COUNT; i++) { const apiData = fetch(...); } const apiData = await Promise.all(promises); return apiDat ...
Currently on a Windows system without virtualenv, psycopg2 is installed through Pip along with the latest version of PostgreSQL. Upon executing ./ manage.py dbshell, the following error is encountered: CommandError: You seem to be missing the 'psql& ...
I am working with a table that holds data in the form of tables stored as JSON in a PostgreSQL column. I am attempting to update a specific cell within this data column, which consists of an array of objects. For example, I need to change the value from " ...