psql وعميل بواجهة رسومية

Written Explanation

The connection command

A psql connection needs the same five facts you met in Lesson 1. The command-line options below are documented in the psql manual:

psql -h localhost -p 5432 -U postgres -d postgres

Password is not an option here: if the server wants one, psql prompts for it and does not echo what you type. That is the safe way to supply it.

SQL versus meta-commands

Inside psql you type two different kinds of line.

• SQL statements end with a semicolon and are sent to the server: SELECT version();.
• Meta-commands start with a backslash, are understood by psql itself, and need no semicolon: \dt.

If you forget the semicolon on SQL, psql waits with a continuation prompt such as postgres-#. It is not frozen; it is waiting for the end of the statement.

The meta-commands you need now (all described in the manual):

On an empty database \dt says Did not find any tables. Once the Library tables exist, a table listing looks like this (from the test server):

             List of tables
 Schema |  Name   | Type  |    Owner
--------+---------+-------+-------------
 public | authors | table | library_app
 public | books   | table | library_app
 public | loans   | table | library_app
 public | members | table | library_app
(4 rows)

You will learn in Lesson 3 what public and library_app in that table mean. For now, read it as: four tables, each in the shelf called public, each owned by one user.

Commands and scripts from outside

You do not have to open an interactive session. Two options run something and return to the terminal:

• -c "SQL or one meta-command" runs one command string and exits.
• -f script.sql reads all commands from a file. The manual notes that with -f, error messages include the file name and line number, which makes a failing script much easier to debug.

psql -U postgres -d library -c "\dt"
psql -U postgres -d library -f schema.sql

A script file is simply the SQL you would have typed, saved as text. Running the same file twice should give the same result: this idea is at the heart of the Lab.

By default psql carries on after an error in a script. The variable ON_ERROR_STOP changes that; setting it on the command line (-v ON_ERROR_STOP=1) stops at the first error. The manual describes this variable only briefly, so test it on a small script before relying on it.

A graphical client

pgAdmin and DBeaver both show a tree of servers, databases, schemas, and tables, let you open a table, and offer a window to type SQL. According to the PostgreSQL Windows download page, the standard Windows installer bundles pgAdmin. DBeaver Community is a separate free, open-source tool that supports PostgreSQL and many other databases.

In either tool you register or create a connection and fill in the same five facts: host (localhost), port (5432 unless you changed it), database, user, and password. Menu names and screens differ between versions and operating systems, so look for "new server" or "new connection" rather than following exact clicks. Some tools offer to save the password; on a shared computer, decline.

After connecting, expand the tree: server, then database, then schemas, then public, then tables. That is the same list \dt printed.

When to use which

A common trap: building tables by clicking in a GUI. The result is correct on your machine, but nobody can reproduce it. Use the GUI to look, and write what matters as SQL in a file.