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

Can you use PREPARE statement or plpgsql function to make first query on new connection fast?

Currently, I am standing up an RShiny (web) application that queries data from an AWS RDS PostgresSQL database (PostgresSQL 12) which is pushed forward to my application. When a new user logs into the ...
1
vote
1answer
10 views

Updating PostgreSQL application_name during the client session

I am trying to associate slow queries on PostgreSQL to applications that cause them. I have set rudimentary application_name tracking. Some applications contain several independent tasks running ...
-1
votes
0answers
22 views

PostgreSQL optimize LEFT JOIN and GROUP BY query

I have been dealing with a large SQL query with multiple LEFT JOINS and a GROUP BY statement. The bottleneck of the query seems to be the GROUP BY part. Here is the query : SELECT SUM(CAST(l.value AS ...
0
votes
0answers
21 views

how to release the postgresql table space smoothly

I am using this command to release PostgreSQL 13 table space right now: VACUUM FULL VERBOSE sentence; but this operation will lock the table, any better way to release table space?
0
votes
1answer
22 views

Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"

Is it possible to move data to the new table sourcing other tables without losing progress in the middle? I think of plpgsql that read data by chunks and commit after every N inserts... If the process ...
0
votes
2answers
25 views

UNION SQL with LEFT JOIN

I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it? CODE : SELECT Date_part('year', pr.data_chiusura) AS anno, ...
1
vote
1answer
16 views

Is PostgreSQL (14.1) able to tell me how much RAM it is currently using?

I'm trying to keep track of how much RAM PostgreSQL is using. I need to do this with a SQL query -- not relying on the always incompatible and finicky OS tools. Is there a way to get the currently ...
0
votes
0answers
6 views

WAL streaming backups with barman - WAL archive: FAILED

I am setting up a barman server to backup a remote PostgreSQL server and am digging my way through a bunch of FAILED messages I get on barman check [pg] Server pg: WAL archive: FAILED (please ...
0
votes
1answer
20 views

Create readonly user in postgresql

Using pg 13.4, created a readonly user reader for database abc, via: CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'...
1
vote
1answer
14 views

Dump/restore Postgres DB without extensions

I'm working on a script that "deploys" a DB from one environment to another (read: dump on A, restore on B). For this, I utilize the built-in pg_dump and pg_restore: pg_dump -v --host="$...
1
vote
1answer
15 views

AWS RDS postgres error when running sql function: RI_constraintrigger_a_19346 is a system trigger

So I need to import django fixtures (e.g. a django's database dump essentially) in postgres on a aws rds instance. I have tested locally that the constraints of the DB are all valid (by importing ...
0
votes
1answer
19 views

Export Postgres data without superuser privileges

I'm using Postgres 14 on CentOS 7. I would like to export a single table's worth of data but I don't have superuser privileges. Unsurprisingly, I get this error myapp=> COPY myapp_currencyprice ...
0
votes
1answer
22 views

what is the performance impact about pg_stat_statements

I am using pg_stat_statements to get the slow query in my production PostgreSQL 13. I am not sure the performance impact about this plugin and what should I do to improve the performance like only ...
0
votes
1answer
26 views

Pass a variable with INSERT, UPDATE, DELETE statements

I am looking for the ways where could be possible to send some data with psql query statement. For example: insert into mytable (id,name,site_id) values (default,'test',1); Real structure of mytable: ...
0
votes
0answers
34 views

SQL query to get daily record for an event

I posted before and made some progress so posting again but with a different question: I now have this table with the following data: start_time end_time id ...
0
votes
1answer
23 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
19 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
9 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
26 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
16 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
37 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
11 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
19 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
39 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
23 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
28 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
18 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
28 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
12 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
16 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
22 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
503 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
35 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
33 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
39 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
71 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
21 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
526 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
25 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
19 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
37 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
51 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
23 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. ...

1
2 3 4 5
283