Questions tagged [postgresql]

PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best directed to dba.stackexchange.com.

0
votes
0answers
4 views

Relational database replication in kubernetes with more than master

I'm working with a kubernetes cluster to deploy a high available web application, the web application needs a database to store data . Is it possible to ensure relational database replication (mysql, ...
0
votes
0answers
7 views

Is it possible to defend my program whilst offline?

I need to find a way to block user access to my database that will be installed in his pc. So, here on the company we have a problem. We need to block user access to our database that will be ...
0
votes
1answer
10 views

Postgres return scalar that is the result of a dynamic sql query

This is a simplified example of what I want to do. I have a function that returns some valid sql statement as text, based on the input of a integer id. These sql statements always return a boolean ...
0
votes
1answer
12 views

Get cutted JSON in query

I have a column in db which constains JSON values like: {"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"} By query like.. SELECT column->>'key-1' FROM table; I can get my val-1. Is ...
0
votes
1answer
11 views

Finding a % from two values in postgresql [duplicate]

I have a table in a database which holds a value for space used, and a value for space available. What I want to do is find the % of used / free space. select datetime AS "time", storage.name as ...
1
vote
2answers
20 views

Select Minimum Date within WHERE Clause

From a PostgreSQL perspective: I have a dataset which consists of indicator values, timestamps, and a flag to denote whether the indicator values pertain to the baseline or follow-up period. I want ...
0
votes
0answers
10 views

How to create sql syntax autocomplete using angular

I am trying to create a sql editor for Postegresql Database( create queries, executing queries, etc) using Anguar. I have been a while searching for tool to autocomplete sql syntax. Any suggestions ...
0
votes
0answers
14 views

Laravel with postgressql db cant insert value to table and get last inserted id with eloquent

Laravel Framework 5.4.36 | psql (PostgreSQL) 11.2 Hi, now i want to migrate my existing laravel project db from mysql into postgresql, i have some trouble here $t = [ "a" => "name", "b" =&...
0
votes
1answer
23 views

How to insert a selected sum and other information to a table?

I need to add a new project bill to a bill-table that has the sum of all work hours multiplied by hourly wage multiplied by discount and sum of all equipment selling prices multiplied by their ...
0
votes
0answers
8 views

payara / glassfish postgresql datasource jdbc connection pool

I'm setting up the JDBC connection pool for Java EE, Payara - PostgreSQL application. Right now, I'm experiencing some unexpected slow SQL queries that happen randomly. Sometimes the SQL response for ...
0
votes
0answers
11 views

how to add postgres-configuration into cordas build gradle deployNodes-task

i want to replace in the build-gradle task deployNodes the configuration for the default h2-database to a postgres implementation i want to replace the entry h2Port 10013 with dataSourceProperties:...
0
votes
1answer
11 views

Postgresql query across different tables with dynamic query

I'm trying to get a customer id which can be placed in one of ten different tables. I don't want to hard code those table names to find it so I tried postgresql function as follows. create or ...
2
votes
2answers
35 views

Slow query time using group by with order by together

I have a query where I need to order results by a column. It works very fast (2.8 ms) if I order by id. But if I try to order by any other column (even indexed) the query execution time goes up high (...
0
votes
1answer
22 views

Why composite of primary key with other (not null) columns not automatically unique in PostgreSQL?

When create two tables using the following queries in PostgreSQL: create table test_unique_pk ( id serial primary key, value varchar not null ); create table refer_unique_pk ( id ...
1
vote
0answers
20 views

Django-Postgres WHERE query using varchar_pattern_ops index instead of pkey index

I have a Django-Postgres setup with this table - class User(models.Model): id = models.CharField(max_length=255, primary_key=True) Running the migration creates two indexes on the field (which ...
1
vote
1answer
42 views

C# data reader value repeats multiple times while debugging

I am reading data from SQL server table and inserting the same value in the PostgreSQL database table. It's a data migration process. The issue is that the data in the SQL is plain text but I see it ...
-2
votes
0answers
7 views

Connecting PostgreSQL database created on Ubuntu to pgAdmin

I have created a PostgreSQL database on Ubuntu 18.04 LTE and am trying to connect the database server to pgAdmin. Firstly, where can I get the connection information about the server? Secondly, how ...
1
vote
1answer
37 views

What is a better way to manage same database tables for multiple products/environment?

I have multiple products/environment, like Google has drive, docs etc. One common Auth server is there which will contain users table. But I have roles table related to each product, which contains ...
0
votes
1answer
32 views

How to insert multiple rows in postgresql using CTE (WITH)

I am trying to write a function that receive an array of ids (posts), and insert all of them to another table (offer). I need the ids from the newly created rows (offers) for more processing later. I'...
3
votes
1answer
44 views

How to create INSERT query that adds sequence number in one table to another

I have a table sample_1 in a Postgres 10.7 database with some longitudinal research data and an ascending sequence number per key. I need to INSERT data from a staging table (sample_2) maintaining the ...
0
votes
1answer
23 views

How does Postgres host based authentication work?

I am installing DSPACE which needs PostgresSQL. Please see this link: DSPACE INSTALL It says: Then tighten up security a bit by editing pg_hba.conf and adding this line: host dspace dspace 127....
0
votes
1answer
27 views

how insert image data to postgres data base and select the image data from data base

I have convert jpg image to base64img format using base64-img module, that base64img data is stored to postgres in varchar datatype. can I store image data without conversion? I'm using the ...
0
votes
1answer
27 views

SQL - Grouping a timestamp utc column for 30 minute intervals?

Hope your all doing well. I had a query such as below SELECT DISTINCT table_1.id, table_1.city_uuid, cast(table_2.score_rate as decimal(5,3)) as score_rate FROM integrated_delivery....
1
vote
0answers
12 views

What rake tasks in Rails 5 are needed to load structure.sql in the test environment?

We have a database schema in our app that is database-dependent, and we would like to load these DB features into the test database. Specifically we need to create materialized views in a Postgres ...
0
votes
1answer
41 views

Insert multiple rows into postgres table per element in JSON array

I'm writing a createRecipient function in a serverless microservice (API) using pg-promise. This function will receive JSON data from the frontend website specifying what productTypes and companies a ...
0
votes
1answer
15 views

How to avoid race condition on a sequential delete and insert in postgres

I have four table called: Participant [Columns : Id, Name] Answer [Columns:Id, Question_id (FK of question table), Option_Id (FK of Option table), Participant_Id (FK of participant table)]. Question ...
0
votes
1answer
23 views

Quarterly comparison in postgres

I would like to know what is the count of questionid's for every quarter,count of the questions present in the current quarter and not present in the previous quarter(new Questions),count of questions ...
0
votes
1answer
15 views

Ho to build Connection URL for Google Cloud SQL Postgresql Instance

I want to connect my app to a managed postgresql instance on google cloud SQL. The app would be deployed via GKE. Normally, i'd connect via a connection string: Eg: postgres://<user>:<...
0
votes
1answer
17 views

How can i input an id into a axios request to pull specific data from backend

OK so kinda new here. So currently I have data in the backend where I needed to pull it using a specific id so it gets the specific data for that user. In the backend, I just need to put it as an ...
0
votes
0answers
27 views

Create Increment Counter Trigger in PostgreSQL

I Would like to ask how to create a specific counter every row in trigger if i inserted data on the table For Example, this is a result in which there are 5 data in every row has 0 counter Table ...
1
vote
1answer
22 views

Google Cloud SQL PG11 : could not resize shared memory segment

I recently upgraded a Postgres 9.6 instance to 11.1 on Google Cloud SQL. Since then I've begun to notice a large number of the following error across multiple queries: org.postgresql.util....
3
votes
1answer
31 views

Should you create separate tables for fixed options?

As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc. I'm doing this because ...
0
votes
1answer
25 views

Reason for table that only references 2 other tables?

For a coursework I have, I have been given some example (Postgres) SQL code for a Vinyl shop. Part of the database is an album table, for storing information about an album (title, genre, etc.) and ...
0
votes
0answers
34 views

Postgres UPSERT and id SERIAL issue

CREATE TABLE: CREATE TABLE tests (id SERIAL PRIMARY KEY, "name" VARCHAR); UPSERT: INSERT INTO tests (id, "name") VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET "name" = EXCLUDED."name"; The ...
1
vote
1answer
19 views

Trigger sometimes fails with duplicate key error

I'm using a PostgreSQL RDS instance in AWS. Basically, there is a query that inserts data into a first table, let's call it table. The data there can have duplicates in some fields (except for the ...
1
vote
1answer
20 views

Unable to divide to counts of two separate lists in SQL, keeps returning 1

I have one list of events. One event name is creating an account and another is creating an account with Facebook. I am trying to see what percentage of accounts created use Facebook. The code below ...
3
votes
1answer
23 views

NULL in column used for range partitioning in Postgres

I have a table partitioned by range in Postgres 10.6. Is there a way to tell one of its partitions to accept NULL for the column used as partition key? The reason I need this is: my table size is ...
0
votes
0answers
29 views

Looping through 3 different SQL results

I need to loop through 3 results of SQL queries. The problem is that it's only bringing the fist result, instead of all of them. exports.rewardsNotifications = function() { var now = new Date() ...
0
votes
0answers
13 views

Summing row by row while checking for constraints after each sum?

This is a lengthy Scenario: I have three ports with a certain amount of product it creates per day and it's maximum capacity in metric tons. I also have all the arrivals at those ports for the last ...
1
vote
0answers
32 views

Deploy Postgres11 to Elastic Beanstalk - Requires /etc/redhat-release

I'm having a hell of a time deploying my first app to Elastic Beanstalk and could really use some help. I cannot get Postgres11 to install though it is officially supported with RDS. ISSUE If I run ...
-1
votes
1answer
37 views

Appending multiple row results in Postgresql

I have a issue whether I'm facing an issue in concatenating multiple values using Cursor/ Function in postgreSQL. I have a table names Products which contains several values of customers who buy ...
2
votes
2answers
30 views

how to find number of active users for say 1 day,2 days, 3 days…postgreSQL

A distribution of # days active within a week: I am trying to find how many members are active for 1 day, 2days, 3days,…7days during a specific week 3/1-3/7. Is there any way to use aggregate ...
0
votes
1answer
17 views

How to query a collection through multiple associations?

I need to collect objects that are all connected through multiple layers of associations, and I don't know who to do so. I need to get a collection of CustomText based on a string query param. ...
0
votes
0answers
17 views

psycopg2 not finding PostgreSQL database

I have been trying to access my PostgreSQL databases using psycopg2 but i get the following error FATAL: database "common_Data" does not exist if it help here is my code ... try: ...
1
vote
2answers
49 views

SQL query that combine 3 tables with a string like output

I need to to write a query that combine 3 different table and present them in a certain way. Table 1: Employees Table 2: Departments Table 3: Cars Employees table has a joint column for join uses to ...
2
votes
1answer
35 views

Activerecord reads UUID as an Integer. I need the actual UUID as a string

I am trying to save a filename with the same name as the hash of the corresponding record. Currently the data importer is using rails, but the database and corresponding schema, were generated ...
0
votes
1answer
37 views

How to speed up bash random name generation?

i have problem with my code performance. It is running very slow. I need to generate million+ random persons for my postgres db and insert them into db. Person has parameters name,birthdate,gender,age....
1
vote
1answer
33 views

Quarkus how to set environment variables in application.properties

recently I swapped from thorntail to quarkus and I'm facing some difficulties trying to find how to set environment variables in application.properties in thorntail I used something like this ${env....
0
votes
2answers
20 views

Secure way of accessing a PostgreSQL database using python

So I have a flask web application that does basic database operations. I am using a PostgreSQL database. And I am using psycopg2 to access it. here is the code snippet for retrieving my data. import ...
0
votes
3answers
27 views

counting records from two tables

I have two tables in tab1 and tab2, goal is to count all occurrences in tab2 and display them next to id which are present in tab1; tab1 looks: id A B C D tab2: in out A B A B C D ...