Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [postgresql-11]

PostgreSQL version 11

Filter by
Sorted by
Tagged with
1
vote
2answers
28 views

How to get the oldest values over each id in a PostgreSQL table efficiently?

How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements? Let me explain the situation with a sample table: CREATE TABLE sensor_data( sensor_id INTEGER, ...
1
vote
2answers
46 views

How to filter hstore where values are not null in a trigger?

The easiest way to explain the question is in the usage of an audit trigger. However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
0
votes
2answers
16 views

Logging user's id with the query he executed

So, I am trying to log not just the postgres user but his ID from my table (for example) Person (person_id) when he calls functions. So far, from what I can tell I can get almost every information I ...
0
votes
0answers
12 views

Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items ...
0
votes
0answers
5 views

Barman + repmgr failover process

I am about to build a PostgreSQL 11 cluster with Repmgr and Pgpool II, and I want to use Barman for backups. The result will be a PostgreSQL cluster with one master node and two standby nodes: ...
2
votes
1answer
16 views

Time Difference Between actual total time and execution time in EXPLAIN ANALYZE

I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5 EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT actor.actor_id, actor.first_name, act.num FROM actor INNER JOIN ( SELECT ...
0
votes
1answer
20 views

Inline expansion of inline composite expression

I want something like this: SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*; ...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws: ERROR: record type has not been ...
0
votes
3answers
261 views

Postgresql 11 - slow insert performance when table reaches ~5 million records

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD). It seems to be a I/O problem, since there are free memory and processing ...
0
votes
2answers
117 views

Horribly performing query, how to fix

select user_id, count(id) as unread_count from notifications where is_read = false and user_id in(select(unnest('{200 user IDs}' :: bigint[]))) group by user_id; Problem is, ...
0
votes
2answers
36 views

pg_switch_wal In Point In Time Recovery PostgreSQL

I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me. Is it necessary to use pg_basebackup every time I want to back up ...
1
vote
1answer
26 views

Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance

The default unaccent module has no or little support for Greek Letters using the unaccent module as seen in following links: https://stackoverflow.com/q/49845063 https://www.postgresql-archive.org/...
0
votes
0answers
21 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...
1
vote
2answers
1k views

Converting VARCHAR to TIMESTAMP with milleseconds yields “ERROR: date/time field value out of range”

I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command: select last_run, to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - ...
2
votes
1answer
44 views

Trigger working on more than 50 million records [closed]

I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD. I need to create a trigger there which will update previous entries flag to false for ...
0
votes
2answers
58 views

Table with too many nullable columns - is there a better design?

I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, ...
0
votes
1answer
42 views

Cast string to decimal without rounding the decimal part

I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don'...
2
votes
2answers
34 views

Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client?

Our application server is on EC2 running on Amazon Linux 1. Postgres dropped support for Amazon Linux and so we depend on Amazon providing the postgres client. The client is 9.6 and our Amazon ...
0
votes
1answer
23 views

Converting ticks into range bars

You have a tick table with 3 columns CREATE TABLE tick ( symbol character(9) NOT NULL, price real NOT NULL, "time" timestamp with time zone NOT NULL ); INSERT INTO ...
0
votes
1answer
30 views

After bulk load, how to ensure an updated visibility map?

We have a number of tables that have an active version, an inactive version, and a view pointing at the active one. We periodically truncate the inactive table, drop the indexes, load a bunch of data ...
2
votes
2answers
105 views

Select previous value in row without using a subquery [closed]

Say you have a table in PostgreSQL 11 with sets of values something like this below. 12 18 11 12 5 12 9 18 7 19 14 18 11 16 6 12 4 11 5 9 Is there a way to do a select without using sub-...
1
vote
1answer
58 views

Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck

We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10. Database is running on a server with 4GB total memory. When the script ...
1
vote
1answer
22 views

PostgreSQL planner over estimates and self join query large table optimization

PostgreSQL 11.5 4 core, 32gigs 250gig SSD Not using default configuration, tuned to fit system memory and cores. Table+index size 14Gigs I'm working on a query that self joins to a table via a ...
0
votes
1answer
26 views

Check if PostgreSQL dump is valid

Suppose a dump is provided from pgdumpall pg_dumpall > outfile.bkp How to verify if the outfile is a vaild database file for restoration. Just as a precautionary step before restoring it like ...
1
vote
1answer
100 views

Efficient group by on sorted CSV using PostgreSQL FDW

I have a large CSV file sorted on the first column. Here is an extremely simplified version): a 7 a 4 b 6 c 3 c 9 c 2 I want to group by the first column and sum the second. How do I tell the ...
0
votes
1answer
25 views

Negative amount of remaining transactions before wraparound

Running a PostgreSQL 11 instance with a table of more than two billion rows. I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens: ...
1
vote
1answer
36 views

HLL aggregation query is very slow

I have the following query select ts_date, sum(bids_placed), hll_cardinality(hll_union_agg(eligible_uu)) from performancedata where ts_date>='2019-08-01' and ...
0
votes
0answers
47 views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
0
votes
1answer
28 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
1answer
55 views

postgresql recovery.conf file location in 11.5

I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
0
votes
1answer
10 views

Reporting table size to grafana

I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created ...
5
votes
1answer
47 views

Bad execution plan when having partial index and big In-clause in Postgres

Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements. Given the following ...
0
votes
1answer
32 views

Postgresql - how to list indexes on a table that is partitioned?

This command works for non-partitioned tables: select * from pg_indexes But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition. ...
0
votes
0answers
33 views

Postgres uses sequential scan with OR condition, but index scan with AND condition

We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
1
vote
1answer
64 views

Unable to alter partition table to set logged

I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command. Afterwards, I ran an alter table set logged ...
0
votes
2answers
38 views

When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes

I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
1
vote
1answer
274 views

Postgres Column Level Security

I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
1
vote
0answers
32 views

Brand new postgres setup - what to check first?

My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. ...
0
votes
1answer
46 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
0
votes
1answer
35 views

Select with if statement return 'column does not exist'

Database, application. PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1), Dbeaver 6.1.1. What I need? Return right values. What I do. Here is part of code. select if(available_count < 5, "neni skladem"...
3
votes
1answer
188 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
0
votes
1answer
33 views

Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements. SELECT u....
0
votes
0answers
14 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
4
votes
1answer
239 views

Postgresql numeric and decimal is automatically rounding off

CREATE TABLE IF NOT EXISTS ttable ( tcol decimal(9,7) ); insert into ttable(tcol) values(17.4604786); the value is getting stored as 17.46 Happens the same if I use decimal/numeric type I am ...
0
votes
1answer
48 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
1
vote
1answer
34 views

Use jsonb array inside where IN or ANY in PostgreSQL

I want to select all products that have active shipments (those have one or more shipment_ids in shipments table). My product table has a jsonb column called data like this: { "name": "foo", ...
0
votes
1answer
22 views

CTE vs Function/trigger performance in Postgresql

I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the ...
0
votes
1answer
141 views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
1
vote
1answer
17 views

Getting 2 columns from sets of 2 rows in Postgresql

So I have my data organized as: id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date 1 | netIncome | 100 | stockid1 | 2019-05-01 2 | totalRevenue |1000 | stockid1 | ...
0
votes
1answer
18 views

Posgresql - deadlock on non related queries

I am using Postgresql 11. I have found an error similar to following in my logs: org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 332091 waits for ShareLock on ...
0
votes
1answer
30 views

How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup ...