Connect to PostgreSQL and Show the Table Schema

Introduction to showing Postgres column names and the information_schema

When you need information about a PostgreSQL table or other object, it can be helpful to look at that object’s schema. In PostgreSQL, those schemas, along with other important information, can be viewed by accessing the information_schema. In this article, we’ll show you how to connect to PostgreSQL and show a table schema using the Postgres information_schema.

Prerequisites to using PostgreSQL

Before we attempt to connect to PostgreSQL and execute some SQL statements, let’s go over some of the key prerequisites for this task:

• First, make sure that you have PostgreSQL installed on your device. If you’re not sure whether this service is installed, use the command service postgresql status to find out if the status is active.

• You’ll also need to install psql in order to interact with PostgreSQL from the command line. You can use the command psql -V to confirm that this interactive PostgreSQL interface is installed and working on your machine.

Create a database for Postgres that will be used to show the table schema

If you don’t have a PostgreSQL database set up on your server, be sure to create one that you can use to follow along with this tutorial. You can create a database using the command shown below:

• Type the command \l in the psql command-line interface to display a list of all the databases on your Postgres server.
• Next, use the command \c followed by the database name to connect to that database.

Accessing the PostgreSQL using the ‘psql’ command-line interface

You can use the following command to access a PostgreSQL database using the psql command-line interface:

You’ll be prompted for the password. After entering it, you’ll have access to PostgreSQL.

Once you’re connected, use the following command to gain access to your database with a username:

The -d flag signifies the Postgres database name. If you’re prompted for a password again, simply enter it and press RETURN.

Display all of the PostgreSQL tables for the database

If you’d like to display all tables that have already been created, you can use either the \d or \dt command to list them. These commands will return a table containing PostgreSQL table data in the following format:

Show the PostgreSQL table using the ‘pg_catalog’ schema

You can also use a SELECT statement to show the pg_catalog schema for all tables in the current database:

The statement shown above will display the table’s name, owner, any indexes and other information:

NOTE: We use the WHERE clause to filter the set PostgreSQL tables that will be returned. The != operator used in our WHERE clause indicates that the value of schemaname must be NOT EQUAL TO the given condition.

Connect to Postgres to show the table schema and column names

Let’s look at a couple more examples that use the information_schema to access column and schema information for a specific PostgreSQL table:

NOTE: When you refer to table name and schema string values in your WHERE clause, be sure to enclose them in single quotation (') marks to avoid any errors.

The next SQL statement will return all the information_schema‘s attributes for a particular table:

Use the information_schema in Postgres to get the column names for a table

If you prefer to access just a table’s column names from the information_schema, you can specify this in your SELECT statement:

The statement shown above should return several rows of data representing the column names for the PostgreSQL table:

Conclusion to the PostgreSQL show table using schema

When you’re working with data in PostgreSQL, you’ll probably find yourself needing to obtain information about your tables. Fortunately, it’s easy to connect to PostgreSQL and show a table schema by using the information_schema. In this article, we looked at a few different ways to select information from the information_schema. With the help of these examples, you’ll be able to access the information you need about your own PostgreSQL tables and columns.