Node PostgreSQL Column Names

Introduction to using Node to get the PostgreSQL column names

The Node-PostgreSQL is a collection of modules used to interface with the Postgres database. Among other functions, it permits retrieving table’s column names through the command-line interface. This tutorial will explain how to use a PostgreSQL table’s information_schema.columns to return the column names using the Node.js pg client library for Postgres. When using node PostgreSQL column names, table column names can easily be retrieved via the psql command-line interface.

Prerequisites to accessing PostgreSQL in Node.js

  • Postgres must be properly installed and configured.

  • A PostgreSQL role, with access to a database.

  • Confirm the installed version of PSQL command-line interface by executing the psql -V command.

  • Node must be installed on the machine or server that has access to Postgres. The Node Package Manager, or NPM, must also be installed on the server that will execute the JavaScript code.

Install the pg module for the Node.js PostgreSQL client

If it is not already installed, execute the following command to install the ‘pg’ client for your Node project directory:

npm install pg

Run the npm init command to initialize Node in the directory and complete the prompts so Node will create the package.json file for the project.

PostgreSQL Information Schema for table columns

Following, in alphabetical order, is a comprehensive list of all of the keys, or properties, for the PostgreSQL table’s information_schema.columns attributes:

character_maximum_length, character_octet_length, character_set_catalog, character_set_name, character_set_schema, collation_catalog, collation_name, collation_schema, column_default, column_name, data_type, datetime_precision, domain_catalog, domain_name, domain_schema, dtd_identifier, generation_expression, identity_cycle, identity_generation, identity_increment, identity_maximum, identity_minimum, identity_start, interval_precision, interval_type, is_generated, is_identity, is_nullable, is_self_referencing, is_updatable, maximum_cardinality, numeric_precision, numeric_precision_radix, numeric_scale, ordinal_position, scope_catalog, scope_name, scope_schema, table_catalog, table_name, table_schema, udt_catalog, udt_name, udt_schema

It can be noted in the above list that some column constraints, such a DEFAULT (column_default) and NULL (is_nullable), can be accessed for the columns.

Getting the PostgreSQL column names in psql

The table’s column names can be retrieved by entering into the psql command-line interface for Postgres. Execute the following bash command to connect to a Postgres database:

psql some_database

Once inside of PSQL, execute the following SQL SELECT statement to retrieve the column names from a PostgreSQL table named “some_table“:

SELECT * FROM information_schema.columns
WHERE TABLE_NAME = 'some_table';

A large amount of seemingly unrelated data should be returned along with the column names. To retrieve a more legible table of data, containing the same column names, execute the following SQL command:

d+ some_table

The results should resemble the following:

Screenshot of psql returning column names for a Postgres table using information_schema.columns

Get Postgres column names using a Node example

The below Javascript code accesses a Postgres table’s column names using the Node.js ‘pg’ module’s Pool() client library to connect to a database.

Use a Node Promise object to connect to Postgres

The following code uses a Node.js promise chain to connect to PostgreSQL using a Pool client instance:

// attempt to connect to Postgres using the Pool
pool
.connect()
.then(client => {

After it creates a client instance, the above code will connect to Postgres via pooling and then proceed to the next code using the pool’s .then() method.

Execute an SQL query statement to get the PostgreSQL column names

The following code declares a string in the SQL statement to obtain the column data for a PostgreSQL table from its information_schema:

// concatenate an SQL string to SELECT the table column names
let sqlString = `SELECT * FROM information_schema.columns
WHERE table_name = $1;`;

The string can then be passed to the Pool client’s query() method, as shown below, to get a response from Postgres:

// Pass the SQL string to the Pool client's query() method
client.query(sqlString, [tableName], (sqlErr, res) => {

Parse the result object returned by Postgres

The following code checks for errors and then parses the result object to obtain its rowCount attribute, or total number of columns, and to obtain the row attribute for each column’s specific set of data:

// Access the error object if returned
if (sqlErr) {
console.log('ninformation_schema.columns --', sqlErr.message)
console.log('information_schema.columns error code:', sqlErr.code)
}

// Access the result object if returned
if (res) {
console.log('col totals:', res.rowCount)
console.log('res.rows type:', typeof res.rows)
console.log('result type:', typeof res)
console.log('result keys:', Object.keys(res))

Iterate through the PostgreSQL table’s column data

As shown below, the last step is to use a for loop to iterate through the array of column data and obtain the column names:

// Iterate over the result rows
let colRows = res.rows
for (i=0; i<colRows.length; i++) {

// Log the column name attributes
console.log('ncolRows[i]["column_name"]:', colRows[i]["column_name"])
console.log('table_name:', colRows[i]["table_name"])
console.log('ordinal_position:', colRows[i]["ordinal_position"])
console.log('is_nullable:', colRows[i]["is_nullable"])
console.log('data_type:', colRows[i]["data_type"])
console.log('is_updatable:', colRows[i]["is_updatable"])
}
}

// Release the client to free resources
client.release()
})
})

The above code also obtains each column’s ordinal position in the table, its NULL constraint status, the data type for the column and checks if the column is updatable. This is shown in the second and third text blocks of the following screenshot:

Screenshot of Node PostgreSQL column names in terminal

Conclusion to using Node to get PostgreSQL column names

This tutorial explained how to use a PostgreSQL table’s information_schema.columns to return the column names using the Node.js pg client library for Postgres. The article explained how to install the pg module for the Node.js PostgreSQL client, use a Node Promise object to connect to Postgres, execute an SQL query statement to get the PostgreSQL column names, parse the result object returned by Postgres and how to iterate through the PostgreSQL table’s column data. When using node PostgreSQL column names, always use the node terminal command when in the Node.js project directory to execute the script. Remember to confirm the correct Postgres credentials are passed to the Pool connection instance.

Just the Code

const Pool = require("pg").Pool;

// Declare a new client instance from Pool()
const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

const tableName = "some_table";

/*
SQL Commands to get PostgreSQL column names from a table
SELECT * FROM information_schema.columns WHERE table_name = 'some_table';

d+ some_table
*/


// attempt to connect to Postgres using the Pool
pool.connect().then(client => {
  // concatenate an SQL string to SELECT the table column names
  let sqlString = `SELECT * FROM information_schema.columns
WHERE table_name = $1;`;

  // Pass the SQL string to the Pool client's query() method
  client.query(sqlString, [tableName], (sqlErr, res) => {
    // Access the error object if returned
    if (sqlErr) {
      console.log("ninformation_schema.columns --", sqlErr.message);
      console.log("information_schema.columns error code:", sqlErr.code);
    }

    // Access the result object if returned
    if (res) {
      console.log("col totals:", res.rowCount);
      console.log("res.rows type:", typeof res.rows);
      console.log("result type:", typeof res);
      console.log("result keys:", Object.keys(res));

      // Iterate over the result rows
      let colRows = res.rows;
      for (i = 0; i < colRows.length; i++) {
        // Log the column name attributes
        console.log('ncolRows[i]["column_name"]:', colRows[i]["column_name"]);
        console.log("table_name:", colRows[i]["table_name"]);
        console.log("ordinal_position:", colRows[i]["ordinal_position"]);
        console.log("is_nullable:", colRows[i]["is_nullable"]);
        console.log("data_type:", colRows[i]["data_type"]);
        console.log("is_updatable:", colRows[i]["is_updatable"]);
      }
    }

    // Release the client to free resources
    client.release();
  });
});

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.