Unraveling PostgreSQL’s Core: A Guide to Essential psql Commands

Unraveling PostgreSQL’s Core: A Guide to Essential psql Commands

In my previous post, I documented how to set up instances of PostgreSQL and PGAdmin using Docker containers to facilitate learning PostgreSQL. Now, I’ll delve further into the topic with essential psql commands and noting some of the differences between T-SQL and PostgreSQL.

For those seeking an alternative approach to learning without dealing with setup intricacies, Crunchydata’s Postgres playground and tutorials present an excellent option. Discover a hassle-free way to grasp the PostgreSQL basics.

CommandDescription
\?Help
\hList SQL commands
\lList databases
\dList objects in current database
\d+List objects with extended information
\d+ customersDescribe a table
\dnList schemas
\dvList views
\duList users
\dfList functions
\dpList access privileges
\c adventureworksSwitch to adventureworks database
spacebar (in pager)Next page on pager
down arrow (in pager)Display pager options
q (in pager)Exit from pager
\pset pager 0Disable pager
\pset pager 1Enable pager
Ctrl-LClear the screen

A valuable habit I acquired while writing T-SQL on SQL Server is consistently terminating statements with a semi-colon, even though it was optional in that context. Interestingly, this habit has proven to be quite advantageous now that I’m working with PostgreSQL, where statements must be concluded with a semi-colon. Notably, when using the command line, statements continue across multiple lines until a semi-colon is finally added.

Example of multi-line statement in psql

I will note differences between the T-SQL and PostgreSQL code structures as I find them, and I will provide each statement in both T-SQL and PostgreSQL flavors, facilitating a clear comparison.

ORDER BY -> LIMIT

SELECT TOP 10 order_id FROM Sales.Orders;      -- T-SQL
SELECT order_id FROM sales.orders LIMIT 10;    -- psql

IDENTITY

CREATE TABLE Sales.Orders (                    -- T-SQL
     order_id INT IDENTITY(1,1)
);
CREATE TABLE sales.orders (                    -- psql
     order_id int generated by default as identity
);

Effectively working with Postgres databases becomes easier once the essential psql commands have been mastered. I’ve provided a overview of some fundamental commands, and in subsequent posts, will describe basic query performance tools, the complexities of indexing, and additional differences between T-SQL and PostgreSQL commands.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.