Loader Postgres
PostgreSQL, often referred to as Postgres, is an object-relational database management system.
Postgres uses and extends the SQL language combined with many other features that safely store and scales the most complicated data workloads.
The native data types that are supported include:
Primitives: Integer, Numeric, String, Boolean
Structured: Date/Time, Array, Range, UUID
Document: JSON/JSONB, XML, Key-value (Hstore)
Geometry: Point, Line, Circle, Polygon
Customizations: Composite, Custom Types
Learn more: about Postgres from PostgreSQL tutorials.
The Postgres loader is used to load data into a table in the PostgreSQL database.
Requirements
Credentials of the database you would like to connect to:
- Host
- Port
- Name of the database, schema
- Username
- Password
If you wish to connect to a database outside of the Meiro Integrations environment, you may need a front-end tool like PgAdmin4, Postico, DBeaver or any other front-end (GUI) tool available online. To learn more about these tools, please refer to their respective documentation online. Connecting via a GUI tool is not mandatory, but it may be useful if you want to access the database outside the Meiro Integrations environment.
Postgres components work only with CSV tables. Other data formats need to be converted to CSV format by processors.
Learn more: PostgreSQL documentation
Features
- One file and multi-file tables,
- Header and no header,
- Custom delimiter, escape and quotation characters,
- Automatic creation of the destination table (all data types as a text for columns) and manual override if the user wants to specify the data type for a column.
Data In/Data Out
| Data In |
Data from the previous component should be saved as a CSV table in the folder
File structure: ID,CAR,COST,DATE_OF_SALE 1,BWM,400000,2019-09-10 2,Volvo,3000,2019-01-10
Or without header 1,BWM,400000,2019-09-10 2,Volvo,3000,2019-01-10 Warning:
|
| Data Out | N/A |
Learn more: about the folder structure here.
Parameters
Table
| DB Table Name (required) | Table’s name in the database. |
| Auto Create(true/ false) | If true, auto-creates columns by headers in CSV files, the type is text, nullable true and default is an empty string. Columns definitions will be ignored. |
| Delimiter (required) | Customer delimiter. |
| Quotation (required) | Custom quotation. |
| Escape(required) | Custom escape character. |
| Headers (required) | If true, headers are specified in the CSV files, if false columns will be populated in order in which they are entered. |
| Path to File/ Folder (required) |
Path to the folder with data folders with specifications on the name of the table. For example,
For loading all CSV files to the database, folder path
For loading one specific CSV file to the table in a database use a file path without |
| Load method (required) |
|
| Full_load method | Method deletes given tables if exists. Re-creates them and inserts data from file(s). |
| Insert method | The method inserts data into the tables and ignores conflicts. |
| Upsert method | Method upsert data to the tables. In case of conflicts, data will be updated. |
| Primary key (required) | List of primary keys (from columns specified further). |
Column
| Name (required) | Name of the column in the input table. If `HEADERS` is False, then the name can be anything. |
| DB Column Name (optional) |
Column’s name |
| Data Type (required) |
Column’s data type. Available native data types:
|
| Nullable (optional) |
Can be nullable
|
| Default (required) |
Default value |
DB
| Host (required) | can be numerical (like 111.11.111.111) or alphabetical (database.client.solutions) |
| Port (required) | usually 5432 by default. |
| Schema (required) | Organization unit in the database. |
| Database (required) | Name of database you want to load. |
| User (required) | Name of user. |
| Password (required) | Password. |
Remember: You should be able to get all the credentials from the database administrator.