Contents x
    Data Destination - PostgreSQL
    • 06 Jan 2023
    • Print
    • Dark
      Light
    • PDF
    Contents

    Data Destination - PostgreSQL

    • Updated on 06 Jan 2023
    • Print
    • Dark
      Light
    • PDF
    Article Summary

    Note

    This is a machine-translated version of the original Japanese article.
    Please understand that some of the information contained on this page may be inaccurate.

    summary

    This page is a help page for setting up data transfer to PostgreSQL.

    Supported Protocols

    constraint

    • Nothing in particular

    Setting items

    STEP1 Basic settings

    Itemnamerequireddefault
    		valuecontent
    PostgreSQL connection informationYes-From the connection information registered in advance, select the one that has the necessary permissions for this transfer setting.
    databaseYes-Specifies the name of a database that exists on the destination PostgreSQL.
    schemaYespublicSpecifies the schema name that exists in the destination database.
    It is also possible to load the list from Load schema list.
    tableYes-Specifies the destination table name.
    If the entered table name does not exist, a new table is created during the transfer.
    Transfer ModeYes-Select the transfer mode. For details, refer to Transfer Modes.
    Default time zoneYesUTCtimestampSpecifies which time zone is used for columns of type.

    STEP2 Advanced settings

    Itemnamerequireddefault
    		valueDetails
    Maximum number of retriesYes12Specifies the maximum number of retries to perform.
    Retry latency (ms)Yes1000Specifies the amount of time to wait when retrying.
    Maximum retry wait time (ms)Yes1800000Specifies the maximum amount of time to wait during retry.
    Setting the Merge Key--You can specify when you select UPSERT (MERGE) in the transfer mode.
    Enter the column name to be treated as the merge key (primary key).
    The merge key should be selected from columns with no duplicate values and no NULL values.
    For details on the merge key, refer to the transfer mode "UPSERT (MERGE)".
    SQL to execute before loadingNo-Describes the query that is executed before the transfer.
    SQL to execute after loadNo-Write a query that will be executed after the transfer.

    Transfer Mode

    ModeDetails
    Append (INSERT)Make an append to the table.
    First, a temporary table is created and populated with data, and then INSERT from the temporary table to the destination table.
    Therefore, if the transfer fails in the middle, halfway data will not remain in the destination table.
    Also, if the destination table does not exist, it automatically creates the table.
    APPEND (INSERT DIRECT)Make an append to the table.
    No temporary tables are created, but data is directly input to the destination table.
    Therefore, if the transfer fails in the middle, the data may remain in a half-finished state.
    Also, if the destination table does not exist, it automatically creates the table.
    TRUNCATE INSERTWash the table.
    First, a temporary table is created and populated with data, and then INSERT from the temporary table to the destination table.
    In addition, since truncate is performed immediately before INSERT, it will be washed.
    The index information of the destination table is maintained.
    REPLACEWash the table.
    First, generate a temporary table and populate it, and if this is successful, delete the destination table and rename the temporary table.
    Index information in the destination table will be lost.
    UPSERT (MERGE)UPSERT the table.
    First, generate a temporary table and populate it with all the data.
    Then, in the destination table, update the records that have merge key values in the temporary table, and then insert records that do not have merge key values. Note that if the merge key value contains NULL, it will not work properly.
    Was this article helpful?
    What's Next