Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
2answers
32 views

Postgres group records by consecutive types

I would like to group objects by their consecutive start and end date. +----------+----------------------------+ | Fruit | Time | +----------+----------------------------+ | ...
1
vote
1answer
36 views

update most recent rows only?

I have a database of a garage of rented cars with a table called Cars with the following columns : Type,First_seen_in_garage,Last_seen_in_garage The table initially have the following rows : Ferrari,...
0
votes
0answers
26 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
-1
votes
2answers
25 views

How do I make PostgreSQL not log specific errors?

ERROR: deadlock detected at character 13 My PG log is full of this, but the thing is, I already handle it (after years of frustrations) in my application. I detect them and repeat until they go ...
0
votes
1answer
35 views

Limit has no effect on aggregation

I have 2 tables: users: id ------ 1 permissions: id | action | userId ---|--------|-------- 1 | read | 1 2 | write | 1 When I run the following query (return users with its permissions): ...
0
votes
1answer
23 views

Is it possible to change collation for an existing database with PostgreSQL?

Such as change from C to utf8? I tried this but seems not allowed. postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8'; ERROR: unrecognized configuration parameter "Collate&...
0
votes
0answers
40 views

Postgresql Performance Diagnostic

I am trying to activate Performance Diagnostic on a Postgresql 12 advanced server from PEM on a windows 10 machine. I am trying to install EDB Wait States module by changing the postresql.conf and ...
0
votes
0answers
27 views

Postgres symbol lookup error : undefined symbol: ing

I have a Docker container running PostgreSQL 12, and on container start-up I had a symbol look-up error. Starting the container again failed, but rebooting the host did lead to recovery. I didn't have ...
-1
votes
2answers
61 views

How can Postgresql use more of the total RAM?

I'm trying to undertstand how Postgresql's (v9.1) memory usage relate to the overall Linux memory. When I look at htop, I see that the system is using about 60GB out of a total of 256GB RAM. That ...
1
vote
1answer
36 views

PostgreSQL - trying to speed up query responses from view that has join and group by

I have these two tables: precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates). Column | ...
0
votes
1answer
43 views

Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

I have a table like this one, that shows payments from each client, for different dates: | stream_datetime | customer_id | order_status | rn | |:----------------:|:-----------:|:------------:|:--:| | ...
0
votes
1answer
36 views

How to delete all records which are not referenced from other tables

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced? I know that I can left join the ...
1
vote
2answers
27 views

nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role

I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are: pid datid datname Other columns ...
0
votes
0answers
33 views

How to get TABLE like dynamic structure output/returned from PostgreSQL function?

I am not very experienced with PLPGSSQL hence seeking some guidance to achieve following... I have an Oracle Procedure which i have to convert to PostgreSQL. This oracle function has declared a TABLE ...
-1
votes
1answer
22 views

Does PostgreSQL 12.x have any built-in abilities to “predict future numbers”? [closed]

I know of third-party extensions for this. I'm not interested in those. I find dealing with extensions and extra software to be a nightmare. I need for it to be built in. I'm basically trying to ...
0
votes
2answers
28 views

What files are safe to delete after upgrade?

This question is very similar to Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade, but I did the upgrade in a different way, so the answer might be/...
0
votes
1answer
24 views

Can PostgreSQL UPDATEs in autocommit mode deadlock?

Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)
0
votes
1answer
36 views

PostgreSQL - Check value is not the same in multiple columns

I have a table to hold user information and is used as part of the login/registration process. Part of the requirement is to allow the user to login using either their username or email address. A ...
0
votes
1answer
26 views

Give alias / column definition list to ROWS FROM()

This obviously doesn't work: SELECT regexp_matches[1], regexp_matches[1] FROM ROWS FROM ( regexp_matches('fooBarBaz', '[[:upper:]]', 'g'), regexp_matches('fooBarBaz', '[[:lower:]]', 'g') ); Error:...
0
votes
1answer
17 views

how do I prevent my postgres connection from timing out? (python, psycopg2)

very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a ...
0
votes
2answers
92 views

Why PostgreSQL volatile function is faster than inline subquery?

The following examples have been tested in PostgreSQL versions 11 and 12. A few of the tables involved have around 2m rows, others not more than 250k. I have very big and nested queries like this (not ...
0
votes
1answer
36 views

Resolve parametrized schema in PostgreSQL

I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function. Why am I looking this way ? This functions will refer %ROWTYPE from multiple schema like Var1 s1....
0
votes
1answer
125 views

/dev/shm size recommendation for postgres database in docker

We have postgresql 11.7 database in docker container. We had an issue "pq: could not resize shared memory segment "/PostgreSQL.XXX" to XXX bytes: No space left on device", ...
-2
votes
1answer
42 views

postgreql 11: how to get result of a query in set of records and then process them in a function

I want to write a function like below.Whats wrong in it? CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS DECLARE myRec RECORD; tab_Rec RECORD[]; --This i want to as a table (...
1
vote
1answer
45 views

PostgreSQL Sorting an output of a fulltext search query takes very long time

I have a table with a few millions of rows where I perform a SELECT with a complex fulltext search query in the WHERE clause (over a fulltext search index column) and with ORDER BY id DESC at the end. ...
1
vote
2answers
31 views

Should a SKU be associated to warehouse or supply table?

This may be a trivial question for any of you experienced in supply chain models. The model I am building in PosgreSQL has 3 tables: create table supply ( id serial primary key, barcode ...
0
votes
1answer
14 views

PG fulltext search: Boost factor for word in the title

I use PostgreSQL full text search and it works fine. The database schema is simple. A page has a title and a content. Up to now I just index the string which I get by joining title+content. In the ...
1
vote
0answers
25 views

Extreme slow query for a simple indexed selection - Why? [duplicate]

i have a table in my database that is described as follows: -- Table: public.devicemeasurement_mqtt -- DROP TABLE public.devicemeasurement_mqtt; CREATE TABLE public.devicemeasurement_mqtt ( ...
0
votes
1answer
20 views

pgBouncer - multiple servers with same username but different password

I have an active pgBouncer setup with one PostgreSQL server. We are planning to add one more PostgreSQL server to the same pgBouncer. But the problem is that both databases servers have the same ...
-1
votes
1answer
24 views

Postgres not using Index for seemingly no reason [closed]

I have a table, items, with about 18 million rows. I want to be able to quickly retrieve where market > 0. This means that the item is on sale, and the market value as its price. To do so, I placed ...
0
votes
0answers
19 views

Group by with clause in postgresql taking lots of time

I am new to sql and trying to write a query in postgresql which is taking lot of time during execution (~8 mins), total number of data in the table is around 298650 rows. and i am using below stated ...
0
votes
1answer
15 views

Select a count of record sharing the same value than record X

Figure a table with two columns: id, catId How to select the records having the same catId than the record having id = 3? Example: id catId 1 3 2 4 3 4 4 4 5 3 Input: 3 Then catId of {id = ...
0
votes
1answer
25 views

Why does my Query use a Bitmap Heap Scan

my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan. explain analyze verbose SELECT connections, epoch_time FROM connections WHERE host_name = 'xyz.z' ORDER ...
0
votes
0answers
35 views

PostgreSQL - INSERT if it doesn't exist/RETURN if it does (query optimization)

This query will insert a new IP if there isn't one and return it's ID. And if there already is the same value it only returns the ID. I'm a newb with SQL and am curious if this could be further ...
1
vote
1answer
31 views

How to run my query on a one-to-many relation to run efficiently

I have two tables in one-to-many rel, I want to run a query efficiently returning data from the base table and some aggregates from the joined subtable. Adding in some indices, I still did not manage ...
0
votes
3answers
68 views

Is a relational database with a dynamic number of tables a good design? [duplicate]

I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records. This table has a fixed structure, so if there are 5 ...
0
votes
1answer
37 views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
1
vote
1answer
31 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
1answer
15 views

remove postgres server from local machine

I know this is a real beginner question. But nevertheless I'm really confused:/ I wanted to have a clean installation of postgres on my ubuntu laptop. So I tried to delete everything related to ...
0
votes
0answers
23 views

PL/PGSQL - Inner Join in Foreach loop

So I have a table, report_items with a structure as follows: id (uuid) | date (date) | sheet (text) | report_item (text) | report_value (numeric) | stock_id (uuid) | period (text, either quarterly or ...
1
vote
0answers
30 views

pgAdmin 4, force “The application has lost the database connection:”

Whenever I put my computer in sleep mode pgAdmin 4 loses the connection to the remote Postgresql database (and that's fine). When I start up the computer again I have two options. Either restart ...
0
votes
1answer
37 views

How to improve the estimated cardinality of inner join lateral

I'm currently running a query that has the following structure: select v1.*, v2.*, ... from t1 inner join lateral f1(t1.id1, t1.id2) as v1 on true inner join lateral f2(t1.id1, t1.id2) ...
0
votes
0answers
58 views

Postgres SQL CPU and RAM optimisation to improve decryption performance with pg_crypto

I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are ...
0
votes
1answer
23 views

Error using pg_restore

I am using the following guide to migrate a database from my Windows 10 machine to my Managed Database on Digital Ocean. https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/ ...
0
votes
2answers
27 views

Independent aggregation of related entities, while also sorting by related (in a single statement)

I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
0
votes
1answer
35 views

PostgreSQL 9.6.17 COALESCE function error

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error: invalid syntax for type double precision: "" Example query: SELECT COALESCE(date_part('...
0
votes
0answers
31 views

JSONB encryption

To search on my JSONB object I use the following query: SELECT JSONB->obj FROM table WHERE JSONB->obj @> '{"element":"param"}' Now the requirement is to encrpyt so I've ...
1
vote
1answer
28 views

PLPGSQL: Cannot use parameters inside a function query

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator. I have tested that the ...
0
votes
1answer
15 views

What steps are required to setup PostgreSQL and create a db in Windows?

I have installed PostgreSQL 12 from https://www.2ndquadrant.com/en/blog/pginstaller-install-postgresql/. and put \C:\...\PostgreSQL\12\bin in the PATH. In an admin powershell I execute psql.exe -U ...
0
votes
0answers
17 views

Postgresql CPU usage out of control, unknown commands in htop [duplicate]

I see random commands in the htop output where postgresql user is consuming more than 200% of CPU all the time. I am new to postgresql and have inherited this setup. I've tried killing the process, ...

1
3 4
5
6 7
249