5.27.24. Postgres Writer

<< Click to Display Table of Contents >>

Navigation:  5. Detailed description of the Actions > 5.27. Output Actions >

5.27.24. Postgres Writer

 

Icon:   clip1220

 

Function: upsertPostgre

 

Property window:

 

clip1222

 

Short description:

 

Upload a table into PostgreSQL.

 

Long Description:

 

The parameter P7 is the operation to perform (you can choose between insert, update, delete or upsert). Each operation can be performed using several different techniques (for example, to do some “INSERTs”, there are 4 different techniques available). Here are the pros & cons of each technique:
 

clip1223

 

*1 : The speed of the insert, update, delete and upsert operations inside PostgreSQL is usually around from 10 to 20 times slower than MS-SQLServer.

*2 : If the parameter P11 is not set to “auto-commit” (i.e. all operations happens inside a user-defined SQL transaction), then you can ignore the content of this column because the status is simply NOT reported at all.

*3 : The transfer is performed using a 8-byte IEEE754 binary representation of the value that guarantees no rounding errors.

*4 : All PostegreSQL column’s types are supported as long as they have a textual representation (which is almost always the case).

*5 : The number of rows inside one block is defined by the parameter P10.

*6 : In this mode, the parameter P10 (i.e. the number of rows inside the buffer to send “in one go” to PostGresSQL) must be set to one.

*7 : The supported column’s types are: Float, Double, NVarChar, VarChar, Char, Int2, Int4, Int8, Date, DateTime.

*8 : Currently in development.

 

The parameter P4 has two meanings: It can either be the “DB Name” or it can be a PostGreSQL connection string as documented here:

 

https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNSTRING

 

When you use a connection string to connect to PostGre you can set several additional optional parameters (this advanced functionnality is not directly accessible when using the “easy” parameters P1 to P5 to setup the connection). Here is an example of connection to PostGreSQL using a connection string:

 

clip1221

 

The syntax is “postgresql://<login>:<password>@<server_ip>/<database>?<option_name>=<option_value>”.