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
0answers
5 views

How to convert INSTR (Oracle) to STRPOS (PostgreSQL)?

How to convert INSTR (Oracle) to STRPOS (PostgreSQL)? The query is: select INSTR('1234&12345&123457&4321','&',1,length(replace('1234&12345&123457&4321',char(10),'&'))-...
0
votes
0answers
5 views

Why postgres logs are visible from a previous, separate docker container which mounted a separate data directory

From a blank directory I create a new subdirectory pg1_data, and mount it as the data directory of a new container running docker's official postgres image (version 12, Dockerfile here). [email protected]:~$ ...
1
vote
0answers
11 views

Why can't a COMMENT string in Postgres be concatenated?

I tried to concatenate a longer comment over several lines to keep my code clean and was surprised to find that I can't. For example, the following returns a syntax error at the pipes: COMMENT ON ...
0
votes
0answers
17 views

Field return count of another table

I need that read_booksfield (in Purchased), return the Rating objects count. Should return the number of objects according to the filter of the same collections of each book in Rating and the same ...
0
votes
2answers
23 views

Error Object of class stdClass could not be converted to string, don't let me eliminate an item from my database in laravel 6.0

I tried to fix it but i couldnt, its for school Queries without eloquent Here i will show the code of my destroy function() ,my view ListEvent and also my query that shows how i can have the ...
0
votes
0answers
13 views

ERROR: could not write block 37583345 of temporary file: No space left on device

I want an output like this: obid | sid_count 1 | 3 2 | 2 3 | 4 The obid is on custdata table and sid_count is get from identifier table. The sample data is: custdata ...
0
votes
0answers
7 views

Flyway Maven Plugin Execute GOLS from external Config File

Guys I'm using flyway on a spring boot project. When I start the application the migration scripts are executed correctly. My migrations are in the folder: flyway.locations = db / migration / ...
0
votes
0answers
10 views

How to render postgresql results in ejs using node pg?

I'm new to node/express/postgres and trying to build a simple todo application. I'm trying to render a view using an ejs file, but instead of displaying the query results, the page is displaying the ...
0
votes
0answers
13 views

Very long sql request nodejs

I have a very big query from nodejs to postgresql that take around 2 minutes to load (the query also takes 2 minutes on pgAdmin). This query takes 2 minutes because there is so many computation to do ...
0
votes
1answer
10 views

How to remove key/value from JSONB column

I want to write a simple update statement to remove a key/value from a jsonb column or even just set the value to null. This doesn't work as I had hoped. UPDATE mytable SET params->>'mykey' = ...
2
votes
2answers
28 views

Is there a way to merge these json aggregations?

I am trying to create json object from getting some info from one table, then creating interger arrays from some other tables' id's and adding n > 1 (2 or more) arrays to the json object. I am ...
0
votes
0answers
11 views

How can I create a Required Owned Type with Entity Framework Core 3.0

I'm struggling creating a non-nullable/required Owned Type with Entity Framework Core. I'm using EF Core 3.0 against PostgreSQL database. My value object: public class PersonName { ...
0
votes
1answer
15 views

psycopg2 cursor.copy_from() loading duplicates?

Using psycopg2 package in Python to load data from a CSV file to a table in Postgres. This CSV file gets regenerated every hour, and there may be duplicates in the file when compared to a file from ...
0
votes
0answers
29 views

update in PostgreSQL function seems to not work

To learn how to use function in PostgreSQL, we have to do an exercise with the following statement: We have a table Skieur (skier), Competition and a table Classement (ranking of a skier in a ...
0
votes
2answers
14 views

Reading an append-only list from PostgreSQL

I would like to implement an append-only list in PostgreSQL. Basically, this is trivial: Create a table, and only ever INSERT into that table. However, I would like to be able to read that list again,...
-7
votes
0answers
22 views

Usar CrossTab no Postgresql agrupando por duas colunas [closed]

Estamos desenvolvendo um relatório em Java com o iReport, estou tendo dificuldade em usar o crosstab no Postgresql ( versão 9.4 - no Amazon em Linux), os exemplos que encontrei na Internet não ...
0
votes
0answers
18 views

Django - One to Many Single Query on Same Table - Get Most Recent and Oldest on Group By

Suppose I have the following: from django.db import models class Book(models.Model): name = models.CharField(max_length=300) price = models.DecimalField(max_digits=10, decimal_places=2) ...
0
votes
2answers
25 views

Creating Postgres View getting ERROR: column “id” specified more than once

SCENARIO: I have this select statement that JOINs a bunch of tables together: SELECT e0.id, e0.name, e0.slug, e1.id, e1.edition, e1.url, e1.date, e1.event_id, v2.id, v2.title, v2....
0
votes
1answer
17 views

Returning dynamic SQL statement in PL/pgSQL Functions

I have a table called "points" with a column called "geom" of type geometry. I want to create a function that returns a table with a column of "geometry" data type. I have been successful in returning ...
0
votes
1answer
24 views

How to correctly store C#'s Bitarray in Postgres table

Currently I am trying to store bit patterns of variable length in a Postgres table. My use-case is that I want to encode some information about data records being assigned to groups in a compact way. ...
2
votes
1answer
27 views

Postgres Serializable does not seem to work as described

I am new to Postgres, and have not looked very carefully at transaction isolation in the past. However, I am not seeing the behaviour I would expect according to the Postgres docs. In particular, it ...
0
votes
1answer
17 views

Optional table layout and index

We are collecting stats and have a few options on how we store it in our db. It's an array with up to 1000 objects in it per row and thousands of rows. We have two different structures we can use. ...
0
votes
0answers
14 views

reset postgres on patroni kubernetes

I've spent a few days trying to solve a devastating issue that happened in our beta environment. Postgres was in a completely broken state, not recoverable in any way we could find, not only that but ...
0
votes
0answers
18 views

Jpa Specification search like in a composed field

I have an Entity class and also a JPARepository: @Entity class MyClass(){ Integer id; String type; String coin; } and in my service I create and return another model class which has a String param: ...
0
votes
0answers
22 views

Efficient Query for Bulk Delete in Postgres

I have to delete records from a table 'project' where internally it has to delete (2000-3000) records from a table 'test_suite' because project_id is foreign key in the test_suite table. The database ...
0
votes
0answers
7 views

How I call PostgreSQL Store procedure in NHibernate

This is my code... I am getting error "Named query not known: GetListOfAvailability" IQuery query =CurrentSession.GetNamedQuery("GetListOfAvailability") .SetGuid("propertyId", propertyId) ....
0
votes
0answers
12 views

How to store postgresql database in django - project folder?

Where is postgreSQL database saved in a django folder (that which has manage.py)? If not, I presume it must be stored in postgres folder of root directory. Then, how to transfer this file to those ...
0
votes
1answer
34 views

Eliminating the value of a field on a Postgres Trigger

I want to create a Postgres trigger that will eliminate a whole field from NEW like in (because this column will be filled automatically by the database and I do not want to change the assigned value ...
0
votes
1answer
26 views

Similar to with regex in Postgresql

In Postgresql database I have a column called names where I have some names which need to be parsed using regex to clean up punctuation parts. I am able to get a clean name using regexp_replace as ...
0
votes
1answer
19 views

Distinct value for multiplie column as one

I have a table transactions with columns : id_transaction, credited_person, debited_person and a table users with id_user. For each user I want a list of all the people with whom he made a transaction ...
0
votes
1answer
18 views

How to check if postgresql crashes and restarts?

I am investigating an error with an application that I think is related to the database restarting and crashing periodically, perhaps it's running out of memory. How do I check to see if a postgresql ...
1
vote
1answer
14 views

Create PostgreSQL Role/User from within Python program

Do you know if it's possible to create a role/user for the Postgresql database from within Python code? I would prefer to use asyncpg library, since my program is based on asynchronous code, but if ...
1
vote
0answers
16 views

Why isn't my table showing when doing a create table statement using airflow?

So, I am trying to create a table in my Redshift DB by using airflow. My connection works and I tested it with a SQL command but when I change the sql command to a create table command it runs ...
0
votes
1answer
31 views

Missing data from one table when LEFT JOIN

I want to join tables below, since there are empty days in some tables, I generated dates to have all the dates and used DISTINCT ON to eliminate duplicate rows from the result. I joined them on ...
0
votes
0answers
26 views

Join tables in different Databases in AWS RDS Postgres Server

I have 2 databases in one AWS RDS Postgres instance. I would like to join the tables across the databases. Is it possible to do that in AWS RDS ? What could be the walk around? I have created ...
0
votes
1answer
26 views

PostgreSQL json in character varying with []

I'm having trouble with accessing elements in a postgreSQL database. In one column of the database, there are multiple json-like strings, wrapped with []. The entry looks like [json1,json2]. [{"...
1
vote
0answers
18 views

Hibernate criteria query with join and where clause

I'm trying to write specific Hibernate criteria query using criteria builder. I want to get java code equivalent to following SQL query: SELECT parent.pk FROM parent_entity parent JOIN child_entity ...
0
votes
2answers
23 views

Unnest json string array

I'm using psql and I have a table that looks like this: id | dashboard_settings ----------------------- 1 | {"query": {"year_end": 2018, "year_start": 2015, "category": ["123"]}} There are numerous ...
0
votes
2answers
24 views

Update column based on array search in postgresql

id | name | status ---+------------------------+-------------------------------- 1 | {'Tom','Jerry','Hary'} | { 'Waiting','Waiting',Waiting'} I want to update status column based ...
0
votes
1answer
30 views

Postgres recursive building of CTE with array values

I have an array of strings which represents function names. I need to iterate through them in order to call each function. They different rows based on the criteria matching against other tables in ...
0
votes
1answer
21 views

Export through ODBC from SQL Server to Postgres

I downloaded a large database which was presented in mdf. Added in SSMS and set up the export through odbc into postgres db. The SSMS wizard said that it successfully exported hundreds of thousands of ...
0
votes
0answers
20 views

How to convert SQL transaction to active record [closed]

Can someone help me to change this to ActiveRecord? I made it using pg_con, but now I want use transaction with ActiveRecord. @mymodel.exec("BEGIN; LOCK TABLE mytable IN SHARE ROW ...
2
votes
2answers
38 views

Postgresql won't build up on Docker

I am trying to run postgresql and set up my Rails app. Here's my dockerfile: FROM ruby:2.6.3 RUN apt-get update -qq && apt-get install -y build-essential && apt-get install -y git ...
2
votes
0answers
24 views

Inserting huge Pyspark df to PostgresSQL

What would be the most efficient way to insert millions of records say 50-million from spark df to Postgres Tables. I have done this from spark to MSSQL in the past by making use of bulk copy and ...
0
votes
0answers
8 views

implement database pagination in search function with Node.js and postgresql

Basically, the API have a search endpoint with multiple parameters. I noticed that the search function is becomming slower by time since the data structure is getting more complex over time and there ...
0
votes
2answers
31 views

PostgreSQL json extraction

I have json data about flight reservations coming from a MongoDB. This data is inseted into a PostgreSQL table and the data type is json. The itinerary information has the following structure. [ ...
0
votes
0answers
18 views

postgres 10 Peer authentication failed

I'm trying to log in to a database with this command: psql -U user my_db This is the error that gets thrown: psql: FATAL: Peer authentication failed for user "user" I figured it had something to ...
-1
votes
0answers
10 views

Single connection from pgbouncer to postgresql

As shown in the image below, Query is created from multiple clients to postgresql with pgbouncer management. The problem here is that from pgbouncer to postgresql all queries are answered on a single ...
0
votes
2answers
26 views

PostgreSQL: Getting date associated with max value

I have a table in the form of USER |VALUE |DATE ------------------------- user1 | 1337 | 2019-11-01 user1 | 1338 | 2019-03-28 user2 | 1234 | 2019-04-23 user2 | 4567 | 2019-05-05 and want to get the ...
-1
votes
0answers
24 views

Make a POST request after getting data with query.params from a GET request

To add some context here, I've been working on refactoring a software registration page. I'm using Sequelize to manipulate the DB. For the registration flow, I'm sending a verification email after ...