PostgreSQL execute SELECT * FROM (t as a); return ERROR: syntax error at or near ")"
Why these SQLs can't work in PostgreSQL? SELECT * FROM (t as a); or SELECT * FROM (t); ERROR: syntax error at or near ")" Those SQLs work well in MySQL.
How can I get SQL join query that the results in json format using postgres? – Postgresql
`How can I get SQL join query that the results in json format using postgres? I believe this can be done but I am struggling to figure it out. Here is the SQL code to create tables and insert test…
SQLAlchemy relationship fields name constructor – Postgresql
I'm using SQLAlchemy 1.4 to build my database models (posgresql). I've stablished relationships between my models, which I follow using the different SQLAlchemy capabilities. When doing so, the fields of the related models get aliases which don't work for me.…
SQL: how to get sum of grouped items for a given corhort – Postgresql
I have the following orders table: (1, 2, '2021-03-05', 15, 'books'), (1, 13, '2022-03-07', 3, 'music'), (1, 14, '2022-06-15', 900, 'travel'), (1, 11, '2021-11-17', 25, 'books'), (1, 16, '2022-08-03', 32, 'books'), (2, 4, '2021-04-12', 4, 'music'), (2, 7, '2021-06-29', 9,…
Apache AGE – Creating Functions With Multiple Parameters – Postgresql
I was looking inside the create_vlabel function and noted that to get the graph_name and label_name it is used graph_name = PG_GETARG_NAME(0) and label_name = PG_GETARG_NAME(1). Since these two variables are also passed as parameters, I was thinking that, if…
How to retrieve rows based on a time interval within rows in Postgres? – Postgresql
I have table (t_answer) like below: user_id created_at answer 1 2023-01-01 1a 1 2023-01-02 1b 1 2023-01-11 1c 2 2023-02-05 2a 2 2023-02-20 2a I want to retrieve the rows within an interval of 1 week starting from each user's…
LEFT JOIN with `json_build_object` returns JSON object initialized with null values, when it should just be null – Postgresql
Suppose the following, CREATE SCHEMA IF NOT EXISTS my_schema; CREATE TABLE IF NOT EXISTS my_schema.city ( id serial PRIMARY KEY, city_name VARCHAR(15) NOT NULL ); CREATE TABLE IF NOT EXISTS my_schema.user ( id serial PRIMARY KEY, city_id BIGINT REFERENCES my_schema.city…
Replace Oracle sql with postgres sql – Postgresql
how to replace bitand function and decode oracle function in postgres sql? select decode(bitand(p.privilege, 2), 2, 'true', 'false') as is_approver from person p; tried below sql select case ((p.privilege & 2 = 2))::int when 2 then 'true' else 'false' end…
Is it safe to insert row, with value incremented by CTE select? – Postgresql
Say we have this table: create table if not exists template ( id serial primary key, label text not null, version integer not null default 1, created_at timestamp not null default current_timestamp, unique(label, version) ); The logic is to insert…
Counting parent records by status, and applying another count by considering that one of the child element must have a particular child-status – Postgresql
I have following tables: Parent: id status 1 SENT 2 CREATED Child: parent_id status 1 a 1 b I have a following select statement: select count(distinct parent.id) as all, count(distinct parent.id) filter (where parent.status = 'CREATED') as new, count(distinct parent.id)…