insert wrong order in postgresql (using pro c)

Question

I wanted to insert data to psql from a text file with proc, after inserting data with my proc program ,the order of data in psql was different from the text file sometimes.

there was no any sql error in the process, I found the problem when I select * from table.

Did there have some method could confirm the order which I inserted the same as the order in database(psql) ?

thanks!

for example:

table: testtable
id int
name char(20)

txt file:
100 amy
200 john
300 joy
400 ruby

but the data in psql is like:

200 john
300 joy
400 ruby
100 amy

my program:

EXEC  SQL BEGIN DECLARE SECTION;
int id;
char name[20];
int id_ind, name_ind;
EXEC  SQL ENDDECLARE SECTION;

main ()
{
   EXEC CONNECT TO　SQL ....

   while ( still have data ){
     read_data_from_file()
     put_data_in_host_varable();

     EXEC SQL INSERT INTO testtable( id, name )
     VALUES (:id INDICATOR id_ind, :name INDICATOR name_ind)
   }
   EXEC SQL COMMIT WORK;
   EXEC SQL DISCONNECT ALL;
}

Trinimon · Accepted Answer · 2015-04-08T10:09:03.367

Check the documentation: "If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on.

So the typical solution is to add a unique, incremented integer identifier and sort by it in your SELECT statement:

SELECT * 
  FROM table
 ORDER BY id;

Alternatively you could use a column like INSERT_DATE and fill it with a time stamp.

p.s.: check data type SERIAL in chapter 8.1.4 at the Numeric Types documentation

score 1 · Answer 2 · answered Apr 08 '15 at 10:00

1

Data loaded into relational database has no inherent order. If you want specific order of the selected data you should use "order by" in your select. It is not an error.

answered Apr 08 '15 at 10:00

hipe

802
6
8

score 0 · Answer 3 · answered Apr 08 '15 at 09:58

When you retrieve data from relational databases without explicitly specifying the order you want the data in (using an order by clause) you'll get a set in random order back. If you care about the order then specify it when you retrieve the data. Don't worry about the order the database choose to store items in.

A statement like select * from ... has no order and you should expect none.

insert wrong order in postgresql (using pro c)

3 Answers3