Connecting to PostgreSQL

2. Connecting to the PostgreSQL server

You’ve got two options here:

1. Use the built-in feature from your IDE
2. Use the Database Navigator plugin for Community editions

Connecting to PostgreSQL via WebStorm

The connection process is pretty much the same for other IDEs too.

1. Open the "Database" panel. If you don’t see it, open it from the menu View → Tool Windows → Database.
2. On the Database panel, click + and in the dropdown menu pick Data Source → PostgreSQL.
3. Set up your connection parameters:
   • If this is your first time connecting to PostgreSQL, the IDE will prompt you to download the required driver.
   • Name: give your connection a name.
   • Host: server address (usually localhost for a local install).
   • Port: default port is 5432.
   • User: username (for example, postgres).
   • Password: your user’s password. You can choose to save the password if you want.
4. Hit the Test Connection button. If it works, you’ll see a green checkmark and the PostgreSQL version.
5. If the test was successful, click Apply and then OK.

After saving the connection, you might see a notification from the IDE suggesting you set up the SQL dialect for your project or files. Click the configure link in that notification. You can always get back to these settings later via: Settings → Languages & Frameworks → SQL Dialects

In the SQL Dialects window that pops up, pick PostgreSQL. These settings are important for proper syntax highlighting, autocomplete, and SQL code analysis.

Your new connection will show up in the Database panel. You can expand it to see the list of databases, schemas, and tables (we only have public for now). Now you can open the SQL console to run queries or double-click tables to view their data.

Once you’re connected, you can run a test command to make sure everything’s working.

 -- Check the version of the PostgreSQL server you’re connected to
SELECT version();

You can read more about all the settings in the JetBrains docs.

Connecting to PostgreSQL via Database Navigator

1. Open the plugin panel: On the IDE sidebar, find and open the DB Browser tab. If it’s not there, you can open it from the menu Main menu → DB Navigator.
2. Create a new connection. On the DB Browser panel, click the plus icon (+) and in the dropdown pick PostgreSQL.
3. Fill in the connection details:
   • Name: come up with a name for this connection (postgres@localhost).
   • Host: if PostgreSQL is installed on your computer, use localhost.
   • Port: default port for PostgreSQL is 5432.
   • User: username for DB access (often postgres by default).
   • Password: password for this user.
4. After entering the details, hit the Test Connection button. You should see a message about a successful connection.
5. If the test was successful, click Apply and then OK to save the settings.

Result:

After saving, your new connection will show up in the list on the DB Browser panel. Now you can expand it to see the database structure, available schemas, tables, and their columns. You’re ready to run your first SQL queries.

Once you’re connected, you can run a test command.

 -- Check the version of the PostgreSQL server you’re connected to
SELECT version();

3. Writing simple queries in the Query Console

You can write queries even without a database. For example:

• SELECT version();
• SELECT 2+2*2
• SELECT NOW()
• SELECT LENGTH('MAY')

4. Creating a database

Run the following SQL query:

CREATE DATABASE university;

You’ll see a confirmation CREATE DATABASE if the database was created successfully.
This short message might seem a bit dry, but trust me, it’s music to a programmer’s ears.

Now we’ve got a database called university.

2

Task

SQL SELF, level 4, lesson 3

Locked

Get the current date and time

Get the current date and time

Open