PostgreSQL and DBeaver
An Easy Guide on How to Connect
What is DBeaver: DBeaver overview
DBeaver is a free, open source multiplatform database management tool and SQL client for developers and database administrators. DBeaver can be used to access any database or cloud application that has an ODBC or JDBC driver, such as PostgreSQL, SQL Server, MySQl, Salesforce, or Mailchimp. Devart DBeaver provides you with the most important features you'd need when working with a database in a GUI tool, such as:
Before using this tutorial you need to download ODBC driver for PostgreSQL (30 Days Free Trial). Driver is compatible with 32-bit and 64-bit Windows, macOS, and Linux.
Creating an ODBC Data Source to Use PostgreSQL Data in DBeaver
The correct connection parameters, such as the hostname assigned to your computer, the username, and the password associated with your PostgreSQL account, must be used in the client software in order to connect to the PostgreSQL server. There is a default value for each connection parameter, but you can alter those according to your needs using program options provided either from the command line or in an option file.
- Click the Start menu and select Control Panel.
- Select Administrative Tools, then click ODBC Data Sources.
- Click on the System DSN tab if you want to set up a DSN name for all users of the system or select User DSN to configure DSN only for your account.
- Click the Add button and double-click Devart ODBC Driver for PostgreSQL in the list.
- Give a name to your data source and set up the connection parameters.
- Click the Test Connection button to verify that you have properly configured the DSN.
When using ODBC driver for PostgreSQL with DBeaver, SQL_WVARCHAR data types may be displayed incorrectly in DBeaver. To prevent this, you need to set the string data types to Ansi either in the Advanced Settings tab of the driver configuration dialog or directly in the connection string (String Types=Ansi) — all string types will be returned as SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR.
Connecting to PostgreSQL Data from DBeaver via ODBC Driver for PostgreSQL
Follow the steps below to establish a connection to PostgreSQL in DBeaver.
- In the Database menu, select New Database Connection.
- In the Connect to database wizard, select ODBC and click Next.
- Enter the previously configured DSN in the Database/Schema field.
- Click Test Connection. If everything goes well, you'll see the Success message.
Viewing PostgreSQL Database Objects and Querying Data
You can expand out the database structure in DBeaver's Database Navigator to visualize all the tables in PostgreSQL database. To view and edit the data in a table, you need to right-click on the target table name and select View data.The content of the table will be displayed in the main workspace.
If you want to write a custom SQL query that will include only the necessary columns from the table, you can select New SQL Editor in the SQL Editor main menu. Create your query and run it by clicking Execute SQL Statement to view the results in the same window.