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

Can PostgreSQL have md5 auth for 1 IP range and certificate authentication for another IP range?

I saw that in pg_hba.conf , we have the option to set different authentication for different IPs . Like can we set password (md5) authentication for 1 IP Range and certificate authentication for another IP range. Please help. Please share any good…
MSTechnie
  • 175
  • 10
0
votes
1 answer

PostgreSQL Basic Authentication with SSL = on

In PostgreSQL with Password authentication , if SSL = on , in postgresql.conf file , then can the client connect without the SSL certificate on his side ? Please help.
MSTechnie
  • 175
  • 10
0
votes
2 answers

libpqxx C Aggregate Extension returns wrong data?

I am learning how to create C aggregate extensions and using libpqxx with C++ on the client side to process the data. My toy aggregate extension has one argument of type bytea, and the state is also of type bytea. The following is the simplest…
0
votes
0 answers

PostgreSQL uninstall on Windows

In the process of upgrading from PostgreSQL 11 to 12 on Windows, I uninstall PG-11 using the uninstall.exe, wait for like 15 seconds (to make sure that the uninstall.exe is complete), delete the bindir (if exists) and then install the…
P_Ar
  • 377
  • 2
  • 9
  • 25
0
votes
2 answers

How to link jobs on coordinator and workers on a Citus database on PostgreSQL 12

I have Citus extension on a PostgresSQL server. And I want to see the statistics from pg_stat_statements of each worker through the coordinator node. However, there is no column to match the tables from coordinator and workers. Does anybody know how…
Miguel Wang
  • 183
  • 1
  • 12
0
votes
1 answer

Is SSL connection possible for PostgreSQL with basic authentication (Username/password authentication)?

I have postgreSQL installed on both Windows and Linux Ubuntu VMs. (1) Is SSL connection possible for PostgreSQL with basic authentication (Username/password authentication) ? If possible , should the certificates be installed on the client machine ,…
MSTechnie
  • 175
  • 10
0
votes
1 answer

Where are PostgreSQL databases stored (on my local machine)?

I installed PostgreSQL 12.4 on MacOS using the EDB Installer (selected all the default options during installation). Using the pgAdmin 4 GUI client, I created a database, let's call it "A". I then uninstalled everything using the uninstaller that…
Baran Karakus
  • 193
  • 1
  • 7
0
votes
1 answer

How do I use the pg_restore --list to inspect the contents of a non-restored directory?

I am trying to inspect the contents of an archive that I just unzipped using the following code pg_restore --list filename I've aslo tried pg_restore --list D:/above_file/filename I'm using PostgreSQL 12
user13456931
  • 25
  • 1
  • 5
0
votes
0 answers

Postgresql not choosing rows grouping

I have query. There is a construction like this example: (online demo) You will see the in result created_at field. I have to use query the created_at field. So I have to use it in select created_at. I don't want to use it created_at field in…
omero
  • 93
  • 1
  • 4
  • 10
0
votes
1 answer

Postgresql - Select in Select Alias Problem

I have query. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d SELECT users.id as user_ids, (SELECT ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses.bonus_id)) as bonus_ids FROM user_has_bonuses …
omero
  • 93
  • 1
  • 4
  • 10
0
votes
1 answer

Separating WAL Log and the data in two two different disk for Postgres SQL

I'm looking for a reference and ways to separating the WAL transaction Logs and the data into two different disk for postgres.
0
votes
1 answer

Postgres custom type mapping to C struct?

I am writing a custom Postgres C AGGREGATE extension. I would like to know how to properly map a type defined in PSQL to a struct defined in C. I have tried doing it as best as I know how but I do not get results, I get an empty row when I test. The…
0
votes
2 answers

Granting local user permissions to a foreign db table in postgres

I set up a connection to a foreign db using dblink_connect according to the docs: CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'foreign_test_db'); CREATE USER test_user WITH PASSWORD 'secret'; CREATE…
0
votes
1 answer

Get column with text datatype slow query

I have the following table: create table tbl_img ( id int, col_img text ); I have stored image in the column col_img. When I try to get data: select t1.id,t1.col1,t2.col_img from tbl_test t1 left join tbl_img t2 on t1.img_id = t2.id; Taking…
MAK
  • 6,824
  • 25
  • 74
  • 131
0
votes
1 answer

Is it a bad idea using a single sequence generator in PostgreSQL?

This is a generic question about PostgreSQL sequence performance, for if they could be a bottleneck in high-write-concurrency databases, when choosing use one instead of one per table. I outlined performance and bottleneck because I am perfectly…
coterobarros
  • 941
  • 1
  • 16
  • 25