Skip to content

Tag: postgresql

Sort by given “rank” of column values

I have a table like this (unsorted): risk category Low A Medium B High C Medium A Low B High A Low C Low E Low D High B I need to sort rows by category, but first based on the value of risk. The desired result should look like this (sorted): risk category Low A Low B Low C

SQL SELECT statement with a many-to-many extra table

I have a table that links users. Consider the following: The user_links table can have 0 rows for a particular user_id, given the user doesn’t have linked users, so a select statement can return either a row or NULL. The approach with left join user_links ul on ul.user_id = contracts.user_id OR ul.linked_user_id = contracts.user_id doesn’t seem to work if there

Postgresql find by two columns

I have join table tags_videos How can I select distinct video_id who have two specific tag_id For example my tag_ids is 1195 and 1198, i should get video_ids 15033 and 15036 (who have 1195 and 1198 tag_id) Answer Extract the unique (tag_id, video_id) pairs for the two tags in t CTE and select these video_id’s that have both tag_id’s (i.e.

PostgreSQL – I have a Syntax error in my SUBSTRING query

I’m trying to make use of the SUBSTRING() function to extract a substring from vm.location_path, starting at the second character and ending at the position of the ‘]’ character, minus two. I want to extract the text between the square brackets ([]) in vm.location_path but I’m hitting a syntax error. It’s between the comma at the end of and What

How do I correctly map letters in the database?

I have two tables. One table with the letters of different countries and a second table with a mapping of these letters to each other. I need to make a query to get the mapped letters of the two languages. Can you tell me how this can be done optimally? The letter table id letter language 1 A en 2

What is a runtime complexity of this sql query?

I would like to know how fast is SELECT * FROM user_table WHERE email = ‘’ is this O(1) or O(n)? how does sql search for a particular row? Answer If there is no index on “email” column, the search complexity is O(N). If there was hash-based index on an “email” column, then the search could be performed in O(1).

How to do conditional aggregate based on values of another column?

I have a table called device_data that looks like below It basically stores the packet drops, jitter, latency on a minute basis. Now there is another column called alert that holds the value HIGH, MEDIUM and LOW based on a threshold and holds empty string if the threshold is not met. Now I do an hourly average on the table,