Questions tagged [postgresql-12]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
27 views

How to split one table into two in a single statement?

I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table ...
0
votes
0answers
24 views

PostgreSQL database cluster

I installed PosgreSQL 12 using the certified installer by EnterpriseDB. I went through the docs. In Chapter 18.2. Creating a Database Cluster, it is said that you need to initialize a database ...
0
votes
2answers
39 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
1
vote
0answers
32 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
1
vote
2answers
40 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
0
votes
0answers
11 views

posgres instance is listening to old port even after changing to new port number

My machine is having posgres server 11th version,for testing purpose i installed another posgres instance(12 version )in same machine.i changed port number of this new instance to 5433 but when i try ...
1
vote
0answers
116 views

Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking: One important query which takes 100ms on version 11 (same VM) ...
0
votes
0answers
11 views

How to create index for XML query in PostgreSQL

I am using PostgreSQL 12. below is my table schema create table soa_rawdata_lp ( date_created timestamp with time zone default now(), filename varchar(128), xmldata xml ); Each ...
1
vote
2answers
38 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
2
votes
1answer
14 views

Postgresql query XMLTable with default namespace

I use PostgreSQL 12 to query XML data. My data is as below CREATE TABLE xmldata AS SELECT xml $$ <ROWS xmlns="http://x.y.z"> <ROW id="1"> <COUNTRY_ID>AU</COUNTRY_ID> ...
0
votes
1answer
91 views

Problem Installing PostgreSQL 12.1 Win8.1: “the database cluster initialisation failed”

I'm getting an error at the end of installing PostgreSQL 12.1 which is "the database cluster initialisation failed" I have been searching all the day, so here what I tried: 1- Run the installation ...
5
votes
1answer
218 views

Case-insensitive collation still comparing case-sensitive

I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The ...
0
votes
0answers
19 views

merging many jsonb objects using aggregate functions in PostgreSQL?

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL ...
0
votes
2answers
37 views

Select records that have at least one link to other tables

I want to select all users that have either a post, a photo or a video. I came up with the following query. select users.username from users where exists (select * from posts where users.id = ...
0
votes
0answers
7 views

How to get all variables for the current plpgsql function?

Is it possible to get all the variables of a PL/pgSQL function (including input parameters and DECLAREed variables)? My goal is to collect these variables into a jsonb object with each variable name ...
-1
votes
1answer
118 views

Can PostgreSQL manage a table with 1.5 billion rows?

The table has three columns: id string (up to 400 characters) length which records the length of string The problem was, when I do a query, e.g. select * from table where length = <whatever ...
0
votes
1answer
141 views

pgAdmin IV and PostgreSQL 12.0: ERROR: schema “public” already exists

I have a database backup from pgAdmin III with PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1) on Ubuntu 16.04 LTS. I would like to restore this database on my pgAdmin IV with PostgreSQL 12.0 on Docker ...
0
votes
1answer
14 views

SQL - Find Results with MAXimum for given colum

I have a question on how to select best match in PostgreSQL for a given result/condition: I have a setup containing 3 tables. 1 is a result overview table with reliability and status| 2 is a ...
3
votes
3answers
268 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
1
vote
0answers
15 views

Understanding PostgreSQL Execution Plans

I'm trying to understand the motivation of PostgreSQL 12 query planner here. Database Structure CREATE TABLE subjects ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ); CREATE TABLE ...
1
vote
2answers
140 views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
0
votes
1answer
52 views

Postgresql - sequences bigger than bigint

PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over ...
-3
votes
1answer
374 views

Return result sets from stored procedure in Postgres v12

How to return result sets from a procedure in Postgres v12(beta). The link below says that changes have been done in Postgres v12 beta. https://wiki.postgresql.org/wiki/TODO#Server-Side_Languages: ...