Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
14,104
questions
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 ...