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
“:
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:
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:
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
:
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:
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:
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:
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:
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
// 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