PG_SQLITE(n)

📦 version 3.1.1, in alpine 3.22, alpine 3.23, alpine 3.24, nixpkgs unstable

💅 pg_sqlite(n) — the PostgreSQL Tcl Interface glow-up 🦋

okay so bestie, welcome to pg_sqlite — it's the bridge between PostgreSQL and Sqlite3, built on the Pgtcl and sqlite3 packages. two databases, one little command, no cap 💖

✨ SYNOPSIS ✨

pg_sqlite sqlite_db command [args]

🌸 DESCRIPTION 🌸

so here's the vibe: pg_sqlite can do three iconic things for you —

  • import the results of a Postgres SQL query (one you already made via pg_exec) straight into an sqlite3 table,
  • export the results of an Sqlite SQL query into a Postgres table (by way of write_tabsep),
  • or import TSV files directly into sqlite3.

the commands we've actually got implemented rn are: import_postgres_result, read_tabsep, write_tabsep, and read_tabsep_keylist. that's the whole lineup, she's serving 🎀

🫶 ARGUMENTS 🫶

  • sqlite_db — an Sqlite3 database handle you already made earlier with the sqlite3 command. like, it has to exist first, bestie, we can't manifest it.
  • command — the actual command you're running, one of info, import_postgres_result, read_tabsep, write_tabsep, or read_tabsep_keylist,
  • args — the arguments that go with whichever command you picked. command-specific, iykyk.

👑 COMMANDS 👑

⭐ info

pg_sqlite sqlite_db info
 [-busy]
 [-filename]
 [-db database_name]

ask your SQLITE database connection for the tea ☕ it hands back a key-value list with whatever values you requested (if they're available). call it with no arguments at all and you get all the available info back, the full dump, main character energy.

  • -filename — ask for the filename associated with a database. 💖
  • -db database — used with the -filename option, this says which database name you mean (default is "main").
  • -busy — ask for a list of prepared commands that are currently busy. they're booked and busy, literally.

⭐ 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]

this one imports the result of a PostgreSQL request into an sqlite3 table. slay 🫶

  • handle — a database handle or a result handle. normally you'd call pg_exec and pass the result handle to pg_sqlite right here. but if you're in row_by_row mode, you use pg_sendquery instead and pass the PostgreSQL database handle here. different handle, same energy.
  • -rowbyrow — do the request in row-by-row mode. this injects the data to sqlite directly without making an internal copy (fast, no middleman 💅) but heads up: the result may be incomplete or inconsistent if an error happens during the request. risky but iconic.
  • -sep separator — the string used to separate columns. default is "\t" (tab).
  • -null null_string — the string used to indicate a null value. default is to treat all strings literally.
  • -sql target_sql — an INSERT statement, suitable for being compiled into a prepared statement and 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 the columns, and the primary key, must be provided. non-negotiable.
  • -into table — an existing sqlite table to insert the data into. the names of the columns must be provided, and if the type can't be inferred it'll be assumed to be "text".
  • -replace — when doing the insert on an existing or newly created table, use "INSERT OR REPLACE" semantics. not compatible with -sql, so don't even try it.
  • -as name-type-list — a list of alternating column names and types. see the note on types below 👇
  • -types type-list — a list of column types. see the note on types.
  • -names name-list — a list of column names.
  • -pkey primary_key — a list containing key names and optional sorting, to indicate the 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. keep the vibe going 💀
  • -recommit count — chunk the operation into 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 it is. this avoids bloating the WAL log during large re-loads of databases, at some performance cost. tradeoff queen 🌈
  • -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]

writes the results of the provided sql to a file handle. 🍓

the way you'd use this 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, and then you write the terminator line to that same handle. see the PostgreSQL docs on the "COPY" command for the full lore.

  • handle — file handle.
  • -sep separator — the string used to separate columns. default is "\t" (tab).
  • -null null_string — the string used 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 made of tab-separated rows.
  • -sep separator — the string used to separate columns. default is "\t" (tab).
  • -null null_string — the string used to indicate a null value. default is to treat all strings literally.
  • -sql target_sql — an INSERT statement, suitable for being compiled into a prepared statement and 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 the columns, and the primary key, must be provided.
  • -into table — an existing sqlite table to insert the data into. the names of the columns must be provided, and if the type can't be inferred it'll be assumed to be "text".
  • -replace — when doing the insert on an existing or newly created table, use "INSERT OR REPLACE" semantics. not compatible with -sql.
  • -as name-type-list — a list of alternating column names and types. see the note on types.
  • -types type-list — a list of column types. see the note on types.
  • -names name-list — a list of column names.
  • -pkey primary_key — a list containing key names and optional sorting, to indicate the 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 into 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 it is. 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. key-value girlies rise 🎀

  • -row single_tab_separated_row — an already-read tab-separated key-value list line.
  • -file file_handle — an open file made of tab-separated key-value list rows.
  • -sep separator — the string used to separate columns. default is "\t" (tab).
  • -null null_string — the string used 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 the columns, and the primary key, must be provided.
  • -into table — an existing sqlite table to insert the data into. the names of the columns must be provided, and if the type can't be inferred it'll be assumed to be "text".
  • -replace — when doing the insert on an existing or newly created table, use "INSERT OR REPLACE" semantics. not compatible with -sql.
  • -as name-type-list — a list of alternating column names and types. see the note on types.
  • -names name-list — a list of column names.
  • -pkey primary_key — a list containing key names and optional sorting, to indicate the 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 into transactions, with one transaction every count rows.

💜 TYPES 💜

a note on types, because this part matters: pg_sqlite supports four types —

  • integer (or int)
  • boolean (or bool)
  • double (or real)
  • text

integer, double, and text match both sqlite and postgresql types, easy. boolean is an integer type in sqlite, and a boolean in postgresql — and it converts postgresql boolean values (like 'yes', 'no', 'true', or 'false') to integer 1 and 0. cute, right? ✨

also, pg_sqlite will accept PostgreSQL boolean values for the integer type too. that might become conditional on a "strict" mode in the future, so like... noted. 🙈

🔥 RETURN VALUE 🔥

number of rows imported or exported. that's it, that's the number, periodt.

🌈 SEE ALSO 🌈

Sqlite 3

okay bestie that's the whole page 💖 hope this was helpful, go forth and bridge those databases 🦋

— signed off with love, 17 September 2020, PostgreSQL Tcl Interface Documentation 👑💅

📦 where this page comes from