pg_sqlite

PostgreSQL Tcl Interface Documentation
Prev	Fast Backward		Fast Forward	Next

Description

pg_sqlite can import the results of a Postgres SQL query (previously made via pg_exec) directly into an sqlite3 table, export the results of an Sqlite SQL query into a Postgres table (via write_tabsep), or import TSV files directly into sqlite3.

The commands currently implemented are import_postgres_result, read_tabsep, write_tabsep, and read_tabsep_keylist.

Arguments

sqlite_db: An Sqlite3 database handle previously created via the sqlite3 command.
command: The command, one of info, import_postgres_result, read_tabsep, write_tabsep, or read_tabsep_keylist,
args: Command-specific arguments.

Commands

info

    pg_sqlite sqlite_db info
        ?-busy?
        ?-filename?
        ?-db database_name?

Request information from an SQLITE database connection. Returns a key-value list containing the values requested if available. With no arguments, all available info is returned.

-filename: Request the filename associated with a database.
-db database: For the -filename option, specify the database name (default "main").
-busy: Request a list of prepared commands that are currently busy.

import_postgres_result

    pg_sqlite sqlite_db import_postgres_result handle
        ?-rowbyrow?
        ?-sql target_sql?
        ?-create new_table?
        ?-into table?
        ?-replace?
        ?-as name_type_list?
        ?-types type_list?
        ?-names name_list?
        ?-pkey primary_key?
        ?-sep separator?
        ?-null null_string?
        ?-poll_interval rows?
        ?-recommit rows?
        ?-check?
        ?-max column-name variable-name?

Import the result of a PostgreSQL request into an sqlite3 table.

handle: A database or result handle. Normally, you would call pg_exec and pass the result handle to pg_sqlite here. If you are using row_by_row mode, though, you use pg_sendquery and pass the PostgreSQL database handle here.
-rowbyrow: Perform the request using row-by-row mode. This injects the data to sqlite directly without making an internal copy, but the result may be incomplete or inconsistent if an error occurs during the request.
-sep separator: String to use to separate columns. Default is "\t" (tab).
-null null_string: String to use to indicate a null value. Default is to treat all strings literally.
-sql target_sql: An INSERT statement, suitable to be compiled into a prepared statement to be applied to each row. For example "INSERT INTO newtable (id, name, value) VALUES (?,?,?)"
-create new_table: A table to be created in sqlite. The names and types of columns, and the primary key, must be provided.
-into table: An existing sqlite table to insert the data. The names of the columns must be provided, if the type can not be inferred it will be assumed to be "text".
-replace: When performing the insert on an existing or newly create table, use "INSERT OR REPLACE" semantics. Not compatible with "-sql".
-as name-type-list: A list of alternating column names and types. See note on types.
-types type-list: A list of column types. See note on types.
-names name-list: A list of column names.
-pkey primary_key: A list containing key names and optional sorting to indicate primary key where needed. For example -pkey {{clock ASC} {sequence ASC}}.
-poll_interval count: Call DoOneEvent() every count rows to keep the event loop alive during long transactions.
-recommit count: Chunk the operation in transactions, with one transaction every count rows.
-check: Check (via a SELECT) whether the exact row is already present, and skip inserting the row if so. This avoids bloating the WAL log during large re-loads of databases, at some performance cost.
-max column-name variable-name: Set $variable-name to the maximum value of column-name imported.

write_tabsep

    pg_sqlite sqlite_db write_tabsep handle sql
        ?sql?
        ?-sep separator?
        ?-null null_string?
        ?-poll_interval rows?

Write the results of the provided sql to a file handle

This command may be used to export sqlite3 data to postgres. You issue a COPY FROM STDIN ... FORMAT text command, then use pg_sqlite write_tabsep ... to write the data directly to the Postgresql handle, followed by writing the terminator line to the same handle. See the PostgreSQL documentation on the "COPY" command for more details.

handle: File handle.
-sep separator: String to use to separate columns. Default is "\t" (tab).
-null null_string: String to use to indicate a null value. Default is to treat all strings literally.
-poll_interval count: Call DoOneEvent() every count rows to keep the event loop alive during long transactions.

read_tabsep

    pg_sqlite sqlite_db read_tabsep
        ?-row single_tab_separated_row?
        ?-file file_handle?
        ?-sql target_sql?
        ?-create new_table?
        ?-into table?
        ?-replace?
        ?-as name_type_list?
        ?-types type_list?
        ?-names name_list?
        ?-pkey primary_key?
        ?-sep separator?
        ?-null null_string?
        ?-poll_interval rows?
        ?-recommit rows?
        ?-check?

Read a previously opened file into an sqlite3 table.

-row single_tab_separated_row: An already read tab-separated line.
-file file_handle: An open file consistion of tab-separated rows.
-sep separator: String to use to separate columns. Default is "\t" (tab).
-null null_string: String to use to indicate a null value. Default is to treat all strings literally.
-sql target_sql: An INSERT statement, suitable to be compiled into a prepared statement to be applied to each row. For example "INSERT INTO newtable (id, name, value) VALUES (?,?,?)"
-create new_table: A table to be created in sqlite. The names and types of columns, and the primary key, must be provided.
-into table: An existing sqlite table to insert the data. The names of the columns must be provided, if the type can not be inferred it will be assumed to be "text".
-replace: When performing the insert on an existing or newly create table, use "INSERT OR REPLACE" semantics. Not compatible with "-sql".
-as name-type-list: A list of alternating column names and types. See note on types.
-types type-list: A list of column types. See note on types.
-names name-list: A list of column names.
-pkey primary_key: A list containing key names and optional sorting to indicate primary key where needed. For example -pkey {{clock ASC} {sequence ASC}}.
-poll_interval count: Call DoOneEvent() every count rows to keep the event loop alive during long transactions.
-recommit count: Chunk the operation in transactions, with one transaction every count rows.
-check: Check (via a SELECT) whether the exact row is already present, and skip inserting the row if so. This avoids bloating the WAL log during large re-loads of databases, at some performance cost.

read_tabsep_keylist

    pg_sqlite sqlite_db read_tabsep_keylist
        ?-row single_tab_separated_row?
        ?-file file_handle?
        ?-create new_table?
        ?-into table?
        ?-replace?
        ?-as name_type_list?
        ?-names name_list?
        ?-pkey primary_key?
        ?-sep separator?
        ?-null null_string?
        ?-poll_interval rows?
        ?-recommit rows?

Read a previously opened file containing alternating key-value columns into an sqlite3 table.

-row single_tab_separated_row: An already read tab-separated key-value list line.
-file file_handle: An open file consistion of tab-separated key-value list rows.
-sep separator: String to use to separate columns. Default is "\t" (tab).
-null null_string: String to use to indicate a null value. Default is to treat all strings literally.
-create new_table: A table to be created in sqlite. The names and types of columns, and the primary key, must be provided.
-into table: An existing sqlite table to insert the data. The names of the columns must be provided, if the type can not be inferred it will be assumed to be "text".
-replace: When performing the insert on an existing or newly create table, use "INSERT OR REPLACE" semantics. Not compatible with "-sql".
-as name-type-list: A list of alternating column names and types. See note on types.
-names name-list: A list of column names.
-pkey primary_key: A list containing key names and optional sorting to indicate primary key where needed. For example -pkey {{clock ASC} {sequence ASC}}.
-poll_interval count: Call DoOneEvent() every count rows to keep the event loop alive during long transactions.
-recommit count: Chunk the operation in transactions, with one transaction every count rows.

Types

A note on types: pg_sqlite supports four types: integer (or int), boolean (or bool), double (or real), and text. Integer, double, and text match both sqlite and postgresql types. Boolean is an integer type in sqlite, and boolean in postgresql, and converts postgresql boolean values (such as 'yes', 'no', 'true', or 'false') to integer 1 and 0.

In addition, pg_sqlite will accept PostgreSQL boolean values for the integer type. This may be made conditional on a "strict" mode in the future.

Return Value

Number of rows imported or exported.

Prev	Home	Next
pg_lo_export	Up	pg_copy_complete

pg_sqlite

Name

Synopsis

Description

Arguments

Commands

Types

Return Value

See Also