44 votes

Granting access to all tables for a user

Granting all privileges to all tables within the database is achieved with GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>; GRANT ALL PRIVILEGES ON ALL SEQUENCES ...
Ganbayar Gansukh's user avatar
34 votes

How do I list all schemas in PostgreSQL?

Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction. Here's how the trick goes. ...
Soni Harriz's user avatar
  • 1,150
22 votes

How can I create readonly user for backups in PostgreSQL?

No, it's easy (now anyway). Grant the connect permission on a new user GRANT CONNECT ON DATABASE mydb TO myReadolyUser; Grant the permissions on all the current database objects. This is schema-...
Evan Carroll's user avatar
  • 63.1k
21 votes

Backup only one schema and restore it on another server

You can add parameter -n [schema name] The comment of this parameter is said: -n schema --schema=schema Dump only schemas matching schema; this selects both the schema itself, and all its ...
yosaku01's user avatar
  • 319
16 votes

Is the keyword "ALIAS" actually used?

PostgreSQL maintains a list of reserved and non-reserved terms in the appendix. ALIAS is absent from that list. You can verify PostgreSQL does not use ALIAS by checking out the YACC grammar. Even as ...
Evan Carroll's user avatar
  • 63.1k
15 votes

How to list all views in SQL in PostgreSQL?

If you only need this interactively while in psql, you can also use \dv to show views, or \dm for materialized views. Or use with +, like \dm+ for example to show some additional information (mostly ...
Tim's user avatar
  • 323
11 votes

How to view the current settings of Autovacuum in Postgres?

If you want to see the vacuum settings for a specific table: SELECT relname, reloptions FROM pg_class WHERE relname='tablename'; The general vacuum settings can be seen in postgresql.conf.
collimarco's user avatar
9 votes

Fragmentation in PostgreSQL 9.1

Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are ...
KookieMonster's user avatar
8 votes

Granting access to all tables for a user

--Create User CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxxx'; -- Grant connect to my data base GRANT ...
Jorge Valenzuela's user avatar
8 votes

Converting rows to columns

Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me: CREATE TABLE tbl (created_at timestamp, status text); INSERT INTO tbl ...
Erwin Brandstetter's user avatar
8 votes

copy command in postgresql to append data

Assuming a Unix system with PostgreQSL 9.3 or newer Use the PROGRAM clause with cat as the program. In the context of a loop in the plpgsql language, where tableName would be a variable, the ...
Daniel Vérité's user avatar
8 votes

Postgresql Log "^M" Meaning

Your log file is created with the 'Windows End-of-Line' way. When you check it with Vi or Vim then the ^M (= CR = Carridge Return) is show because it is not part of the 'Linux End-of-Line' way. You ...
Marco's user avatar
  • 3,710
8 votes

Is the keyword "ALIAS" actually used?

It is used at least in various flavours of Db2: ALIAS is an object that allows you to specify a different name for another object, like table. It's frequently used to allow references in one schema to ...
mustaccio's user avatar
  • 26k
7 votes

How can I create readonly user for backups in PostgreSQL?

After testing back up with Evan Caroll's solution I came across this error: ERROR: permission denied for relation 'table' there's one other permission missing: GRANT SELECT ON ALL SEQUENCES IN ...
Jean-François Godbout's user avatar
7 votes

pg_dump with ssl certificates

The following worked for me: pg_dump "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-ca" -f &...
Kasun's user avatar
  • 71
6 votes

How can I create readonly user for backups in PostgreSQL?

If you want (or can live with) a backup user having read-only access to all databases, you may use the pg_read_all_data role since the release of Postgres 14. You can create such new user with ...
F30's user avatar
  • 181
6 votes

Index max row size error

I was getting this on a PostGIS geography column. It was because I accidentally created the index incorrectly. You should include the USING GIST parameter when creating such indexes.
Brad Mathews's user avatar
6 votes
Accepted

Alter postgresql table with huge data

One idea, without knowing the error you get, is that the operation times out. This sometimes happens when the locks to be taken conflict with other operations or there are too many indexes and ...
András Váczi's user avatar
5 votes

PostgreSQL: Cannot change directory to /root

For me this did the trick, pay attention to quotes (') sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)...
Philippe Gachoud's user avatar
5 votes

Moving postgresql data to different drive

Stop postgresql service cp -a source_data_directory destination_data_directory chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to ...
adi's user avatar
  • 51
5 votes

Converting rows to columns

To answer this, I did the following. (I found the following three threads helpful - 1, 2 and 3. I also found the documentation on generate_series and CROSSTAB here and here respectively to be useful). ...
Vérace's user avatar
  • 29.9k
5 votes
Accepted

Efficient PostgreSQL Updates Using a Temporary Table

Things that can be done, although whether they help or not... is another story: Given that your UPDATE is quite simple, my first guess is your triggers are hurting your performance. Processing ...
joanolo's user avatar
  • 13.4k
4 votes

How do I save functions to individual files in PostgreSQL?

Run the following mkdir /tmp/foo chown postgresql:postgresql /tmp/foo Then in psql, run this command whose inner workings I shamelessly stole from Craig's answer on SO, SELECT FORMAT( 'COPY (...
Evan Carroll's user avatar
  • 63.1k
4 votes

Algorithm for finding the longest prefix

A string S is a prefix of a string T iff T is between S and SZ where Z is lexicographically larger than any other string (eg 99999999 with enough 9's to exceed the longest possible phone number in the ...
KWillets's user avatar
  • 141
4 votes
Accepted

How to count multiple values of a column as one group?

Use the aggregate FILTER clause in Postgres 9.4+: SELECT count(*) FILTER (WHERE rank = ANY ('{a,b,v}')) AS myvalues , count(*) FILTER (WHERE rank = ANY ('{c,d,j}')) AS yourvalues , count(*) ...
Erwin Brandstetter's user avatar
4 votes
Accepted

copy command in postgresql to append data

I think you cannot (easily) do that (at least on your PostgreSQL version, see Daniel's answer). What you definitely can, however, is changing your approach, and do a UNION ALL from all those tables ...
András Váczi's user avatar
4 votes
Accepted

How can I optimize a SELECT DISTINCT on a subselect?

Instead of what you've got, remove the virtual table and try to query directly. SELECT DISTINCT id AS id10 FROM note j WHERE j.modified_date >= '2016-10-01 23:12:34.000000' AND j.clientid = ...
Evan Carroll's user avatar
  • 63.1k
3 votes

Postgresql out of memory while trying to a request of ~500mb

I am not sure if it helps here, but I have had problems when work_mem was too big. With 16MB or bigger I got into such troubles. I reduced to 8MB but now had out of memory condition again. So reduced ...
Gunther Schadow's user avatar
3 votes

How to list all views in SQL in PostgreSQL?

Here's a query that will bring up your materialized views as well and show you the views' dependencies. -- Get a list of views that have dependencies on other views with view_oids as ( select ...
Henry Tseng's user avatar
3 votes

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

Stop the primary service systemctl stop postgresql ( This command might be different on your OS ) login to the secondary and promote it to primary ( -D is for the data directory ) pg_ctl promote -D ...
sam's user avatar
  • 131

Only top scored, non community-wiki answers of a minimum length are eligible