CODEWITHSUNDEEP
javascriptnode.jspostgresqlpg-promise
Galadriel
Galadriel

Reputation: 369

Relation not found error in multi-row insert with postgresql and pg-promise

I'm developing a nodejs project with a postgresql-db. I've created a schema for the db with some tables and now I've created a file with some prepared data (multilple rows) to insert. For that I followed the instructions of this entry: Multi-row insert with pg-promise.

The problem is, that I get following error: { error: relation "tenantx.ObjectGroup" does not exist ... but it does. I can also see that from my api, when I want to read the table content, it returns an empty object. Can the "schema.table"-declaration be the reason?

The code for the prepared data looks like this: 

    'use strict';
     const pgp = require('pg-promise')({
                // Initialization Options
            });

        const csObjectGroup = new pgp.helpers.ColumnSet(['objectgroup', 'description'], {
                    table: 'tenantx.ObjectGroup'
                });
                // data input values:
                const valuesObjectGroup = [{
                    objectgroup: 'bla',
                    description: 'bla'
                }, {
                    objectgroup: 'blu',
                    description: 'blu'
                }, {
                    objectgroup: 'bla',
                    description: 'bla'
                }];
module.exports = {
    csObjectGroup: csObjectGroup,
    valuesObjectGroup: valuesObjectGroup
}

And the code which is called from api to setup content: // insert prepared data into tables

function initializeData(pTenantId, dbUri) {
    var dbPostgres = dbUri;
    const datafile = require("./../Data/data_" + pTenantId);

    var statements = [];

    var valuesObjectGroup = datafile.valuesObjectGroup;
    var csObjectGroup = datafile.csObjectGroup;

    statements.push(valuesObjectGroup, csObjectGroup);

    var i, query;
    for (i = 0; i < statements.length - 1; i += 2) {
        query = pgp.helpers.insert(statements[i], statements[i + 1]);
        dbPostgres.none(query)
            .then(data => {
                console.log("+++++ Data successfully initialized.");
            })
            .catch(err => {
                console.log("----- Data could not be initialized.");
                console.log(err);
            });
    }
    return true;
}

I've created the table from this:

const createObjectGroupTable =
    'CREATE TABLE IF NOT EXISTS tenantx.ObjectGroup \
            ( \
                id serial, \
                objectgroup varchar(50), \
                description varchar(100), \
                PRIMARY KEY (id) \
            )';

Upvotes: 3

Views: 447

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

If you look at the reported error with more attention:

{ error: relation "tenantx.ObjectGroup" does not exist ...

That's not "tenantx"."ObjectGroup", that's just one table name "tenantx.ObjectGroup".

And the problem originates from the way you declare the table:

const csObjectGroup = new pgp.helpers.ColumnSet(['objectgroup', 'description'], {
                    table: 'tenantx.ObjectGroup'
                });

instead of providing schema + table names, you specify it all as a table name, and so it is escaped accordingly.

The correct way to specify schema + table can be done in one of the following ways:

table: {schema: 'tenantx', table: 'ObjectGroup'}

or:

table: new pgp.helpers.TableName('ObjectGroup', 'tenantx')

or:

table: new pgp.helpers.TableName({table: 'ObjectGroup', schema: 'tenantx'})

See API: TableName.

UPDATE

Starting from v11.7.8 onward, you can also make use of the _TN helper like so:

const {ColumnSet, _TN} = pgp.helpers;

const csObjectGroup = new ColumnSet(['objectgroup', 'description'], {
                        table: _TN('tenantx.ObjectGroup')
                 });

Upvotes: 3

Related Questions