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
3
votes
2 answers

How to setup a password for PostgreSQL in postgreapp?

I am using postgresapp for the PostgreSQL and without password I was able to connect to the database and perform operations. I am curious to learn about the password. Also I use Postico as Interface. Open to any suggestions.
Venkateshreddy Pala
  • 873
  • 1
  • 6
  • 11
2
votes
1 answer

Open Standby Postgres database in read-write mode for testing purpose

I am currently using Postgres 12.6 Enterprisedb version. I have a disaster recovery database that is synchronized with the production database in read-only mode. Now, I want to temporarily break the synchronization and open the disaster recovery…
Anuj Gupta
  • 27
  • 6
2
votes
1 answer

Upper() doesn't uppercase accented characters

Within our postgres 12 database, using united_states.utf8 collation, we have a dataset with Spanish data that includes accented characters. However when we upper() the values in the field, unaccented characters are correctly uppercased, but accented…
A Smith
  • 33
  • 6
2
votes
2 answers

Why Postgres is doing "Index Scan Backward" to get max ID?

I am running a simple query to get the max ID in a table: SELECT max(ID) FROM t WHERE m=345; Table (t) has 20 million records and 2000 distinct values for m. There is a primary key index on ID and an index on m. For some reason the explain plan…
Matt_Wifi
  • 31
  • 3
2
votes
1 answer

Generate new UUID per row depending on gaps in existing time series

I have a table1 with timestamps, grouped by an added “Period ID”: Period ID (uuid) Created At (timestamptz) A 2017-04-11 11:13:47.997+00 A 2017-04-11 14:42:51.843+00 B 2017-05-21…
SSD
  • 33
  • 5
2
votes
1 answer

How to add a new column on NOT NULL constraint

I have a table with 2 columns requested with NOT NULL constraint as shown below Col1 Col2 Val1 Val2 Val3 Val4 I am trying to add a new column called 'Col3' ALTER TABLE table ADD COLUMN Col3 INTEGER NOT null But it returns an error…
드봉드봉
  • 23
  • 3
2
votes
1 answer

Is it possible to create a UNIQUE index on geometric types?

I have a table like my_type | text my_box | box where my_box is of type box from the geometric types, and not from postgis. I want a way to ensure that there are no duplicate boxes across all values of my_type. Usually, if it were an integer or…
Hoopes
  • 3,943
  • 4
  • 44
  • 60
2
votes
1 answer

Search and extract element located in various path of json structure

I have a json in a PostgreSQL database and I need to extract an array not always located in same place. Problem Need to extract array choicies of a particular element name Element name is known, but not where he's sitting in…
Camille
  • 2,439
  • 1
  • 14
  • 32
2
votes
2 answers

How to remove first row in every grouped result ordered on date column in postgresql

I have a sql data (table) which consists of n number of columns, but 3 are of particular…
AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
2
votes
3 answers

How to compress postgres database backup using barman

We are backing up 1TB+ of postgres database using barman. Barman is backing up full database daily. Every day we are dumping 1TB+ database. So it's consuming huge space. We have following configuration in barman:(broad - level…
SAKA RAM DEWASI
  • 39
  • 5
2
votes
0 answers

Why do Postgres hash indexes take so long to be created?

Self-explanatory question. I have tested on a very large PostgreSQL table (+500M rows) where a B-TREE index takes around ~30 minutes to be created on a column which contains UUID or email-sized strings, while a hash index addition operation hasn't…
Z. M.
  • 329
  • 5
  • 13
2
votes
1 answer

Why UPDATE permission does not work without SELECT permission in PostgreSQL?

What is the purpose of UPDATE permission if it does not allow to update without SELECT permission? Generally, I understand that update internally causes select to find target rows, but this "internal select" does not leak to a user, so it is unclear…
diziaq
  • 6,881
  • 16
  • 54
  • 96
2
votes
2 answers

How to convert PostgreSQL 12 generated column to a normal column?

I have a generated column in PostgreSQL 12 defined as create table people ( id bigserial primary key, a varchar, b boolean generated always as (a is not null) stored ); but now i want column b to be settable but i don't want to lose the…
zola
  • 5,737
  • 8
  • 33
  • 48
2
votes
1 answer

Is there any case of table rewrite on add nullable column?

On the one hand the documentation states clearly that When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value…
Dmitrii Apanasevich
  • 325
  • 3
  • 13
2
votes
1 answer

IF ELSE in postgresql function

I am trying to create a function which is as follows create function public.getSomething( value1 integer ) returns table ( id integer, name varchar(20) ) as $$ BEGIN …
Shuvo Barua
  • 105
  • 2
  • 10
1 2 3
…
36 37