Add PostGIS layer to QGIS via Python Console

How can I add a PostGIS layer to QGIS via the Python Console?

With some server setups, especially with a large number of tables, it can be very slow to load the list of layers via the Browser or Data Source Manager.

Answer

Here are code snippets to add a layer.

I put table name in a variable since it's used twice and the geometry column because that's what I most often change.

QGIS 3.x

tablename = "thetable"
geometrycol = "geom"

from qgis.core import QgsVectorLayer, QgsDataSourceUri
uri = QgsDataSourceUri()

uri.setConnection("host", "5432", "db", "user", "pass")
uri.setDataSource ("schema", tablename, geometrycol)
vlayer=QgsVectorLayer (uri .uri(False), tablename, "postgres")
QgsProject.instance().addMapLayer(vlayer)

QGIS 2.x

tablename = "tmo_njcov2_60mbin_clean_goodbad_201801"
geometrycol = "geom"

from qgis.core import QgsVectorLayer, QgsDataSourceURI

uri = QgsDataSourceURI()
uri.setConnection("host", "5432", "db", "user", "pass")
uri.setDataSource ("schema", tablename, geometrycol)
vlayer=QgsVectorLayer (uri .uri(False), tablename, "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)