I have a db where I can have multiple rows with same field “aid” (it’s a sort of historical changelog of same items). With the following query, I’m extracting rows with same aid orderd by descending date: That should be a child table related to a father layer (made by a postgres materi…
Tag: postgresql
How can I unite these two related queries?
I have this query to check if a person is a customer or have been: And I have this other query to get the locations: How can I unite them and show person location as a single row on the first query? Answer If I follow this correctly, you can use lateral joins: As commented already, your original first query
SELECT NOT IN with multiple columns in subquery
Regarding the statement below, sltrxid can exist as both ardoccrid and ardocdbid. I’m wanting to know how to include both in the NOT IN subquery. Answer I would recommend not exists: Note that I changed the table aliases to things that are more meaningful. I would strongly recommend prefixing the column…
Way to suppress PostgreSQL-Error with custom error
For the last hours I got stuck on the problem, that I want a custom error (“Wrong airline_id. Please check your input again”) over the normal error code (insert or update on table “flightplan” violates foreign key constraint “fk_flightplan_airline”). But no matter what I tr…
Querying specific relation that doesnt have a value on a column
I have the following tables: Person (id, name, address) Person_subscription (id, person_id, status) Does a person have many subscriptions but no active one? get that person as ‘Ex-Customer’ Does a …
postgresql jsonb case insensitive query with index
I was looking for a way to make a case insensitive query, and I found it here (postgresql jsonb case insensitive query), more precisely with a query like this : select … where upper(data::text)::jsonb @> upper(‘[{“city”:”New York”}]’)::jsonb However, I can’t …
How to make Custom attributes for SQL table
What is the proper way to give user to use custom attributes ? As an example Consider that, There is a web application, using the frontend user interface admin user need to be able to add custom …
Why I get division by zero for PostgreSQL v13? I suppose rows should be filtered out
Running same query on different versions of PostgreSQL gives different result. WITH t(a, b) AS (VALUES (1, 0)) SELECT a / b FROM t WHERE b 0; On v11.10 I get no rows as expected fiddle On …
How to get first row of data for each month (postgres)
I am a beginner in postgres and would like to get the first row for each month (group by) but I’m not really sure how. My table order is as per below: order_id | cust_id | order_date ——————…
SQL Sorted Count
I have the following table sorted by date: date id 9/1/20 1 9/1/20 2 9/3/20 1 9/4/20 3 9/4/20 2 9/6/20 1 I’d like to add a count column for each id so that the first count for each id is the earliest date and latest date would receive the highest count for each id: date id count 9/1/20 1