Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
12,786
questions
0
votes
0answers
3 views
PostgreSQL predicate not pushed down (through join conditions)
Consider the following data model in a PostgreSQL v13 system;
Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
0
votes
0answers
11 views
How do I set up a self referential relationship using postgresql and sqlalchemy
I have a table of Users, and for some of these users I have information on their parents. I would like to build a table structure so that I can call User.mother and User.father and return their ...
0
votes
0answers
13 views
How to check active connections in PostgreSQL?
All connection are in LAN.
I want to know IP Adresess and if it is possible toogether with ProcessID of All active connection (to all databases).
How to check active connections in PostgreSQL ?
0
votes
1answer
30 views
SQL (Postgres): Subquery in WHERE clause with IN operator is slow
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
select tp.task_id
from task_participant tp
where tp.person_id = p.id )
and p.id = 1234
...
0
votes
2answers
16 views
How to use a postgres variable in the select clause
With MSSQL it's easy, the @ marking the start of all variable names allows the parser to know that it is a variable not a column.
This is useful for things like injecting constant values where a ...
1
vote
0answers
7 views
Query not well formed in Sqlalchemy for select [migrated]
I am trying to generate the following raw query using sqlalchemy core:
SELECT library.name, (SELECT ARRAY (select book.title
from books
where book....
0
votes
1answer
14 views
Does pg_restore continue after internet connection got lost
I know a query runs until it returns data when connection to the client is lost. However, what if you use pg_restore. Does it upload the restore file and then does the restore on the server? Or does ...
0
votes
0answers
10 views
How to change the Postgres hostname in pgadmin 4?
By default, the database hostname is localhost after the Postgres has been installed.
Can the hostname be changed to something else like localhost\\abc?
If yes, how can I do it in Windows/pgadmin?
...
0
votes
0answers
9 views
Can't refresh Materialized View Concurrently
I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error:
SQL Error [XX000]: ERROR: unexpected attlen: -2
Where: SQL statement "SELECT ...
1
vote
1answer
20 views
PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table
I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done.
I have a table:
create table test(action_date date, item_num int, <...
0
votes
0answers
6 views
Why all instances of PostgreSQL are not listed in stackbuikder (enterpriseDB)?
On a windows server 2012, There are two instances of PostgreSQL 11.5.
The first in running on port 5432 and was created with EnterpriseDB installer.
The second was created manualy with initdb then ...
0
votes
1answer
21 views
Permission denied for view beyond its own schema
There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.
CREATE VIEW staging_fiat.udz_odx_dtcs AS SELECT * FROM rohdaten_fiat....
0
votes
0answers
4 views
Pgpool create a cluster node with no application traffic
I have a pgpool cluster with 2 standby and 1 primary node. I am trying to create another node which will get replicated but will not handle any request or application traffic. It will solely be use ...
2
votes
1answer
23 views
Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL
I am currently working on a small presentation showing when postgresql will switch from index scan to bitmap scan to sequence scan.
First example (hint all index scan…):
Loosely based on a similar ...
2
votes
1answer
70 views
To reduce recovery time (and save space): Is it possible to ignore some PostgreSQL 13 tables (or schemas) when backing up using Barman?
We have some large staging/scratch tables in one of our PostgreSQL databases. The data in them can be automatically regenerated from other databases after recovery of the database, and aren't business ...
0
votes
1answer
14 views
SQL Error [42501]: ERROR: permission denied for table
I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the ...
1
vote
0answers
16 views
insert or update row based on timestamp
I'm looking for a solution in postgresql to insert or update a row, based on the timestamp of that row.
What I'm looking for is the following:
I want to insert the use of a specific hashtag, and count ...
4
votes
2answers
173 views
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id | name | owner |
----------...
0
votes
0answers
11 views
Postgressql role drop
I am having the same issue dropping a role. Did so much research on google but no help.
SQL Error [2BP01]: ERROR: role "readonly" cannot be dropped because some objects depend on it Detail: ...
0
votes
1answer
13 views
Variable problems in PostgreSQL with language plpython3u
I am setting up a function where recursive action is relevant. The function basically maps two floats according to a rule and then returns a string. The code below is stripped for the actual function,...
0
votes
1answer
8 views
postgresql crash in master node with streaming replication
We have a master/slave postgresql cluster with streaming replication and pgpool.
Versions in both postgresql servers are.
postgres: 9.4
OS: Debian GNU/Linux 8 (jessie)
PGPOOL server:
version pgpool: ...
-1
votes
1answer
34 views
Improving database design, is this a valid case for entity attribute values?
Well currently we are not using eav, however our database seems a bit finicky and one suggestion was something along the lines of entity attribute values.
What we have is a table which acts as "...
0
votes
1answer
25 views
Unused Indexes - PostgreSQL Database [closed]
I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes?
SELECT
idstat.relname AS ...
0
votes
0answers
24 views
Database system is in recovery mode: Segmentation fault
PostgreSQL version : 12.4
Server: RHEL 7.9
I got postgres server into recovery mode for a minute and then came back normal.
Looking into logs, found this error before it went to recovery mode:
db=,...
2
votes
1answer
24 views
PostgreSQL Query with pagination is slow when using Merge Join
I am experimenting with trying to optimize a query that's used to iterate over pages of rows with a joined table. After changing the query from using OFFSET to WHERE id > last_id_from_prev_page and ...
0
votes
0answers
35 views
Which DBs hide rows INSERT order (header metadata related)?
I discovered that PostgreSQL has a "t_xmin" row header metadata field that is used to store the transaction ID of an INSERT command.
So, as I understand, even if I use a random INT primary ...
0
votes
1answer
30 views
(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres
So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects.
How I ...
1
vote
1answer
33 views
postgres query performance
I can't find a good way to rewrite this query or improve the performance:
query:
update company_holding c
set issuer_stim_buy_quarter = sq.estim_quarter_buy
from (select h.company_id
,h....
0
votes
1answer
20 views
Safely shutting down seemingly misconfigured (but working) PostgreSQL replication/archiving
I'm helping administer two PostgreSQL servers (one primary, one replica) plus a separate server running Barman for WAL archiving. I was not originally involved in setting up the replication and ...
0
votes
1answer
62 views
Using function is much slower than running query
I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this :
CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
DECLARE
q ...
0
votes
1answer
28 views
Performance improvement needed for query with large IN
I have a relatively simple table:
CREATE TABLE t_balances (
f_index BIGINT NOT NULL
,f_epoch BIGINT NOT NULL
,f_balance BIGINT NOT NULL
);
CREATE UNIQUE INDEX i_balances_1 ...
2
votes
1answer
73 views
Numerate duplicate of every value
I need to add a column to my table with the count of every occurrence of. So if the value is unique the column value should be 1, but if there is multiple copy i need a differente value for every row (...
1
vote
1answer
27 views
Postgres tsrange index for small sliver of time
I have a table (events) that has two timestampz columns (start_time and end_time). My program queries the table every minute for all records that are happening right now:
select * from events
where ...
0
votes
1answer
14 views
ForeignKey setup clarification on updating related fields
Considering these two tables in PosgtreSQL:
# report
+----+-------------+--------------------+
| id | report_name | report_description |
+----+-------------+--------------------+
| 1 | RPT | ...
0
votes
0answers
35 views
What would cause a database‐wide desync of primary key sequences in PostgreSQL? [closed]
We had a bit of a crisis last week: the sequences governing the primary keys in every single table of our PostgreSQL database reset to values that were in use around April last year. The result, of ...
0
votes
1answer
26 views
Thousands foreign keys referenced to one table and other postgres scaling philosophy
I'm inspecting one legacy and quite big database which is growing faster nowadays than before.
Following query
select count(*) from information_schema.tables where table_schema = 'public' returns a ...
0
votes
2answers
18 views
Overriding default datetime output format
By default, my Postgres database outputs a DateTime in the current format YYYY-MM-DD hh:mm:ss. However, I want to override this and output it in this format YYYY-MM-DDThh:mm:ss with the T in between ...
1
vote
1answer
38 views
High amount of disk writes during upsert with no changes on table
We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently).
...
0
votes
0answers
26 views
Storing URL paths as unique identifiers in Postgres [closed]
I need to find a performant way for storing and looking up friendly paths for a multi-tenant SaaS, e.g. app.com/client. I also need to retrieve the paths from the database to generate human-readable ...
0
votes
1answer
28 views
How to show number of tables for each database in Postgres?
Related to my previous question, I use the code below to get all databases:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
AND datallowconn
AND datname <> 'postgres';
How to get ...
0
votes
1answer
22 views
Is there any better way to list all the databases in Postgres except database named postgres?
SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')
Using the code above, I get all the databases except postgres as shown in the image ...
-1
votes
1answer
46 views
What would be the best database for this set of environmental data? [closed]
Thanks for being out here. For educational purposes I want to create a website that queries a database of rising temperatures across the globe. The data can be seen here in an .xlsx format: Climate-...
0
votes
1answer
19 views
FATAL: database does not exist in postgresql for jdbc
I create a database by installing Postgres on my ubuntu 20.04 machine and used the following commands to create it:
sudo su - postgres
psql
CREATE USER preopus WITH PASSWORD 'admin222';
CREATE ...
0
votes
0answers
15 views
PostgreSQL and node.js problem with error at or near “WHERE” [closed]
I am making a query where I want to assign a new order to courier with minimum active orders and need to write his id into order table. [order] is value I picked up from form of web application and ...
0
votes
0answers
22 views
Postgres: Allowed connections from any ip but still can't connect from Github Actions
I'm having some trouble setting my Postgres database to allow connections from all origins, specifically I can't seem to get my API to connect to it while running Github Actions for testing purposes.
...
1
vote
1answer
38 views
Postgresql Query estimated time vs actual time difference
We have a hosted Postgres 11 DB server to fetch medium sized dataset and ingest into another application.
"States" table is the largest one in the following query, which will fetch ~1.5 ...
-2
votes
3answers
57 views
Is there really no SIMPLE way to achieve “recursive lookup” in PostgreSQL?
I have a table of categories:
id,label,parent_id
1,Animals,null
2,Dogs,1
3,Cats,1
4,Black,3
This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
0
votes
0answers
17 views
Can I restore the dump of a PostgreSQL 9.3 database into a PostgreSQL 12.5 or Postgres-XL 10 instance?
I've got a fairly old TAR dump of a PostgreSQL 9.3 database I'd like to re-awake. After several system upgrades, I've now got version 12.5 of the PostgreSQL server. In the medium future, I'd like to ...
0
votes
1answer
23 views
4 operations with Postrgres
(select price from product_price where product_id=1)/(select price from comp_product_price where comp_product_id =1)
I'm holding the current price in the product price and the competitor product ...
0
votes
1answer
20 views
Missing FROM-clause in RETURN QUERY EXECUTE FORMAT loop
This query works fine, but I need to execute it for multiple tables:
select 'service:dms_fdo_query:joined' as metric_name, hypertable_size('prom_data."service:dms_fdo_query:joined"') as ...
