Stack Overflow
Stack Overflow

Questions tagged [postgresql-13]

Use this tag to indicate that your question is about PostgreSQL version 13. Questions concerning database administration should go to https://dba.stackexchange.com/

368 questions
2
votes
1 answer

Postgresql group by query to convert json format

My tabla data as follows; _id product_id product_name sell_price purchase_price country 1 prd_1 product one 15 12 usa 2 prd_1 product one 16 13 canada 3 prd_2 product two 7 5 china 4 prd_2 product two 12 10 japan I want to group it…
gencero
  • 68
  • 1
  • 8
2
votes
1 answer

H2 vs PostgreSQL generated column with function

I'm trying to setup a generated column which will also take null checks into consideration when subtracting values. In PostgreSQL I did: ALTER TABLE session ADD COLUMN duration INTERVAL GENERATED ALWAYS AS age(time_ended, time_started)…
agilob
  • 6,082
  • 3
  • 33
  • 49
2
votes
3 answers

How to do a pg_dump for only tables only and not triggers and functions?

What I want: I want a pg_dump of a database (let's call the database as 'test'). In this pg_dump I want only the tables without the following: data, triggers, functions, sequences, etc. What I am doing to get what I want: The command I run is as…
theQuestionMan
  • 1,270
  • 2
  • 18
  • 29
2
votes
0 answers

Permission denied: PostgreSQL

For one of my projects, I had to install the PostgreSQL server. I followed all the necessary steps and the server was installed successfully. Nevertheless, when I am trying to run SQL Shell (psql) it does not allow me to access the…
Pawara Siriwardhane
  • 1,873
  • 10
  • 26
  • 38
2
votes
1 answer

How to call PostgreSQL 13 stored procedure (no function) with cursor INOUT parameter from Npgsql 4.1.5.0 in C#

I have this Stored Procedure with IN char parameter and INOUT cursor parameter: CREATE OR REPLACE PROCEDURE SP_ObtenerFacturaPorNombreCliente(IN p_nombreCliente CHAR, INOUT p_cursorFacturas REFCURSOR) LANGUAGE PLPGSQL AS $$ BEGIN OPEN…
Ejrr1085
  • 975
  • 2
  • 16
  • 29
2
votes
1 answer

PostgreSQL running with versions 12 and 13 confusion

I am fairly new to PostgreSQL but I have some knowledge of SQL. I've been using a database with tables and data on postgres that (I'm assuming) comes with MacOS because when I start the server, access a given database and run SELECT version(); on…
everspader
  • 1,272
  • 14
  • 44
2
votes
1 answer

How to add Foreign key constraint on array in PostgreSQL?

How to add Foreign key constraint on array in PostgreSQL? look_up table for roles CREATE TABLE party_role_cd ( party_role_cd bigint NOT NULL, code character varying(80) NOT NULL, CONSTRAINT party_role_cd PRIMARY KEY (party_role_cd) ); Party…
Thirumal
  • 8,280
  • 11
  • 53
  • 103
1
vote
1 answer

find out elevated privileges in Postgres database

I was trying to find out the user/roles which got elevated privileges in the postgres database. Elevated privileges like alter/drop/create/owner of table i.e. any user who can change the existing tables or can create/drop the table.. I was going…
Sandy
  • 419
  • 1
  • 8
  • 15
1
vote
0 answers

Postgresql update is hanging without having blocking locks

I have a table which has an id and a lock column. create table mytable(id varchar(255),lock varchar(255), primary key (id)); create index idx_mytable_lock on mytable (lock); I populated it with 1 million records where only the id gets a unique UUID…
thehpi
  • 5,683
  • 4
  • 17
  • 24
1
vote
0 answers

PostgreSQL query on union - filter condition not being "pushed down" into subqueries resulting in much worse performance

I'm using postgres version 13.3, and I have multiple views, each constructed with a join between a (foreign) data table and a shared metadata tables that serves as a lookup. each view is constructed like this CREATE VIEW pg_table__view AS…
Vlad Keel
  • 372
  • 2
  • 13
1
vote
0 answers

Dynamically assign a default value for Postgres column

I have a nullable column whose values are taken from an enum. I want to define that column's default value based on another column's value. For example, given the following table: name | type | status -----|------|------- abcd | t1 | null efg |…
Eliran Turgeman
  • 1,526
  • 2
  • 16
  • 34
1
vote
0 answers

How to insert a json data into postgresql using database function cal without create entity model(getter and setter) in spring boot

I am new to spring java. I have a controller class and repository class. what I actually want is to insert a json data into postgresql database by calling a database function(stored procedure)and return a json data as response.It shows that BAD SQL…
shameera
  • 11
  • 2
1
vote
1 answer

Postgresql - CHECK constraint not preventing ACCESS EXCLUSIVE lock and table scan when attaching new partition

I'm running postgresql 13. The below section of the postgres doc doc says I should be able to avoid a scan and ACCESS EXCLUSIVE lock to validate the partition constraint. Before running the ATTACH PARTITION command, it is recommended to create a…
user797963
  • 2,907
  • 9
  • 47
  • 88
1
vote
1 answer

Postgres - how to add a partition to a table that has a DEFAULT partition, without holding AccessExclusiveLock?

Specifically, this section of the postgres doc doc says I should be able to avoid a scan of the default partition: Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached that matches…
user797963
  • 2,907
  • 9
  • 47
  • 88
1
vote
0 answers

What level of parallel safety avoids "cannot assign XIDs during a parallel operation"?

I recently ran into this error message while executing a query that called one of my PL/pgSQL functions: ERROR: cannot assign XIDs during a parallel operation CONTEXT: SQL statement [redacted] PL/pgSQL function [redacted] The function is marked…
Ben Millwood
  • 6,754
  • 24
  • 45
1 2 3
…
24 25