8 votes
Accepted

using "\dp table_name" in "psql" throws "operator is not unique"

The cause of the problem is that commit 07eee5a0dc changed the type category of the internal data type "char" (which is the type of pg_policy.polcmd) from "string" to "...
Laurenz Albe's user avatar
  • 51.2k
2 votes
Accepted

Set 'timing on' for the duration of the current `psql` session using command-line options

You can use the -c option and the -f option together. The -c must come before if you want it processed before the file is. psql ... -c '\timing on' -f file.sql
jjanes's user avatar
  • 39.7k
2 votes
Accepted

Issues using a psql -v variable with jsonb_set

To inject the :slack_token variable as a literal in the SQL query, the proper syntax to use is :'slack_token'. From the psql documentation in "SQL interpolation": When a value is to be used ...
Daniel Vérité's user avatar
2 votes

Execute gexec in a psql command

General options: -c, --command=COMMAND run only single command (SQL or internal) and exit Since the command you're trying to run doesn't begin with a backslash, it's considered to be an SQL ...
mustaccio's user avatar
  • 25.8k
2 votes

How to use psql with no password prompt?

Way-1: PGPASSWORD="<password>" psql -U <db_user> -d <db_name> -h <host_name/domain> -p <port> Way-2: psql postgres://<db_user>:<user_password>@<...
Sadia's user avatar
  • 21
1 vote

How do I list all databases and tables using psql?

<Database> This lists all databases: SELECT datname FROM pg_database; These list all databases in detail: \list \l These list all databases in more detail: \list+ \l+ <Table> These ...
Super Kai - Kazuya Ito's user avatar
1 vote
Accepted

Merging information from multiple rows into one row

If I were you, I would lose the JSON and use the PostgreSQL ARRAY type - PostgreSQL has very powerful array processing capabilities which might be better suited to your needs. JSON is usually for &...
Vérace's user avatar
  • 29.8k
1 vote

Merging information from multiple rows into one row

You can use a correlated subquery where you aggregate JSON from the other tables. SELECT e.event_id, e.event_name, e.event_date, ( SELECT jsonb_agg(to_jsonb(p.*)) FROM ...
Charlieface's user avatar
  • 12.7k
1 vote
Accepted

Unable to revoke privileges in Postgres even though revoke statements succeed

The error message is not talking about privileges on tables in the schema, but about privileges on the schema itself. REVOKE ALL ON SCHEMA myschema FROM myuser;
Laurenz Albe's user avatar
  • 51.2k
1 vote
Accepted

Chain 2 queries with \gexec in psql

\gexec ... Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as an SQL statement to be executed. Bold emphasis mine. Currently, you ...
Erwin Brandstetter's user avatar
1 vote

PostgreSQL 16, pg_dumpall Restore

I managed to resolve this, because I was using pg_dumpall --clean when I was trying to restore the db it was trying to drop the db first which the caused the error as the db didn't exist. I reran the ...
AStaUK's user avatar
  • 11
1 vote
Accepted

Setup Citus extension in a ssh environment

You don't have to "modify root" (whatever you mean by that) to change shared_preload_libraries. All you need is either shell access to the PostgreSQL server as PostgreSQL user, so that you ...
Laurenz Albe's user avatar
  • 51.2k
1 vote
Accepted

psql meta-command \d is really slow

If you run psql with the -E command line option, you will be able to see the query it emits when you issue \d. You can then run EXPLAIN (ANALYZE, BUFFERS) for that query to see where it may be ...
mustaccio's user avatar
  • 25.8k
1 vote

How to add information about two same named databases on different hosts in PgBouncer config file?

Pgbouncer has [databases] section where you define names clients use to connect and the actual databases that mapped to these names. Assuming you have database db1 in 2 hosts, you will have something ...
a1ex07's user avatar
  • 8,990
1 vote

Using PostgreSQL

The syntax to execute an SQL script through the command line with psql is: psql "host=servername port=etc..." -f okta.psql The metacommand \i okta.sql is to be used inside psql or inside an ...
Daniel Vérité's user avatar
1 vote
Accepted

data modeling - alternatives to NULL foreign key?

In my opinion, it doesn't really matter. If the foreign key is on villages (and pointing to map), it will be efficient enough to find all the spots on the map without a village, as long as there is an ...
BigSmoke's user avatar
  • 684
1 vote
Accepted

psql: Preventing Idle Transactions by Default

You can specify the parameter in your connection string, then it will be set during a reconnect: psql 'options=-cidle_in_transaction_session_timeout=2000 host=localhost user=postgres dbname=postgres' ...
Laurenz Albe's user avatar
  • 51.2k
1 vote

"NOTICE: identifier will be truncated" in psql when I try "ALTER SYSTEM SET"

The correct command line, after properly quoting the command string constant (literal), as Laurenz Albe suggested in a comment): psql -c "alter system set archive_command="\ "'...
1 vote

COPY ... FROM STDIN command failing on null values exported as "\N"

this was in another post to use this option on loading the file. psql -P 'null=\N' If you want to stop in any errors add -v ON_ERROR_STOP=1 This seams to work for me, it stops if I get an error. Only ...
Robert's user avatar
  • 11

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