Search code examples
sqlpostgresqljdbcinsertionpgadmin

Tuples are not inserted sequentially in database table?

I am trying to insert 10 values of the format "typename_" + i where i is the counter of the loop in a table named roomtype with attributes typename (primary key of SQL type character varying (45)) and samplephoto (it can be NULL and I am not dealing with this for now). What seems strange to me is that the tuples are inserted in different order than the loop counter increments. That is:

typename_1
typename_10
typename_2
typename_3
...

I suppose it's not very important but I can't understand why this is happening. I am using PostgreSQL 9.3.4, pgAdmin III version 1.18.1 and Eclipse Kepler.

The Java code that creates the connection (using JDBC driver) and makes the query is:

import java.sql.*;
import java.util.Random;

public class DBC{
    Connection _conn;

    public DBC() throws Exception{

        
        try{
            Class.forName("org.postgresql.Driver");
        }catch(java.lang.ClassNotFoundException e){
            java.lang.System.err.print("ClassNotFoundException: Postgres Server JDBC");
            java.lang.System.err.println(e.getMessage());
            throw new Exception("No JDBC Driver found in Server");
        }

        try{
            
            _conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/hotelreservation","user", "0000");
            ZipfGenerator p = new ZipfGenerator(new Random(System.currentTimeMillis()));
            _conn.setCatalog("jdbcTest");
            Statement statement = _conn.createStatement();
            String query;
            
            for(int i = 1; i <= 10; i++){
                String roomtype_typename = "typename_" + i;
                query = "INSERT INTO roomtype VALUES ('" + roomtype_typename + "','" + "NULL" +"')";
                System.out.println(i);
                statement.execute(query);
            }


        }catch(SQLException E){
            java.lang.System.out.println("SQLException: " + E.getMessage());
            java.lang.System.out.println("SQLState: " + E.getSQLState());
            java.lang.System.out.println("VendorError: " + E.getErrorCode());
            throw E;
        }

    }

}

But what I get in pgAdmin table is:

pgAdmin table

Solution

  • This is a misunderstanding. There is no "natural" order in a relational database table. While rows are normally inserted in sequence to the physical file holding a table, a wide range of activities can reshuffle physical order. And queries doing anything more than a basic (non-parallelized) sequential scan may return rows in any opportune order. That's according to standard SQL.

    The order you see is arbitrary unless you add ORDER BY to the query.

    pgAdmin3 by default orders rows by the primary key (unless specified otherwise). Your column is of type varchar and rows are ordered alphabetically (according to your current locale). All by design, all as it should be.

    To sort rows like you seem to be expecting, you could pad some '0' in your text:

    ...
typename_0009
typename_0010
...

    The proper solution would be to have a numeric column with just the number, though.

    You may be interested in . You may also be interested in a serial column.