Last active
December 14, 2022 01:00
-
-
Save partap/fefc18a0d294dae033c893f2d2828187 to your computer and use it in GitHub Desktop.
Trying to make postgres interpolate table name and columns with COPY
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import postgres from 'postgres' | |
import { pipeline } from 'node:stream/promises' | |
import { Readable } from 'node:stream' | |
// Enter your local values | |
const connectUrl = 'postgres://user:password@host/db' | |
const sql = await postgres(connectUrl, { debug: true }) | |
async function importData (tableName, data) { | |
await sql`DROP TABLE IF EXISTS ${sql(tableName)}` | |
await sql`CREATE TABLE ${sql(tableName)} ( | |
name varchar(32), | |
age int | |
)` | |
// Translate data to tab-separated, newline terminated values | |
const tsv = data.map(rec => [...Object.values(rec)].join('\t') + '\n') | |
// console.log('tsv:', tsv) | |
const readStream = Readable.from(tsv) | |
const cols = Object.keys(data[0]) | |
// console.log('cols:', cols) | |
// This is what I *want* to do | |
const query = sql`COPY ${sql(tableName)} (${sql(cols)}) FROM STDIN` | |
// --> Error: Could not infer helper mode | |
// Try interpolating columns but not table name | |
// with parens around columns | |
// const query = sql`COPY test_table ( ${sql(cols)} ) FROM STDIN` | |
// --> Error: Could not infer helper mode | |
// Try without parens | |
// const query = sql`COPY test_table ${sql(cols)} FROM STDIN` | |
// --> Error: Could not infer helper mode | |
// Try interpolating tablename but not columns | |
// const query = sql`COPY ${sql(tableName)} ( name, age ) FROM STDIN` | |
// --> PostgresError: syntax error at end of input | |
// Only works without any interpolation | |
// const query = sql`COPY test_table ( name, age ) FROM STDIN` | |
// console.log('query:', query) | |
const writeStream = await query.writable() | |
await pipeline(readStream, writeStream) | |
} | |
const testTable = 'test_table' | |
const testData = [ | |
{ | |
name: 'Murray', | |
age: 68 | |
}, | |
{ | |
name: 'Walter', | |
age: 80 | |
} | |
] | |
try { | |
await importData(testTable, testData) | |
const contents = await sql`SELECT * from ${sql(testTable)}` | |
console.log(contents) | |
} catch (err) { | |
console.error('testImport:', err) | |
} | |
await sql.end() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment