Grant “create schema” ON database to a user?
It’s more simple than I thought. GRANT CREATE ON DATABASE db TO user;
postgresql-9.1
PostgreSQL: Show all the privileges for a concrete user
table permissions: select * from information_schema.role_table_grants where grantee=”YOUR_USER” ; ownership: select * from pg_tables where tableowner=”YOUR_USER” ; schema permissions: select r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable from pg_namespace join lateral ( SELECT * from aclexplode(nspacl) as x ) a on true join pg_user e on a.grantee = e.usesysid join pg_user r on … Read more
Postgres SELECT … FOR UPDATE in functions
Does it matter which columns I select? No, it doesn’t matter. Even if SELECT 1 FROM table WHERE … FOR UPDATE is used, the query locks all rows that meet where conditions. If the query retrieves rows from a join, and we don’t want to lock rows from all tables involved in the join, but … Read more
How to handle special characters in the password of a Postgresql URL connection string?
See Connection URIs in the doc. There are a few things that don’t seem quite right in your question: URIs are supported by postgres since version 9.2 only, so with a 9.1 client that’s not supposed to work at all. Or you’re using a client that implements connection URIs itself. Percent-sign encoding is supported. Per … Read more
How to create a new database with the hstore extension already installed?
Long story short: Install hstore in the template1 database: psql -d template1 -c ‘create extension hstore;’ Step-by-step explanation: As stated by the PostgreSQL documentation: CREATE EXTENSION loads a new extension into the current database. Installing an extension is database-specific. The following returns you the current database name: $ psql -c ‘select current_database()’ current_database —————— username … Read more
How does one drop a template database from PostgreSQL?
postgres=# UPDATE pg_database SET datistemplate=”false” WHERE datname=”template_postgis”; UPDATE 1 postgres=# DROP DATABASE template_postgis; DROP DATABASE postgres=#
Easy way to view postgresql dump files?
This was what I was looking for: pg_restore db.bin > db.sql Thanks @andrewtweber
Postgresql insert trigger to set value
You are correct that you need a trigger, because setting a default value for the column won’t work for you – default values only work for null values and don’t help you in preventing blank values. In postgres there are a couple of steps to creating a trigger: Step 1: Create a function that returns … Read more
PL/pgSQL checking if a row exists
Simpler, shorter, faster: EXISTS. IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN — do something END IF; The query planner can stop at the first row found – as opposed to count(), which scans all (qualifying) rows regardless. Makes a big difference with big tables. The difference is small for a condition … Read more
PostgreSQL date() with timezone
Basically what you want is: $ select starts_at AT TIME ZONE ‘UTC’ AT TIME ZONE ‘US/Pacific’ from schedules where id = 40 I got the solution from this article is below, which is straight GOLD!!! It explains this non-trivial issue very clearly, give it a read if you wish to understand pstgrsql TZ management better. … Read more