Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
14,273
questions
0
votes
0answers
3 views
Store LDAP bind credentials in environment variables for pg_hba.conf
I am working on setting up LDAP authentication for a PostgreSQL database and have added the following in my pg_hba.conf file:
# TYPE DATABASE USER ADDRESS METHOD
host all all ...
0
votes
0answers
3 views
error installing PostgreSQL, Could not find registry key HKEY_LOCAL_MACHINE\
i've been trying install postgres, i try solutions related to WSH,
nothing works to me.
here's the log
Log started 01/19/2022 at 21:06:35
Preferred installation mode : qt
Trying to init installer in ...
0
votes
0answers
5 views
Postgres 13: improve dense matrix creation from sparse matrix table
To represent a sparse 2D matrix of float values, I use a simple Postgres 13 database table that stores a single matrix cell value per row. I'd like to improve a query that computes the dense version ...
1
vote
0answers
9 views
ERROR: invalid byte sequence for encoding "UTF8": 0x00 in pg_stat_statements
I'm attempting to use pg_stat_statements to optimise my queries but ran into an unexpected roadblock.
SELECT total_plan_time + total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY ...
0
votes
0answers
9 views
How I can create a foreign table using a foreign data wrapper that references to a remote one with the same name?
I have 2 databases one named logs that has the table:
http_log:
id: serial
method: Varchar(10)
url: varchar(150)
I also have an another database named archiving that also has a table named http_log:
...
0
votes
1answer
11 views
Export colums containing (geo)JSON elements to CSV while keeping the JSON double quotes properly
I want to export 5 columns of a table, 3 of them contain ID, text and timestamp values but 2 of them contain JSON and GeoJSON elements, e.g.:
// Please, note the non-quoted string Value1
// when ...
2
votes
1answer
18 views
Wall clock hourly buckets out of time intervals with postgres or timescale
I have a record of session durations with start and end timestamps:
user_id | session_id | session_start | session_end
--------+------------+-------------------------------+------------...
1
vote
0answers
15 views
Independently checking if PostgreSQL server and client are installed
How can I independently determine whether the PostgreSQL server and client are installed in the Unix-based system?
I believe running psql --version checks only for the PostgreSQL client.
Is postgres --...
4
votes
1answer
20 views
PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be ...
2
votes
0answers
22 views
How to reduce the cost of a nested loop in a PostgreSQL query
PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012.
My table uniteobservation has 824 605 rows:
CREATE TABLE activite.uniteobservation (
gid serial NOT NULL,
numunobs character varying(20),
typunobs ...
1
vote
0answers
11 views
Greedily select rows that match against rules for each group
Suppose there's a product table with the following fields
id (pk), category (index), name, picture, stock
and we want to find ONE product in each of these category (1, 2, 3) that match at least one ...
0
votes
2answers
21 views
How to drop all indexes not used for primary keys or constraints
I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added.
Is there a way to drop all indexes except the ...
0
votes
2answers
30 views
Is updating a table by delete+insert better than merging with a temp table?
I have two tables in Postgres. The first table has 120 columns (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete ...
-2
votes
0answers
25 views
Does casting to type text slow a query down?
For example, the query I am looking at has a WHERE clause with:
WHERE table.col1::text = 'ABCD'::text
The column, col1 is type varchar(50)
Would these casts to text cause the query to be ...
2
votes
1answer
62 views
Environment variables in Postgres C extension
I cannot get environment variable in my PostgreSQL C extension code.
For example, this function always returns 111:
#include "postgres.h"
#include "fmgr.h"
#include <stdlib.h>...
0
votes
1answer
12 views
Potential pitfalls for upgrading PostgreSQL from 9.x/10.x to 13.x?
I have inherited from a predecessor a large number of Debian 9 systems that we are now looking at upgrading to Debian 11. Most of these run PostgreSQL 9 or 10, installed from Postgres's own ...
2
votes
2answers
24 views
Reclaim space after a failed VACUUM FULL
I made small miscalculation and my VACUUM FULL ended up filling the disks of the instance. I managed to free enough space to restart postgres but I am afraid it won't be enough to run VACUUM FULL to ...
1
vote
1answer
25 views
Postgresql 12 - ddluser and dmluser for same schema dml permission denied on tables [duplicate]
I have created a postgres 12 instance and executed the below command sequence:
psql --host localhost -U postgres -c "CREATE DATABASE mydb"
psql --host localhost -U postgres -d mydb -f /tmp/...
3
votes
2answers
43 views
Command to validate a postgresql configuration file?
I am generating postgresql configuration files via ansible, and putting them in /etc/postgresql/XX/main/conf.d/whatever.conf. I accidentally made a syntax error and broke my postgresql, requiring ...
0
votes
2answers
46 views
Best practice for a database used by multiple versions of an application
I'm working on a new product which will run on-premise at our customers. The configuration and usage of this application will be stored in a database. One of the requirements is that a newer version ...
-2
votes
0answers
18 views
writing a conditional sql select query based on checkbox value
I'm getting a syntax error at or near if. Any help would be appreciated
1
vote
0answers
29 views
How to prove Unique index could be better than non Unique index by the execution plan
I know Unique index will be faster than non Unique index theoretically if data is unique.
Because Unique index be able to provide more information let query optimizer choose more effective execution ...
1
vote
0answers
15 views
limit user's SELECT query to a fixed number of rows
i have read the following questions:
Limit Postgres results for a user
and also noted the recommendation of limiting a user's statement_timeout, however i would like something a little more consistent....
1
vote
0answers
12 views
Debugging connection leak in postgres instance in RDS
Trying to debug a connection leak in postgres database instance in RDS
SELECT * FROM pg_stat_activity;
Running the above query shows around 500 sessions, but all of them have wait_event_type Client ...
0
votes
1answer
38 views
+250
Problems with pg_upgradecluster
Friday I did an upgrade from PostgreSQL 13 to 14 with pg_upgradecluster (on Debian Buster). This worked so today I wanted to upgrade another server (Linux Mint 20.3 Cinnamon). This time it does not ...
0
votes
0answers
19 views
How to organize common columns to a single table?
I'm very new to database designing and I'm creating a small project to understand it better, I'm trying to come up with a new school crm. I started with the below schema:
create table student(
id ...
0
votes
1answer
16 views
What do these weird errors logged mean?
Today, I got these errors logged for the first time:
stats_timestamp 2022-01-16 10:58:25.625142+01 is later than collector's time 2022-01-16 10:58:23.356175+01 for database 0
statistics collector's ...
-3
votes
0answers
41 views
LIGHTEST weight relational database for node [closed]
I know that SQLite is lightweight but I wonder how much of the ram it takes up. I am trying to set up a personal POC in a aws micro (.5GB ram) and I am trying to max the efficiency at the knitty ...
0
votes
0answers
13 views
column "tum_first_name" of relation "tbl_users" does not exist, even when it does [duplicate]
I have a tbl_users, where there are a few columns like TUM_First_Name,TUM_Last_Name and so on. However, when I try an insert query, an error says :-
column "tum_first_name" of relation "...
1
vote
1answer
32 views
Upgrade to PostgreSQL 13 fails
I'm trying to upgrade PostgreSQL 12 cluster to version 13, with following script:
/usr/lib/postgresql/13/bin/pg_upgrade --check \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/...
1
vote
0answers
17 views
Postgres timezone issue when using postgres_fdw
I have a view stored on a foreign Postgres server (v 9.03) which pulls all of the data that was posted for the day.
I have foreign tables (using postgres_fdw) stored on my local Postgres servers (...
0
votes
0answers
20 views
If I have only 1 single type of record, but trillions of them, what is a sharding scheme that would scale?
Say I have 1 type of record, object, with title/slug/description fields, but I have trillions of them. Well, I start off with 1,000 records on day one, by end of year one I have 1M records, by year 2 ...
0
votes
0answers
41 views
Issue with query planner in postgres
I have this query which has suddenly turned really really slow
select *
from satelliteproduct
where supplier_id = 226
and supplierproduct_id IN ('123', '234');
If I query an explain analyze I can ...
3
votes
0answers
53 views
Azure postgresql really slow compared to VM
I work with postgresql (and Postgis) for years now on a VM with dockers, and I start to be used to tweak servers parameters and optimize request, and I never had the kind of problems I have with Azure ...
0
votes
0answers
17 views
Installing older versions of timescaledb for postgresql on macos
I'm running postgresql 14.1 on MacOs Monterey, this was installed as a requirement of timescaledb which is at version 2.5.1.
I had issues while installing timescaledb with another version of ...
-1
votes
0answers
12 views
Finding good index/constraint on Timescale Hypertable
So we got multiple Timescale Hypertables on our PSQL Cluster. I try to optimize the tables in the last time. Our Background: the Tables are around 300 - 500GB in size. Normally we will use queries ...
1
vote
1answer
16 views
Does the Read IOPS metric on RDS count OS cache hits?
I'm doing some tuning on a read-only replica, and I want to make sure I understand the "Read IOPS" metric. Certainly, any pages that are found in the postgres shared_buffers cache don't ...
2
votes
2answers
59 views
Why does SELECTing sum() return null instead of 0 when there are no matching records?
This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be.
...
0
votes
0answers
20 views
Creating multiple POSTGRES partitions using a for....loop
I've created a table that's partitioned by date ranges, (one partition per month) and the month is to be incorporated into the name of each partition.
I'd like to create a single partition for each ...
0
votes
1answer
17 views
Postgresql: most common values in array column
I have a table which looks like this:
post_id tags
--- ----
1 {'tag1','tag2','tag3'}
2 {'foo','tag3', 'tag1'}
3 {'bar','tag3','anothertag'}
...
Tags is an array column....
0
votes
0answers
15 views
PostgreSQL COALESCE arguments change resulting timezone for DateTime
I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:
SELECT
"CreatedOn", -- 7/24/2020 8:15:03 AM
COALESCE(&...
1
vote
1answer
32 views
Adding data to a DB as migrations
Some Background
We currently have a PostgreSQL DB that is "read-only", i.e. no user is allowed to write to it and all insertions are done in a controlled manner.
For a while, these were done ...
-1
votes
0answers
13 views
pgAdmin3 does not work on Raspberry [duplicate]
I have now spent quite some time to get pgAdmin3 working on a Raspberry without success. The Raspberry was installed with the latest Raspbian image (using the imager_1.6.2.exe program), postgresql 13 ...
0
votes
1answer
9 views
Can you run PL/Python or PL/v8 code from the database itself?
It it possible to run code that is stored in the database, on the database.
For example, I'd like a trigger to execute a function whose Javascript code is stored in the same database. (Normally, ...
1
vote
1answer
19 views
PostgreSQL: Suitable ID to uniquely identify client socket connection
I'm working on an application which needs to track the connection status of TCP sockets to a PostgreSQL database instance. I'm looking for a suitable ID which can uniquely identify a particular ...
1
vote
0answers
35 views
Windows Suspending PostgreSQL Process
Scenario:
Customer’s system
VM running Windows Server Standard 2019, 16GB RAM, Intel Xeon Gold 5122 @ 3.6GHz (2 processors 4 virtual processors)
PostgreSQL starts and runs fine for 5 minutes - 2 ...
0
votes
2answers
46 views
upgrade to postgres 14 from postgres 13 using pg_basebackup
I want to upgrade/move a running timescale cluster manage by Patroni using PostgreSQL 13 to a new hardware with PostgreSQL 14, the dataset is ~ 40GB but is taking ages take a backup using:
pg_dump -j ...
2
votes
0answers
37 views
What is the recommended host and VM block sizes, and RAID ARRAY strip size for a PostgreSQL OLTP database?
What I learn is: PG block size >= ni * VM block size >= nj * host block size
But what happens when the DB in a VM is on an SSD RAID ARRAY? The ARRAY strip size changes the game and puzzle me. ...
0
votes
1answer
18 views
does using PV and pipe affect perfomance importing database?
I'm importing a database that's almost 2.5 GB inside a docker postgres container. I wanted to know the stimated time and the progress so to check the progress I use PV command like this (I'm not sure ...
2
votes
1answer
38 views
Limiting the number of records from nested INNER JOIN table
The context is as follows:
We have 3 tables: We'll call them A,B and C.
A has a foreign key referencing table B, we'll call it "bId". The relationship is a ManyToOne. (One record in B can ...
