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
2
votes
1 answer

Foreign table created with inherits stuck

I've been trying to create a foreign table in my PSQL database. The point here is to concatenate the same steps table from 2 different databases. I want to use INHERITS to copy the schema. I have a steps table locally, and a steps table (with…
RobinFrcd
  • 4,439
  • 4
  • 25
  • 49
2
votes
1 answer

PostgreSQL multicolumn index not fully used

I have a large (~110 million rows) table on PostgreSQL 12.3 whose relevant fields can be described by the following DDL: CREATE TABLE tbl ( item1_id integer, item2_id integer, item3_id integer, item4_id integer, type_id…
mariof
  • 36
  • 3
2
votes
1 answer

PostgreSQL running with versions 12 and 13 confusion

I am fairly new to PostgreSQL but I have some knowledge of SQL. I've been using a database with tables and data on postgres that (I'm assuming) comes with MacOS because when I start the server, access a given database and run SELECT version(); on…
everspader
  • 1,272
  • 14
  • 44
2
votes
2 answers

In PostgreSQL , can certificate authentication and basic authentication(username/password authentication) co-exist at one time?

Can we configure both Basic authentication and Certificate authentication for PostgreSQL simultaneously for the same IP address of client. I think no , because they are 2 different authentication mechanisms and cannot co-exist at same time...…
MSTechnie
  • 175
  • 10
2
votes
3 answers

How to order by a varchar choosing the collate order with a case-when switch, in PostgreSQL

This simple function returns an ordered list of title strings. create or replace function testfunction1 () returns table ( id bigint, lang_code tlang_code, title varchar ) stable language sql as $$ select id, lang_code,…
coterobarros
  • 941
  • 1
  • 16
  • 25
2
votes
3 answers

How to assign a local variable in an update sentence in PostgreSQL

I was trying to assign a local variable (well, in fact two) using the SET clause of an update sentence running on several rows. Ok, I am doing this ala MySQL. drop table if exists stocks cascade; create table stocks ( id serial, …
coterobarros
  • 941
  • 1
  • 16
  • 25
2
votes
2 answers

Connect to a older Postgresql database

I have PostgreSQL installed in my Linux machine, I'm following a old YT tutorial for work and the instructor is using PostgreSQL 11. I have already used the following command: sudo apt-get -y install postgresql-11 To install PostgreSQL 11 in my…
Jeremy
  • 1,447
  • 20
  • 40
2
votes
1 answer

pg_upgrade for Postgres12 failing with PG_VERSION

I am trying to upgrade my postgreSQL 10 database on Ubunutu 18.04 server to postgreSQL 12. I have installed the 12 binaries and post shutdown of the 10 intance, I am running the check command as shown below and it fails with the below error. The…
balaks80
  • 136
  • 1
  • 7
2
votes
2 answers

PostgreSQL: Sorting the rows based on value of a JSON in an array of JSON

A table says products have a JSONB column called identifiers that stores an array of JSON objects. Sample data in products id | name | …
Surya
  • 2,429
  • 1
  • 21
  • 42
2
votes
1 answer

Is it possible to monitor PostgreSQL server performance from inside a PL/PGSQL function?

This may sound exotic, by I would like to programmatically know if it is a 'good moment' to execute a heavy-write PL/PGSQL function in a server. By 'good moment' I mean pondering some direct or calculated indicator of the load level, concurrency…
coterobarros
  • 941
  • 1
  • 16
  • 25
2
votes
1 answer

How to add Foreign key constraint on array in PostgreSQL?

How to add Foreign key constraint on array in PostgreSQL? look_up table for roles CREATE TABLE party_role_cd ( party_role_cd bigint NOT NULL, code character varying(80) NOT NULL, CONSTRAINT party_role_cd PRIMARY KEY (party_role_cd) ); Party…
Thirumal
  • 8,280
  • 11
  • 53
  • 103
2
votes
1 answer

json_array_elements: invalid input syntax for integer

I have the following sample data for demo: Table: create table tbl_json ( id json ); Some values: insert into tbl_json values('[{"id":1},{"id":2},{"id":3}]'); Query: Convert/cast id into integer from json column. Tried: select…
MAK
  • 6,824
  • 25
  • 74
  • 131
2
votes
1 answer

Debugging high PostgreSQL memory usage (per connection)

Is there any way to check how memory assigned to each connection is actually used? After upgrading from PostgreSQL 9.3 to PG 12 the memory usage for each PostgreSQL connection doubled or even tripled. So I had to go from 32GB machine…
user158037
  • 2,659
  • 1
  • 24
  • 27
2
votes
1 answer

Enconding issue when importing postgreSQL database in a new server

I am new to postgreSQL, so please talk to me like I'm in kindergarden. By the way, everything in this question is happening on Windows machines. I pg_dumped a database, creating a backup.dump file. Now, I am trying to import backup.dump to a new…
Akiryou
  • 21
  • 2
2
votes
2 answers

How to hierarchical query in PostgreSQL with language-dependent data

I am trying to retrieve a hierarchical ordered result from a query on an auto referenced table like this: create table category ( id serial, -- parent category, parent_id integer default null, -- null for root category --…
coterobarros
  • 941
  • 1
  • 16
  • 25
1 2 3
…
36 37