[GENERAL] ORDER with CASE and Random for each case

Hi, If have a view that I would like to sort where I divide the return in 3 different groups. These 3 groups then should have a random sort order each. As I am I using it with an offset, and limit, the randomness should be the same. For example: SELECT user_id, age FROM view_users ORDER BY CASE

[GENERAL] Searching array for multiple items

Hi, I can search an array with 1 = ANY('{1,3,4,7}'::int[]) I need to check for one or multiple items in the array. e.g. '1,7,3' = ANY('{1,3,4,7}'::int[] I do need to check if a) all items exist in the array b) at least one item exists in the array Is there a an operator that allows me to do th

[GENERAL] Extract data from JSONB

Hi, I need some help with extracting data from json. I have the following jsonb field modules { "accounts": {"status": true}, "admin":{"status": true}, "calendar": {"status": false}, "chat": {"status": true}, "contacts": {"status": true}, "dashboard":

[GENERAL] Retrieving comment of rules and triggers

Hi, is there a way to retrieve the comment of rules and triggers. I worked it out on functions, tables, views but am kind of stuck with rules and triggers. Any help is appreciated. Thanks Alex

[GENERAL] Keeping top N records of a group

Hi, i want to archive data and am looking for a query to replace a rather slow function i am currently using. The idea is that for every sym and doc_key I want to keep the records of the top 2 sources. Eg. in below table I want to archive sym 1022 of source 3000 but keep the 4 records from sources

[GENERAL] Log Monitoring with PG Admin

Hi, i am running and RDS instance on AWS but monitoring logs is a bit cumbersome. Is there a way to do the through pgadmin3 ? Like tailing the logfile? Does anyone know when 9.5 will be available on aws? Thanks Alex

[GENERAL] Cannot install Extention plperl in 9.5

Hi, I installed 9.5 as described in http://tecadmin.net/install-postgresql-9-5-on-centos/ on centos7. Is there a problem with the Repo or do I miss something? [local]:template1=# \dx List of installed extensions Name | Version | Schema |

[GENERAL] Cannot Create Objects

Hi, I am having a few problems with access permissions. When I create a new role with NOCREATEUSER and then create a database for that role I can connect to the DB but when trying to create a db object I will get the ERROR: permission denied for schema public. Strangely though, if the role is cr

[GENERAL] Creating Report for PieChart

Hello, I need to process some statistics for a pie chart (json) where I only want to show a max of 8 slices. If I have more data points like in below table I need to combine all to a slice called others. If there are less or equal 8 i use them as is. I am currently doing this with a plperl functio

[GENERAL] Left Join with Limit 1

Hello, I am trying to extract ip addresses from golite by joining two tables as posted below. Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds. Is there a way to use a limit in the join?

[GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

Hi, is it possible to grant select to views and functions without the need to also grant the user the SELECT privileges to the Tables used in the views or functions? That way I could create read only users on a website and limit their access to the bare minimum. Thanks in advance for any advise o

[GENERAL] Inject rows into query resultes

Hi, I am having a problem where I need to inject rows into a query result. That is I have a list of add records, lets say car listings which I select from a table. The order of the results are given either by the user or default/initial. I now want to inject featured car adds after every 5th reco

[GENERAL] Rounding Float Array

Hello, I have a float array holding geo location information. geoloc --- {5.3443133704554,100.29457569122} {5.3885574294704,100.29601335526} {3.1654978750403,101.60915851593} {5.3766154817748,100.31472444534} {3.1545014704258,101.70036971569} (5

[GENERAL] PLPerl Trigger to update text search

Hi, I am trying to update a tsvector field through a plperl trigger. $_TD->{new}{text_search} = to_tsvector('pg_catalog.english', 'text1 text2'); but plperl does not seem to like that... ERROR: Undefined subroutine &main::to_tsvector called anyone done that and could help me out if that is act

Re: [GENERAL] Sorting CSV string and removing Duplicates

Hi Danish, yes thats the one I was looking for. Thanks a lot!!! On Tue, Jul 28, 2015 at 1:32 AM, dinesh kumar wrote: > > > On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar > wrote: > >> On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum >> wrote: >> >>> He

[GENERAL] Sorting CSV string and removing Duplicates

Hello, I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 i tried string to array and unique but that did not work... Any suggestions on how to do this without wri

[GENERAL] Working with Array of Composite Type

Hello, I am struggling with finding the right way to deal with arrays of composite types. Bellow is an example of the general setup where I defined an image type to describe the image properties. A user can have mulitple images stored. What is the best way to: a) search for an image within the