CODEWITHSUNDEEP
javasqlpostgresql
rkubapl
rkubapl

Reputation: 3

org.postgresql.util.PSQLException: ERROR: syntax error at or near "7"

I'm trying to create private chats in my app, but when I try to create the table I get this error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "7" Position: 14

Code:

 public static String checkIfChatExists(String code, String friend) throws SQLException {
        String i = LoginManager.checkCode(code);
        if(i.equals("code-not-exists")) {
            return "invalid-code";
        }

        ResultSet rs = conn.prepareStatement("SELECT * FROM pwchats").executeQuery();
        while (rs.next()) {
            if(rs.getString("user1").equals(i) && rs.getString("user2").equals(friend) || rs.getString("user1").equals(friend) && rs.getString("user2").equals(i)) {
                return "exists";
            }
        }
        PreparedStatement pre = conn.prepareStatement("INSERT INTO pwchats(user1, user2) VALUES(?, ?)");
        pre.setString(1, i);
        pre.setString(2, friend);
        pre.execute();
        PreparedStatement getChatId  = conn.prepareStatement("SELECT * FROM pwchats WHERE user1 = ? AND user2 = ?;");
        getChatId.setString(1, i);
        getChatId.setString(2, friend);
        ResultSet rss = getChatId.executeQuery();
        while (rss.next()) {
            PreparedStatement chat = conn.prepareStatement("CREATE TABLE " + rss.getInt("id") + "chat (username text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), avatar text NOT NULL, message text NOT NULL, id serial NOT NULL);");
            chat.execute(); //ERROR
            return "done";
        }
        return "wat";
    }

Upvotes: 0

Views: 1367

Answers (1)

GMB
GMB

Reputation: 222702

The error that you are seeing likely comes from there:

preparedStatement chat = conn.prepareStatement("CREATE TABLE " + rss.getInt("id") + "chat (...);");

Usually in SQL, a table name cannot begin with a number. Postgres would still allow that if you were to surround the table name with double quotes, but then you would need to double quote the table name everytime your query it, which could be tedious.

One solution would to put the number at the end of the table name, like:

preparedStatement chat = conn.prepareStatement("CREATE TABLE chat" + rss.getInt("id") + " (...);");

Demo on DB Fiddle:

create table 7chat(x int);

ERROR:  syntax error at or near "7"
LINE 1: create table 7chat(x int);
                     ^

create table "7chat"(x int);
-- works

create table chat7(x int);
--works

Upvotes: 1

Related Questions