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 "...
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
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 ...
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 ...
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>@<...
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 ...
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 &...
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 ...
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;
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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'
...
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 ...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
psql × 360postgresql × 316
pg-dump × 19
copy × 15
permissions × 11
linux × 11
scripting × 11
command-line × 11
ubuntu × 10
postgresql-9.3 × 9
pgadmin × 9
mac-os-x × 9
windows × 8
errors × 7
csv × 7
bash × 7
backup × 6
postgresql-9.4 × 6
pg-restore × 6
index × 5
schema × 5
postgresql-9.5 × 5
database-design × 4
restore × 4
foreign-key × 4