Tag: postgresql

Using if-then or case-when-then to select from dataset

I have a dataset with students in 1st, 2nd and 3rd grades. I want to select the names of all students in 1st and 2nd grades, but only students named John, Jane or Smith from 3rd grade. Here is what I have so far: I’m not sure what I’m getting wrong there but I’d appreciate some help. Thanks Answer You

How can I get last 2 records from another table as columns

postgresql sql

I have a table called products with this schema: And another table with fee change log with this schema: Is there anyway to get last 2 fee changes for each sku in one sql and not 2 rows for each sku, I want to have 2 new columns with old_fee_1, new _fee_1, old_fee_2, new_fee_2: Desired result: dbfiddle Answer As starting

How to use between if value is not null?

postgresql sql

Im trying to write query where im using valid_from and valid_to but those values can be null or one of them. This is my query, so i want to return data using if both valid_from and valid_to is null, or both not null or only valid_to is null. Any suggestion how can i achive that? Answer You could just explicitly

SQL Query to fetch the customers registered in the DB without email address(CS can have phonenumber and email in the same field but duplicating)

postgresql sql sql-scripts subquery union

I need help with writing this query please , in the Database – the customer is registered twice , one row with the email address in the VALUE field and the other row with phone number in the SAME VALUE field . I want to fetch customer who DO NOT HAVE email address in the VALUE FIELD . For example

PostgreSQL query which return metrics joining different tables need to be more efficient and fast

postgresql sql

I have a query that can be seen on this fiddle and I’m relatively new to PSQL, having a few months of experience. DB Fiddle The link to the third party is to show the result of it otherwise would be difficult to visualize it here. This query collects and returns some metrics joining 3 tables about candidates being in

How to use ANALYZE EXPLAIN in a with_something query

postgresql sql

I’m trying to find out how to use ANALYZE EXPLAIN on my PSQL query. If I add ANALYZE EXPLAIN SELECT, I get a syntax error. I have no clue how to use it. I added the ANALYZE before the first and second SELECT, but both throw a syntax error. I was unable to find different examples but on documentation, I

Get distinct sets of rows for an INSERT executed in concurrent transactions

concurrency locking multithreading postgresql sql

I am implementing a simple pessimistic locking mechanism using Postgres as a medium. The goal is that multiple instances of an application can simultaneously acquire locks on distinct sets of users. The app instances are not trying to lock specific users. Instead they will take any user locks they can get. Say, for example, we have three instances of the

translate query from Oracle to Postgres

oracle postgresql regex sql

I need to translate this query from Oracle to Postgres: can someone help me? thanks in advance for your attention and support Answer In postgres the operateur ~ performs a regex comparison as thus replaces the Oracle function regexp_like(). Your query therefore becomes. I would like to alert your attention that the test not upper(location) ~ ‘^[0-9]{1,5}.* [a-z]’ will always

Postgres: Where value equal decimal value

postgresql sql

My table looks like this: When the WHERE clause is equal to value with decimal point I get no valid output like: Output: transponder_id = None When the query is for integer value, query works correctly: Output: transponder_id = 1009 Also using BETWEEN query returns expected values Output: transponder_id = 1010 Why is my query using ‘equal’ operator not working