Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing an application for searching, using text search of Postgresql. I am a beginner in Database. I created a Gin index and i am trying to get data from it. My code is

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class JDBCPostgressRetrieve {

    private static String Database = "jdbc:postgresql://localhost/search";
    private static String user = "postgres";
    private static String password = "abcd";

    public static String getDatabase() {
        return Database;
    }

    public static String getUser() {
        return user;
    }

    public static String getPassword() {
        return password;
    }

    public void retrieve(int limit) {

        Statement stmt = null;
        Connection connection = null;
        ResultSet rs = null; 
        int i = 0;
        try {

            Class.forName("org.postgresql.Driver");

        } catch (ClassNotFoundException e) {

            System.out.println("Where is your PostgreSQL JDBC Driver? "
                    + "Include in your library path!");
            e.printStackTrace();
        }

        try {

            connection = DriverManager.getConnection(
                    JDBCPostgressRetrieve.getDatabase(),
                    JDBCPostgressRetrieve.getUser(),
                    JDBCPostgressRetrieve.getPassword());

            if (connection == null) {
                System.out.println("Failed to make connection!");
            } else {

                DateFormat dateFormat = new SimpleDateFormat(
                        "yyyy/MM/dd HH:mm:ss");
                Date date = new Date();

                // Creating Statement for query execution
                stmt = connection.createStatement();
                // creating Query String                
                String query = "SELECT (person_name,email,mobile_number,key_skills,current_employer)\n" +
                                "FROM bhubneshwar\n" +
                                "WHERE textsearchable_index_col @@ to_tsquery('MBA & HR')\n" +
                                "LIMIT 10";

                // excecuting query
                rs = stmt.executeQuery(query);
                while (rs.next()) {
                    // Didplaying data of tables
                    System.out.println("Name : " + rs.getString("person_name"));
                    System.out.println("email : " + rs.getString("email"));
                    System.out.println("mobile_number : " + rs.getString("mobile_number"));
                    System.out.println("key_skills : " + rs.getString("key_skills"));
                    System.out.println("current_employer : " + rs.getString("current_employer"));
                }
            }

        } catch (SQLException e) {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
        }
        finally {
            try {
                rs.close();
                stmt.close();
                connection.close();
            }catch(SQLException e) {
                System.err.println("SQLException while closing connection. "+ e.getMessage());
            }           
        }
    }

    public static void main(String args []) {
        JDBCPostgressRetrieve ret = new JDBCPostgressRetrieve();
        ret.retrieve(100);
    }
}

textsearchable_index_col is the index column in the table. But after running the code I am getting an error.

org.postgresql.util.PSQLException: The column name person_name was not found in this ResultSet.
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2727)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2567)
at com.presciencesoft.postgres.search.JDBCPostgressRetrieve.retrieve(JDBCPostgressRetrieve.java:77)
at com.presciencesoft.postgres.search.JDBCPostgressRetrieve.main(JDBCPostgressRetrieve.java:103)

In this way can I retrieve from index as table ?? This query is running perfectly in command prompt.

share|improve this question
2  
The ( ) around the selected columns looks strange to me. Please try and run this on the command line, chances are 95% of the code you posted isn't relevant (and we're not all Java developers here). –  Mat Jun 14 '13 at 13:27

1 Answer 1

up vote 2 down vote accepted

The parentheses form a row-constructor, so your query returns a single column row literal, essentially an anonymous composite type.

Compare:

regress=> SELECT (1,2);
  row  
-------
 (1,2)
(1 row)

regress=> SELECT 1, 2;
 ?column? | ?column? 
----------+----------
        1 |        2
(1 row)

You would've quickly realised this if you'd run the query on the psql command line (or in PgAdmin-III if you prefer GUIs).

A second misunderstanding: You don't retrieve data from an index1. You get data from a table. The database engine might used any indexes that are present to speed your query up, but you're still getting data from the table. You can see this if you explain a command to get its query plan.

1: ... well, arguably until PostgreSQL 9.2's with index-only scans, but that's a bit of an implementation detail.

share|improve this answer
    
Sorry my mistake. Nice explanation –  Neeraj Jun 14 '13 at 14:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.