Skip to content

Instantly share code, notes, and snippets.

@partap
Last active December 14, 2022 01:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save partap/fefc18a0d294dae033c893f2d2828187 to your computer and use it in GitHub Desktop.
Save partap/fefc18a0d294dae033c893f2d2828187 to your computer and use it in GitHub Desktop.
Trying to make postgres interpolate table name and columns with COPY
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