CREATE TABLE IF NOT EXISTS schema1.table1 ( ID bigint NOT NULL, eid bigint NOT NULL, sID bigint, oname character varying COLLATE pg_catalog."default" NOT NULL, obtype integer NOT NULL, parentid bigint, pageorder integer, obsubtype integer NOT NULL DEFAULT 0, pstate integer…
I'm learning PostgreSQL and I'm trying to understand the details of how to choose how to scan this database. I have postgtresql 14.2 and run the following code: DROP TABLE IF EXISTS t_test; CREATE TABLE t_test (id serial, name text);…
I am adding some columns to a table, and want a generated column that combines them together that I'll use for a unique index. When I try to add the column, I get the error ERROR: generation expression is not…
I have this basic PostgreSQL query: SELECT AGE('2021-01-21', '1942-11-20'); Which returns an interval in days: output: 28531 days, 0:00:00 I am using PostgreSQL version 14, according to the docs, AGE() should return a symbolic result in years, months and days…
I am restoring a postgres database via .backup file from one postgres-14 instance to another: pg_restore -h localhost -p 5432 -U postgres -d mydatabase -v "mybackupfile.backup" The backup fails, complaining about pg_restore: error: could not execute query: ERROR: function public.uuid_generate_v1()…
Trying to format the number as per the given format and culture. Given: -4059587.225000, --Value '#,##0.00;(#,##0.00)' --Format 'en-US' --Culture Will have many patterns, the given one is for example. Expected output: (4,059,587.23) In SQL Server we have format() function, what's…
I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds. The minimum query to reproduce this issue is the following : WITH MyTable…
In Postgres, when a table owner, say "xxx" tries to transfer the table ownership to user "yyy", he is getting error ERROR: must be member of role "yyy" When I searched further, it can be fixed in two ways. As…
We are trying to query millions of rows of data from 1 table (14 columns) in under 2 seconds if possible. This table is composed of more than 10 million records and is growing exponentially as live data is being…