On Oct 7, 2011, at 6:39 AM, "Gery ." <[email protected]> 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: [email protected]
>> To: [email protected]
>> 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: [email protected]
>>> To: [email protected]
>>> 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: [email protected]
>>>> To: [email protected]
>>>> 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
> [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
