Use this tag to indicate that your question is about PostgreSQL version 13. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-13]
368 questions
1
vote
2 answers
PostgreSQL: increment column value in select statement based on previous values
I am using Postgresql 13 and I have a table that looks something like this:
event_id
timestamp
1
2022-11-28 00:00:00
1
2022-11-28 00:00:10
2
2022-11-28 00:00:20
2
2022-11-28 00:00:30
2
2022-11-28 00:00:40
3
2022-11-28…
dalleraro
- 23
- 3
1
vote
2 answers
postgresql psql command \d does not list indexes
I created a table in PGSQL (version 13) using the following command:
db1=# create table temp2(
foo int PRIMARY KEY,
bar varchar(20) UNIQUE NOT NULL
);
CREATE TABLE
The \d or d+ command does not list the associated indexes for the table (contrary to…
Ahmed A
- 3,362
- 7
- 39
- 57
1
vote
0 answers
Encountering failure during restore of TimeScale (Postgres) database
I am trying to restore a sizeable dump file (100GB) from a TimeScaleDB (Postgres) database, but the restore appears to catastrophically fail after a few hours with the following error:
pg_restore: error: could not uncompress data: (null)
I have no…
docf
- 11
- 3
1
vote
2 answers
PostgreSQL - Cannot drop unique constraint because it doesn't exist BUT it does exists
How is it possible that when I try to add a unique constraint named "serveurs_domain" it says I can't because the constraint already exists. BUT, when I try to drop the constraint "serveurs_abbr" it says it doesn't exist.
I'm confused ???
SQL…
Karam
- 31
- 4
1
vote
1 answer
Is it possible to find duplicating records in two columns simultaneously in PostgreSQL?
I have the following database schema (oversimplified):
create sequence partners_partner_id_seq;
create table partners
(
partner_id integer default nextval('partners_partner_id_seq'::regclass) not null primary key,
name …
tftd
- 16,203
- 11
- 62
- 106
1
vote
0 answers
SQL query using Common Table Expression (CTE) not returning results in some cases
I am using a CTE to insert a row and then a SELECT/UNION/SELECT clause to fetch the inserted row (or if the row already exists, return that row).
I want this query to always be returning a row but I'm finding there are certain cases where nothing is…
Dylan
- 43
- 5
1
vote
1 answer
Set concatenated string as a application_name
I'm trying to set application_name with some concatenated string as shown below in the example:
Example:
do
$$
declare var1 text := 'Text1';
var2 text := 'Text2';
result text;
begin
set application_name = var1||'-'||var2;
…
MAK
- 6,824
- 25
- 74
- 131
1
vote
0 answers
Postgresql pg_table_size() returning wrong data
Table queried size is not same as on physical layout
and table fsm file is of same size as table file. Ideally It should have been very small comparatively.
Postgresql Engine= 13
OS = centos7
Table file size:
# du -sh 16385_vm
8.0K 16385_vm
# du…
Monika Yadav
- 381
- 2
- 12
1
vote
1 answer
Exception while reading from stream ---> System.IO.IOException
I use Postgresql-13 in my ASP MVC NET 6 project, occasionally, I face this issue below:
Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.IO.IOException: Unable to read data from the transport connection: An…
Dev
- 107
- 6
1
vote
2 answers
oracle %rowtype equivalent in postgresql
Need to convert the %rowtype of oracle to equivalent in postgresql.
My try:
create table public.test
(
id int,
name varchar(20)
);
insert into test values (1,'A');
insert into test values (2,'B');
insert into test values (3,'C');
Note: I…
MAK
- 6,824
- 25
- 74
- 131
1
vote
0 answers
Postgres SQL ERROR: XX001: invalid page in block
This error has just started popping when I run queries against TABLE_A .......
ERROR: XX001: invalid page in block 38 of relation pg_tblspc/16402/PG_14_202107181/16404/125828
If I try a very simple query against the same table for example SELECT *…
ConanTheGerbil
- 677
- 8
- 21
1
vote
2 answers
ERROR: requested character too large for encoding: 14844072
I am converting following line of code from Oracle to PostgreSQL.
In Oracle:
select CHR(14844072) from dual
Output:
"
"
In postgresql:
select CHR(14844072);
Getting an error:
SQL Error [54000]: ERROR: requested character too large for…
MAK
- 6,824
- 25
- 74
- 131
1
vote
1 answer
PostgreSQL 13 - "Vacuum full" process killed but disk does't free up
I have killed the VACUUM FULL was running on a database since 5 hrs.
Before initiation the disk was 286 GB free and the VACUUM full took 150 GB while processing.
My question is , Why the 150 GB is not released after the process is killed?
and how…
Siddheshwar Soni
- 182
- 1
- 7
1
vote
1 answer
Performance regression in PostgreSQL 14.3 compared to PostgreSQL 13.7 on the same query
I migrated a database from Postgresql 13.7 to Postgresql 14.3 recently, and I saw a x2 execution time in a specific case which I can't understand.
The issue has been seen first on Postgres on Linux, and I reproduced the issue using the following…
whisust
- 176
- 3
- 16
1
vote
1 answer
Postgres SQL query takes more than a minute SOMETIMES to load data from a large table
I have a Postgres database with a fairly large table for events, average query time for this table is around 1 second when querying data between two dates, but sometimes the same query takes more than 1 minute to complete the execution.
Table size…
Shresthdeep Gupta
- 36
- 6