All Questions
Tagged with postgres-12 or postgresql-12
602 questions
3
votes
1
answer
5k
views
Postgres upgrade from 10 to 12: pg_upgrade fail for presence of required library
$ /usr/pgsql-12/bin/pg_upgrade \
> -b /usr/pgsql-1
pgsql-10/ pgsql-12/
> -b /usr/pgsql-10/bin/ \
> -B /usr/pgsql-12/bin/ \
> -d /var/lib/pgsql/1
10/ 12/
> -d /var/lib/pgsql/10/data/ \
&...
- 371
0
votes
0
answers
54
views
How to get ROW_NUM from dynamic query
I have this piece of code :
DO
$$
DECLARE v_RowCountInt Int;
BEGIN
execute ('insert into vcc.toto select * from vcc.segment s limit 50
on conflict (id_segment,pds) do update
...
- 47
0
votes
2
answers
242
views
How do I convert a T-SQL query to PostgreSQL that concatenates substrings?
This is my query:
SELECT
*,
CONCAT(
RIGHT( account_no, 4),
RIGHT( customer_id, 5 )
) AS "password for my diplomo"
FROM
account_info;
But I get this error:
...
- 13
0
votes
1
answer
919
views
Postgres SQL query slow with large table (AWS RDS)
Currently the table have minimum row of 30million, and it is growing, whenever try to do SELECT query, it take extremely long time. What need to optimize the query before I increase the performance of ...
- 1,532
0
votes
1
answer
574
views
Convert PostgreSQL JSONB column results for use in condition with IN
I have a table with a JSONB column that is used to store multiple tags (integer) that have been applied to a task, eg.: '[123, 456, 789]'.
ALTER TABLE "public"."task" ADD COLUMN &...
- 779
0
votes
1
answer
1k
views
Why does psql -f COPY FROM STDIN fail when -c succeeds?
Using psql with COPY FROM STDIN works fine when executed via -c (inline command) but the same thing fails if -f (script file) is used. I've created a Docker-based test to demonstrate below; tested on ...
- 20.6k
0
votes
1
answer
2k
views
How to change effective_io_concurrency in Postgresql
I'm trying to change these two parameters (random_page_cost and effective_io_concurrency) in my Postgresql 12 database. For the first one I found this command :
alter database mydb set ...
0
votes
1
answer
283
views
behavior of storing string in bytea across postgresql-8 & postgresql-12
currently I am migrating the postgres database from pg8 to pg12, but I have encounter with a new issue
previously in pg8
create table test (id int, data bytea);
insert into test values (1,'hello ...
- 639
1
vote
1
answer
649
views
PostgreSQL SELECT based on value in JSON object
I would like to query a table, orders, anad select all rows based on a value in a json object in a column, updates.
I want to select all rows that are within 5 days from the last time it was closed.
...
- 1,349
0
votes
0
answers
198
views
psql could not connect to server after complete reinstallation [Ubuntu 21.10 / PostgreSQL 13]
After experiencing issues related to the existence of two different PostgreSQL versions installed on the same machine, I decided to remove --purge postgresql* and reinstall postgresql-13. Now the ...
- 142
1
vote
1
answer
7k
views
Postgres : can't make JSONB_PATH_EXISTS work correctly
I'm struggling with JSONB_PATH_EXISTS Postgres function
I'm using PG 12 and following this documentation : https://www.postgresql.org/docs/12/functions-json.html
With the following request (test it on ...
- 927
0
votes
0
answers
331
views
Tiger Geocoder - Postgres SSL SYSCALL error: EOF detected with signal 11 segmentation fault
I'm using r6g.4xlarge AWS machine to test tiger geocoder of my own and succeeded in setting up the Tiger Geocoder data of 2019 in a postgresql version 12. I've setup pgtune for better performance and ...
- 229
4
votes
1
answer
895
views
Circular delete cascade in Postgres
(For background I'm using Postgres 12.4)
I'm unclear why deletes work when there are circular FKs between two tables and both FKs are set to ON DELETE CASCADE.
CREATE TABLE a (id bigint PRIMARY KEY);
...
- 61
0
votes
1
answer
189
views
Postgres ECPG: adding a function outside main() breaks the program
I have successfully connected and obtained data from Postgresql with my C application. My connector.pgc file looks like this:
#include <stdlib.h>
#include <stdio.h>
//#include "...
- 1
0
votes
2
answers
1k
views
Hide POSTGRESQL partitioned tables
I'm using pg_partman to partition three of my tables and and ended up with a large number of child tables.
Some users find it difficult to navigate whit their database tool (DBeaver or SQuirreL) with ...
-1
votes
1
answer
2k
views
PostGIS: function ST_AsRaster does not exist. Even using examples from the docs
I'm trying to convert geometries to images, and the functions to do so don't seem to exist.
The following example is from the ST_AsRaster Docs WHich specify the requirements are Availability: 2.0.0 - ...
- 10.3k
2
votes
1
answer
791
views
Is it possible to create a UNIQUE index on geometric types?
I have a table like
my_type | text
my_box | box
where my_box is of type box from the geometric types, and not from postgis.
I want a way to ensure that there are no duplicate boxes across all values ...
- 4,245
2
votes
1
answer
2k
views
Slow Query in partitioned table
We have postgresql12 and have a large table of size 108gb including index. Since, the query got slow, we tried partitioning the table. But this did not help.
EXPLAIN (ANALYZE, COSTS, VERBOSE, ...
- 100
2
votes
1
answer
2k
views
Search and extract element located in various path of json structure
I have a json in a PostgreSQL database and I need to extract an array not always located in same place.
Problem
Need to extract array choicies of a particular element name
Element name is known, but ...
- 2,561
1
vote
2
answers
1k
views
Postgres select specific nested keys from json column
Have a table with json column, this json is quite big, so I want to filter/select only speicific nested keys.
Json Example:
{
"title":{
"nested_1":"This is nested key ...
- 4,188
0
votes
1
answer
1k
views
is there any way to reference array to another table in PostgreSql?I am doing it by getting error
CREATE TABLE destinations(code varchar(80) PRIMARY KEY,
name varchar(80),
updated_at varchar(80),
latitude varchar(80),
...
- 163
3
votes
1
answer
913
views
Why does a user receive a "permission denied" error when trying to update their user password in PostgreSQL?
I have several users of our PostgreSQL (12) database who are not superusers. From the docs, it would appear that any "ordinary" user should be able to set their password to something new, ...
- 117
0
votes
2
answers
6k
views
How to update a column date of postgres to some days ahead
Im using postgres as my database and I have a table that has a date column.
The current value in this column is 2021-04-1 17:19:08
I want to update this column's value and move the date 20 (or any ...
- 915
1
vote
1
answer
84
views
Is BRIN used for delete cascade?
I need indexes on some large tables to support ON DELETE CASCADE, but the size of btree indexes would be a problem. I therefore tried with BRIN, since the performance isn't critical. However, it seems ...
- 69
3
votes
2
answers
3k
views
Postgresql ORDER BY not working as expected
Let's try this simple example to represent the problem I'm facing.
Assume this table:
CREATE TABLE testing1
(
id serial NOT NULL,
word text,
CONSTRAINT testing1_pkey PRIMARY KEY (id)
);
...
- 1,960