[postgis-users] FW: Problem with correlative ID after using INSERT INTO

[Puneet Kishor]

On Oct 6, 2011, at 3:07 PM, Gery . wrote:

> 
> 
> Thanks Sandro, what means "You want to order at query time."? I did a simple "select * from both_lines order by id;" and worked perfectly, I was thinking about creating a new table, based on the headers of the both_line table and copy all the ordered values into this new table, but after reading for a while I noticed that the geometrycolumn information will be lost and adding the same into it can cause some problems.
> 


A good database (and, perhaps even a not-so-good database) guarantees that you can get out exactly what you put in it. It doesn't guarantee any specific way of storing the data inside. The way it stores the data is supposed to be hidden from you, the user. In order to get the data out a certain way, a certain order, you have to specify the ORDER BY clause at SELECT time.

So, don't worry about how you INSERT the data. Once you have inserted it, the order in which you inserted it is not guaranteed. Instead, worry about the way you take out the data, hence, the ORDER BY clause during SELECT.

I believe you are inserting data from tables "a" and "b" into table "c". If you want the data to have a certain order, use one of the columns to specify that order. If you don't have a column you can control (for example, if your ID column is auto incrementing SERIAL), then create a new column in tables "a" and "b", update that column in both tables with values that you specify, and then use that column to order the data when you take them out of the table "c".


> 
> 
> ----------------------------------------
>> From: gamejihou at hotmail.com
>> To: postgis-users at postgis.refractions.net
>> Subject: FW: Problem with correlative ID after using INSERT INTO
>> Date: Thu, 6 Oct 2011 18:41:21 +0000
>> 
>> 
>> 
>> Thanks Paul, I put ORDER BY ID before Select and after it but didn't work. Could you provide me please an example based on the code below?
>> 
>> INSERT INTO both_lines (ID, SURVEY, PROFILE, TYPE, SOURCE, LENGTH_KM, LENGTH_NM, COMMENTS, GEOM)
>> SELECT ID, SURVEY, PROFILE, TYPE, SOURCE, LENGTH_KM, LENGTH_NM, COMMENTS, GEOM FROM line;
>> 
>> Thanks,
>> 
>> 
>> ----------------------------------------
>>> From: gamejihou at hotmail.com
>>> To: postgis-users at postgis.refractions.net
>>> Subject: Problem with correlative ID after using INSERT INTO
>>> Date: Thu, 6 Oct 2011 12:17:54 +0000
>>> 
>>> 
>>> 
>>> Hello,
>>> 
>>> I'm trying to get a table, composed of other two tables, but the correlative ID of one of them changes, it "jumps", this is what happens:
>>> 
>>> **************
>>> id | name
>>> ...
>>> 34 | 79-051
>>> 35 | 79-052
>>> 52 | 79-076
>>> 214 | 96-008A
>>> 215 | | 96-009
>>> 36 | 79-053
>>> 37 | 79-055
>>> ....
>>> ****************
>>> 
>>> this shape was inserted as follows:
>>> 
>>> shp2pgsql -s 4326 -W UTF-8 -g geom -I -D /home/postgres/shp/line/line.shp | psql -U postgres -d mydb
>>> 
>>> after checking it in my db, it looks like:
>>> 
>>> **********
>>> ...
>>> 34 | 79-051
>>> 35 | 79-052
>>> 36 | 79-053
>>> 37 | 79-055
>>> ...
>>> **********
>>> 
>>> this is the part of the code I used to insert this table:
>>> 
>>> INSERT INTO both_lines (ID, SURVEY, PROFILE, TYPE, SOURCE, LENGTH_KM, LENGTH_NM, COMMENTS, GEOM)
>>> SELECT ID, SURVEY, PROFILE, TYPE, SOURCE, LENGTH_KM, LENGTH_NM, COMMENTS, GEOM FROM line;
>>> 
>>> I tried adding "ORDER BY ID" at the end of the second line (after "line") but didn't work.
>>> 
>>> Any suggestions are very welcome, thanks in advance,
>>> 
>>> Gery
>>> 
>> 
> 		 	   		  
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users