ads' corner

Single line input for psql copy command

I have the following problem:

A database server and another client machine. I have to execute several \copy commands in psql on the client machine to retrieve data and store the output in CSV files. The PostgreSQL COPY command is not an option because it stores the results on the database server, not the client machine. All \copy commands are executed from several files containing SQL commands, which are concated together by a Makefile and feeded into psql. So far so good.

role ... cannot be dropped because some objects depend on it

I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:

table size, database size

I have seen this question more than once in the past: “How much storage space do I need for my table?”

PostgreSQL can give you this information.

stop psql on first error

Because I always forget how to stop psql on the first error:


You can include this at the top of your script/file, psql will stop right after the first error and will not try to continue any other commands in the chain.

Avoid Unique Key violation

If you have a table with an unique key field, want to insert new values but you don’t know if the value may already be in the table, you can use the following query:

Reverse a text in PostgreSQL

I’m always searching for a reverse function in PostgreSQL, so here is the link: