Category : postgresql

Let’s say I have a simple table. In raw SQL it looks like this: CREATE TABLE events (id INT PRIMARY KEY) WITH (fillfactor=60); My question is how to specify fillfactor for a table using sqlalchemy declarative base? I’ve tried the following approach, but that didnt’t work: from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import ..

Read more

Alright, new day, new query, happy life… SELECT dt.dt AS date FROM generate_series( ‘1986-02-01 00:00:00+01’::timestamp with time zone, ‘2021-01-14 00:00:00+01’::timestamp with time zone, ‘1 mon’::interval) dt(dt); so far so good. now let’s get this thing running in a nice script in psycopg2…. with connection.cursor() as cur: cur.execute( "SELECT dt.dt AS date FROM generate_series(" "’1986-02-01 00:00:00+01′::timestamp ..

Read more

I am trying to generate the following raw query using sqlalchemy core: SELECT library.name, (SELECT ARRAY (select book.title from books where book.library_id = 1 and book.is_free = false) as book_lists) from library where id = 1; So the query I generate in sqlalchemy is: query = sa.select( [ library.name, sa.func.array( sa.select( [books.title] ).where( sa.and_( books.c.library_id ..

Read more

I want to catch the PostGres query results that gets executed in the remote server (passwordless-ssh) via expect command that is used to supply the password for the given PostGres user. Actually, there are two versions of our application, the older version does not require Postgres password where as the new version requires it. So ..

Read more

Hi i wrote the insert APi inthe Api i am sending one json object as input parameter,even my function also working fine,when i am trying to executing the Api getting below error: cannot call json_populate_recordset on a scalar CONTEXT: SQL statement "insert into public."VOfficeApp_project_resources" (project_id,employee_id,assigned_date,assigned_by) SELECT v_project_id,employeeid,utcdatetime,assignedby FROM (select employeeid,assignedby from json_populate_recordset (NULL ::public.vofficeapp_project_resource_type,projectresource_json )) ..

Read more

I have two modes one is IndexDetails and another is PriceIndex. In PriceIndex foreign key doesn’t map properly IndexDetails primary keys.parent_id should be between 1 to 4 but it doesn’t. class IndexDetails(Base): __tablename__ = ‘index_details’ __table_args__ = ({"schema": "demo"}) id = Column(Integer, primary_key=True,nullable= False, unique=True,autoincrement=True) ticker = Column(String(20)) name=Column(String(20)) region=Column(String(20)) # children = relationship("IndexPrice", back_populates="parent") ..

Read more