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:

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:

Once inside of PSQL, execute the following SQL SELECT statement to retrieve the column names from a PostgreSQL table named “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:

The results should resemble the following:

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:

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:

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

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:

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:

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:

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