Questions tagged [postgresql-12]
The postgresql-12 tag has no usage guidance.
23
questions
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:
...
