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
2 answers
26 views

Can I create a cast from a composite (table or row) type to a domain?

Let's say I have a simple table foo, and domain bar CREATE TABLE foo(x) AS VALUES (1),(2),(3); CREATE DOMAIN bar AS text; Is it possible to make a cast such that I can do SELECT foo::bar FROM foo; ...
0 votes
1 answer
15 views

Is it slower to pass a composite type representing a table to a function then the individual arguments?

Let's say you want to create a function CREATE FUNCTION f(a,b,c,x,y,z) FROM t; Is it any slower to restructure the function such that this works, CREATE FUNCTION f(t) FROM t;
-1 votes
1 answer
15 views

Sensible way to keep data consistent primary/standby in postgres after primary vm revert

Lets say that I create a primary/secondary postgres nodes using wal streams and pg_basebackup. Create a snapshot on vm level of primary node. Insert some random data that is synced to secondary as it ...
1 vote
1 answer
589 views

Django Multi Table Inheritance and Preserving Child and Child History

Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
1 vote
1 answer
34 views

What is the best strategy for changing a primary key from int to bigint in postgres when the column has dependencies?

We have a few primary key id columns in our database with type integer that will soon overflow. We need to change the types of these columns to bigint with zero downtime. In order to do this, we've ...
0 votes
1 answer
33 views

PostgreSQL: Generate rows for missing years within range (system year + 9)

I have an PostgreSQL 13.6 table that has rows for certain years: with data (year_, amount) as ( select 2024, 100 union all select 2025, 200 union all select 2025, 300 union all select 2026, 400 union ...
1 vote
1 answer
232 views

Reusing the same connection to read uncommited transaction

My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging What I tried: I start a pgbouncer instance, and set the max-db-connection to 1 From my node ...
0 votes
2 answers
24 views

NpgSql - How to pass parameter into DO block in PostgreSQL query

I am trying to use a parameter passed into a PostgreSQL query inside a DO block within that query. It fails though, and seems to be parsing the statement as if the parameter was a column. Is it ...
53 votes
7 answers
64k views

What are the differences between "Stored Procedures" and "Stored Functions"?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
0 votes
0 answers
33 views

Migrate postgresql to MariaDB

I try to migrate mysql Zabbix database from Postgresql to MariaDB using MySQL Workbench migration Wizard I have compiled psqlODBC and enable it When I setup the source database in the wizard the test ...
0 votes
0 answers
15 views

PostgreSQL Real-Time Analytics

Latest months I am working with PostgreSQL while coming from Microsoft world (SQLServer). My scenario is the following: Transfer data from different databases into single one (same set of tables and ...
0 votes
1 answer
21 views

Time series analysis and custom aggregate function for Postgres

I have a table with time series. It contains snapshot and increment value snapshot means concrete value increment means take previous snapshot and add increment value to get snapshot value sensor_id ...
3 votes
2 answers
3k views

How do I get Postgres DO $$ to work from the bash prompt?

Postgres 9.6.9 on Linux RHEL 6.10 If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail. So I switched ...
0 votes
3 answers
3k views

Postgresql Backup all databases separate files

I need to create an automatic backup of all databases from PostgreSQL (running on a Windows machine). I need to know if it is possible to get a backup of all databases but in separate files. Using ...
29 votes
3 answers
6k views

Why is the default page size for databases so small?

In PostgreSQL and SQL Server, the default page size is 8 KB, in MySQL is 16 KB and in IBM DB2 and Oracle it is only 4 KB. Why are these page sizes so small? Is there a historical or memory usage ...
2 votes
1 answer
1k views

Can't enable wal2json in postgres 12 docker image

I'm trying to enable wal2json in an image extended from postgres:12 image. But i'm getting an error: postgres=# create extension wal2json; ERROR: could not open extension control file "/usr/share/...
0 votes
1 answer
11 views

When performing a migration, how do you handle a new column needing a value but not being able to provide one for the migration?

I'm adding a timezone column to a table; the value cannot be NULL. However, I can only alter the table if I add a default value, but there isn't a way to determine that value before the migration. How ...
1 vote
1 answer
286 views

Why do PostgreSQL WINDOW functions affect the final ordering of all rows in the select?

Let's say we have the following query. select name, pos, rank() over (partition by constructor) r, format('%s / %s', row_number() over (partition by constructor), count(...
1 vote
1 answer
17 views

Does it make sense to prefer jsonb data type over text[] even if I am only planning to store text array?

I am thinking from the perspective that (I believe) jsonb is more efficient and can get indexed more easily.
0 votes
0 answers
21 views

Refresh materialized views upon database restore

Our database (PG 12) uses a few materialized views, some depending on others. We use pg_dump for backups and psql to restore. And, we do this restore every once in a while, for example when upgrading ...
0 votes
1 answer
374 views

Filling in Dates - Postgres/Redshift SQL

I have a dates table with a "calendar_date" field and I have been trying to utilize it for when I need to fill in missing dates when an id does not have a record for a particular day AND ...
0 votes
0 answers
24 views

Using different public keys on the same (encrypted) column [migrated]

Generally, when encrypting sensitive-data columns, we use the same public key for all the rows. I have noticed that it is possible to use different public keys for different rows. For example, ...
0 votes
0 answers
16 views

Postgres 9.6 where two DB

I have a configuration where one system is running the main db another system is the hot standby DB. The connection is managed by pgpool. The issue I am getting is: Primary DB node XLOG is [...
0 votes
0 answers
13 views

Postgres 12 CREATE Statistics not making a difference to reduce overestimate the planner is making on query hitting large table

Have a query which is hitting 2 tables which are quite large in size (1 has nearly 2 billion rows and the other nearly 1 billion). Query: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) ...
0 votes
1 answer
31 views

Is it possible to write a generic trigger for all tables in Postgres?

I want to listen to every transaction that is recorded in the database And I noticed that there is a concept in Postgres under the title LISTEN and NOTIFY (Of course, I am not sure that this is the ...
0 votes
1 answer
444 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
2 votes
2 answers
2k views

Postgresql return results of dynamic SQL

I'm using Postgresql, version 12. My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
3 votes
2 answers
373 views

Getting Postgres CREATE TABLE statements

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
56 votes
3 answers
67k views

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
0 votes
1 answer
699 views

Ways to optimize the PostgreSQL/TimeScaleDB query

What approaches I could take to optimize the performance of the following joinless query on the following PostgreSQL/TimeScaleDB table? So far, I managed to create the right index which is obviously ...
0 votes
0 answers
38 views

Automatic data decimation for old partitions in Postgres

We store our timeseries data in a partitioned Postgres table with a 1 month period. The frequency with which new data is received ranges from 30 seconds to several hours. For all partitions except the ...
0 votes
1 answer
25 views

problem in updaing table in postgresql [closed]

select * from student update student set name = 'Ronald' where ID = 2; Gives ERROR: syntax error at or near "student" LINE 2: update student
0 votes
1 answer
362 views

Ram consumed by single query in Postgres using SqlAlchemy

I have a list of 36 queries ranging from low complexity(No joins) to high complexity(joins with subquery). I want to find the RAM Consumed by each query. This is required for performance testing for ...
2 votes
1 answer
923 views

Why AWS RDS PostgreSQL suddenly consumed all free storage

I've got a AWS RDS PostgreSQL (13.x). The DB had 100 GB storage of which around 30 GB was used. Suddenly, during one day all the free storage was eaten by PostgreSQL (so it seems) and even after ...
2 votes
1 answer
200 views

A complicated scenario with TimescaleDB and compression

We have a timescaleDB with a fairly large data set (> 1.5TB, >1B rows). The project has been running into one delay after another because we just can't make the queries we need to perform fast ...
1 vote
0 answers
20 views

PostgreSQL: data retrieval from a private (authentication-based) RESTful web API using a foreign data wrapper

In some cases, one want to feed or populate some PostgreSQL database tables using data fetched from the web, e.g. through a RESTful API (serving JSON features). I've recently discovered foreign data ...
3 votes
1 answer
1k views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
2 votes
1 answer
242 views

Enterprisedb, PostgreSQL cast to date

How to cast '2015-11-13 00:00:00' (text or timestamp) to '2015-11-13'(date) in Enterprisedb (EDB) ? With 4 queries below: 1/ SELECT '2015-11-13 00:00:00'::date ; --> result: "2015-11-13" 2/ ...
2 votes
2 answers
10k views

Pgpool install - libpq is not installed or libpq is old

Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error: configure: error: libpq is not installed or libpq is old ...
0 votes
1 answer
137 views

Postgres query stuck on wait_event "MessageQueueSend"

I recently upgraded a postgres database from version 9.3 to 14.5 (yes, quite a jump). The upgrade seemed successful, but I am getting an issue with a particular query (see at the end). The query ...
0 votes
0 answers
17 views

How can I debug an issue causing queries to get stuck waiting on ClientRead indefinitely?

Today an issue suddenly appeared in our database clients where ~0.01% of queries would get stuck indefinitely - Postgres says it's waiting on the client to read and the client also says it's waiting ...
0 votes
1 answer
31 views

Postgres return multiple columns from subquery

I have found older posts but I'm curious if there is any better solution for my case. I have working code but it is really slow. I tried to return two columns in subquery but I have an error (the ...
0 votes
0 answers
27 views

Is calling a stable function performance comparable to reading values from a table?

Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name). _test_has_group_feature(group_name, ...
0 votes
1 answer
73 views

PostgreSQL pg_basebackup: error: could not receive data from WAL stream: SSL SYSCALL error: EOF detected

I am running a WAL log shipping standby/backup server configuration, and I have been using pg_basebackup and to create a full backup of a PostgreSQL database without issues for awhile. But as of this ...
1 vote
1 answer
252 views

PostgreSQL pg_rewind use cases

Let's say that I configured streaming replication between Server A (master) and Server B (slave). If server A goes down and Server B is promoted, would I be able to use pg_rewind, where Server B is ...
0 votes
1 answer
13 views

run multiple function selects postgres

I need to run a remove_user function 1000s of times in single postgres query with a different values. I have created the statements. Currently If I select all still only executes first line. I am ...
0 votes
0 answers
21 views

Aws DMS CDC Replication is not working, giving errors

Firstly I successfully migrated DB from mysql mariaDB to PostgreSQL via Aws DMS Task1 with full-load migration type selected. Now with the Task2 of enabling replication with cdc migration type, its ...
1 vote
0 answers
17 views

Merging by name from two tables

I have a table 'labels' which has a many to many relationship to the table 'tokens' using the reference table 'tokens_labels'. I'm trying to put together a postgres function for merging classifiers ('...
3 votes
1 answer
4k views

Why pgAdmin4 invoke "'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte" error?

I'm newbie in postgreSQL ,when I'm trying to create a new local server using pgAdmin4 ,it shows me this error : 'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte I'm ...
0 votes
1 answer
25 views

PostgreSQL directories explained

I almost 'randomly' dig into some of the classical PostgreSQL installation folders on Ubuntu 22.04.1 and I was naively wondering what exactly are the following different postgres directories intended ...