Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,904
questions
0
votes
0
answers
5
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query:
select * from public.extrinsics where chain_id = 1 ORDER BY "extrinsics".&...
- 113
-1
votes
0
answers
12
views
What permissions are needed to prevent ERROR: permission denied for tablespace pg_global in RDS?
I'm looking for some help identifying a permission I need to grant to prevent an error in a proprietary app. The App is loading data into an AWS RDS postgresql database. Loading data is working fine....
0
votes
3
answers
596
views
Do I have to add accounts to "pg_hba.conf" to allow the account to use the database?
I was following a tutorial to use PostgreSQL with a server application (both are running on the same computer). The tutorial was using SQL commands, but I used pgAdmin. I added a new user 'gitea' with ...
CommunityBot
- 1
0
votes
1
answer
15
views
Unnest is not preserving nulls or empty values while inserting the data
I have a Table Student where I have an XML as input
inputXML=
<Students><student><id>1</id><Name>SAM</Name></student>
<student><id>1</id>&...
- 77.2k
0
votes
1
answer
227
views
Postgres notify if connection timeout is occured
I want to set up a separate connection for every user of my app.
I have some data structure for this to store connections.
Obviously, I should delete the expired connections somehow.
There are ...
CommunityBot
- 1
0
votes
1
answer
309
views
auto_explain does not log
looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain'
...
CommunityBot
- 1
-1
votes
0
answers
18
views
Postgresql won't start after moving data directory
My use case: I am running Linux Mint 21. I have the OS on a 500 GB drive. I have a mounted 8 TB drive to contain data for use in machine learning. Some projects can conveniently work with .csv files ...
- 99
0
votes
1
answer
882
views
Does a read replica help if the problem are heavy queries?
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
CommunityBot
- 1
0
votes
1
answer
242
views
Postgres Repeatable Read vs Selecting rows that are changed by another ongoing transaction
Let's say I have a set of select statements that query important field values in a for-loop.
The goal is to make sure that the rows are not updated by any other transaction so that this set of selects ...
CommunityBot
- 1
0
votes
1
answer
2k
views
How to create a database or role in pgAdmin4?
I want to create a database in pgAdmin4 but a corresponding item in the menu is grayed out.
I also tried to create a role, thinking that I might be lacking permissions to create database in first ...
1
vote
1
answer
267
views
How to force Redshift/Postgres to aggregate nth_value?
I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:
select
customer_email,
nth_value(created_at, 1) over (partition by ...
CommunityBot
- 1
0
votes
2
answers
327
views
Parameterized Query without Prepared Statements and PgBouncer
my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements.
So I have 2 options, ...
CommunityBot
- 1
1
vote
3
answers
19k
views
How to update complex jsonb column?
I have a table with following definition:
create table json_test (
filter_data jsonb);
and i insert to it values like this:
'{"task_packets": [
{
"state": "PROCEEDING",
"...
- 163k
1
vote
1
answer
898
views
Remove the deeper jsonb element in Postgres [duplicate]
[
{
"type":"test.added",
"values":[
{
"field":"field1",
"value":"fieldValue1"
...
- 163k
0
votes
0
answers
19
views
Postgresql swapping even there is plenty free memory
Let me ask a question about my problem im dealing with in our production database. We have postgresql 13 database which which using swap even when there is enough RAM and i dont understand it at all. ...
Ivo Mičánek
1
vote
3
answers
30
views
Seq scan on OR condition
I'm using PostgreSQL 11. I have a table with ~500к records and its size is around 2GB. If I'm running this query:
SELECT DISTINCT point.id
FROM "buildings_point" as point
LEFT OUTER JOIN ...
- 163k
0
votes
1
answer
24
views
Is UPSERT feature complete?
Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value.
Say we have a table of businesses and some ...
- 163k
29
votes
2
answers
25k
views
PostgreSQL UPSERT issue with NULL values
I'm having an issue with using the new UPSERT feature in Postgres 9.5
I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
- 163k
0
votes
0
answers
12
views
Does pg_listen blocks a thread on the database server?
As stated in the title. If yes, is this blocking per listener?
- 21
0
votes
1
answer
483
views
PostgreSQL server running very slow at minimal work load
Postgres server running very slow. DB has two tables and when I run explain analyze for below query, taking more than 10min and query running forever.
Tables DDL, db<>fiddle.
User query:
...
CommunityBot
- 1
0
votes
0
answers
17
views
How can i get the count of millions of records from a view in PostgreSQL as fast as possible?
I created a view that hold millions of records,
I want to get count of all rows,
I used this query but it takes time alot to run
select count(*) from my_view
is there any way to get faster?
- 30.5k
2
votes
1
answer
279
views
Postgres partition pruning with text prefix matching
I have a table where the natural identifier is a text like this: AB-C123456E-F, which is my primary key.
The table is approaching 200 million rows, and so to get better performance on the unique ...
CommunityBot
- 1
9
votes
3
answers
10k
views
the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?
Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it.
quote from MySQL 5.7 Reference Manual / GRANT Syntax
REFERENCES ...
- 505
0
votes
1
answer
318
views
optimizing or estimating postgresql table size on disk
This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there.
I have a table of web ...
CommunityBot
- 1
0
votes
2
answers
14
views
How can reset query buffer in psql's console?
I want to set a comment on some column ,in the sample database to add comment on table tablename's column field:
sample=# comment on column tablename.field is 'don\'t use it';
The console turn into ...
- 222
0
votes
0
answers
14
views
Create index on big table increase IO:XactSync wait event for other sessions
AWS Aurora (postgres 11.9) after downgrading instance from db.r6g.8xlarge to db.r6g.4xlarge instance and enabling logical_replication
I'm trying to rebuild indexes (cause of bloat) for table size 30GB ...
1
vote
2
answers
556
views
pg_restore fails with drop default on identity column
Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below:
pg_restore --verbose -...
CommunityBot
- 1
11
votes
5
answers
4k
views
How can I export a subset of tabledata from a prodution database into my local testing database?
We have a relatively big production postgres based db: ~20GB.
The PostgreSQL database is hosted on heroku.
I would like to copy a small subset of the table data to my local database so I can run ...
1
vote
1
answer
329
views
Remap array columns to single column with value
Here is the sample database schema.
| id | Critical | High | Low |
--------------------------------------------
| 1 | {apple, ball} | {cat} | {dog, egg} |
Now I want to remap these to ...
CommunityBot
- 1
3
votes
2
answers
6k
views
Convert SQL server stored procedures to postgreSQL
I have a SQL Server database that I am migrating to PostgreSQL 12.0.
I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, ...
- 1
1
vote
0
answers
8
views
Limit access to specific view and restrict access to it
I have database that has lot of users, roles and groups. I've just created new view using postgres user and i've created new user usera. I want only postgres (the creator) and usera to have access to ...
- 111
0
votes
0
answers
10
views
Grant access to view only to specific user
I have a view which was created by postgres user. I want to give access to that view also to userx user. Other users shouldn't have access to that view. How can i achieve that?
- 111
1
vote
1
answer
194
views
pg_cron timezone issue, unable to run at specified time
Unable to run at proper timezone.
I want to run two things
bash scripts
Stored procedure
For both case I found it run at different time. Instead of 1 AM and 3 AM pg_corn runs another time. Why it is ...
- 166
1
vote
0
answers
14
views
"ERROR: could not access status of transaction Could not open file "pg_xact/0029": No such file or directory" when attempting to LISTEN
When attempting to LISTEN to any channel, PostgreSQL says that Could not open file "pg_xact/0029": No such file or directory.
This is a freshly restored database, so it can't be database ...
- 111
0
votes
0
answers
6
views
Portgresql Insert from CSV to table1 when exists in table 2 (Foreign Key) else Insert value in table 2 and then inser in table1
I want to insert data in table 1 imported from csv performing the following steps.
Copy values from csv_depo to T1_depo only if they exist in T2_depo. If not, first copy the values from csv that ...
0
votes
0
answers
10
views
backend_xmin or backend_xid go back to NULL in running transaction
I have monitoring on pg_stat_activity that alerts me when a transaction that has (backend_xmin IS NOT NULL OR backend_xid IS NOT null) gets older than 35 minutes (judging by xact_start). Sometimes, ...
- 126
1
vote
1
answer
49
views
Mismatch between the total number of rows in a sql join
When I join entries from the points table (n = 34, 436) to the mid_pts table (n=1.98 million) the total number of rows of the table_joined is n = 26,016. However, when I add the unjoined_pts table of ...
- 163k
3
votes
2
answers
123
views
psql can't connect to existing db
i have problem with connecting to database via psql. I get this error:
postgres@server:/var/lib/pgsql # psql -d aisgdmp2
psql: error: FATAL: database "aisgdmp2" does not exist
DETAIL: The ...
- 2,062
0
votes
0
answers
12
views
PostgreSQL trigger to allow update of only one specific column
Is it possible to create such trigger function, without specifying by name the other columns that shouldn't be affected?
Example:
Create table test(id integer, name text, amount numeric);
I'd like to ...
- 119
1
vote
4
answers
3k
views
Return sample row if result set is empty
What would be the most practical way to write a query, that returns single row in case of empty result set.
Something like (pseudosql)
(SELECT 1 AS c1, 2 AS c2 FROM ... WHERE ...) OR_ELSE(SELECT 0 ...
- 101
0
votes
0
answers
13
views
How I can aggregate a final store schedule in postgresql?
I have the following table that I keep for each day of week when a store is open:
store
---
id PK
name VARCHAR
weekly_store_open_schecule
---
id PK UNSIGNED SERIAL
store_id INTEGER not null ...
2
votes
1
answer
661
views
Filter by list of values pairs
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal
Products table may contain millions and billions of ...
- 973
13
votes
3
answers
15k
views
How to properly monitor the number of PostgreSQL database connections?
I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
- 172
-1
votes
0
answers
25
views
Double encoding in Postgresql log file
I have server Debian 9 with Postgresql 11.7. When I read a log file (like less), some messages are in UTF-8, others are in CP1251. How to bring the log file to one encoding?
List of databases
...
12
votes
2
answers
5k
views
Is the system column "ctid" legitimate for identifying rows to delete?
I have a table with hundreds of millions of rows that I need to delete data from.
The existing indexes are the most efficient.
I can however use the existing indexes to find the rows to delete by ...
- 163k
4
votes
1
answer
371
views
Pagination - Text comparison with greater than and less than with DESC
I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
CommunityBot
- 1
2
votes
1
answer
772
views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
CommunityBot
- 1
0
votes
1
answer
341
views
Consequences of cascade drop on sequences/functions/views
I am migrating some data to another server while dropping all sequences/functions/views. When I try to do simply
DROP VIEW | SEQUENCE | FUNCTION | AGGREGATE without CASCADE, errors are thrown to ...
CommunityBot
- 1
6
votes
5
answers
854
views
Are 'Functional Indexes' Supported in MS SQL Server?
I've just come across expression based indexes (aka "functional indexes") when reading through the documentation for PostgreSQL (I wasn't aware of such indexes previously).
I'm just ...
- 457
0
votes
1
answer
62
views
Performant design for updating/creating multiple rows at high frequency
I have a postgres table with rows retrieved using an asset and timestamp column. In a short time interval (about a second) on hundreds of assets, I need to
retrieve the latest couple rows of each ...
- 618