Why do certain psql commands from terminal work for local database and not for hosted database?-CodePudding

I have imported a local PostgreSQL database to a managed cluster on Digital Ocean. It will be used with a Python app that will also be hosted on Digital Ocean. I used pg_dump and pg_restore to achieve the import. Now, to make sure the import was successful, I am running some psql queries and commands via my MacOS terminal app that is set up with zsh and it connects via a shell script that prompts me for host, database name, port, user and password. I am successful in connecting to the managed cluster this way, and I can execute some queries with no problem, while others are causing errors. For example:

my_support=> \dt
                List of relations
 Schema |         Name         | Type  |  Owner  
-------- ---------------------- ------- ---------
 public | ages                 | table | doadmin
 public | articles             | table | doadmin
 public | challenges           | table | doadmin
 public | cities               | table | doadmin
 public | comments             | table | doadmin
 public | messages             | table | doadmin
 public | relationships        | table | doadmin
 public | topics               | table | doadmin
 public | users                | table | doadmin
(9 rows)

my_support=> \dt 
sh: more: command not found
my_support=>

Also:

my_support=> SELECT id,sender_id FROM messages;
 id | sender_id 
---- -----------
  1 |         1
  2 |         2
  3 |         4
  4 |         1
  5 |         2
(5 rows)

my_support=> SELECT * FROM messages;
sh: more: command not found
my_support=>

So the terminal app seems to dislike certain characters, such as * and , but I can't find any documentation that tells me I should escape them, or how. I tried backslash in front of them, but it did not work. And what's more confusing is that these very same queries are successful when I connected to my LOCAL copy of the database, using the very same terminal app, launched from the very same shell script.

In case it's helpful, here's what I see in the CLI when I connect:

psql (14.1, server 14.2)
SSL connection (protocol: TLSv1.3, cipher: <alphanumeric string here>, bits: 256, compression: off)
Type "help" for help.

my_support=>

Does it matter that my local PostgreSQL version is 14.1 and the server is 14.2? I'm assuming the "server" refers to the hosted environment, but it seems like something as basic as "SELECT * FROM" should not be version-dependent.

Ultimately what matters is whether my Python app (which uses psycopg library to talk to PostgreSQL) can run those queries, and I haven't test that yet. But it sure would be handy to test things on the managed cluster using my local terminal app.

BTW, I have an open ticket with Digital Ocean to answer this question, but I find SO to be faster and more helpful in most cases.

CodePudding user response：

psql is trying to use a pager to display results that are longer than the number of lines in the terminal. The error message

more: command not found

indicates that the pager (more) it tries to use is not available. You can turn off the use of a pager:

\pset pager off

or set a different command to be used as the pager. See the manual for details