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.

Filter by
Sorted by
Tagged with
0
votes
2answers
27 views

How to count the values in a selected row with some condition - Potgresql

I am looking for some built-in Postresql functionality which can count the row values (not columns) with some conditions. Some analog of =countif(a:a;"Yes") in Excel I saw many answers to simular ...
1
vote
1answer
15 views

Postgresql: Double.MinValue is out of range for type double precision

I have a postgres function with parameter double precision. Sometimes i want to pass the minimum value of double from server side [Double.MinValue] c# code. Now i'm facing an issue as below -...
-1
votes
0answers
13 views

How to use POWER and SQRT in Postgresql to calculate distance?

How do you calculate distance using math functions POWER and SQRT in PostgreSQL pulling data from two tables? Both tables contain location_id and one table has the XY coordinates. Not meant to do it ...
0
votes
0answers
7 views

Very high RDS DBS Connection Spike But no Active Queries (Postgres)

I have a Postgres RDS instance and over the last 1 month, I see that there are DB connection spikes happening once in 3-4 days average (3 connections to 500 connections in 2 minutes span). When I try ...
0
votes
1answer
13 views

I forgot to configure login Email id and password while install pg admin 4. how i can change after installation

I am new to pgAdmin 4. I have forgot to setup of login Email id and password while install pgAdmin 4. How can I change after installation? Any one can help me?
0
votes
1answer
25 views

How to avoid space issue during query execution in postgresql?

This is in continuation of one of my previous post which was about performance. But this time I got an error message. I am just trying to join 9 tables. Please note that am able to join 8 out of 9 ...
2
votes
2answers
37 views

How to check if json array already contains a certain key?

Let's say I have this json in my jsonb column { "fields": [ { "name": "firstName", }, { "name": "lastName", }, ... } How can I know if the "firstName" already exist?...
-1
votes
0answers
8 views

SQLAlchemy - load CSV and generate ORM class from Schema

I have multiple CSV files (with Schema information), which have to be loaded in a PostgreSQL database and generate coresponding ORM classes. The existing solutions that I found enter the Schema ...
0
votes
1answer
26 views

Postgres Dump Selected Rows

I have a database that has a central object that most of the data depends on; many reference table, one to many joins and many to many joins. I need to back up all rows based on a property in the ...
0
votes
0answers
13 views

Exporting varchar type with numbers as values to excel as text

Supposing I have this query result. Long Number + =============+ character varying =============+ 14080111534656478 When I export it to excel. It results to "1.40801E+16" and clicking on the ...
0
votes
0answers
24 views

Postgres query gets slower and slower

I have a Knex.js client connecting to a Postgres database. Somewhere in my code, I have to iterate over ~10,000 rows and check if they exist in my database. To be able to do so I have to where over 5 ...
1
vote
0answers
20 views

how to spring Jpa.unsafe to sort a json field in postgresql

In spring jpa doc, the example shows a way to sort by a sql function like Length(field). public interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u ...
0
votes
0answers
7 views

Cannot Save New Records in PgAdmin4- “SAVE” Icon Grayed Out

I'm saving new record entries in PgAdmin4 database, but cannot saved the new records because the "SAVE" icon is grayed out. What should I do to enable the SAVE function? I'm just learning the ...
1
vote
3answers
37 views

Psql - generate series with running total

I have the following table: create table account_info( id int not null unique, creation_date date, deletion_date date, gather boolean) Adding sample data to it: insert into ...
0
votes
0answers
7 views

Sequelize Migrations don't run using AWS Aurora Postgres

As the title suggests. We've been using plain ol' RDS Postgres in our lower environments but moved to Aurora Postgres with a write/read endpoint for Staging + Prod. Migrations don't run. Our DB ...
0
votes
1answer
12 views

How to permanently change timezone in postgresql

I'd like to know how to permanently change the timezone on postgresql -- using set timezone TO 'UTC' only changes it in current session, upon restarting it's back to my region timezone. If i change ...
0
votes
1answer
17 views

Change PostgreSQL data directory to directory created by PostgresSQL on another machine

I have a database stored on an external hard drive. The database was created using PostgreSQL 11, on an Ubuntu 18.04 machine. The folder it's stored in was the data directory of my PostgreSQL instance ...
0
votes
1answer
20 views

Need help installing postgresql on ubuntu 18.04.3

I'm looking to implement postgresql with apache airflow, but first I need to install and set up a postgres database. I'm having a ton of trouble. I've tried many tutorials, like the following: Link ...
0
votes
2answers
37 views

Add custom config location to Docker Postgres image preserving its access parameters

I have written a Dockerfile like this: FROM postgres:11.2-alpine ADD ./db/postgresql.conf /etc/postgresql/postgresql.conf CMD ["-c", "config_file=/etc/postgresql/postgresql.conf"] It just adds ...
0
votes
1answer
28 views

What is the most straightforward way to consolidate data from multiple different RDBMS systems into a queryable database

I have few tables that I have to sync between 3 different RDBMS systems (PostgreSQL databases , a SQL Server and a Firebird Database). Currently I simply connect to my Firebird DB and pull the few ...
0
votes
0answers
21 views

Is there any way to makemigration on a pre-existing docker database?

I have a pre-existing docker container with a postgres database that I'm connecting to my django app, but when I try to "makemigrations" and "migrate" it doens't affect the container. Any clue how I ...
0
votes
0answers
33 views

Postgres isn't accepting TCP/IP on port 5432

I've got this docker-compose.yml file: version: '3.7' services: users: build: context: ./services/users dockerfile: Dockerfile-prod expose: - 5000 environment: ...
-3
votes
0answers
22 views

How to convert an app that uses MongoDB to a local PostgreSQL database [on hold]

I'm trying to rewrite the code for the server so that it now connects to a local postgreSQL database while I build the rest of the app. Unfortunately I have made a mistake somewhere and the app keeps ...
0
votes
1answer
31 views

How to filter only by a single column?

I have a sql which gives the following result. !(https://imgur.com/a/cQjceR2) I want to filter the results by the first column "eventid", where only the first three results of the particular month ...
0
votes
1answer
12 views

return variable and CTE result in postgres

I have a function that should return two integers: study asset store and origin asset store. I have the study asset store saved as a variable from a query and then use CTE to find out what the ...
0
votes
2answers
19 views

Need help for code returning ambiguous error

I'm new to PostgreSQL and am trying to create a view and getting the following error: ERROR: column reference "stn_master_id" is ambiguous Code is: CREATE VIEW V_StationStaff AS SELECT ...
0
votes
0answers
16 views

Need help to solve complex sql query with django queryset

I need help to translate a sql query to a django queryset... I have tried many things but nothing that can solve it The query below is exactly what I need and it actually give me the result I expect. ...
0
votes
1answer
18 views

execute sql files from a folder in postgres

I have a set of sql files placed in a folder in postgres docker image. Whenever a new account is created, I want a new database to be created and all these scripts should get executed on that new ...
0
votes
1answer
29 views

How to UPDATE multiple rows, based on multiple conditions?

I'm trying to update a single column in a table for many rows, but each row will have a different updated date value based on a unique where condition of two other columns. I'm reading the data from a ...
0
votes
0answers
5 views

How to design a database for a multiple choice survey with editable answers

I am creating a Facebook Workplace bot using Node.js. I need to survey employees once a week the same survey every time. The important data is which day they checked and which date it was when they ...
0
votes
2answers
17 views

json_agg() array to object

json_agg() returns result like this [{..},{..}...] I trying to find is there a way to change this array to a single object, my result is a single object since its returning an array of object I ...
0
votes
0answers
28 views

Read multiple rows of a column from an excel sheet and save it as a dictionary

Someone please help me with this, am new to python language! I have an excel file with columns as Bank Name, About, Products, Popular Products, Loans, Insurance, Cards and Banking. What I have done ...
0
votes
1answer
36 views

The most efficient method of performing a large number of similar SELECT queries on PostgreSQL?

I have a set of large tables with many records each. I'm writing a Python program that SELECTs a large number of records from these tables, based on the value of multiple columns on those records. ...
-1
votes
1answer
19 views

How to install libpq-dev for psycopg2 in docker

I have python application running in docker container based on rhel7-atomic (with python36 installed). I also have separate docker container with PostgreSQL latest version (11.x). I need to install ...
0
votes
1answer
26 views

sequelize where clause on first level query with jsonb data type from included table

This is what I'm trying to query: Table1.findAndCountAll({ include: [ model: Table2, as: 'table2Alias' attributes: ['metadata'] // --> metadata is jsonb type: { level: 'senior' } ],...
0
votes
0answers
46 views

How to start new Rails project with existing database?

I would like to start new Rails(v 6.0) project with an existing postgresql database on local machine. In order to move the data I used gem 'yaml_db' which dumped data from the existing db into data....
0
votes
2answers
50 views

tricky SQL with substrings

I have a table (postgres) with a varchar field that has content structured like: ".. John;Smith;uuid=7c32e9e1-e29e-4211-b11e-e20b2cb78da9 .." The uuid can occur in more than one record. But it must ...
1
vote
0answers
39 views

efficiently group many fields including large text and jsonb

Apologies in advance... long-winded question. Suppose I have a table table_x with 20 fields in it: table_x_id (identity pk) int1 int... int8 text1 text... text8 jsonb1 jsonb2 Now suppose I want to ...
0
votes
1answer
15 views

providing certificates to tomcat jndi connection to postgresql

I want to connect to a postgresql server from a servlet in tomcat, using jndi. I have added the server cert to the truststore using keytool -keystore /usr/lib/jvm/java-11-openjdk-amd64/lib/security/...
1
vote
0answers
45 views

Find count for function

I have a function that checks another function for a count, if the count is nothing then I want it to set an alert. This is the counting function function agentn_load($agentid) { $dbh = ...
0
votes
0answers
14 views

How to execute copy command from Psycopg2 on remote database connection? [duplicate]

I'm running 2 docker containers. Flask app - which has file volume attached. Postgres database which has a database volume attached. I'm connecting to the Postgres container using Psycopg2 from ...
0
votes
1answer
22 views

Best Practice for window functions in PostgreSQL

I have a table with record_id and record_created_time columns (postgresql). I want to calculate duration by minute between timeline of each records. Can I do it with window functions (using partition ...
0
votes
0answers
29 views

Visual Studio 2015, create .edmx file for postgresql without visual studio extension

I would like to use the .edmx designer in Visual Studio 2015 for managing objects on postgresql 12. I've installed: EntityFramework 6.2.0 EntityFramework6.Npgsql 3.2.1.1 that in turn installs: ...
1
vote
1answer
18 views

I am not able to insert values into temporary table in a postgres function

I have created a temporary table in PostgreSQL create temporary table tempTable( val int ) I have another table newTable which has the following data: val 5 6 0 8 7 0 9 2 0 But my function ...
0
votes
0answers
19 views

How to migrate nvarchar to postgres python

I am migrating data from a MS SQL database to Postgres. The tables I'm migrating are a collection of KEYS to other tables with names corresponding to those keys (for example unit name). For most part ...
-1
votes
0answers
19 views

When try to import csv file in python i am getting this error after executing import.py file (Background on this error at: http://sqlalche.me/e/9h9h) [on hold]

my csv file have more than 4K rows and when i try to import it with python import.py it gives me error (Background on this error at: http://sqlalche.me/e/9h9h) import csv import os from ...
1
vote
2answers
40 views

Try to update, if update fails then delete (RubyOnRails-PostgreSQL)

I am trying to, update a record, however if it fails due to unique validation, then I am deleting the record since the intended record is already present. def update_dependent_model(records, ...
0
votes
0answers
21 views

Select ALL from many postgres tables

We have around 40 tables, all with similar, but not identical fields. Each table has created_by and created_at fields. We're trying to query a specific set of tables, and return only the created_at ...
1
vote
0answers
23 views

Django after migration UndefinedColumn error in production

I have a website in production. I have an app, with a model. It contained a markdown_file attribute: markdown_file=models.FileField(upload_to='/media/') But since the number of file is limited, I ...
1
vote
0answers
23 views

Geoserver and OpenLayers

<html xmlns="http://www.opengis.net/wfs"> <head> <title>Openlayers test</title> <script src="https://cdn.rawgit.com/openlayers/openlayers.github.io/master/en/v6.0.1/...