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

[Puneet Kishor]

On Oct 7, 2011, at 6:39 AM, "Gery ." <gamejihou at hotmail.com> wrote:

> 
> 
> Thanks Puneet, it's enough what I have now in the table, the scheme you showed (a & b into table c) is actually what I did, but the order just in one table (4000 rows) changed I dont know why.



Because, as I said in my earlier email, you can't rely on the db to store data the way you want. Generally, if you don't provide any ORDER BY, and if the table has a primary key, the data will come out ordered by the primary key (data in indexes are ordered, which is why the db is able to find your results quickly). So, remember -- don't worry too much about how the data go in. Keep your mind on how you want the data to come out. Make sure you have a column in your table that you can control, and use that to ORDER BY when you SELECT.

> The other table (50 rows) remained the same after being inserted in c. Both a and b had their correlative IDs. Anyway, in order to update my table I just order by id and know which one are the ones to be updated. Thanks.
> 
> 
> ----------------------------------------
>> 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 20:07:56 +0000
>> 
>> 
>> 
>> 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.
>> 
>> 
>> 
>> ----------------------------------------
>>> 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