psycopg (v3)

Common errors

• OperationalError: could not connect to server

  cause This error often indicates issues with database connection parameters (host, port, user, password, dbname), network connectivity, or the PostgreSQL server not running or being inaccessible.
  fix Verify that your database credentials and connection string are correct, the PostgreSQL server is running and reachable, and that no firewall is blocking the connection. Example fix: `conn = psycopg.connect(host='localhost', port=5432, dbname='your_db', user='your_user', password='your_password')`.

• OperationalError: fe_sendauth: no password supplied

  cause This error occurs when the PostgreSQL server requires password authentication for the user, but `psycopg` is not provided with a password in the connection string or through a `.pgpass` file.
  fix Provide the correct password in the connection string. Example fix: `conn = psycopg.connect(dbname='your_db', user='your_user', password='your_password', host='localhost')`. Alternatively, ensure your `pg_hba.conf` allows passwordless connections for the specified user and host, or configure a `.pgpass` file.

• ModuleNotFoundError: No module named 'psycopg2'

  cause This error happens when you have installed `psycopg` (version 3) but your code attempts to import the older `psycopg2` library, which is a different package. `psycopg` is not backward compatible with `psycopg2` imports.
  fix If you intend to use `psycopg` (version 3), change your import statements from `import psycopg2` to `import psycopg`. If you genuinely need `psycopg2`, install it separately using `pip install psycopg2-binary`.

• AttributeError: 'Connection' object has no attribute 'mogrify'

  cause In `psycopg` (version 3), the `mogrify` method was moved from the `Connection` object to the `Cursor` object, unlike `psycopg2` where it was available on both.
  fix Access `mogrify` through a cursor object. Example fix: `conn = psycopg.connect(...); cur = conn.cursor(); query = cur.mogrify('INSERT INTO test VALUES (%s)', ('value',));`

• psycopg.InterfaceError: Psycopg cannot use the 'ProactorEventLoop' to run in async mode. Please use a compatible event loop, for instance by setting 'asyncio.set_event_loop_policy(WindowsSelectorEventLoopPolicy())'.

  cause This error occurs when using `psycopg`'s asynchronous features on Windows with the default `ProactorEventLoop`, which is not compatible. `psycopg` requires a different event loop policy for async operations on this platform.
  fix Before running any async code, set the event loop policy to `WindowsSelectorEventLoopPolicy`. Example fix: `import asyncio; import sys; if sys.platform == 'win32': asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy()); asyncio.run(your_async_function())`.

Warnings

• breaking 'with connection' behavior changed from psycopg2. In psycopg2 it manages the transaction. In psycopg v3 it closes the connection. Major silent bug when migrating.
  Fix: Use 'with psycopg.connect(...) as conn:' for connection management. Use 'with conn.transaction():' for transaction blocks.
• breaking cursor_factory moved from cursor() to connect(). psycopg2 pattern: conn.cursor(cursor_factory=DictCursor). psycopg v3: psycopg.connect(..., row_factory=dict_row).
  Fix: Pass row_factory=psycopg.rows.dict_row to connect() not cursor().
• breaking Package name changed. Install: 'psycopg' not 'psycopg3' or 'psycopg2'. Import: 'import psycopg' not 'import psycopg2'. LLMs confuse the package names.
  Fix: pip install psycopg; import psycopg
• gotcha psycopg2 and psycopg cannot be used interchangeably in the same codebase — APIs differ. Cannot drop-in replace 'import psycopg2' with 'import psycopg'.
  Fix: Treat migration as a deliberate refactor. Key differences at psycopg.org/psycopg3/docs/basic/from_pg2.html
• gotcha row_factory=dict_row is imported from psycopg.rows, not from psycopg directly. 'from psycopg import dict_row' fails.
  Fix: from psycopg.rows import dict_row — or use psycopg.rows.dict_row
• gotcha COPY command API changed. psycopg2 had copy_expert(), copy_from(), copy_to(). psycopg v3 has a single copy() method with unified interface.
  Fix: Use conn.copy('COPY table FROM STDIN') context manager pattern.
• breaking psycopg.OperationalError: connection failed: Connection refused indicates the PostgreSQL server is not running, is not listening on the specified host/port, or is inaccessible (e.g., due to firewall rules). This is an infrastructure issue, not a psycopg library usage error.
  Fix: Ensure your PostgreSQL server is running and configured to accept connections on the specified host and port (e.g., 127.0.0.1:5432). Verify network connectivity and firewall settings if connecting to a remote server. Double-check connection parameters in your code.
• breaking Connection refused: psycopg.OperationalError when connecting to PostgreSQL. This error indicates that the database server is not running, is not accessible at the specified host/port, or is not configured to accept incoming connections from the client.
  Fix: Ensure the PostgreSQL server is running. Verify that the server is configured to listen on the correct host and port (default 5432). Check PostgreSQL's pg_hba.conf and postgresql.conf files to ensure connections are allowed from the client's IP address and for the specified user.

Install

• pip install psycopg Python (pure Python — no system deps)
• pip install psycopg[binary] Python (with C extension — faster)
• pip install psycopg[pool] Python (with connection pool)

Imports

• connect (sync)
  wrong:

  import psycopg
  
  conn = psycopg.connect('...')
  # WRONG: 'with conn' closes the connection in v3
  # In psycopg2, 'with conn' only managed the transaction
  with conn:
      pass  # connection is now closed!

  correct:

  import psycopg
  
  # row_factory for dict rows — moved to connect() not cursor()
  with psycopg.connect(
      'postgresql://user:pass@localhost/mydb',
      row_factory=psycopg.rows.dict_row
  ) as conn:
      with conn.cursor() as cur:
          cur.execute('SELECT * FROM users WHERE id = %s', (user_id,))
          row = cur.fetchone()
          print(row['name'])

  'with connection' closes the connection in psycopg v3. In psycopg2 it only managed the transaction. Use 'with psycopg.connect(...) as conn:' pattern.
• AsyncConnection
  wrong:

  # Using psycopg2 in async context — blocks event loop
  import psycopg2
  async def main():
      conn = psycopg2.connect(...)  # blocks

  correct:

  import psycopg
  import asyncio
  
  async def main():
      async with await psycopg.AsyncConnection.connect(
          'postgresql://user:pass@localhost/mydb',
          row_factory=psycopg.rows.dict_row
      ) as conn:
          async with conn.cursor() as cur:
              await cur.execute('SELECT * FROM users')
              rows = await cur.fetchall()
              for row in rows:
                  print(row['name'])
  
  asyncio.run(main())

  psycopg v3 has native async via AsyncConnection. psycopg2 has no real async support. Use psycopg for async PostgreSQL.

Quickstart

Minimal psycopg v3 sync connection with dict row factory.

# pip install psycopg
import psycopg
from psycopg.rows import dict_row

# Sync usage
with psycopg.connect(
    'postgresql://user:pass@localhost/mydb',
    row_factory=dict_row
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            'SELECT id, name FROM users WHERE active = %s',
            (True,)
        )
        for row in cur.fetchall():
            print(row['name'])  # dict access
    conn.commit()