Skip to main content

​
PostgreSQL Connections

TablePro provides full support for PostgreSQL 12 and later versions. PostgreSQL is known for its advanced features, standards compliance, and extensibility.

​
Quick Setup

1

Open Connection Form

Click New Connection from the Welcome screen or File > New Connection
2

Select PostgreSQL

Choose PostgreSQL from the database type selector
3

Enter Connection Details

Fill in host, port, username, password, and database name
4

Test and Connect

Click Test Connection, then Create

​
Connection Settings

​
Required Fields

FieldDescriptionDefault
NameConnection identifier-
HostServer hostname or IPlocalhost
PortPostgreSQL server port5432
UsernamePostgreSQL userpostgres
PasswordUser password-
DatabaseDatabase name to connect to-
Unlike MySQL, PostgreSQL requires specifying a database name to connect.

​
Example Configurations

​
Local Development Server

Name:     Local PostgreSQL
Host:     localhost
Port:     5432
Username: postgres
Password: (your password)
Database: postgres

​
Docker PostgreSQL Container

Name:     Docker Postgres
Host:     localhost
Port:     5432 (or your mapped port)
Username: postgres
Password: (password from POSTGRES_PASSWORD)
Database: postgres

​
Homebrew PostgreSQL

Name:     Homebrew Postgres
Host:     localhost
Port:     5432
Username: (your macOS username)
Password: (empty for local trust auth)
Database: postgres
Homebrew PostgreSQL often uses “trust” authentication for local connections, meaning no password is required.

​
Remote Server

Name:     Production DB
Host:     db.example.com
Port:     5432
Username: app_user
Password: (secure password)
Database: myapp_production

​
Cloud Databases

​
Amazon RDS

Name:     AWS RDS Postgres
Host:     mydb.abc123.us-east-1.rds.amazonaws.com
Port:     5432
Username: postgres
Password: (your RDS password)
Database: postgres

​
Heroku Postgres

Name:     Heroku Postgres
Host:     ec2-xx-xxx-xxx-xxx.compute-1.amazonaws.com
Port:     5432
Username: (from DATABASE_URL)
Password: (from DATABASE_URL)
Database: (from DATABASE_URL)
Parse your Heroku DATABASE_URL to extract host, port, username, password, and database name.

​
Features

​
Schema Support

PostgreSQL organizes tables into schemas. TablePro displays:
  1. All schemas accessible to your user
  2. Tables within each schema
  3. The search path order
Schema browser

​
Table Information

For each table, TablePro shows:
  • Structure: Columns with PostgreSQL-specific types
  • Indexes: Including partial and expression indexes
  • Constraints: Primary keys, foreign keys, check constraints
  • DDL: The CREATE TABLE statement

​
Query Execution

Execute queries with full PostgreSQL syntax support: 
-- JSONB queries
SELECT data->>'name' as name, data->'address'->>'city' as city
FROM customers
WHERE data @> '{"active": true}'::jsonb;

-- Array operations
SELECT name, tags
FROM products
WHERE 'electronics' = ANY(tags);

-- Window functions
SELECT
    department,
    employee,
    salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) as salary_change
FROM employees;

-- CTEs with RETURNING
WITH inserted AS (
    INSERT INTO orders (customer_id, total)
    VALUES (1, 99.99)
    RETURNING *
)
SELECT * FROM inserted;

-- Full-text search
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

​
PostgreSQL-Specific Types

TablePro properly displays PostgreSQL’s rich type system:
TypeDisplay
jsonbFormatted JSON
arrayArray notation
uuidUUID string
inetIP address
timestamp with time zoneLocalized timestamp
intervalHuman-readable duration
byteaBinary data indicator

​
Troubleshooting

​
Connection Refused

Symptoms: “Connection refused” or “could not connect to server” Causes and Solutions:
  1. PostgreSQL server not running 
    # Check if PostgreSQL is running
pg_isready -h localhost -p 5432

# Start PostgreSQL (macOS with Homebrew)
brew services start postgresql@16
  2. PostgreSQL not accepting connections
    • Check listen_addresses in postgresql.conf
    • For remote connections, set listen_addresses = '*'
  3. Firewall blocking connections
    • Check macOS firewall settings
    • Verify port 5432 is accessible

​
Authentication Failed

Symptoms: “password authentication failed” or “Peer authentication failed” Understanding pg_hba.conf: PostgreSQL uses pg_hba.conf to control authentication. Common entries: 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
Solutions:
  1. For password auth: Ensure md5 or scram-sha-256 is set
  2. For local development: Use trust (no password required)
  3. Reset password if forgotten: 
    ALTER USER postgres WITH PASSWORD 'newpassword';

​
Database Does Not Exist

Symptoms: “database ‘xyz’ does not exist” Solution: Connect to an existing database first (usually postgres), then create your database: 
CREATE DATABASE myapp;

​
Permission Denied

Symptoms: “permission denied for table” or “permission denied for schema” Solutions:
  1. Check current permissions: 
    SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'username';
  2. Grant necessary permissions: 
    GRANT ALL ON DATABASE mydb TO username;
GRANT ALL ON SCHEMA public TO username;
GRANT ALL ON ALL TABLES IN SCHEMA public TO username;

​
SSL/TLS Connections

TablePro supports SSL/TLS encryption for PostgreSQL connections. Configure SSL in the SSL/TLS section of the connection form.
SSL ModePostgreSQL sslmodeDescription
DisableddisableNo SSL encryption
PreferredpreferUse SSL if available, fall back to unencrypted
RequiredrequireRequire SSL, but don’t verify certificates
Verify CAverify-caRequire SSL and verify the server certificate against a CA
Verify Identityverify-fullRequire SSL, verify CA, and verify the server hostname
For Verify CA and Verify Identity modes, provide the path to your CA certificate (root certificate). You can also provide optional client certificate and key files for mutual TLS authentication.
Cloud providers like AWS RDS, Heroku, and Supabase typically require SSL. Use Required or Verify CA mode depending on your security needs.
If you prefer not to configure SSL certificates, SSH tunneling is an alternative that encrypts all traffic through an SSH tunnel.

​
PostgreSQL Extensions

TablePro works with databases using popular extensions:
ExtensionSupport
postgisDisplays geometry/geography types
pg_trgmFull query support
uuid-osspUUID display
hstoreKey-value display
ltreeTree path display
To see installed extensions: 
SELECT * FROM pg_extension;

​
Performance Tips

​
Query Analysis

Use EXPLAIN ANALYZE to understand query performance: 
EXPLAIN ANALYZE
SELECT * FROM users WHERE email LIKE '%@example.com';

​
Connection Limits

PostgreSQL has a default connection limit. Monitor connections: 
SELECT count(*) FROM pg_stat_activity;

​
Large Tables

For tables with millions of rows:
  1. Always use LIMIT in exploratory queries
  2. Create appropriate indexes
  3. Use pagination in the data grid

​
Differences from MySQL

If you’re coming from MySQL:
MySQLPostgreSQL
SHOW DATABASES\l or query pg_database
SHOW TABLES\dt or query information_schema.tables
DESCRIBE table\d table or query information_schema.columns
AUTO_INCREMENTSERIAL or GENERATED AS IDENTITY
LIMIT x, yLIMIT y OFFSET x
Backtick quotesDouble quotes for identifiers

​
Next Steps

SSH Tunneling

Connect securely to remote PostgreSQL servers

SQL Editor

Master the SQL editor features

Import/Export

Import and export PostgreSQL data

Table Structure

View and modify table structures