Cannot escape column names

[jussikinnula]

Test case:

// postgres_restricted_word_insert.ts
import postgres from 'postgres';

(async function run() {
  const sql = postgres(process.env.DATABASE_URL || 'postgres://localhost:5432/test');

  await sql`
    DROP TABLE IF EXISTS users
  `;

  await sql`
    CREATE TABLE users (
      "user" varchar(255) NOT NULL,
      CONSTRAINT users_pk PRIMARY KEY ("user")
    );
  `;

  // Works
  console.info('Step 1: Insert user with manually escaping restricted word');
  const rows = await sql`
    INSERT INTO users ("user") VALUES('test user')
  `;
  console.info('Step 1: Completed\n')

  // Does not work
  console.info('Step 2: Insert user by using JS object');
  const user = { user: 'another test user' };
  await sql`
    INSERT INTO users ${sql(user, 'user')}
  `.catch(() => {
    console.error('Step 2: As expected, it did fail...');
    return Promise.resolve();
  });
  console.info('Step 2: Completed\n');

  console.info('All done!');

  process.exit();
})();

[akheron]

There's a typo in the second insert statement. The table name should be users instead of foo.

[jussikinnula]

There's a typo in the second insert statement. The table name should be users instead of foo.

Sorry about the typo. It is now fixed. Anyway, if changing the table column name user to for example user2 - both of the cases work.

There's a list of reserved words, which need quotes. So there should be anyway some way to use column names with those reserved words, like on the Step 1 one can do.

I'm talking about this list: https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html

[akheron]

Yeah. It would probably be easiest to just quote all column names.