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
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 ...

1
2 3 4 5
286