Tag: PostgreSQL

Someone asked me to quickly write them some code in any language that would accomplish a certain task. The following, which is in PostgreSQL, is the second solution that I came up with after doing the first one in my favorite language, JavaScript: SELECT ARRAY_TO_STRING(ARRAY( SELECT CHR(num + CASE WHEN Read more…

The following is one answer to this Problem of the Week that works in PostgreSQL (and probably in other flavors of SQL): SELECT id FROM (SELECT id + 1 AS id FROM stars UNION SELECT 1 AS id) t WHERE NOT EXISTS (SELECT s.id FROM stars AS s WHERE s.id Read more…

Something I had to do today was figure out how to find the SUM of an array in PostgreSQL. I went through many different ways, but this seemed to be the best: SELECT SUM(t) FROM UNNEST(ARRAY[1,2,3,4,5,6,7,8,9]) t; If you know of a shorter or better way to do this for Read more…

Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views: SELECT 'GRANT SELECT ON ' || Read more…

Someone had an issue today where they were using a string as if it were a regular expression. Not totally understanding what the desired end result would be, I had suggested they use something equivalent to Java’s Pattern.quote() function. It turned out that they really just wanted to determine if Read more…

Another DBA task that I seem to have to carry out is making sure that a specified user or group has all (CREATE & USAGE) permissions to all of the schemas (except pg_catalog for which they only get USAGE) in the database. For this reason, I end up using the Read more…

One of the things that I often had a difficult time with was finding a way to select the newest records from a table that had one or more columns to group by and then one column to order by. Let’s use the following table as an example: Version Name Read more…

Recently at work we had the need to convert a string parameter (passed into an iReport) into a table. For many people, the first thing that may come to mind is either the STRING_TO_ARRAY(...) function combined with the UNNEST(...) function. The biggest issue with using just those two functions is Read more…

Now to grant all access for roles on all of the functions in the database, you can use a query such as the following: SELECT 'GRANT ALL ON FUNCTION ' || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || ' (' || pg_get_function_identity_arguments(p.oid) || ') TO john_doe;' FROM pg_language AS l JOIN Read more…

Once again, since the DBA is on vacation today, I have to assume some of his duties. It seems that nobody has access to anything in the development databases. After granting them permissions to all of the tables in the public schema (figured this out last time), I was asked Read more…