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 statusto find out if the status isactive.You’ll also need to install
psqlin order to interact with PostgreSQL from the command line. You can use the commandpsql -Vto 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:
1 | CREATE DATABASE some_db; |
- Type the command
\lin thepsqlcommand-line interface to display a list of all the databases on your Postgres server. - Next, use the command
\cfollowed 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:
1 | sudo su - postgres |
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:
1 | psql some_username -h 127.0.0.1 -d some_database |
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:
1 2 3 4 5 6 7 8 9 | some_db=# \d List OF relations Schema | Name | TYPE | Owner --------+-----------------+----------+---------- public | employee | TABLE | postgres public | employee_id_seq | SEQUENCE | postgres public | students | TABLE | postgres public | students_id_seq | SEQUENCE | postgres (4 ROWS) |
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:
1 | SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; |
The statement shown above will display the table’s name, owner, any indexes and other information:
1 2 3 4 5 | schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------+------------+------------+------------+----------+-------------+------------- public | students | postgres | | t | f | f | f public | employee | postgres | | t | f | f | f (2 ROWS) |
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:
1 2 3 | SELECT * FROM information_schema.columns WHERE table_schema = 'some_schema' AND TABLE_NAME = 'some_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:
1 | SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'some_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:
1 | SELECT column_name FROM information_schema.columns WHERE TABLE_NAME = 'some_table'; |
The statement shown above should return several rows of data representing the column names for the PostgreSQL table:
1 2 3 4 5 6 7 | column_name ------------- id str int bool (4 rows) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started