Postgresql error column of relation does not exist - Исправление ошибок и поиск оптимальных решений проблем

Asked
6 years, 6 months ago

Viewed
31k times

I am getting an error while running an update query with table name specified along with column name:

UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'

This is the error:

ERROR:  column "temp" of relation "temp" does not exist
LINE 1:      UPDATE Temp SET Temp.Id='23...
                               ^
********** Error **********

ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24

dezso

29.9k13 gold badges95 silver badges140 bronze badges

asked Aug 3, 2016 at 9:02

You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE. This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.

Fortunately, the ever helpful documentation explicitly mentions your case:

column_name

The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript,
if needed. Do not include the table’s name in the specification of a
target column — for example, UPDATE tab SET tab.col = 1 is invalid.

So, the solution is to simply remove temp. from the SET clause:

UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'

Notes:

Are you really storing numbers as text? If yes, why? It is usually a recipe for disaster. For example, how do you prevent something like 'mkjcvnd7y78r3tgbhvcjh' entering your id column?
The way you are using object names starting with capital letters is confusing. Without double-quoting its name, your table in reality is called temp as opposed to Temp. Using it the latter way may decrease readability (depending on your preferences and habits, of course).

answered Aug 3, 2016 at 9:13

dezsodezso

29.9k13 gold badges95 silver badges140 bronze badges

What would happen in the case of doing an update for 2 tables that have the same field name?

update car c, airplane a
set c.nr_rute = 1
set a.nr_rute = 1

answered Dec 8, 2021 at 17:52

Источник

Introduction

Importing or restoring data from an SQL file consisting of INSERT statement stop when an error appear. The following is the error appear preventing the operation for importing or restoring data to go on further :

C:>psql -Udb_user -d db_app < "C:UsersPersonalDownloadsinsert-active-employee.sql" 
Password for user db_user: 
ERROR: column "employment_date" of relation "org_employee" does not exist 
LINE 1: ...birthdate, birthplace, address, postal_code, status, employment_date...   
^

Actually, this article has some several articles which is quite related upon the process for importing or restoring data from an SQL file. Those articles exist in the article with the title of ‘How to Solve Error Message ERROR: value too long for type character varying(10) on the Restore Process of a Database in PostgreSQL Database’ in this link. Another one also exist in this link with the title of ‘How to Solve Error ERROR: syntax error at or near “[” on the Restore Process of a Database in PostgreSQL Database’. Those error message are facing different types of errors.

In this context, the error type is focusing on the non-existence of a column. Actually, the table with the name of ‘org_employee’ is a Django generated table from executing the migration script. So, apparently after finishing on generating the table, there is a column missing or it is not available after the attempt to run an SQL file to restore records or data rows.

Solution

Normally, the solution just executing a certain query to add the non-existent column in the table. But since the table has a strong connection with the model available in a Django model exist in the Django-based application, there is another suitable way to solve the problem. Those way exist in the following steps :

First of all, just access the ‘models.py’ file in the root folder of the application.
Next, edit the ‘model.py’ file to add the necessary attribute or field which is going to represent the non-existent column. In this context, it is the column with the name of ’employment_date’. The following below is the revision of the class of the model representing the ‘org_employee’ table :

# Create your models here.
class Employee(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, blank=True, null=True)
    name = models.CharField(max_length=100, blank=True, null=True)
    birthdate = models.DateField()
    birthplace = models.CharField(max_length=50, blank=True, null=True)
    address = models.CharField(max_length=255, blank=True, null=True)
    employment_date = models.DateField()
    def __str__(self):
        return self.name

Finally, execute the following command to generate a migration script. Furthermore, that script is important for implementing the new added attribute or field into a new column for the table. The command is ‘python manage.py makemigrations’.
Last but not least, execute the command ‘python manage.py migrate’ to implement the migration script into the table.
Last step, just run the command again for restoring the records or row data to its associated table.

Источник

There are a lot of PostgreSQL errors out there.
Way too much, right?

You as a sysadmin know that for sure – Syntax Errors, Relation Errors, Server Connection Errors, and other Error Codes.

Here you’ll find a list of the most common PostgreSQL errors and proven quick fix solutions:

PostgreSQL error “Syntax error at or near ‘grant’”
PostgreSQL error code “42501” or “Permission denied”
PostgreSQL error code “1053” or “The service did not respond to the start or control request in a timely fashion”
PostgreSQL error “Role does not exist”
PostgreSQL error “Relation does not exist”
PostgreSQL error ”Could not connect to server: no such file or directory” or “Could not connect to server: connection refused”
PostgreSQL error “Invalid input syntax”
PostgreSQL error “Permission denied for database”
PostgreSQL error Code “42703” or “Column does not exist”
PostgreSQL error “Could not extend file” or “No space left on device”

And you’ll find the solution to get rid of ALL PostgreSQL errors – forever: Test PRTG as your new monitoring tool and get started within minutes!

1. PostgreSQL error

“Error: syntax error at or near ‘grant’”

Quick fix

The error message “syntax error at or near ‘grant’” is one of the most common PostgreSQL database errors. However, it can easily be identified and resolved.

To understand this issue, you need to know that SQL distinguishes between reserved and non-reserved key word tokens. Reserved key words, such as “grant”, are never allowed as identifiers. Most reserved tokens are not allowed as column or table names, but may be allowed as an “AS” column label name.

If you come across this error message, check your code and make sure that the reserved keyword, for example “grant”, is quoted. Without using quotes, the error message will pop up in the PostgreSQL database.

Best Solution: https://severalnines.com/blog/decoding-postgresql-error-logs

2. PostgreSQL error

“Error 42501” or “Permission Denied”

Quick fix

PostgreSQL error 42501 is a common error that sometimes occurs in response to a PostgreSQL database query. In most cases, error code 42501 implies that the user has insufficient privilege for the database. As soon as a user with insufficient privileges make a query, PostgreSQL responds with the error message.

To fix the problem, check the database user privileges. If the user who attempted the query lacks permission, simply change the privileges accordingly. You can give privileges for a table either to the public using “GRANT SELECT ON table_name TO PUBLIC;” or to only a few users using the command “GRANT SELECT ON table_name to user_name;”.

Best Solution: https://bobcares.com/blog/postgresql-error-42501/364570

3. PostgreSQL error

“Error 1053” or “The service did not respond to the start or control request in a timely fashion”

Quick fix

Are you facing error code 1053 while working with the PostgreSQL database? Then you have come across a common PostgreSQL error. The error code is usually accompanied by the message “the service did not respond to the start or control request in a timely fashion”.

There are several possible causes for error 1053, such as low timeout values, firewall restrictions, corrupted files and permission of files. The solution for PostgreSQL error 1052 depends on the individual cause:

If caused by a low timeout value, get rid of error code 1053 by setting a ServicesPipeTimeout DWORD value in the registry editor to override the default timeout time of your database.
If your firewall prevents your PostgreSQL database from working correctly, disable your firewall or change the settings to allow all database requests to run smoothly.
If corrupted files or permission of files are the cause of this error to occur, use the file checking tools to check the system file structure and replace corrupted files to eliminate of error 1053.

Best Solution: https://bobcares.com/blog/postgresql-error-1053/

4. PostgreSQL error

“Role does not exist”

Quick fix

PostgreSQL error message “role does not exist” occurs when connecting to PostgreSQL using a user name that does not exist. The full error message usually states something similar to “FATAL: role “username” does not exist”.

For easy troubleshooting, make sure you have logged in to the correct user. If the user does not exist yet, create the user account on the PostgreSQL database. You should now be able to connect to PostgreSQL.

Best Solution: https://knowledgebase.progress.com/articles/Article/postgresql-error-role-does-not-exist

5. PostgreSQL error

“Relation does not exist”

Quick fix

Are you looking for a solution to PostgreSQL error message “relation does not exist”? As there are several possible causes for this common error, it is often necessary to do some digging in order to find out what causes the PostgreSQL database to respond with the error message.

One of many possible causes is that your postgres user is configured not to use a password, while your connection string includes “password=”. This configuration can result in the error “relation does not exist” to occur. To solve the problem, remove “password=” from the connection string. It should now look like this:

“host=localhost port=5432 user=postgres dbname=t11 sslmode=disable”

Another workaround is to alter the postgres user to require a password, then change the connecting string accordingly.

Best Solution: https://medium.com/@raajyaverdhanmishra/when-you-get-relation-does-not-exist-in-postgres-7ffb0c3c674b

6. PostgreSQL error

”Could not connect to server: no such file or directory” or “Could not connect to server: connection refused”

7. PostgreSQL error

“Invalid input syntax”

Quick fix

If you have encountered the error message “invalid input syntax” while working with the PostgreSQL database, you are dealing with a common error. The full error message usually looks like this, or similar:

ERROR: invalid input syntax for type numeric: «b» at character 26

The error occurs when the user attempts to insert a value that does not match the column type. If the problem is not caused by an attempt to enter a faulty, it may be an application side error that needs to be solved by the developer.

Best Solution: https://severalnines.com/blog/decoding-postgresql-error-logs

8. PostgreSQL error

«Permission denied for database»

Quick fix

“Permission denied for database” is a group of PostgreSQL errors that is in most cases caused by a lack of user privileges. Depending on the reason for the error to occur, common error messages include “Permission denied for relation”, “Permission denied for sequence”, or “Permission denied for schema”. All of these PostgreSQL errors are related privilege issues.

To solve the problem, there are several possible troubleshooting methods:

Make sure that the user is granted the Connect privilege. To grant the privilege, use the command “GRANT CONNECT ON DATABASE userdb TO user ;”.
To read data from the table, users require the privilege Connect, Create, Temporary and Select. Whenever you are granting access to a new user, make sure that all necessary privilege is granted.
The permission denied error can also be caused by a missing user. If this is the case, update all PostgreSQL users with the proper password and sync it with the Plesk panel.

Best Solution: https://bobcares.com/blog/permission-denied-for-database-postgres/

9. PostgreSQL error

“42703” or “Column does not exist”

Quick fix

Another common error code with PostgreSQL database is 42703 as well as the error message “column does not exist”. This error indicates either that the requested column does not it exist, or that the query is not correct.

There are many possible reasons for this issue. To get started, check your query for any mistakes. Often, the error is caused by a lack of quotes. If this is the case, add double quotes to the column name, then try again.

Best Solution: https://stackoverflow.com/questions/52007364/postgresql-column-doesnt-exist

10. PostgreSQL error

“Could not extend file” or “No space left on device”

Quick fix

Lack of disk space is a common problem that can easily be prevented. If you are facing the error message “no space left on device”, there is not enough space on your disk to run the database.

To solve the problem, free some space on the disk and make sure to avoid running out of disk space in the future.

Best Solution: https://www.percona.com/blog/2020/06/05/10-common-postgresql-errors/

Choose your solution: Bugfix or replacement

With PRTG you’ll never have to deal
with PostgreSQL errors again. Forever.

Trusted by 500,000 users and recognized
by industry analysts as a leader

“Fantastic network and infrastructure monitoring solution that is easy to deploy and easier still to use. Simply the best available.”

Definition of PostgreSQL column does not exist exception

PostgreSQL column does not exist exception occurs when we have used column did not exist in the table or it will occur when the used column name has lower case name and we have used upper case in our query. We can avoid this exception in many ways like double-quote the column name for which column we have to get the exception. We can also check the column name which existed in the table or not. If the column name does not exist in the table then we need to create the same into the table.

Syntax:

Below is the syntax of the column name that does not exist except in PostgreSQL.

Column name does not exist exception using select

Select name_of_column from name_of_table limit (number);

Error: column name_of_column not exist
Line1: select name_of_column from name_of_table limit (number);

Column name does not exist exception using insert

Insert into name_of_table (name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN) values (Value_of_column1, Value_of_column2, Value_of_column3, …, Value_of_columnN);

ERROR: column “name_of_column” does not exist
LINE 1: insert into name_of_table (name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN) values (Value_of_column1, Value_of_column2, Value_of_column3, …, Value_of_columnN)

Column name does not exist exception using update

update name_of_table set name_of_column = (value_of_column) where condition;

ERROR: column “name_of_column” does not exist
LINE 1: update name_of_table set name_of_column = value_of_column where name_of_column = ‘value_of_column’;

Column name does not exist exception using delete

Delete from name_of_table where name_of_column = value_of_column;

ERROR: column “name_of_column” does not exist
LINE 1: delete from name_of_table where name_of_column = value_of_column;

Below is the parameter description syntax of column name does not exist exception in PostgreSQL.

Select –Column name does not exist exception will display while we have to execute select operation on the specified column.
Insert – Column name does not exist exception will display while we have to execute insert operation on specified column.
Update – Column name does not exist exception will display while we have to execute update operation on the specified column.
Delete – Column name does not exist exception will display while we have to execute delete operation on the specified column.
Name of column –This is defined as the column name from which we have received exception that column name does not exist.
Name of the table –This is defined as the table name from which we have received the exception that the column name does not exist.

How column does not exist exception raised in PostgreSQL?

The column does not exist exception occurs when a column does not exist in the table. If the searching column does not exist in the table then it will raise the exception that the column does not exist in the table.
The below example shows that if a searching column does not exist in the table it will give the exception that the column name does not exist.

d+ test_col; select ID_Name from test_col;

In the above example, we have used the ID_Name column for searching the data from the test_col table but it will issues an error that the column name does not exist in the table, because the ID_Name column does not exist in the table.
To select, update, delete, and insert the data into the table we need to defined the correct column name which was we have searched the data.
Also, we need to the defined the column name in a double quote if our column name contains the mixed letter.
The below example shows that we need to define the double quote when we have used a mixed letter column in operations.

select address from test_col; d+ test_col;

In the above example, we have used the address column which contains the mixed letter, after using this column it will issue an error because it will contain the mixed column letter.

Examples

Below is the example of a column does not exist exception. We have using a test_col table to describe an example of a column name that does not exist exception.
Below are the data and table structure of the test_col table.

select * from test_col; d+ test_col;

1. Column name does not exist exception using select

The below example shows that the column name does not exception using select operations.

select Name, ID from test_col; select "Name", "ID" from test_col;

2. Column name does not exist exception using insert

The below example shows that the column name does not exception using insert operations.

insert into test_col (ID, Name, AddRess, PhoNe) values (3, 'ABC', 'Mumbai', 1234567890); insert into test_col ("ID", "Name", "AddRess", "PhoNe") values (4, 'PQR', 'Mumbai', 1234567890);

3. Column name does not exist exception using update

The below example shows that the column name does not exception using update operations.

update test_col set ID = 5 where Name = 'ABC'; update test_col set “ID” = 5 where “Name” = 'ABC'; select * from test_col;

4. Column name does not exist exception using delete

The below example shows that the column name does not exception using delete operations.

delete from test_col where ID = 4; delete from test_col where “ID” = 4; select * from test_col;

How to avoid column does not exist exception?

We can avoid the column does not exist exception by specifying the name of the column. Below is the example to avoid the column does not exist exception.

select test_name from test_col; select “Name” from test_col;

We can also avoid the exception by using the double quote to the column. Below example shows that use a double quote to avoid exception.

select Name from test_col; select “Name” from test_col;

Conclusion

The column does not exist exception occurs in PostgreSQL when we have not used a specified column name while doing any operations. Also, it occurs when we have not used a double quote to the mismatch case letter column name in PostgreSQL.

What are you doing?

edit2: Remember folks, when you change your env variables, you have to restart your server/pm2 instance =) This fixed it, although I would expect a more helpful error message when host, port etc. are undefined.

Hey guys,

I am switching my node/express app from mysql to postgresql. Everything was pretty seamless except I had to swap some data types. When I try to run the following command I get an error.

edit: Looks like something else is up. Sequelize throws the same error for all other queries, including relation "users" does not exist. I know this was marked as support, but mysql was working perfectly before changing to postgres, so I imagine it should also work now.

const [ serviceUser, created ] = await ServiceUserAccountModel.findOrCreate({ 
          where: { service_user_id: '123456' },
        });

relation "serviceUserAccounts" does not exist. or with users relation "users" does not exist

const userModel = Sequelize.define('user', {
    // has many ServiceUserAccounts

    id: { 
      type: DataTypes.INTEGER, 
      primaryKey: true, 
      autoIncrement: true 
    },

    email: {
      type: DataTypes.STRING,
      allowNull: false
    },

    age: {
      type: DataTypes.SMALLINT,
      allowNull: false
    },

    gender: {
      type: DataTypes.STRING,
      allowNull: false
    },

    first_name: {
      type: DataTypes.STRING,
      allowNull: true
    },

    last_name: {
      type: DataTypes.STRING,
      allowNull: true
    }
       
  });

    const serviceUserAccountsModel = Sequelize.define('serviceUserAccount', {
      // belongs to User
      
      id: { 
        type: DataTypes.INTEGER, 
        primaryKey: true, 
        autoIncrement: true 
      },

      display_name: {
        type: DataTypes.STRING,
        allowNull: true
      },

      email_address: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      service_id: { 
        type: DataTypes.SMALLINT,
        allowNull: true,
      },

      service_user_id: { 
        type: DataTypes.STRING,
        allowNull: true,
      },
  
      refresh_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      access_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      token_type: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      expiration_date: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
  
      storage_limit: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      trashed_storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      
    });

// Relations

module.exports = function( database ){
  const User = database.models.user.user;
  const ServiceUserAccounts = database.models.user.serviceUserAccounts;
  
  User.hasMany(ServiceUserAccounts);
  ServiceUserAccounts.belongsTo(User);
};

What do you expect to happen?

As it was working perfectly before with mysql dialect, I expect it to also work with Postgresql.

What is actually happening?

relation "serviceUserAccounts" does not exist. I’m able to run the query just fine in pgAdmin, so it must be something with sequelize. What am I missing?

Here’s the gist with the stacktrace
https://gist.github.com/Mk-Etlinger/569093387a0cb97699acfcba3994f59d

Any ideas? I checked my permissions and it came back public, $user.

also looked here but no luck:
https://stackoverflow.com/questions/28844617/sequelize-with-postgres-database-not-working-after-migration-from-mysql

https://stackoverflow.com/questions/946804/find-out-if-user-got-permission-to-select-update-a-table-function-in-pos

Dialect: postgres
Dialect version: XXX
Database version: 9.6.2
Sequelize version: ^4.38.1
Tested with latest release: Yes, 4.39.0

Note : Your issue may be ignored OR closed by maintainers if it’s not tested against latest version OR does not follow issue template.

Источник

Introduction

Solution

There are a lot of PostgreSQL errors out there. Way too much, right?

1. PostgreSQL error

“Error: syntax error at or near ‘grant’”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

2. PostgreSQL error

“Error 42501” or “Permission Denied”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

3. PostgreSQL error

“Error 1053” or “The service did not respond to the start or control request in a timely fashion”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

4. PostgreSQL error

“Role does not exist”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

5. PostgreSQL error

“Relation does not exist”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

6. PostgreSQL error

”Could not connect to server: no such file or directory” or “Could not connect to server: connection refused”

7. PostgreSQL error

“Invalid input syntax”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

8. PostgreSQL error

«Permission denied for database»

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

9. PostgreSQL error

“42703” or “Column does not exist”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

10. PostgreSQL error

“Could not extend file” or “No space left on device”

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="time blue" height="30" src="https://hlassets.paessler.com/common/files/icons/time-blue.svg" width="30" />Quick fix

Choose your solution: Bugfix or replacement

With PRTG you’ll never have to deal with PostgreSQL errors again. Forever.

Trusted by 500,000 users and recognized by industry analysts as a leader

Definition of PostgreSQL column does not exist exception

How column does not exist exception raised in PostgreSQL?

Examples

How to avoid column does not exist exception?

Conclusion

Recommended Articles

What are you doing?

What do you expect to happen?

What is actually happening?

There are a lot of PostgreSQL errors out there.
Way too much, right?

Quick fix

Quick fix

Quick fix

Quick fix

Quick fix

Quick fix

Quick fix

Quick fix

Quick fix

With PRTG you’ll never have to deal
with PostgreSQL errors again. Forever.

Trusted by 500,000 users and recognized
by industry analysts as a leader