Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
12,431
questions
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, ...
