Database
========

.. only:: html

   .. contents::
      :local:
      :depth: 1

.. _qgisimportintopostgis:

Export to PostgreSQL
--------------------
Exports a vector layer to a PostgreSQL database, creating a new
relation.
If a relation with the same name exists, it can be removed before the
new relation is created.
Prior to this a connection between QGIS and the PostgreSQL database
has to be created (see eg :ref:`vector_create_stored_connection`).

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40
   :class: longtable

   * - Label
     - Name
     - Type
     - Description
   * - **Layer to import**
     - ``INPUT``
     - [vector: any]
     - Vector layer to add to the database
   * - **Database (connection name)**
     - ``DATABASE``
     - [string]
     - Name of the database connection (not the database name).
       Existing connections will be shown in the combobox.
   * - **Schema (schema name)**

       Optional
     - ``SCHEMA``
     - [string]

       Default: 'public'
     - Name of the schema to store the data.
       It can be a new one or already exist.
   * - **Table to import to (leave blank to use layer name)**

       Optional
     - ``TABLENAME``
     - [string]

       Default: ''
     - Defines a table name for the imported vector file.
       If nothing is added, the layer name will be used.
   * - **Primary key field**

       Optional
     - ``PRIMARY_KEY``
     - [tablefield: any]
     - Sets the primary key field from an existing field
       in the vector layer.
       A column with **unique** values can be used as
       Primary key for the database.
   * - **Geometry column**
     - ``GEOMETRY_COLUMN``
     - [string]

       Default: 'geom'
     - Defines the name of the geometry column in the
       new PostGIS table.
       Geometry information for the features is stored
       in this column.
   * - **Encoding**

       Optional
     - ``ENCODING``
     - [string]

       Default: 'UTF-8'
     - Defines the encoding of the output layer
   * - **Overwrite**
     - ``OVERWRITE``
     - [boolean]

       Default: True
     - If the specified table exists, setting this option to
       ``True`` will make sure that it is deleted and a new
       table will be created before the features are added.
       If this option is ``False`` and the table exists, the
       algorithm will throw an exception ("relation already
       exists").
   * - **Create spatial index**
     - ``CREATEINDEX``
     - [boolean]

       Default: True
     - Specifies whether to create a spatial index or not
   * - **Convert field names to lowercase**
     - ``LOWERCASE_NAMES``
     - [boolean]

       Default: True
     - Converts the field names of the input vector layer
       to lowercase
   * - **Drop length constraint on character fields**
     - ``DROP_STRING_LENGTH``
     - [boolean]

       Default: False
     - Should length constraints on character fields be
       dropped or not
   * - **Create single-part geometries instead of multi-part**
     - ``FORCE_SINGLEPART``
     - [boolean]

       Default: False
     - Should the features of the output layer be
       single-part instead of multi-part.
       By default the existing geometries information
       are preserved.

Outputs
.......

The algorithm has no output.

Python code
...........

**Algorithm ID**: ``qgis:importintopostgis``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgisimportintospatialite:

Export to SpatiaLite
--------------------
Exports a vector layer to a SpatiaLite database.
Prior to this a connection between QGIS and the SpatiaLite database
has to be created (see eg :ref:`label_spatialite`).

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40
   :class: longtable

   * - Label
     - Name
     - Type
     - Description
   * - **Layer to import**
     - ``INPUT``
     - [vector: any]
     - Vector layer to add to the database
   * - **File database**
     - ``DATABASE``
     - [vector: any]
     - The SQLite/SpatiaLite database file to connect to
   * - **Table to import to (leave blank to use layer name)**

       Optional
     - ``TABLENAME``
     - [string]

       Default: ''
     - Defines the table name for the imported vector file.
       If nothing is specified, the layer name will be used.
   * - **Primary key field**

       Optional
     - ``PRIMARY_KEY``
     - [tablefield: any]
     - Use a field in the input vector layer as the primary key
   * - **Geometry column**
     - ``GEOMETRY_COLUMN``
     - [string]

       Default: 'geom'
     - Defines the name of the geometry column in the new
       SpatiaLite table.
       Geometry information for the features is stored in this
       column.
   * - **Encoding**

       Optional
     - ``ENCODING``
     - [string]

       Default: 'UTF-8'
     - Defines the encoding of the output layer
   * - **Overwrite**
     - ``OVERWRITE``
     - [boolean]

       Default: True
     - If the specified table exists, setting this option to
       ``True`` will make sure that it is deleted and a new
       table will be created before the features of the layer is
       added.
       If this option is ``False`` and the table exists, the
       algorithm will throw an exception ("table already
       exists").
   * - **Create spatial index**
     - ``CREATEINDEX``
     - [boolean]
       
       Default: True
     - Specifies whether to create a spatial index or not
   * - **Convert field names to lowercase**
     - ``LOWERCASE_NAMES``
     - [boolean]

       Default: True
     - Convert the field names of the input vector layer
       to lowercase
   * - **Drop length constraint on character fields**
     - ``DROP_STRING_LENGTH``
     - [boolean]

       Default: False
     - Should length constraints on character fields be
       dropped or not
   * - **Create single-part geometries instead of multi-part**
     - ``FORCE_SINGLEPART``
     - [boolean]

       Default: False
     - Should the features of the output layer be
       single-part instead of multi-part.
       By default the existing geometries information
       are preserved.

Outputs
.......

The algorithm has no output.

Python code
...........

**Algorithm ID**: ``qgis:importintospatialite``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgispackage:

Package layers
--------------
Adds layers to a GeoPackage.

If the GeoPackage exists and ``Overwrite existing GeoPackage``
is checked, it will be overwritten (removed and recreated).
If the GeoPackage exists and ``Overwrite existing GeoPackage``
is not checked, the layer will be appended.

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **Input layers**
     - ``LAYERS``
     - [vector: any] [list]
     - The (vector) layers to import into the GeoPackage.
       Raster layers are not supported. If a raster layer is
       added, a
       :class:`QgsProcessingException <qgis.core.QgsProcessingException>`
       will be thrown.
   * - **Overwrite existing GeoPackage**
     - ``OVERWRITE``
     - [boolean]

       Default: False
     - If the specified GeoPackage exists, setting this option to
       ``True`` will make sure that it is deleted and a new one
       will be created before the layers are added.
       If set to ``False``, layers will be appended.
   * - **Save layer styles into GeoPackage**
     - ``SAVE_STYLES``
     - [boolean]

       Default: True
     - Save the layer styles
   * - **Save only selected features**
     - ``SELECTED_FEATURES_ONLY``
     - [boolean]

       Default: False
     - If a layer has a selection, setting this option to ``True``
       will result in only selected features being saved. For
       layers without a selection all features will be saved.
   * - **Export related layers following relations defined in the project**

       |328|
     - ``EXPORT_RELATED_LAYERS``
     - [boolean]

       Default: False
     - If an input layer has :ref:`relations <vector_relations>` set in the project,
       setting this option to ``True`` will result in exporting also its related layer(s).
       If the layer has features selected, then only their related features will be exported
       unless the related layer was also an input layer.
   * - **Destination GeoPackage**
     - ``OUTPUT``
     - [file]

       Default: ``[Save to temporary file]``
     - Specify where to store the GeoPackage file. One of

       .. include:: ../algs_include.rst
          :start-after: **file_output_types**
          :end-before: **end_file_output_types**

Outputs
.......

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **Layers within new package**
     - ``OUTPUT_LAYERS``
     - [string] [list]
     - The list of layers added to the GeoPackage.

Python code
...........

**Algorithm ID**: ``native:package``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgispostgisexecuteandloadsql:

PostgreSQL execute and load SQL
-------------------------------

Allows a SQL database query to be performed on a PostgreSQL database
connected to QGIS and loads the result.
The algorithm **won't** create a new layer: it is designed to run
queries on the layer itself.

.. _qgis_postgis_execute_sql_example:

.. **postgisexecutesqlexample**

.. The following section is included in database algorithms such as
 qgispostgisexecutesql, qgispostgisexecuteandloadsql

**Example**

#. Set all the values of an existing field to a fixed value. The SQL query string
   will be:

   .. code-block:: sql

    UPDATE your_table SET field_to_update=20;

   In the example above, the values of the field ``field_to_update`` of the table
   ``your_table`` will be all set to ``20``.

#. Create a new ``area`` column and calculate the area of each feature with the
   ``ST_AREA`` PostGIS function.

   .. code-block:: sql

    -- Create the new column "area" on the table your_table"
    ALTER TABLE your_table ADD COLUMN area double precision;
    -- Update the "area" column and calculate the area of each feature:
    UPDATE your_table SET area=ST_AREA(geom);

.. **end_postgisexecutesqlexample**

.. seealso:: :ref:`qgispostgisexecutesql`, :ref:`qgisexecutesql`,
 :ref:`qgisspatialiteexecutesql`

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **Database (connection name)**
     - ``DATABASE``
     - [string]
     - The database connection (not the database name).
       Existing connections will be shown in the combobox.
   * - **SQL query**
     - ``SQL``
     - [string]
     - Defines the SQL query, for example
       ``'UPDATE my_table SET field=10'``.
   * - **Unique ID field name**
     - ``ID_FIELD``
     - [string]

       Default: id
     - Sets the primary key field (a column in the result table)
   * - **Geometry field name**

       Optional
     - ``GEOMETRY_FIELD``
     - [string]

       Default: 'geom'
     - Name of the geometry column (a column in the result table)

Outputs
.......

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **SQL layer**
     - ``OUTPUT``
     - [vector: any]
     - The resulting vector layer to be loaded into QGIS.

Python code
...........

**Algorithm ID**: ``qgis:postgisexecuteandloadsql``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgispostgisexecutesql:

PostgreSQL execute SQL
----------------------

Allows a SQL database query to be performed on a PostgreSQL database
connected to QGIS.
The algorithm **won't** create a new layer: it is designed to run
queries on the layer itself.

.. include:: ./database.rst
   :start-after: .. **postgisexecutesqlexample**
   :end-before: .. **end_postgisexecutesqlexample**

.. seealso:: :ref:`qgispostgisexecuteandloadsql`,
   :ref:`qgisexecutesql`, :ref:`qgisspatialiteexecutesql`

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **Database (connection name)**
     - ``DATABASE``
     - [string]
     - The database connection (not the database name).
       Existing connections will be shown in the combobox.
   * - **SQL query**
     - ``SQL``
     - [string]
     - Defines the SQL query, for example
       ``'UPDATE my_table SET field=10'``.

Outputs
.......

No output is created.
The SQL query is executed in place.

Python code
...........

**Algorithm ID**: ``native:postgisexecutesql``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgisspatialiteexecutesql:

SpatiaLite execute SQL
----------------------

Allows a SQL database query to be performed on a SpatiaLite database.
The algorithm **won't** create a new layer: it is designed to run
queries on the layer itself.

.. seealso:: :ref:`qgispostgisexecutesql`, :ref:`qgisexecutesql`

 For some SQL query examples see :ref:`PostGIS SQL Query Examples
 <qgis_postgis_execute_sql_example>`.

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description
   * - **File Database**
     - ``DATABASE``
     - [vector]
     - The SQLite/SpatiaLite database file to connect to
   * - **SQL query**
     - ``SQL``
     - [string]

       Default: ''
     - Defines the SQL query, for example
       ``'UPDATE my_table SET field=10'``.

Outputs
.......

No output is created.
The SQL query is executed in place.

Python code
...........

**Algorithm ID**: ``native:spatialiteexecutesql``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. _qgisspatialiteexecutesqlregistered:

SpatiaLite execute SQL (registered DB)
--------------------------------------

Allows a SQL database query to be performed on a SpatiaLite database
connected to QGIS.
The algorithm **won't** create a new layer: it is designed to run
queries on the layer itself.

.. seealso:: :ref:`qgispostgisexecutesql`, :ref:`qgisexecutesql`

 For some SQL query examples see :ref:`PostGIS SQL Query Examples
 <qgis_postgis_execute_sql_example>`.

Parameters
..........

.. list-table::
   :header-rows: 1
   :widths: 20 20 20 40

   * - Label
     - Name
     - Type
     - Description

   * - **Database**
     - ``DATABASE``
     - [enumeration]
       
       Default: not set
     - Select a SQLite/SpatiaLite database connected to the current session
   * - **SQL query**
     - ``SQL``
     - [string]

       Default: ''
     - Defines the SQL query, for example
       ``'UPDATE my_table SET field=10'``.

Outputs
.......

No output is created.
The SQL query is executed in place.

Python code
...........

**Algorithm ID**: ``native:spatialiteexecutesqlregistered``

.. include:: ../algs_include.rst
  :start-after: **algorithm_code_section**
  :end-before: **end_algorithm_code_section**


.. Substitutions definitions - AVOID EDITING PAST THIS LINE
   This will be automatically updated by the find_set_subst.py script.
   If you need to create a new substitution manually,
   please add it also to the substitutions.txt file in the
   source folder.

.. |328| replace:: ``NEW in 3.28``