Category "postgresql"

Postgresql error 53300 even though there are fewer connections than maximum allowed

I'm using npgsql on my ASP.NET application to connect to a postgres database. On trying to open a connection, I get the following error: 53300: remaining connec

SELECT DISTINCT, ORDER BY expressions must appear in select list - error when migrating from Oracle to Postgres

I'm in the process of migrating our database from Oracle to Postgres. We have a number of hardcoded queries in our spring boot application that now break as res

Concat rows based on condition [duplicate]

I'm working with a table that has values like this ID Name Value 123 Size 1 123 Size 2 123 Size 3 123 Type Shoes 234 Size 6 234 Size 7 234 Ty

Postgres order civic numbers by digits and letters

I've two columns in text format containing street names and civic numbers. I'd like to set a query to order rows by street name and then by digits and by charac

SQL comparison matching ORDER BY

When one orders results by multiple columns the database has to do something to compare NULL values (e.g. order them first last). But how can I replicate that

Postgres Citus, immutable date conversion

Trying to update some dates programmatically on Citus I always get [0A000] ERROR: STABLE functions used in UPDATE queries cannot be called with column reference

copying a table across servers with psycopg3

I'm working from and example in the psycopg3 documentation to copy a table from one database to another: link dsn_src = 'postgresql:///dev_db' dsn_tgt = 'postgr

PostgreSQL version compatible with Windows 7 32 bit

I have tried to install PostgreSQL 10.6 on Win 7 SP1 32 bit and I got following error after trying to run pgadmin4: "postgresql application server could not be

Django web site URLS gets "?next=" randomly after deployed to heroku

After deploying django app on heroku the url after every form submission or link in site been press resulting with "?next=" inserts into the URL address, I don'

Accidentially deleted rows from pg_enum

Was looking to drop an enum and did it incorrectly by deleting the rows from the pg_enum table. I should have removed it with this: DROP TYPE IF EXISTS "enum_Or

Why include `pg_temp` in `search_path` at all when using SECURITY DEFINER

The docs recommend setting pg_temp as the last entry in search_path. Why not leave it out of the list altogether? Would this accomplish the same?

Error: trying to push Table SQLAlchemy models to my production heroku-server using Alembic

I am trying to push my SQLAlchemy-models to create Tables in my heroku-postgres-database. I use this command: heroku run alembic upgrade head It starts to Run

How to extract data from DWH on a certain date? Is there the special pattern for this case in Postgres SQL?

Usually we use EXTRACT (FROM YEAR date_column) = 2000 (let it be 2000 year). Also we can add EXTRACT (MONTH FROM date_column) = 1 (let it be January). Also we c

Real-time pipeline for processing data, insert into PSQL

I have files that will be coming in daily that I would like to process as they come in and insert into existing sql tables (using postgres). What is the best wa

complex UPDATE query in psycopg2

I've stumbled upon an interesting problem while trying to compose an SQL query using psycopg2.sql I have a function that takes in some kind of data in a dict fo

Is it possible to upgrade Cloud SQL postresql version with data in the 'postgres' database

We are planning to upgrade our Cloud SQL postgresql instances to V12. According to the instance upgrade guide, we should export data from the current instance

GCP Server crashing with "cloudsql.enable_instance_password_validation"

Over the past day I have been experiencing this error very frequently which results in the cloud instance needing to be reset in order to continue connections:

Add key/value pair to all objects inside a jsonb array in POSTGRES if key does not already exist

I have a table, let's call it my_table with the following structure ID | data ____________ uuid | jsonb The data in the jsonb field is an object structured i

EXIT CODE 1 when restore sql file in postgres

I have an SQL file which is manually generated. I use the code below to create it. conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_nam

Benefit to adding an Index for an order by column?

We have a large table (2.8M rows) where we are finding a single row by our device_token column CREATE TABLE public.rpush_notifications ( id bigint NOT NULL,