Answer

On INSERT statements using an external table, will the rows in the external file retain the same order? On SELECT statements from external tables, will the returned data be sorted in the same way as the file?

In NPS 4.0 and later, the answer to both questions is yes.

If data is inserted using a certain order into an external table, the original sorting will be maintained and the selected data will be sorted in the same way as the file.

The following examples demonstrate the sorting behavior:

test(admin)=> CREATE EXTERNAL TABLE INT_STR_EXT (
test(admin)(>    i   integer,
test(admin)(>    str varchar(8)
test(admin)(> )
test(admin)-> USING (
test(admin)(> DATAOBJECT ('/tmp/int_str_ext.dat')
test(admin)(> DELIMITER '|'
test(admin)(> NULLVALUE ''
test(admin)(> )
test(admin)-> ;
CREATE EXTERNAL TABLE

test(admin)=> select * from INT_STR order by 1;
 i |  str
---+--------
 1 | uno
 2 | dos
 3 | tres
 4 | cuatro
(4 rows)

test(admin)=> insert into INT_STR_EXT select * from INT_STR;
INSERT 0 4

test(admin)=> select * from INT_STR_EXT;
 i |  str
---+--------
 3 | tres
 2 | dos
 1 | uno
 4 | cuatro
(4 rows)

test(admin)=> select * from INT_STR_EXT where i between 2 and 3;
 i | str
---+------
 2 | dos
 3 | tres
(2 rows)

test(admin)=> \q
[nz ~]$ cat /tmp/int_str_ext.dat
1|uno
2|dos
3|tres
4|cuatro