org.postgresql.util.PSQLException:ERROR: relation "contacts" does not exist
843859Sep 19 2006 — edited Sep 20 2006Hello, I am pretty new to java and NetBeans. I am getting an error that appears when NetBeans tries to insert a record into a PostgreSQL database. I am using the files that are from a Sun Java tutorial.
-------------------------------------
insert into contacts(id, first_name, last_name)
org.postgresql.util.PSQLException: ERROR: relation "contacts" does not exist
-------------------------------------
I think PostgreSQL needs a SQL statement to formatted like the following: "schema"."tablename"
To include parenthesis around the schema name followed by a dot, followed by the table name. But in the insert statement that NetBeans is creating, just includes the the table name. I have tried to modify the code in different ways, but I can't get it to format the SQL statement correctly.
I have included the entire statement below. Thanks again for any help.
------------------------------------------------------------------------------------------------
/**
* Updates the selected contact or inserts a new one (if we are
* in the insert mode).
*
* @param firstName first name of the contact.
* @param lastName last name of the contact.
* @param title title of the contact.
* @param nickname nickname of the contact.
* @param displayFormat display format for the contact.
* @param mailFormat mail format for the contact.
* @param emails email addresses of the contact.
*/
public void updateContact(String firstName, String lastName, String title, String nickname,
int displayFormat, int mailFormat, Object[] emails) {
int selection = getContactSelection().getMinSelectionIndex();
Statement stmt = null;
try {
if (!insertMode) {
rowSet.absolute(selection+1);
}
Connection con = rowSet.getConnection();
stmt = con.createStatement();
String sql;
if (insertMode) {
sql = "insert into public." + CONTACTS_TABLE + "(" + CONTACTS_KEY + ", " + CONTACTS_FIRST_NAME + ", "
+ CONTACTS_LAST_NAME + ", " + CONTACTS_TITLE + ", " + CONTACTS_NICKNAME + ", "
+ CONTACTS_DISPLAY_FORMAT + ", " + CONTACTS_MAIL_FORMAT + ", " + CONTACTS_EMAIL_ADDRESSES
+ ") values ((case when (select max(" + CONTACTS_KEY + ") from " + CONTACTS_TABLE + ")"
+ "IS NULL then 1 else (select max(" + CONTACTS_KEY + ") from " + CONTACTS_TABLE + ")+1 end), "
+ encodeSQL(firstName) + ", " + encodeSQL(lastName) + ", " + encodeSQL(title) + ", "
+ encodeSQL(nickname) + ", " + displayFormat + ", " + mailFormat + ", "
+ encodeSQL(encodeEmails(emails)) + ")";
} else {
sql = "update public." + CONTACTS_TABLE + " set ";
sql += CONTACTS_FIRST_NAME + '=' + encodeSQL(firstName) + ", ";
sql += CONTACTS_LAST_NAME + '=' + encodeSQL(lastName) + ", ";
sql += CONTACTS_TITLE + '=' + encodeSQL(title) + ", ";
sql += CONTACTS_NICKNAME + '=' + encodeSQL(nickname) + ", ";
sql += CONTACTS_DISPLAY_FORMAT + '=' + displayFormat + ", ";
sql += CONTACTS_MAIL_FORMAT + '=' + mailFormat + ", ";
sql += CONTACTS_EMAIL_ADDRESSES + '=' + encodeSQL(encodeEmails(emails));
sql += " where " + CONTACTS_KEY + '=' + rowSet.getObject(CONTACTS_KEY);
}
System.out.println(sql);
stmt.executeUpdate(sql);
rowSet.execute();
} catch (SQLException sqlex) {
sqlex.printStackTrace();
} finally {
setInsertMode(false);
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlex) {
sqlex.printStackTrace();
}
}
}
}