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.
Command | Description |
---|---|
\? | Help |
\h | List SQL commands |
\l | List databases |
\d | List objects in current database |
\d+ | List objects with extended information |
\d+ customers | Describe a table |
\dn | List schemas |
\dv | List views |
\du | List users |
\df | List functions |
\dp | List access privileges |
\c adventureworks | Switch 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 0 | Disable pager |
\pset pager 1 | Enable pager |
Ctrl-L | Clear 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.
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.