PostgreSQL | Connecting to PostgreSQL with psql | Executing SQL Commands in psql
This page explains how to execute SQL commands such as SELECT and CREATE statements in psql.
Executing SQL Commands
To create databases and tables or retrieve data stored in tables, execute SQL commands in psql. In practice, the SQL commands are sent to the server and processed there. To execute SQL commands, first start Command Prompt and connect to PostgreSQL with psql.
C:\Users\kimkc>psql -U postgres
Password for user postgres:
psql (12.2)
Type "help" for help.
postgres=#
The following prompt appears.
postgres=#
To execute a SQL command, enter it as follows.
postgres=# SQL statement;
When entering a SQL command, type a semicolon (;) at the end and then press Enter to execute it. If you press Enter before entering the semicolon, the command is only split across lines and is not executed.
Try it in practice by creating a new database. Enter the following command and press Enter.
postgres=# create database sample;
postgres=# create database sample;
CREATE DATABASE
postgres=#
The SQL command was sent to the server and processed.
Entering a SQL Command Across Multiple Lines
When a SQL command is long, you can split it across multiple lines. Try this by entering a SQL command for creating a table over several lines. First, enter only create and press Enter.
postgres=# create
postgres-#
When executing a SQL command, enter a semicolon at the end and press Enter. If you press Enter before entering the semicolon, psql treats it as continued input.
During continued input, the prompt changes from postgres=# to postgres-#, so you can tell that the command is still being entered.
Next, enter table staff and press Enter.
postgres=# create
postgres-# table staff
postgres-#
Finally, enter (id integer, name character varying(10)); and press Enter. Because the semicolon (;) has been entered before pressing Enter, psql determines that the SQL command input is complete and executes it.
postgres=# create
postgres-# table staff
postgres-# (id integer, name character varying(10));
CREATE TABLE
postgres=#
The SQL command was executed and a new table was created.
In this way, long SQL commands can be entered across multiple lines by pressing Enter while typing.
Canceling Input While Entering Multiple Lines
If you want to cancel a SQL command while entering it across multiple lines, enter the psql command \r and press Enter.
Try it in practice. Enter create table and press Enter.
postgres=# create table
postgres-#
To cancel the input here, enter \r and press Enter.
postgres=# create table
postgres-# \r
Query buffer reset (cleared).
postgres=#
This cancels the SQL command input.
–
This page explained how to execute SQL commands in psql.