When working with PostgreSQL through SSH, many developers struggle with executing complex, multiline SQL statements - especially those coming from GUI tools like Microsoft Management Studio. The console interface presents unique challenges for writing CREATE TABLE statements, complex joins, or procedural code blocks.

The psql client supports multiline input naturally. After connecting via SSH:

psql -U username -d database

Then enter your multiline query terminated with semicolon:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

For scripted environments, use a here document:

psql -U username -d database << EOF
CREATE TEMPORARY TABLE temp_data (
    id INTEGER,
    value NUMERIC(10,2)
);
INSERT INTO temp_data VALUES (1, 23.45);
SELECT * FROM temp_data;
EOF

Edit queries locally in your favorite editor, then execute via SSH:

cat query.sql | ssh user@server "psql -U username -d database"

For stored procedures or transactions, use the -v ON_ERROR_STOP=1 flag to ensure proper error handling:

psql -v ON_ERROR_STOP=1 -U username -d database << SQL
BEGIN;
INSERT INTO accounts (balance) VALUES (1000);
UPDATE users SET last_deposit = NOW() WHERE id = 1;
COMMIT;
SQL

For those needing a GUI occasionally:

ssh -L 5432:localhost:5432 user@postgres-server

Then connect pgAdmin to localhost:5432.

When working with PostgreSQL through an SSH connection, executing multi-line SQL statements like CREATE TABLE or complex SELECT queries can be frustrating. Unlike GUI tools, the command-line psql interface requires special handling for multi-line statements.

The most straightforward approach is to use psql's built-in interactive mode. After connecting via SSH, simply type:

psql -U username -d database_name

Then enter your multi-line query normally. The prompt will change to =# to indicate continuation. Example:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10,2)
);

Press Enter twice to execute.

For scripting or one-off execution, use a here document directly in your SSH session:

psql -U username -d database_name << EOF
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(255) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget NUMERIC(15,2)
);

INSERT INTO projects (project_name, start_date, budget)
VALUES ('Database Migration', '2023-06-01', 50000.00);
EOF

For complex queries, create a local SQL file and transfer it to the server:

# On local machine
cat > query.sql << 'EOSQL'
CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMERIC, performance NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * performance * 0.1;
END;
$$ LANGUAGE plpgsql;
EOSQL

# Transfer and execute
scp query.sql user@remote:/tmp/
ssh user@remote "psql -U username -d database_name -f /tmp/query.sql"

1. Use \\e command in psql to open your default editor (vim/nano)
2. For repeating queries, use \\i filename.sql
3. Enable timing with \\timing on
4. Use \\watch [sec] for periodic execution

If you get "statement is too large" errors, try these solutions:
- Increase max_stack_depth in postgresql.conf
- Break into smaller transactions using BEGIN; ... COMMIT;
- Use prepared statements for parameterized queries