Add support for `postgresql+psycopg` as allowed scheme to use with PostgreDsn

[morian]

Initial Checks

• I have searched Google & GitHub for similar requests and couldn't find anything
• I have read and followed the docs and still think this feature is missing

Description

SQLAlchemy 2.0 (currently in beta) now supports psycopg (a.k.a. psycopg3).

This was introduced as a new dialect by the following commit:
sqlalchemy/sqlalchemy@5eb407f

The connection string appears to be postgresql+psycopg://user:password@host:port/dbname[?key=value&key=value...] but unfortunately it is not recognized by pydantic as a valid scheme by PostgresDsn from networks.py.

Would it be possible to add it as an allowed scheme for PostgresDsn?

By looking at the code and while not familiar with cockroachdb, it seems like it would also benefit from this support:
https://www.cockroachlabs.com/docs/stable/build-a-python-app-with-cockroachdb-psycopg3.html

Thanks!

Affected Components

• Compatibility between releases
• Data validation/parsing
• Data serialization - .dict() and .json()
• JSON Schema
• Dataclasses
• Model Config
• Field Types - adding or changing a particular data type
• Function validation decorator
• Generic Models
• Other Model behaviour - construct(), pickling, private attributes, ORM mode
• Plugins and integration with other tools - mypy, FastAPI, python-devtools, Hypothesis, VS Code, PyCharm, etc.

[samuelcolvin]

PR welcome to add postgresql+psycopg on main and we can cherry-pick it to v1. 10.

[samuelcolvin]

closed via #4690.