ChatGPT解决这个技术问题 Extra ChatGPT

Run PostgreSQL queries from the command line

I inserted a data into a table....I wanna see now whole table with rows and columns and data. How I can display it through command?


T
Tomalak

psql -U username -d mydatabase -c 'SELECT * FROM mytable'

If you're new to postgresql and unfamiliar with using the command line tool psql then there is some confusing behaviour you should be aware of when you've entered an interactive session.

For example, initiate an interactive session:

psql -U username mydatabase 
mydatabase=#

At this point you can enter a query directly but you must remember to terminate the query with a semicolon ;

For example:

mydatabase=# SELECT * FROM mytable;

If you forget the semicolon then when you hit enter you will get nothing on your return line because psql will be assuming that you have not finished entering your query. This can lead to all kinds of confusion. For example, if you re-enter the same query you will have most likely create a syntax error.

As an experiment, try typing any garble you want at the psql prompt then hit enter. psql will silently provide you with a new line. If you enter a semicolon on that new line and then hit enter, then you will receive the ERROR:

mydatabase=# asdfs 
mydatabase=# ;  
ERROR:  syntax error at or near "asdfs"
LINE 1: asdfs
    ^

The rule of thumb is: If you received no response from psql but you were expecting at least SOMETHING, then you forgot the semicolon ;


Since at least 7.2 (that's as far back as I bothered to check, it might be even earlier), psql has had the option --single-line (or -s) that makes every command immediate with no need to add a semicolon.
I spent an hour debugging and the problem was the missing semicolon. THANK YOU
The single-line option mentioned by @Gordon is a CAPITAL s (i.e -S), if you use lower-case -s that means single-step mode (confirm each query)). You can see the options by running psql --help.
a
a_horse_with_no_name
SELECT * FROM my_table;

where my_table is the name of your table.

EDIT:

psql -c "SELECT * FROM my_table"

or just psql and then type your queries.


psql -U username -c your_database "SELECT * FROM my_table"
as said, you should end your command with semicolon ; if you're on a windows system, see notws for windows users basically, just put cmd.exe /c chcp 1252 after you've opened console prompt.
for future users, be sure to add the -d before your database name, and -c before the query: psql -U username -d mydatabase -c 'SELECT * FROM mytable'
didn't worked for me .I did psql: FATAL: database "SELECT * FROM tb_name" does not exist
@GstjiSaini looks like DrColossos forgot -d param psql -U ravil -d hw5 -c "SELECT * FROM table_name;" -- it works
ס
סטנלי גרונן

If your DB is password protected, then the solution would be:

PGPASSWORD=password  psql -U username -d dbname -c "select * from my_table"

Never know can provide password this way. It is really handy when doing test. Thanks
G
Gene

Open "SQL Shell (psql)" from your Applications (Mac).

https://i.stack.imgur.com/CAbO6.png

Click enter for the default settings. Enter the password when prompted.

https://i.stack.imgur.com/CSH1j.png

*) Type \? for help

*) Type \conninfo to see which user you are connected as.

*) Type \l to see the list of Databases.

https://i.stack.imgur.com/CWPQX.png

*) Connect to a database by \c <Name of DB>, for example \c GeneDB1

https://i.stack.imgur.com/UKbo0.png

https://i.stack.imgur.com/jeq7Q.png

*) Now that you're in a given DB, you want to know the Schemas for that DB. The best command to do this is \dn.

https://i.stack.imgur.com/58NCB.png

Other commands that also work (but not as good) are select schema_name from information_schema.schemata; and select nspname from pg_catalog.pg_namespace;:

https://i.stack.imgur.com/w2ydF.png

-) Now that you have the Schemas, you want to know the tables in those Schemas. For that, you can use the dt command. For example \dt "GeneSchema1".*

https://i.stack.imgur.com/RtKc2.png

*) Now you can do your queries. For example:

https://i.stack.imgur.com/y64ac.png

*) Here is what the above DB, Schema, and Tables look like in pgAdmin:

https://i.stack.imgur.com/v3iYN.png


How we can get chunks for whole schema? After connecting db following doesnt work SELECT show_chunks(older_than => interval '1 day');
Looks like show_chucks() is a method or Stored Procedure that you're calling. I don't know because I don't know the documentation for that method. You try \dt "YourSchema".* ???
B
Ben

I also noticed that the query

SELECT * FROM tablename;

gives an error on the psql command prompt and

SELECT * FROM "tablename";

runs fine, really strange, so don't forget the double quotes. I always liked databases :-(


D
Devil's Dream

I have no doubt on @Grant answer. But I face few issues sometimes such as if the column name is similar to any reserved keyword of postgresql such as natural in this case similar SQL is difficult to run from the command line as "\natural\" will be needed in Query field. So my approach is to write the SQL in separate file and run the SQL file from command line. This has another advantage too. If you have to change the query for a large script you do not need to touch the script file or command. Only change the SQL file like this

psql -h localhost -d database -U postgres -p 5432 -a -q -f /path/to/the/file.sql

K
KPatel

Open a command prompt and go to the directory where Postgres installed. In my case my Postgres path is "D:\TOOLS\Postgresql-9.4.1-3".After that move to the bin directory of Postgres.So command prompt shows as "D:\TOOLS\Postgresql-9.4.1-3\bin>" Now my goal is to select "UserName" from the users table using "UserId" value.So the database query is "Select u."UserName" from users u Where u."UserId"=1".

The same query is written as below for psql command prompt of postgres.

D:\TOOLS\Postgresql-9.4.1-3\bin>psql -U postgres -d DatabaseName -h localhost - t -c "Select u.\"UserName\" from users u Where u.\"UserId\"=1;


S
Stefan

I will add my experience for one command, on windows machine. I wanted to try to run single command from which i would get table content.

This is the single command which worked for me:

psql -U postgres -d typeorm -c "SELECT * FROM \"Author\"";

-U postgres - user

-d typeorm - my database to which i want to connect

-c ... - my query command

; - semicolon

I had issues, mostly with figuring out how to exactly setup query part. I tried with different commands like: with ', ", (), but nothing worked for me but this notation.


M
Mehdi Hamin

For running SQL command directly in password-protected database. It'd rather using connection string format in command line. Use this command :

psql -d postgresql://postgres:password@localhost:5432/dbname 
-c "create database sample1 --or any command"

D
DEV Tiago França

## Using SQL file
PGPASSWORD=myPassword psql -h myDbHost -U myUser -p 5432 -d my_db -a -q -f /path/to/sql/file.sql
## Or
PGPASSWORD=myPassword psql -h myDbHost -U myUser -p 5432 -d my_db < /path/to/sql/file.sql

## Using command
PGPASSWORD=myPassword psql -h myDbHost -U myUser -p 5432 -d my_db -c "select * from my table limit 1;"