database 从命令行运行 PostgreSQL 查询

Run PostgreSQL queries from the command line

提问by Manu Lakaster

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?

我将数据插入表中....我现在想查看包含行、列和数据的整个表。如何通过命令显示它？

回答by Grant

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

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

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

如果您不熟悉 postgresql 并且不熟悉使用命令行工具，psql那么当您进入交互式会话时，您应该注意一些令人困惑的行为。

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 psqlwill 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.

如果您忘记了分号，那么当您按 Enter 键时，您将不会在返回行中得到任何信息，因为您psql会假设您尚未完成输入查询。这可能会导致各种混乱。例如，如果您重新输入相同的查询，您很可能会创建语法错误。

As an experiment, try typing any garble you want at the psql prompt then hit enter. psqlwill 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:

作为实验，尝试在 psql 提示符下输入您想要的任何乱码，然后按 Enter。psql将默默地为您提供一条新线路。如果您在该新行上输入分号，然后按回车键，则会收到错误消息：

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

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

经验法则是：如果您没有收到任何回复，psql但您至少期待某事，那么您忘记了分号;

回答by Paul Draper

SELECT * FROM my_table;

where my_tableis the name of your table.

my_table你的表的名字在哪里。

EDIT:

编辑：

psql -c "SELECT * FROM my_table"

or just psqland then type your queries.

或者只是psql然后输入您的查询。

回答by pyAddict

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

如果您的数据库受密码保护，那么解决方案是：

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

回答by Gene

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

从您的应用程序 (Mac) 打开“SQL Shell (psql)”。

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

点击进入默认设置。出现提示时输入密码。

*) Type \?for help

*)\?输入帮助

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

*) 键入\conninfo以查看您连接的用户。

*) Type \lto see the list of Databases.

*) 键入\l以查看数据库列表。

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

*)\c <Name of DB>例如，通过 连接到数据库\c GeneDB1

You should see the key prompt change to the new DB, like so:

您应该会看到密钥提示更改为新数据库，如下所示：

*) 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.

*) 现在您在给定的数据库中，您想知道该数据库的架构。执行此操作的最佳命令是\dn.

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;:

其他也有效（但效果不佳）的命令是select schema_name from information_schema.schemata;and select nspname from pg_catalog.pg_namespace;：

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

-) 现在您有了架构，您想知道这些架构中的表。为此，您可以使用该dt命令。例如\dt "GeneSchema1".*

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

*) 现在您可以进行查询了。例如：

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

*) 以下是 pgAdmin 中上述 DB、Schema 和 Tables 的样子：

回答by 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 naturalin 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

我对@Grant 的回答毫无疑问。但有时我会遇到一些问题，例如列名是否与 postgresql 的任何保留关键字（例如自然）相似，在这种情况下，很难从命令行运行类似的 SQL，因为查询字段中将需要“\natural\”。所以我的方法是在单独的文件中编写 SQL 并从命令行运行 SQL 文件。这也有另一个好处。如果您必须更改大型脚本的查询，则无需修改脚本文件或命令。只更改这样的 SQL 文件

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

回答by KPatel

1. Open a command prompt and go to the directory where Postgres installed. In my case my Postgres pathis "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>"
2. 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".

1. 打开命令提示符并转到安装 Postgres 的目录。在我的例子中，我的Postgres 路径是“D:\TOOLS\Postgresql-9.4.1-3”。之后移动到 Postgres 的 bin 目录。所以命令提示符显示为“D:\TOOLS\Postgresql-9.4.1-3 \bin>"
2. 现在我的目标是使用“UserId”值从用户表中选择“UserName”。所以数据库查询是“ Select u.”UserName” from users u Where u."UserId"=1“。

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

对于 postgres 的 psql 命令提示符，如下编写相同的查询。

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;

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;

回答by Ben

I also noticed that the query

我还注意到查询

SELECT * FROM tablename;

SELECT * FROM 表名；

gives an error on the psql command prompt and

在 psql 命令提示符下给出错误并

SELECT * FROM "tablename";

SELECT * FROM "表名";

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

运行良好，真的很奇怪，所以不要忘记双引号。我一直喜欢数据库:-(

回答by gotqn

For particular type of statements you can find more appropriate to use Azure Data Studio- for everything else it comes with built-in command line. Using the studio you can find typing statements and reading the output more comfortable.

对于特定类型的语句，您可以找到更适合使用的语句Azure Data Studio- 对于其他所有内容，它都带有内置命令行。使用工作室，您可以找到更舒适的键入语句和阅读输出。

Follow the steps below:

请按照以下步骤操作：

1. Install Azure Data Studio- is a cross-platform modern editor focused on data development; it is open source and extensible - two things that PostgreSQL itself is based on;
2. Install the PostgreSQL extension

3. Connect to your local or remote server:

   

4. Focus on writing and executing SQL only:

   

1. 安装Azure Data Studio- 是一个专注于数据开发的跨平台现代编辑器；它是开源和可扩展的——PostgreSQL 本身基于两件事；
2. 安装 PostgreSQL 扩展

3. 连接到您的本地或远程服务器：

   

4. 只专注于编写和执行 SQL：

   

There are manynice features as saving connections, creating server groups and even built-in terminal if you are more comfortable doing some task via the psql.

有许多不错的功能，如节能连接，创建了服务器组，甚至内置的终端，如果你是更舒适的操作的方式从该些任务psql。