Connecting to Postgres

psql postgresql://myuser:mypassword@localhost:5432/mydb

psql -h localhost -p 5432 -U myuser -d mydb

mydb=>

mydb=> \conninfo
You are connected to database "mydb" as user "myuser" via socket in "/tmp" at port "5432".

Snowflake Postgres works with standard psql. Use your existing client with the connection string for your instance, in the form psql "postgresql://user:pass@<your-instance-host>:5432/dbname?sslmode=require". The meta-commands and settings in this guide work the same way there.

Meta-Commands and Query Execution

Exploring Your Database

mydb=> \dt
              List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+---------
 public | customers     | table | myuser
 public | orders        | table | myuser
 public | products      | table | myuser
(3 rows)

mydb=> \d customers
                                   Table "public.customers"
   Column   |          Type          | Collation | Nullable |                Default
------------+------------------------+-----------+----------+---------------------------------------
 id         | integer                |           | not null | nextval('customers_id_seq'::regclass)
 name       | character varying(100) |           | not null |
 email      | character varying(255) |           |          |
 created_at | timestamp with time zone |         | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

Running Queries

mydb=> SELECT name, email FROM customers LIMIT 3;
    name     |        email
-------------+---------------------
 Alice Chen  | alice@example.com
 Bob Smith   | bob@example.com
 Carol Jones | carol@example.com
(3 rows)

mydb=> SELECT name,
mydb->        count(*) AS order_count
mydb-> FROM customers c
mydb->   JOIN orders o ON c.id = o.customer_id
mydb-> GROUP BY name
mydb-> ORDER BY order_count DESC;

Expanded Display

mydb=> \x
Expanded display is on.
mydb=> SELECT * FROM customers WHERE id = 1;
-[ RECORD 1 ]----------------------------
id         | 1
name       | Alice Chen
email      | alice@example.com
created_at | 2024-11-15 09:23:14.521+00

Timing and Performance

mydb=> \timing
Timing is on.
mydb=> SELECT count(*) FROM orders;
 count
--------
 284710
(1 row)

Time: 42.138 ms

Getting Help

mydb=> \h CREATE INDEX
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON ...

Customizing Your psql Environment

Making NULLs Visible

mydb=> \pset null '(null)'
Null display is "(null)".
mydb=> SELECT name, phone FROM customers WHERE id IN (1, 2);
    name     |    phone
-------------+-------------
 Alice Chen  | 555-0142
 Bob Smith   | (null)
(2 rows)

Pretty Output with Unicode Borders

mydb=> \pset linestyle unicode
Line style is unicode.
mydb=> \pset border 2
Border style is 2.
mydb=> SELECT name, email FROM customers LIMIT 2;
┌─────────────┬─────────────────────┐
│    name     │        email        │
├─────────────┼─────────────────────┤
│ Alice Chen  │ alice@example.com   │
│ Bob Smith   │ bob@example.com     │
└─────────────┴─────────────────────┘

The .psqlrc File

-- Quiet the confirmation messages while these settings load
\set QUIET 1

-- Make NULLs visible
\pset null '(null)'

-- Unicode borders
\pset linestyle unicode
\pset border 2

-- Show timing on every query
\timing

-- Use expanded mode when needed
\x auto

-- Custom prompt showing database and user
\set PROMPT1 '%n@%/%R%# '

-- Turn off the pager entirely (the default only pages output too long for the screen)
\pset pager off

\set QUIET 0

Seeing the SQL Behind Meta-Commands

mydb=> \set ECHO_HIDDEN on
mydb=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' ...
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
...
**************************

Saved Queries

-- Add to .psqlrc
\set active_queries 'SELECT pid, state, query FROM pg_stat_activity WHERE state != \'idle\';'

mydb=> :active_queries
 pid  | state  |                query
------+--------+-------------------------------------
 1234 | active | SELECT * FROM orders WHERE ...
(1 row)

History Search and Tab Completion

Importing and Exporting Data

Running SQL Files

mydb=> \i /path/to/schema.sql
CREATE TABLE
CREATE TABLE
CREATE INDEX

Sending Output to a File

mydb=> \o /tmp/report.txt
mydb=> SELECT * FROM orders WHERE total > 1000;
mydb=> \o

Using an External Editor

COPY for Bulk Data

-- Export to CSV
mydb=> \copy orders TO '/tmp/orders.csv' WITH CSV HEADER;
COPY 284710

-- Import from CSV
mydb=> \copy staging_orders FROM '/tmp/new_orders.csv' WITH CSV HEADER;
COPY 500

Conclusion

Related Resources

---

---

---