In my data ‘table1’, there are multiple records for each app_id. I’m trying to get a latest app_id entry. Which one of the below queries would be quick or better to consider in terms of performance …
Tag: postgresql
case statement in index creation
How do i use a CASE statement when creating a UNIQUE INDEX? My statement looks like this CREATE UNIQUE INDEX my_unique_creation ON junk ((CASE WHEN nlevel(path) > 1 THEN (…
Update a field in a JSON column in PostgreSQL
I have a work_item table that has the following schema and a document_type table with the following schema: The data column is a json column that has a Type field. This is a sample column data: I need to update data columns whose data_type column value is DocumentModel and Type field values matches a value in…
Postgres on conflict do update on composite primary keys
I have a table where a user answers to a question. The rules are that the user can answer to many questions or many users can answer one question BUT a user can answer to a particular question only once. If the user answers to the question again, it should simply replace the old one. Generally the on conflict…
How to create a database with UTF-8 collation in PostgreSQL on Windows?
I’m configuring PostgreSQL db for the Bitbucket Server on Windows. In the official guide it says that: The database must be configured to use the UTF-8 character set. It doesn’t strictly say that you have to set collation to UTF-8, but for other atlassian procucts it’s recommended so I assum…
Postgresql, retrieve value for specific key from json array
I have Postgres JSONB array of objects, looking like this : This JSONB is a function argument. What is the most efficient way to retrieve skillLevel for skillId = “1”. I’ve tried to play with jsonb_array_elements but everything I’ve done so far is looking really messy. Answer In Postgr…
How to change the position of column in postgresql without dumping
I know the only workaround is to dump the table and then recreate the whole database with correct positioning of the required columns. But the problem is one of the columns is a foreign key to many tables in the database so it would not be possible to just delete the whole table. Also I cannot delete any colu…
Input of anonymous composite types is not implemented – Postgres
I have a table with an XML column. The XML can contain an optional link element. 1d177dee-1da4-4451-b175-396666afc370 I …
SQL: Running Total of Occurrences
I am using Metabase with a PostgreSQL implementation. I’d like to achieve the outcome in the photo. I wish to include a column that counts how many times a field has occurred above. It should include the current instance as well. Other examples I’ve seen have simply counted total occurrences. As I…
Return default rows from a function when first SELECT does not return rows
I have this function http://rextester.com/VIHMIG61446 and I’m looking a way to not repeat the whole query on the if, so I decided to use with as to store the result but it does not work for me http://rextester.com/MVMVA73088 How should I use with as? Answer The if-else logic can be avoided completely. T…