Postgresql ошибка column does not exist

As others suggested in comments, this is probably a matter of upper-case versus lower-case, or some whitespace in the column name. (I’m using an answer so I can format some code samples.) To see what the column names really are, try running this query:

SELECT '"' || attname || '"', char_length(attname)
  FROM pg_attribute
  WHERE attrelid = 'table_name'::regclass AND attnum > 0
  ORDER BY attnum;

You should probably also check your PostgreSQL server log if you can, to see what it reports for the statement.

If you quote an identifier, everything in quotes is part of the identifier, including upper-case characters, line endings, spaces, and special characters. The only exception is that two adjacent quote characters are taken as an escape sequence for one quote character. When an identifier is not in quotes, all letters are folded to lower-case. Here’s an example of normal behavior:

test=# create table t (alpha text, Bravo text, "Charlie" text, "delta " text);
CREATE TABLE
test=# select * from t where Alpha is null;
 alpha | bravo | Charlie | delta  
-------+-------+---------+--------
(0 rows)

test=# select * from t where bravo is null;
 alpha | bravo | Charlie | delta  
-------+-------+---------+--------
(0 rows)

test=# select * from t where Charlie is null;
ERROR:  column "charlie" does not exist
LINE 1: select * from t where Charlie is null;
                              ^
test=# select * from t where delta is null;
ERROR:  column "delta" does not exist
LINE 1: select * from t where delta is null;
                              ^

The query I showed at the top yields this:

 ?column?  | char_length 
-----------+-------------
 "alpha"   |           5
 "bravo"   |           5
 "Charlie" |           7
 "delta "  |           6
(4 rows)

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.

Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:

DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
  id smallint GENERATED ALWAYS AS IDENTITY NOT NULL,
  holiday_date DATE,
  name CHARACTER VARYING(80)
);
ALTER TABLE config.trading_holiday
  ADD CONSTRAINT trading_holiday_pk
  PRIMARY KEY (id);

Then a very simple stored procedure, config.sp_add_holiday is created to add holidays to the table:

CREATE OR REPLACE PROCEDURE config.sp_add_holiday(holiday_date DATE, 
                                                  name CHARACTER VARYING(80))
AS $$
BEGIN
  INSERT INTO config.trading_holiday(holiday_date,name)
    VALUES(sp_add_holiday.holiday_date,sp_add_holiday.name);
END
$$
LANGUAGE PLPGSQL;

Then a simple call is made to add the first holiday:

CALL config.sp_add_holiday(holiday_date='2018-01-01',name='New Years Day');

And I get the following error message:

[2018-08-07 11:56:18] [42703] ERROR: column "holiday_date" does not exist
[2018-08-07 11:56:18] Position: 21

Doing a manual insert, e.g.:

INSERT INTO config.trading_holiday(holiday_date,name)
  VALUES('2018-01-01','New Years Day');

Works successfully:

[2018-08-07 12:04:01] 1 row affected in 2 ms

Despite being new to the PostgeSQL 11 SQL procedure functionality (who isn’t?), this seems like such a simple proc. What in the world am I doing wrong?

Wondering how to fix PostgreSQL Error code 42703? We can help you.

One of the most common error codes with the PostgreSQL database is 42703. It will be seen along with the error message “column does not exist”. This error indicates either that the requested column does not exist, or that the query is not correct.

Here at Bobcares, we often handle requests from our customers to fix similar PostgreSQL errors as a part of our Server Management Services. Today we will see how our support engineers fix this for our customers.

How to fix PostgreSQL Error code 42703

Often, the error is caused by a lack of quotes. We can add double quotes to the column name to fix this error.

For example:

We will try to run a simple select query:

SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMIT 10;

And get the following error:

ERROR: column return_part_i.cntrcttrmntnind does not exist LINE 1: SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMI... ^ HINT: Perhaps you meant to reference the column "return_part_i.CntrctTrmntnInd". SQL state: 42703 Character: 8

if we have a camel case in our column name we must ensure to wrap the column name with a double quote.

This can be done in the following way:

SELECT "CntrctTrmntnInd"  FROM return_part_i LIMIT 10;

PostgreSQL columns (object) names are case sensitive when specified with double quotes. Unquoted identifiers are automatically used as lowercase so the correct case sequence must be written with double quotes.

If we want a LIMIT in result we must use an order by

SELECT "CntrctTrmntnInd" FROM return_part_i ORDER BY "CntrctTrmntnInd" LIMIT 10;

When used with quotes, Postgresql is case sensitive regarding identifier names like table names and column names.
So a common issue that triggers this error is when we use the column name in our commands in any other cases other than that of the original one.

For instance, if the column name is “Price”, using “price” in the command can trigger the error.

Thus we need to make sure that the cases are correct.

[Need assistance? We can help you]

Conclusion

In short, we saw how our Support Techs fix PostgreSQL Error code 42703 for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;