Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
14,078
questions
0
votes
0answers
3 views
Postgres: How to replace a single character of many?
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 dollar char with another character, for exmaple '-'. After the replacement, the ...
0
votes
1answer
13 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
7 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
29 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
38 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
15 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
20 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
16 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
17 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
36 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
11 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
30 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
17 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
17 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
15 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
26 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
15 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
499 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)...
0
votes
4answers
36 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
33 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
12 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 ...
0
votes
1answer
37 views
how to find out network traffic for a query in PostgreSQL?
On a production PostgreSQL server I am seeing intermittent enormous spikes in data traffic. Up to 400Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out ...
0
votes
1answer
17 views
Use pg_cron to run VACUUM on multiple tables
Reading the pg_cron documentation I saw that the examples only execute a command when scheduling a task.
In a StackOverflow post I saw that a user tried to run multiple VACUUM when scheduling the task,...
0
votes
0answers
11 views
How to restore the backup of schema taken using pgdump from a different RDS to a different RDS in postgresql?
I have 2 RDS in AWS actually . I need to take the schema from one of the RDS and restore it into the second RDS without taking the table data. So i have taken the backup using this command in bastion ...
1
vote
2answers
76 views
What are the Cons of using triggers in place of count(*) for counting related entities?
What are the cons of using the below approach?
What steps can be taken to prevent someone from incorrectly updating the count?
Is the below approach an anti-pattern? If yes, what better approaches ...
0
votes
0answers
25 views
Scalable database design for supporting automation / workflow
I'm trying to come up with a scalable database design that supports automation/workflow.
In my current solution, I have a trigger model that requires a field, condition value, and action.
Example ...
0
votes
2answers
27 views
barman installation without root priviledges - where to put barman.conf?
I am trying to figure out a Barman-2.13 installation via Ansible from the source code from github as barman user inside /opt/barman (which also is the home of the barman user).
I use the barman manual ...
1
vote
3answers
44 views
JOINing tables when PK and FK differ
I'm new to SQL so this might be easy. I'm trying to join two tables where the first has a standard incremented primary key, but the second table has a column with values like "Ref:36736", ...
1
vote
1answer
23 views
Postgres initdb with custom timezone directory
How can I run Postgres initdb with a custom timezone folder?
I've tried to use the -L flag and PGSHAREDIR env var to control where initdb searches for Postgres share data. However, those options don't ...
0
votes
2answers
24 views
Start PostgreSQL from different data directory on windows
I set up PostgreSQL on my mac and I am trying to share the database files through GitHub to my Windows machine and other users.
I know how to start the PostgreSQL Service from a specific data ...
-1
votes
1answer
15 views
ALTER DEFAULT PRIVILEGES IN SCHEMA vs ALTER DEFAULT PRIVILEGES FOR ROLE, what is the difference?
What is the functional difference between these two commands?
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE ALL ON FUNCTIONS FROM old_role;
ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN ...
0
votes
1answer
14 views
What is the point of "grant select on sequence api.todos_id_seq to my_user"?
In step one on this page it says to add select on sequence. Being relatively new to Postgres, what is a sequence and why does it need separate permissions and why is there what looks like an extra ...
0
votes
1answer
28 views
Postgres Upgrading Errors due to table not existing
I am trying to do an upgrade from Postgres 9.6 to 12, I have done this in the past but in this instance I am having difficulty.
When running the pg_upgrade command followed by the parameters:
/usr/...
0
votes
0answers
56 views
+100
Materialized view creates quickly with data but cannot refresh
I am working with a database on Amazon RDS and have a specific query which joins about 6 tables on keys. Creating the materialised view with data takes about 3 minutes and behaves as expected with ...
0
votes
0answers
30 views
Get update query result in a select query
I want to use a result of an update query in a select query. The idea is the following: every time I query for a question, I am doing an update of when it was viewed the first time, so the update ...
0
votes
1answer
28 views
Is it possible to cast record to anonymous composite type in PostgreSQL?
I need to create a table with a constraint that no two rows with coinciding value pair exists, the pair consists of an ordinary field value (date) and an expression (procured from the json field), ...
2
votes
1answer
78 views
Postgres connections are in "idle" state but cannot find any connected client
I have a single Postgres server and a set of PgBouncers configured to help me with connection pooling.
Recently, I discovered that the database connections are getting accumulated in "idle" ...
8
votes
1answer
479 views
Query time of fetching a particular, single row id by PK is extremely slow
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM contract
WHERE contract.id = 33129;
Index Scan using contract_pkey on public.contract (cost=0.29..8.31 rows=1 width=305) (actual time=0....
0
votes
1answer
53 views
What does "connection matched" in PostgreSQL logs mean?
I have this in the logs of a postgres instance:
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:20:02.277 UTC [78602] FATAL: password authentication ...
-1
votes
1answer
12 views
Why can't copy data into csv file with double quotes?
The data can be exported with single quote in copy command:
COPY mytest TO '/tmp/test.csv' DELIMITER ',' CSV HEADER;
COPY 5
With double quotes:
COPY mytest TO "/tmp/test.csv" DELIMITER ',' ...
0
votes
0answers
19 views
Please let me know if my query is right and why I have an error - must appear in the GROUP BY clause
I have a table flickr_edin with columns id, date_taken,usertags,userid and geom
I want to select all the rows that have "Museum" in the usertags column from my table
along with the geom ...
0
votes
1answer
15 views
pg_basebackup and multiple tablespaces
I have the following case:
the master database has an additional tablespace
I'm trying to replicate the master with the pg_basebackup tool
the directory / partition layout on the slave is the same as ...
0
votes
0answers
10 views
how can I grant permissions by table name, for all schemas, with postgres?
If I have a database called 'A' and multiple schemas but they all have a table called 't',
Schemas get added per account, but they all look the same inside and have the same tables.
I'd like some ...
-1
votes
0answers
28 views
Postgres Materialized View / FDW performaance
I am looking at the performance of a reporting application. The architecture is a summary matview (top tier) joining matviews (mid tier) that respectively join matviews (bottom tier) that query ...
0
votes
0answers
15 views
pglogical: list Schema/table part of the replication sets?
Is there a way to list all the Schemas which have been added to the replication_set?
# SELECT * FROM pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | ...
1
vote
1answer
36 views
Convert PostgreSQL table to structured JSON output
I have been having a horrible time dumping this example output into a neatly structured JSON output.
Here is some example data
CREATE TEMP TABLE isuck (path text[], city text);
INSERT INTO isuck(path, ...
3
votes
1answer
163 views
Is it possible in PostgreSQL to have some DBs under PITR stragtegy while other DBs are not?
I have a PostgreSQL server that contains a number of databases. The number varies over time between 20 to 30.
Some of these databases contain real production information for what we want to have the ...
-1
votes
1answer
27 views
INSERT-only tables? [duplicate]
I know I could stop normal users from using UPDATE and DELETE by revoking permissions.
No-one (including DB admins) should be able to UPDATE or DELETE from these tables. (This is for a write-model in ...
