Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
0
votes
1answer
16 views

Postgres how do return a message if no results for SELECT query

Have been trying to work out how to return a message if the result is empty. I am running a select statement as below: select * from maxIDValue where max_value > 1000000 order by max_value desc; I ...
0
votes
0answers
16 views

what is the most performance way to remove duplicate data of PostgreSQL 13

I have a table more than 1000000 rows, I am remove the duplicate data from PostgreSQL 13 using this SQL: delete from sentence where ctid not in ( select min(ctid) from sentence group by ...
0
votes
0answers
7 views

How to loop through table rows and run a count of all rows in same table that match a criteria

I am pretty new to working with anything other than simple SELECT statments in SQL. I have a database of geospacial data (around 4 million points) using Postgres/PostGIS I would like to add a value to ...
0
votes
2answers
24 views

how to replace the first start of string in PostgreSQL 13

I am using this command to replace the string from PostgreSQL column: update sentence set sentence_en = replace(sentence_en,"1. ","") but I just want to replace the first occure ...
0
votes
1answer
10 views

barman check - what do the Error Messages mean?

Trying to get wal-streaming backups for PostgreSQL running with a barman backup server I face quite some errors on barman check [postgres_server] here an example :~> barman check pg Server pg: ...
0
votes
0answers
10 views

is it possible to add a similiar constraint with a column in PostgreSQL 13

I have a table that store words demo sentence, and I have a scrapy process to fetch new demo sentence and store into database. Now I do not want to insert into duplicate sentence. So I want if the new ...
0
votes
1answer
26 views

SQL query that is able to count days based on event queries

I have a table with the following data: id date_changed color_start color_end ------------------------------------------------------------------- 1 2020-05-27 16:33:52....
0
votes
0answers
10 views

Aurora Postgres Global DB- how to code without using Temp tables

my org is migrating to postgres individual instances to global database in aws. I learnt there is a writer and reader instance. Some of my Pg sql statements are using temp tables, where i store data ...
0
votes
2answers
18 views

What is the difference between tsvector_update_trigger and ts_vector generated column?

Newbie to full text search, I have a feed_items table as follows feed_items (...title varchar, summary varchar, fullarticle varchar,...) I want to create a ts_vector column to search query from ...
2
votes
0answers
35 views

Which is the best approach for one table with some one to many relationships

I do not know which is the best approach in terms of database modeling. I have 3 kind of entities "Shipping Costs", "Billing costs", "Order costs" with different columns ...
1
vote
1answer
13 views

DBMS in a container/cluster vs data corruption

I am fairly new to db administrations as well as containerization/kubernetes concepts. I see every here and then containerized/clustering solutions for i.e. running PostgreSQL (i.e. here. This ...
1
vote
1answer
23 views

Atomic updates to ordering in a table for a waiting list application

I am trying to implement a gamified waiting list. This waiting list is for eventual access to an application. A user on the waiting list can perform certain actions which may move them up in the ...
0
votes
0answers
18 views

Metadata retrieval query executed for every invocation of ResultSet.updateRow in PostgreSQL JDBC driver

I'm using an updatable ResultSet, where I want to update the columns directly in the ResultSet and commit (for performance and avoiding complex rollback logic). For this, I do the following - Create ...
0
votes
1answer
17 views

Show size of tables and other database objects in Postgres

I need to clear disk space, so I want to see which tables and other objects are using the most disk space in my Postgres databases. Ideally, I'd like to see: database name, schema name, object type, ...
2
votes
1answer
19 views

PostgreSQL INSERT INTO 4 million rows takes forever

I use Postgres 14.1 on Windows 10 for development and I have little experience with Postgres. Recently, I wrote an INSERT query to copy records from one table to another. The source table has less ...
0
votes
1answer
14 views

Grant read only access to postgresql schema

I want to grant user foo_user read only access to schema bar_schema. I executed the following commands postgres=> GRANT SELECT ON ALL TABLES IN SCHEMA bar_schema TO foo_user; GRANT postgres=> ...
0
votes
0answers
11 views

SUM `amount` column based on `transactionType` in Postgresql "transactions" table

I have this table: +--------+-----------------+ | amount | transactionType | +--------+-----------------+ | 10 | Debit | | 5 | Credit | | 15 | Credit | | 20 ...
0
votes
0answers
15 views

PostgreSQL versioning question

In my terminal, a CLI query tells me my PostgreSQL is currently 13.2. I am using MacOS and have likely also used Homebrew to take care of installs and updates. I check things out with the brew command ...
0
votes
1answer
20 views

Why can't reset password for postgres?

I can login postgresql database without password : sudo -u postgres psql Now i want to reset password 'xxxxxxxx' for postgres: sudo -u postgres psql postgres=# ALTER USER postgres WITH PASSWORD '...
5
votes
1answer
493 views

How to check if int[] array contains a value less than X?

We can check if a specific value is contained in an array using 'X' = ANY(haystack). However, how do I check if an array of integers contains a value that is less than X?, e.g. I want to find all ...
0
votes
2answers
33 views

Postgresql pg_toast tables, are they mandatory?

I have a small schema in PostgreSQL v13 with only schema structure and still no data. What I have seen, that I got some pg_toast tables for normal tables although there is no data inside. I have a ...
-1
votes
2answers
30 views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
-2
votes
1answer
20 views

How to backup a live database especifically Postgres

I have production Postgres database that is running remotely and I want to backup the live database without stopping it. I don't want to use pg_dump as it takes too long and not really reliable. Tried ...
0
votes
2answers
35 views

Postgresql query optimisation with left join

I'm trying to optimize query run on Postgresql 12. There are 2 tables users (5 mln rows) grants (8 mln rows) I'm running a following query SELECT * FROM users u LEFT JOIN grants g ON u.id = g....
0
votes
1answer
26 views

How i can use the function encrypt in one consult update

I'm trying to duplicate a column, but I would like to encrypt it with not very high data value, and I was able to do it, well, just the first part. CREATE TABLE probeta ( id int primary key, ...
3
votes
1answer
68 views

Why is FROM VALUES massively faster than visiting a table once?

Given this setup, CREATE TABLE t(foo) AS SELECT 'foobar'::text; Why is this query, EXPLAIN (ANALYZE, TIMING OFF) SELECT generate_series(1, 1000000), upper(foo) FROM ( VALUES ('foobar') ) AS t(foo)...
0
votes
0answers
20 views

postgresql performance issue with multiple EXISTS

explain select count(*) from (SELECT tcy.clientid, tcy.projectid, max(tcy.lastmodifieddate) AS projectupdatedat FROM test_cycles tcy JOIN projects ...
3
votes
3answers
520 views

How can you replace the last occurrence of a character in a string (text column)?

Let's say I have a text field with this value in Postgres: 'bar$foo$john$doe$xxx' I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. ...
0
votes
1answer
24 views

Postgres list only user's database and keep other hidden?

I've created a postgres user 'tester' and allowed to readonly to a database A. This user has no permisssion on database B & C. But when I do \l to list database. The database B and C are also ...
0
votes
0answers
16 views

Connection to AWS database gives back "You may not specify an IPv4 CIDR for an existing referenced group id rule."

I try to connect to a AWS database instance from a client by following this tutorial: https://medium.com/simplebackups/allow-a-remote-ip-to-connect-to-your-amazon-rds-mysql-instance-2d5074d601eb I ...
1
vote
1answer
36 views

Postgres find gaps between one date range and others

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one. In my case is also guaranteed that &...
1
vote
1answer
49 views

Order by one column if not null, otherwise by another column, columns are of not matching types

I have an application that uses PostgreSQL as RDBMS. There is a table containing orders placed by customers. The table has an id as primary key, an order_number (integer) and a created_at field (...
0
votes
0answers
21 views

Access ForeignKey columns via dot in PostgreSQL?

I have a table user with columns id, name, email I have a table book with columns id, name, author (ForeignKey to user). Is there a way to write this SQL query in PostgreSQL? select name, author....
0
votes
1answer
21 views

PostgreSQL - psql \watch command - how to interrupt on Windows binary distro (EDB)

The output of SELECT version(); ------------------------------------------------------------ PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit (1 row) and it's a binary distribution from ...
0
votes
0answers
18 views

Many-to-many relationship vs one-to-many relationships

Recently I read an article about building a tag table for better seraching post by tags. The author suggested using one-to-many relationship with post table -> tag table with tag as text[] type. ...
0
votes
0answers
20 views

Postgres providing default to LAG makes the colum NULL

I've got the query below: WITH CTE AS ( SELECT b.provider_id, b.customer_id customer_id, c.email email, c.created_at created_at, c.deleted_at deleted_at, ...
0
votes
1answer
38 views

Postgres Speeding up Query to to calculate Intersection over Union (IoU)

I am attempting to optimize the following query, and have gotten to a stage where I am pretty stumped on what I could do to improve. Given a spotify database, essentially what I am trying to do is &...
0
votes
0answers
14 views

Postgres before insert trigger access field of a type

In the DB, I have a type and a table that one of it's rows is that type, such as this: CREATE TYPE state (last_active timestamp, ...) CREATE TABLE devices ( state state, ... ) For any interaction ...
0
votes
1answer
40 views

How to turn off "autocommit" in PostgreSQL

I am working with PostgreSQL version 11, and would like to turn of autocommit. Either permanently or per query through a pl/pgsql command. In the documentation it says it is turned off by default, ...
1
vote
1answer
21 views

Slow simple select query on Postgresql 13 RDS hosted indexed table

I am working with a simple table, hosted on RDS instance running Postgresql 13. It is using t3.small instance with general purpose SSD storage. The table and its index look like this measurements=> ...
0
votes
1answer
19 views

Pivot table in PostgreSQL by pivoting 10~20 values in a column

Is it possible to crosstab a table in PostgreSQL without having to define an "as" clause given by a tuple (column type)? Example: The created table "t" would have only 3 columns '1'...
1
vote
1answer
31 views

Postgresql RAM optimization for containers and kubernetes

I wondered how to optimize the RAM usage of my PostgreSQL database deployed in Kubernetes. Particularly I'm interested in configuring the shared_buffers and effective_cache_size values. Typically the ...
0
votes
1answer
19 views

Join tables on full text search in Postgres

I would like to join two tables based on if a column value in one table has an exact match in a large text column in the other. I guess you could do something like .... JOIN .... ON t1.col ILIKE '%' ||...
1
vote
0answers
35 views

Improve performance of view based on TSDB continuous aggregation

I am currently trying to create a (materialized) view which is based on a TSDB (timescale DB) continuous aggregation. Some context: I have a table which takes consumption data of meters through a ...
0
votes
0answers
16 views

How to replicate a table inside another database in PostgreSQL?

I have two services in a microservice environment (service A and B) that each has its own database (the PostgreSQL instance is shared currently, but might not be in the future) I want to replicate the ...
6
votes
5answers
519 views

Fetch a row that contains the set of last non-NULL values for each column

Suppose there is a table version col_A col_B col_C 1 A1 B1 (null) 2 A2 B3 (null) 3 A3 B2 (null) 4 A5 (null) C1 5 A1 (null) (null)...
1
vote
4answers
40 views

Postgres cast split string to double precision

I have a cost column in my postgres db whose type is varchar. It stores the costs of each service a client gets in this format "KES 0.80". I am trying to sum up everything to get a cost ...
1
vote
0answers
35 views

Query that normally takes 100ms, occasionally takes 30+s. Need help analyzing query plan

I am using Google Cloud SQL. I am using a g1-small. I have a table that looks like this: +-----------+-------------------+-------------+ | Column | Type | Modifiers | |-----------+---...
1
vote
1answer
35 views

Postgres v10 is not using the index in a subquery

I have a subquery that will return 3 records, to later add the result of some calculations (using some functions). When I am running the full query, Postgres is calculating a high number of expected ...
-1
votes
0answers
13 views

Postgres High Availablility multi-master replication on kubernetes

I'm trying to implement an multi-master replication on Kubernetes for Postgres database with High availability. If anyone tried this, Please help me I tried multiple ways, which are available in ...

1
2 3 4 5
283