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

Prevent consecutive duplicate values without a trigger

Within a group, I'd like to prevent INSERTs of consecutive duplicate values, where "consecutive" is defined by a simple ORDER BY clause. Imagine a set of experiments which is regularly sampling values from a sensor. We only want to insert a value if…
LondonRob
  • 73,083
  • 37
  • 144
  • 201
0
votes
0 answers

Inconsistent Permission Error on postgres schema

I have remote database server and I have different roles and users. for example I have database test and schema test_schema and table test1. I am connected to the database using test_user and I am querying the table like below. select * from…
Awaish Kumar
  • 537
  • 6
  • 22
0
votes
1 answer

How to reuse a newly assign column in a PostgreSQL update sentence

From this simple example it is clear that a newly assigned value of a column in an update sentence cannot simply be reused in other columns assignment. drop table if exists stocks cascade; create table stocks ( id serial, …
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
1 answer

implement partitioning on PostgreSQL tables

I'm implementing partition to PostgreSQL (version 12). I'll use the transaction_date (year) to create the partitions, one partition per year, trigger and function to save data on the right partition. I'm applying the implementation Using Inheritance…
Julian Leite
  • 114
  • 1
  • 2
  • 8
0
votes
2 answers

Npgsql C# - pass parameters as array of composite type into stored procedure

Maybe this topic is duplicated from this Array of composite type as stored procedure input passed by C# Npgsql. But that is old one from 2017 and some APIs, properties are deprecated. Currently, I am trying to pass an array of composite types to the…
Triet Nguyen
  • 763
  • 9
  • 20
0
votes
1 answer

How to return an array of table records in PostgreSQL

I am getting a type mismatch error when trying to return an array of elements of type table1, the inherent type of the table1 I have declared. Error occurred during SQL query execution Razón: SQL Error [42P13]: ERROR: return type mismatch in…
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
1 answer

Get next value of big serial and insert

I have the following table, In the present table we have data inserted manually like shown below but now I want to insert latest cola values into table. create table tbTest ( cola bigserial not null primary key, colb varchar(10) ); insert…
MAK
  • 6,824
  • 25
  • 74
  • 131
0
votes
1 answer

PostgreSQL trigger function syntax error help request

I haven't the slightest idea why postgres is complaining about the word 'INTO' after 'INSERT'. Does anyone know? FWIW I only created the body of the code, pgAdmin created the header and footer. CREATE FUNCTION public.tf_test() RETURNS trigger …
Rick DeBay
  • 315
  • 1
  • 5
  • 13
0
votes
1 answer

How to avoid a `select sum() into` set target variables to null in PostgreSQL

This select sum() is part of a trigger function and it is woking correctly, -- loop over all order lines without grouping select sum(tp.pack_volume), sum(tp.pack_mass), sum(tl.qty_ordered), sum(tl.qty_fulfilled) into …
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
1 answer

postgresql 12 function strange value / length behaviour

I have a char(13) column named a in table test. I created a classic before insert function with the following line : raise notice 'a: -->%<-- len = %', new.a, length(new.a); When I run insert into test (a) values('1');, I get the following output…
Fred Sullet
  • 371
  • 1
  • 6
  • 18
0
votes
0 answers

Have there been significant performance improvements in TABLESAMPLE bernoulli since PostgreSQL 9.5?

I have been testing queries with the same data in PostgreSQL 9.6.11 and PostgreSQL 12.3 using the TABLESAMPLE bernoulli clause. It seems PostgreSQL 12.3 executes the query about 25x faster than the old version. Before i dig into other performance…
Nicholas Tulach
  • 1,023
  • 3
  • 12
  • 35
0
votes
0 answers

no pg_hba.conf entry for host \"IP-ADDRESS", user \"username\", database \"database\", SSL off

I am using Digitalocean managed PostgreSQL and trying to access the database from node.js (using pg-promise) which is a Google Cloud Function. no pg_hba.conf entry for host "IP-ADDRESS", user "username", database "database", SSL off Below is my db…
Giri
  • 451
  • 1
  • 4
  • 13
0
votes
2 answers

How to respect the order of an array in a PostgreSQL select sentence

This is my (extremely simplified) product table and some test data. drop table if exists product cascade; create table product ( product_id integer not null, reference varchar, price decimal(13,4), primary key…
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
2 answers

postgresql query result using where condition another query

my goal is detect all usernames and count each username login time in 24 hours. I was rite code separately but i cant combine it. SELECT DISTINCT "username" FROM my_table; my second query is get me count log related this username select…
Mehmet Başaran
  • 77
  • 2
  • 10
0
votes
0 answers

Postgres multi column index with many columns vs many single indexes

I have a very large table with over two billion rows and ~90 columns. The table size is 1.8TB and the goal is being able to do a query on any single column or combination of columns in the shortest time possible. The data will not be updated and no…
Arthur B
  • 1
1 2 3
…
36 37