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

Postgres query json array to find the count which matches the condition

I have a table my_table with a column name itinerary in my Postgres 12 DB. select column_name, data_type from information_schema.columns where table_name = 'my_table' and column_name = 'itinerary'; column_name |…
work_in_progress
  • 747
  • 1
  • 10
  • 27
0
votes
1 answer

Postgres query planner filter order affected by using now on sequential scan

I have a query whereby, when enabling sequential scanning on the postgres database and using now() in the where clause, the query planner will prefer a sequential scan of the table and then filter: EXPLAIN ANALYZE SELECT action_id …
Willeman
  • 720
  • 10
  • 24
0
votes
1 answer

Create multiple users, to have all privileges on each others relations/objects

I'm logged with postgres user (BTW, this is not superuser, DB is hosted on google cloud). I need that create multiple users, and all that users to be have access on each other's objects So, with postgres user I created 2 users like: CREATE USER…
oh no
  • 49
  • 1
  • 9
0
votes
2 answers

High speed single row inserts with PostgreSQL & TimescaleDB

I have a case with a TSDB Hypertable looking approximately like this: CREATE TABLE data ( pool_id INTEGER NOT NULL, ts TIMESTAMP NOT NULL, noise_err DECIMAL, noise_val DECIMAL, signal_err DECIMAL, signal_val DECIMAL, high_val DECIMAL, …
Daniel
  • 4,272
  • 8
  • 35
  • 48
0
votes
1 answer

PostgreSQL 12 - List sequences that do not match their related table name

I have a database with plenty of tables. I want to tidy up relations that do not fit namewise anymore, due to name changes of the tables. I was able to fix the constraints, but I am not able to put the lines together to list the sequences and the…
JBecker
  • 124
  • 7
0
votes
3 answers

How can i find all linked rows by array values in postgres?

i have a table like this: id | arr_val | grp ----------------- 1 | {10,20} | - 2 | {20,30} | - 3 | {50,5} | - 4 | {30,60} | - 5 | {1,5} | - 6 | {7,6} | - I want to find out which rows are in a group together. In this example 1,2,4…
HrkBrkkl
  • 613
  • 5
  • 22
0
votes
2 answers

Postgresql allows nonexistent foreign keys

I have a rails app that uses postgresql 12. Recently, I wrote some tests and saw some strange behavior. I have a countries table. Its schema looks like that: qq2_test=# \d countries; Table…
airled
  • 177
  • 3
  • 15
0
votes
1 answer

Run a database dump (Restore) from an sql file in PHP + PostgreSql

I'm trying to create a system where a user can create a database and have it populated from a UI, pretty much similar to the installation of most CMS's. I can programatically create the database but having the tables created and populated is where…
Clint_A
  • 518
  • 2
  • 11
  • 35
0
votes
1 answer

Is it possible to guarantee a minimum set number of workers for a parallel aggregate function in postgres?

I have a custom parallel aggregate extension to Postgresql and I would like to have at least N worker processes performing the aggregation every time the aggregate function is used. Is it possible to tell postgresql to ALWAYS use at least N workers…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
1 answer

Update whole JSONB object in Postgres 12

I am having a hard time to replace the JSONB object I have in a column named config with a fresh JSONB object. According to Postgres (12) documentation, one should use jsonb_set in order to update existing JSON records: I am trying to replace the…
elmazzun
  • 1,066
  • 2
  • 18
  • 44
0
votes
1 answer

postgreSQL-12: Is there a way to enable FIFO to limit database table storage capacity?

I have a ThingsBoard PE setup using AWS EC2 instance, with postgreSQL-12 as the database. There is a table ts_kv_2020_10 which stores all telemetry data for the month of October. Is there a way I can enable FIFO on this ts_kv_2020_10 table to keep…
ta.ng
  • 55
  • 1
  • 8
0
votes
0 answers

Postgres C extension parallel aggregate, how to get worker id and row number?

I am writing a Postgres C extension for a Parallel Aggregate function. Normally, in C or C++ I get my thread id and indexing of the work to be done, and then I can make sure the work can be done with no dependency and that the final result is always…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
0 answers

Postgres custom parallel aggregation extension in C, shared state?

I am creating a Postgres C extension that is a parallelized aggregate function. Is there any way I can share the state, or any any state/memory-block I choose, among all the workers? I dont want each worker process to have its own copy, I want a…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
0 answers

PostgreSQL12.4 install on CentOS8 Warning: /etc/ld.so.conf.d/postgresql-pgdg-libs.conf: No such file or directory

I am installing PostgreSQL12.4 on a CentOS8 system by using the libs, PGDG, server and devel rpm's and getting the following warning: ldconfig: Warning: ignoring configuration file that cannot be opened: /etc/ld.so.conf.d/postgresql-pgdg-libs.conf:…
P_Ar
  • 377
  • 2
  • 9
  • 25
0
votes
0 answers

Postgresql 12 CAST now() to date not working

I am finding strange why database is having following result: my1db=# select now()::date; now ---------------------------- 2020-10-08 19:57:24.483647 (1 row) But here is the result of my other datbase: my2db=# SELECT…
iccy
  • 51
  • 2
  • 6
1 2 3
…
36 37