Stack Overflow
Stack Overflow

Questions tagged [postgresql-12]

For PostgreSQL questions specific to version 12.

PostgreSQL 12 is a major release of the PostgreSQL RDBMS.

Improvements include, but are not limited to:

  • performance improvements for partitioning and indexes
  • CTEs (WITH queries) are by default inlined for better query performance
  • JSON path queries per SQL/JSON specification
  • support for case- and accent-insensitive ICU collations
  • (stored) generated columns
  • extended statistics for “most common values”
  • page checksums can be enabled and disabled without initdb
  • authentication: support for encrypted GSSAPI authentication and LDAP server discovery
  • “pluggable storage” to add different table storage methods

More information regarding the release is available here.

554 questions
0
votes
1 answer

Updating json columns in psql

I have a table containing a json column. The json values will look something like this: {'john': 1, 'alex' : 4, 'harry' :2} If I wanted to add 1 to john, how would I go about doing this?
user15178581
  • 57
  • 1
  • 4
0
votes
1 answer

Extract some value from complex json field type in Postgresql

I have postgres table (let say my_table) containing JSON field (let say json_field) as: (1, {"data": [{"val_a":1, "b":"somevalue"}, {"val_a":2, "b":"othervalue"}, ...]}), (2, {"data": [{"val_a":22, "b":"somevalue2"}, {"val_a":12,…
prze gee
  • 61
  • 4
0
votes
3 answers

Indexing is not applying on the date column in postgres. Top-N-heapsort is applying not Index scan

I have a table 'post' which has millions of rows. I need top N records using the date field but it's taking too much time because of Top-N-heapsort. How can I optimize this query? Note: On the staging server where I have less data it's working fine.…
Nishant Khandelwal
  • 199
  • 1
  • 5
  • 13
0
votes
2 answers

Different path formats for PostgreSQL JSONB functions

I'm confused by how path uses different formats depending on the function in the PostgreSQL JSONB documentation. If I had a PostgreSQL table foo that looks like pk json_obj 0 {"values": [{"id": "a_b", "value": 5}, {"id": "c_d", "value":…
lebolo
  • 2,120
  • 4
  • 29
  • 44
0
votes
0 answers

Postgresql 12 high CPU Spike on Slave fixed by running analyze.. unable to RCA

Hi Postgresql 12 gradually spiked to 100% CPU. App team was looking after issue from 60 minutes. 2 different queries were taking high cpu. Tables involved were small so i just ran analyze on all 3 involved tables to fix any underlying statistics…
Monika Yadav
  • 381
  • 2
  • 12
0
votes
1 answer

Query Tuning PostgreSQL stored procedure which has 1000 queries Inside

I want to Tune my PostgreSQL stored procedure which has 1000 queries Inside. My SP is suddenly started to lack Perfomance. How can I debug this SP which query is lagging performance inside the SP? Since Explain analyze doesn’t really show the much…
Prasanth Reddy
  • 43
  • 1
  • 1
  • 3
0
votes
1 answer

pg_basebackup stop wal location

I'm trying to find a way to get the last pg wal (stop wal location) which was stored in the backup made by pg_basebackup. Is it there any function in pg which could give me the wal in pg_lsn format? In old-level-api there was pg_start_backup,…
qwertyu
  • 5
  • 3
0
votes
1 answer

Postgresql pg_basebackup and archive_command

I'm considering the delete archive_command due to the pg_basebackup introduction which executed is during replication. I'm not sure if I understand it correctly - I assume that I don't need archive_command, because pg_basebackup -X stream - will…
qwertyu
  • 5
  • 3
0
votes
1 answer

PostgreSQL create index on JSONB[]

Consider a table defined as follows: CREATE TABLE test ( id int4 NOT NULL, tag_counts _jsonb NOT NULL DEFAULT ARRAY[]::jsonb[] ); INSERT INTO test(id, tag_counts) values(1,array['{"type":1, "count":4}','{"type":2, "count":10}'…
MojoJojo
  • 3,897
  • 4
  • 28
  • 54
0
votes
0 answers

Get DDL of all tables in a postgresql schema

I need to get the "CREATE TABLE" statements for all the tables in a particular schema of postgreSQL. How can I do so? Thanks in advance.
Anand Rajakrishnan
  • 148
  • 12
0
votes
0 answers

Improve query performance by running ANALYZE?

PostgreSQL version: 12.4 TimescaleDB version: 1.7.4 Hi, I have a table in which data is distributed in multiple chunks based on time only i.e. created hypertables for one day. I am trying to improve the performance of SELECT query. To achieve that…
Satish Kumar
  • 63
  • 6
0
votes
1 answer

How to store HTML emoji v3.0 code in PostgreSQL?

I would like to store a couple of country flags into my PostgreSQL database. But when my website's view file fetches the flag data the source output looks like this text 🇺🇸 and not the flag Emoji. INSERT INTO mytable…
piggy
  • 83
  • 1
  • 3
  • 11
0
votes
1 answer

Postgres local procedure or local module

I'm porting a stored procedure from Oracle to Postgres 11. The procedure has a local procedure created inside it and being used multiple times. I tried creating the same in Postgres but I'm not able to do so. Is this option not available in…
Mano
  • 601
  • 10
  • 32
0
votes
1 answer

Compare a record value containing a list with column values in Postgres

I'm in need of writing a select statement that has to link two tables together using one column value containing a list of items separated by ',' and a column with each item listed individual, that can also filter the result using those same…
tmattson
  • 3
  • 3
0
votes
1 answer

get filtered rows by especific rules in postgres12

Aditional info: My table works from 2 lines, first and second line, id always start by 1 or 2, but sometimes, we have to reprocess it, and number get updated i have a query that shows a lot of id's usually, mi id's start by 1 or 2 for…
Dani V
  • 11
  • 4
1 2 3
…
36 37