Example PostgreSQL data source
This example is intended for use with the fbsql loader command and provides:
- valid SQL to create a table then insert data
- TOML key/values to save as a configuration file
Before you begin
- Learn about PostgreSQL
- Create a PostgreSQL Database
- Obtain your PostgreSQL connection string
- Learn about TOML configuration settings
- Create target table
Step 1 - Create data source
- Connect to your database
- Create the source table:
CREATE TABLE postgres_table (
idkey int,
intf int,
stringf varchar(30),
idf int,
stringsetf varchar(30),
idsetf varchar(30));
- Insert data to the source table:
INSERT INTO postgres_table VALUES
(0, 0, 'a', 0, 'a', '3'),
(1, 0, 'a', 0, 'c', '4'),
(2, 0, 'a', 0, 'd', '5');
Step 2 - Create TOML configuration file
- Open a terminal then run
nano example-config.toml - Add the following keys and values and substitute your PostgreSQL connection string as directed:
table = "loader-target"
query = "select idkey, intf, stringf, idf, stringsetf, idsetf from postgres_table;"
driver = "postgres"
connection-string = "postgres://<postgres-username>:<postgres-user-password>@localhost:5432/mydatabase?sslmode=disable"
batch-size = 1
The following [[fields]] keys and values are optional
[[fields]]
name = "idkey"
source-type = "id"
primary-key = true
[[fields]]
name = "intf"
source-type = "int"
[[fields]]
name = "stringf"
source-type = "string"
[[fields]]
name = "idf"
source-type = "id"
[[fields]]
name = "stringsetf"
source-type = "stringset"
[[fields]]
name = "idsetf"
source-type = "idset"
- Save then exit nano.