Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,802
questions
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;
...
- 58.9k
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;
- 58.9k
-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 ...
- 38.4k
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 ...
CommunityBot
- 1
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 ...
- 15.3k
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 ...
- 111
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 ...
CommunityBot
- 1
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 ...
- 156
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 ...
- 23.3k
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 ...
- 123
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 ...
- 111
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 ...
- 15.7k
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 ...
CommunityBot
- 1
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/...
CommunityBot
- 1
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 ...
- 670
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(...
- 331
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.
- 38.4k
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 ...
- 381
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 ...
CommunityBot
- 1
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, ...
- 729
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 [...
- 15.7k
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 ...
- 121
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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%';
- 103
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 ...
CommunityBot
- 1
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 ...
- 101
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
- 670
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 290
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-...
CommunityBot
- 1
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/ ...
CommunityBot
- 1
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 ...
- 111
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 ...
- 101
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 ...
- 3,098
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, ...
- 1,026
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
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 ...
CommunityBot
- 1
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 ...
- 101
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 ('...
- 11
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 ...
CommunityBot
- 1
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 ...
- 38.4k