In PostgreSQL, multiple scenarios can cause a Column doesn’t exist error. For instance, the searched column doesn’t exist in the targeted table, the column’s naming convention doesn’t match, typo mistakes, etc. The stated error might occur in Postgres while executing the SELECT query, UPDATE query, INSERT query, ALTER statement, etc. To rectify the stated error, multiple approaches can be used in PostgreSQL.

This write-up will show you various causes and their respective solutions in Postgres. This post will cover the below-listed content to fix the “column does not exist exception/error” in Postgres.

Reason 1: Column Doesn’t Exist
- Solution: Add the Respective Column

Reason 2: Incorrect Column Spelling
- Solution: Correct the Column Name

Reason 3: Using Column Alias Incorrectly
- Solution: Use the Column Alias Correctly

So, let’s begin!

Reason 1: Column Doesn’t Exist

The most common reason that causes the stated error can be selecting a column that doesn’t exist in the targeted table. For instance, we have created a table named emp_info, whose data is shown in the below snippet:

SELECT * FROM emp_info;

The above snippet shows that the emp_info table has two columns: emp_id and emp_name. Now, trying to insert the data into a column that doesn’t exist in the table will throw the stated error:

INSERT INTO emp_info(emp_id, emp_name, emp_email)
VALUES (3, 'Joe', 'joe321@xyz.com');

The output shows that a “column doesn’t exist” error occurred when we accessed a column that didn’t exist in the table.

Solution: Add the Respective Column

To resolve the stated error, specify only those columns that exist in the targeted table or first add the desired column to the table; then, you can insert the data into that column. Execute the below command to add the “emp_email” column to the “emp_info” table:

ALTER TABLE emp_info
ADD COLUMN emp_email VARCHAR;

A new column has been added to the emp_info table. Now you can insert the data into that particular column as well:

INSERT INTO emp_info(emp_id, emp_name, emp_email)
VALUES (3, 'Joe', 'joe321@xyz.com');

The above snippet shows that the INSERT query was executed successfully, and the stated error has been resolved. You can verify the inserted data using the SELECT query:

SELECT * FROM emp_info;

This is how you can rectify the stated error in Postgres.

Reason 2: Incorrect Column Spelling

Another notable reason that can cause this error is accessing a column with incorrect spellings:

SELECT emp_nam
FROM emp_info;

The output snippet showed an error when a column was accessed with incorrect spellings. Postgres shows a hint that will help you rectify the stated error.

Solution: Correct the Column Name

To rectify the stated error, you must access the table’s column with the correct spellings as shown in the “HINT”:

SELECT emp_name 
FROM emp_info;

The output shows that correcting the column’s spelling resolves the stated error.

Reason 3: Using Column Alias Incorrectly

The “column doesn’t exist” error can occur if a column alias is used incorrectly. For instance, we have created two tables in Postgres named “article_details” and “article_info,” whose details are shown below:

SELECT * FROM article_details;

Now, we will fetch all the details about the “article_info” table:

SELECT * FROM article_info;

In the below snippet, we will use the column alias for the article_title column of the article_details table. Next, we will use the EXCEPT operator to find all those records that don’t exist in the article_info table. Finally, we will use the ORDER BY clause to sort the result set in descending order:

(SELECT article_title AS titles FROM article_details)
EXCEPT
(SELECT article_title FROM article_info)
ORDER BY article_title DESC;

In the above snippet, the stated error occurs because we use the column name instead of the column alias, which creates ambiguity.

Solution: Use the Column Alias Correctly

To fix the stated error, either remove the column alias from the SELECT statement or use the column alias in the ORDER BY clause. Let’s use the column alias in the ORDER BY clause and see whether the stated error has been resolved or not:

(SELECT article_title AS titles FROM article_details)
EXCEPT
(SELECT article_title FROM article_info)
ORDER BY titles DESC;

The output snippet proves that the “column doesn’t exist” error has been resolved successfully.

Conclusion

In PostgreSQL, the “column doesn’t exist” error can occur because of various reasons, such as the searched column doesn’t exist in the targeted table, typo mistakes, column alias being used incorrectly, etc. The stated error might occur in Postgres while executing the SELECT query, UPDATE query, INSERT query, ALTER statement, etc. Multiple approaches are explained in this Postgres guide to rectify the stated error.