Problem
Building from source on M2 mac doesn't work without linking the libpq libraries manually.
Improvement
Add the following suggested CMAKE flags when using brew to install libpq and postgresql
mkdir build
cd build
cmake .. -DPOSTGRESQL_INCLUDE_DIR:PATH=/opt/homebrew/opt/libpq/include -DPOSTGRESQL_LIBRARIES=/opt/homebrew/opt/libpq/lib/libpq.dylib
This might help people as finding the locations for the libraries took a bit of time