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
1answer
21 views

Postgresql comment on specific role

I am trying to store a specific user's description in the Postgres by doing this: comment on role blog is 'blog user long name'; However, when I look for it in the pg_description table it is not ...
0
votes
0answers
5 views

Adding Business Identifier to Multi-tenant Rate Limiting Table

My table keeps tracks of attempted actions by IP: postgres=# \d rate_limit Table "public.rate_limit" Column | Type | Collation | Nullable | Default ...
1
vote
0answers
26 views

Vote based approval system in DBMS

I try to create a system to auto approve proposed posts by popular vote. I am currently evaluating what portions of this I can do within the DBMS and if it is sensible. I use PostgreSQL and I can go ...
-2
votes
1answer
15 views

Store Unix timestamp without datetime conversion in postgresql

I would like to store unixtime stamp sent from my iot device to database. since i don't want to store it using date time conversion. i just want to add it raw so that i can show it to frontend. so ...
-1
votes
0answers
21 views

How to specify partitions in range partitioning by multi column while creation?

I am using MySQL Server, PostgreSQL and PostgreSQL XL in three different systems for my tests I am using multi column partitioning, and I want to specify which node my partition goes to. How to ...
0
votes
1answer
24 views

What are the implications of constraint locality in Postgres?

I'm a bit confused as to how constraints are managed in Postgres. The source of this confusion is a diff between the output from two runs of pg_dump (on different boxes) that should be identical. The ...
1
vote
1answer
45 views

Postgres: How can I force index usage for view with latest rates?

We import currency rates into DB: CREATE TABLE currency_rate ( id int8 NOT NULL, date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(12,6) NOT NULL, CONSTRAINT ...
0
votes
1answer
50 views

Select distinct combinations of rows

I try to select distinct combinations of rows (author_id, recipient_id), preserving following order and limit. Like on Facebook you have a list of recent conversations with given users. For example I'...
0
votes
1answer
33 views

Update table with values from another table runs really slow

I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A ...
0
votes
0answers
3 views

using ORDER BY instead of PARTION BY row_number().over() [migrated]

While using sqlalchemy.func.rownumber().over its generating PARTITION BY how to change it to ORDER BY. query = session.query( sqlalchemy.func.row_number().over(Record.id).label('#'), Record....
0
votes
1answer
30 views

Distinct on two columns, how to rid from 'duplicates'

I have a table with messages, that have 'msg_from' and 'msg_to' fields. when I want to select all current chats and last message from each of them I use the following query: select distinct on (...
0
votes
1answer
41 views

Patroni with etcd starting up

I have an existing PostgreSQL cluster v11.5 with streaming replication. I want to set up high availability managed by Patroni. I installed Patroni on both machines, I set up an etcd cluster on three ...
0
votes
0answers
20 views

pg_upgrade says pg_resetxlog.exe is missing

I'm trying to upgrade my PostgreSQL installation from 9.5 to 11 on a Windows Server 2012 64bit machine using the instructions on the docs. I have both instances installed and the services stopped and ...
0
votes
1answer
26 views

What would be best data model for finding difference within n, n-1 and n-2 elements?

This is from betting domain which has something that is called a long list: a list of a "home team win/draw/away team win" markets for 13 games. A punter can select any combination of the possible ...
0
votes
1answer
24 views

Extremely long postgres query

I've run a migration query within a transaction, it went like this: alter table big_and_loaded_table add column col1 bool; update big_and_loaded_table set col1 = false; It seems quite innocent, ...
0
votes
1answer
27 views

which command to execute to run multiple OLAP queries?

I have 10 sql files that contains different OLAP queries. I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its ...
0
votes
1answer
47 views

Store timeseries data in normal SQL database

I have got historic data of around 20 machine sensors with a time resolution of one second stored in csv-files which need to be imported to a SQL database prior to further data handling and analysis. ...
0
votes
1answer
46 views

Can't connect psql on macOS

Install $ brew install postgresql Updating Homebrew... ==> Downloading https://homebrew.bintray.com/bottles/postgresql-12.1.mojave.bottle.tar.gz Already downloaded: /Users/user/Library/Caches/...
0
votes
1answer
17 views

How do I determine the current “idle timeout” value for PostgreSQL?

Sigh. I've been searching forever for this and, as usual, find nothing. I'm not talking about the "connection timeout" for how long it waits before giving up when trying to connect to the database. I'...
0
votes
1answer
26 views

What to expect from a benchmark?

I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration. Meaning, that even before I start tuning PostgreSQL ...
0
votes
0answers
18 views

Indexing an attribute of the elements of an array of another array in a jsonb field

I have the following document that is stored in a jsonb field in a postgres 11 DB. { "bundles": [ { "id": "9c9c1b73-b058-3f22-a48b-725ce39eb861", "items": [ ...
0
votes
1answer
18 views

PostgreSQL equivalent query which has order by clause alone

Please help me to get equivalent PostgreSQL query for the below oracle query SELECT sum(COLUMN1),sum(COLUMN2) FROM Table_name ORDER BY COLUMN3;
0
votes
1answer
26 views

How to fold an event stream into a table?

I'm trying to experiment a pure-postgres event sourced projection, and I'd like to represent a stream of events as a table: imagine this flow of events: ┌───────┬───────────────────────┬───────┬─────...
0
votes
1answer
23 views

Can we allocate 10 gb RAM to postgres out of 30gb

Team, Can we allocate 10 gb RAM to postgres out of 30gb on Linux machine. If yes can someone share me the steps
0
votes
0answers
13 views

How to get rid of the “LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.” error? [duplicate]

On Windows, my PostgreSQL window logs this regularly every day in its GUI window if I bring it up and look at it: LOG: could not receive data from client: An existing connection was forcibly closed ...
0
votes
1answer
23 views

Postgres authentication fails: log refers to “line 90” in pg_hba.conf that shouldn't exist

Whatever settings I try, I keep getting connection errors, from external clients as well as trying to connect from a running console on the server itself. My last attempt for a local connection with "...
0
votes
1answer
63 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
0
votes
1answer
33 views

Table with huge data takes long time to query

I have a table in Postgres that is having over 50million records in production. It’s a table that I have to query it often for debugging and data extraction because it is heavily used by the ...
1
vote
1answer
24 views

How can I create a recursive query over a column that has gaps?

I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly. Given a table of games like: id | home_player | away_player | ...
2
votes
3answers
204 views

Phone number with different formats lookup

I have different phone formats stored in my db, such as : 727.170.4799 1-416-958-6390 1.561.374.4268 (813) 929-5892 There are approximately ~2 million records in this table so modifying would take a ...
0
votes
0answers
24 views

What means execute S_2, S_3 and S_4: COMMIT in Postgresql logs?

I'd noticed different commit types in Postgres log: 2019-12-20 12:00:00 [99999]: [12-1] host=...,user=...,db=... LOG: duration: 1000.000 ms execute S_2: COMMIT 2019-12-20 12:00:00 [99999]: [12-1] ...
2
votes
2answers
29 views

Where can I read about the behavior of the postgress casting operators?

I want to do something like this: alter table foos alter column bar type bool using bar::boolean; bar is currently a text column. Some experimentation shows that postgres will do things like convert ...
4
votes
3answers
63 views

Truncate text encoding safe

What would be the most efficient way, within Postgres, to truncate a text field to a maximum number of bytes, in such a way that the encoding is preserved? I.e. how to keep at most N bytes while ...
0
votes
1answer
27 views

How to use a recursive CTE to get ancestors in a hierarchy

create table division ( id serial primary key, name varchar not null ); -- nested sets table create table location ( id serial primary key, name varchar not null, division_id integer not ...
0
votes
0answers
28 views

How to avoid null values when generating insert statement

I´m trying to create an insert statement with my database values, to send to my app. But I don´t know how to avoid values if they are NULL, and if NULL I want to put an empty string, like ''. My ...
0
votes
1answer
14 views

How do I sort a PG table primarily by a timestamp column, but last_updated takes precedence?

I'm trying to sort my table (containing drafts) by their timestamp, youngest first/topmost, but taking precedence to the "last_updated" timestamp column. That is, whatever record has been either ...
0
votes
0answers
39 views

Convert Stored Procedure in postgres C function

I've a stored procedure written in PL/postgresql, which returns results in json format. I want to convert that stored procedure into postgres Compiled (C) Language Function. Now the problem is if I ...
1
vote
1answer
16 views

How to block pg_stat_reset() statement in DB

Team, Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take ...
0
votes
1answer
26 views

Blocking drop table for specific schema using trigger

I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas. CREATE OR REPLACE FUNCTION ...
0
votes
1answer
25 views

Time interval is returning all records or no records

I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial After running the query at 18:30 UTC, my query looks as follows: RETURN ...
2
votes
0answers
31 views

Booking service problem

I'm working on booking service. I have a table where users enter their open hours. These pieces of information allow me to generate slot times for a particular day. For example, for row one, I know ...
0
votes
1answer
22 views

Select one of multiple columns into a new column Ask Question

My database table looks like table 1 below: Table 1: +----------------------------------------------------------------------------------+ | id | distance | duration_sec | speed | foot | ...
0
votes
1answer
22 views

generate_series : how to remove or no generate the last one

I use this function for generating time slots. It's work well. But, there is a little problem. My business opens at 10:00 and close at 12:00. -- generate slots CREATE OR REPLACE FUNCTION slots(...
0
votes
1answer
54 views

How to setup an ssh connection to a postgres 12 db using dbeaver

I am currently in the process of trying to connect to my local postgres 12 db. In the past (9.3/9.6) you were able to edit the .pg_hba file to modify which connections were allowed but that file doesn'...
1
vote
0answers
92 views

Storing JSON in Postgres as BYTEA or other types

I need to have a generic key-value data-store in Postgres and Sqlite3. I originally planned to store this as a JSONB type but the SQL statements I execute needs to be compatible with both Postgres and ...
2
votes
1answer
30 views

Why EXPLAIN doesn't show heap fetches for index scan

I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output. Test environment -- function to fill ...
0
votes
1answer
94 views

Problem Installing PostgreSQL 12.1 Win8.1: “the database cluster initialisation failed”

I'm getting an error at the end of installing PostgreSQL 12.1 which is "the database cluster initialisation failed" I have been searching all the day, so here what I tried: 1- Run the installation ...
0
votes
2answers
43 views

renaming result of query

i have two joined table in my postgres db tb_items and tb_colors id_item | group |id colors -------------------------- 1 a 1 2 b 1 3 b 2 4 c ...
0
votes
1answer
16 views

PostgreSQL stop streamig replication

I've setup streaming replication between a master and a backup server. Now I want to stop the replication but I've noticed that every time I stop postgres in the backup server some old WALs files stay ...
0
votes
3answers
44 views

Retrieving users by their age efficiently

I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range: SELECT * FROM users WHERE age(birthday) >= 21 AND age(birthday) <= 30; How can I ...