Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Folks, Not being a PostgreSQL expert I can't comment about OIDs except to say that, from the discussion so far it appears that: 1. They are numeric (32 bit integers) 2. Will restart numbering once Max(32BitInteger) is reached (though very few database tables with transactional spatial data would hit this limit in the short or medium term). 3. OIDs may be dropped by the PostgreSQL team at some stage (a virtual concern). That data in relational databases should have primary keys (and other contraints) is a given that all on this forum acknowledge. However, what you cannot assume is that the primary key of any one table will be a single, integer, column. A primary key may be: A. Text, Timestamp, Float or Integer etc datatypes. B. May include multiple attributes. Generally, GIS like SINGLE column INTEGER primary keys or unique columns/indexes. I have yet to find a GIS that supported anything else. (I am keen to hear of one that does support A and B above.) So, where an existing primary key is not based on a single integer column, for GIS access one is left with limited options: 1. Add a new unique integer column (identity or trigger populated from a sequence) - This may not be possible is the data modeller / application does not want this done. 2. Or use the OID. Interesting discussion. regards Simon On Sun, 10 May 2009 06:12:29 +1000, pcr...@pcreso.com wrote: Hi guys, Andreas is correct in that Postgres OIDs do not form a reliable primary key for a table, and has explained why. However, in terms of providing an ad hoc column that QGIS can use to uniquely identify a record, it is very unlikely that oids will not work. In theory it is possible that a given table may have two records with the same oid value, but the odds against it are enormous, just not quite impossible. I think it will be useful in this discussion to distinguish between a QGIS unique identifier a database primary key. They do not have to be the same thing, and often are not, but when appropriate convenient, a PK can also be a QGIS unique identifier. Brent Wood --- On Sat, 5/9/09, Andreas Neumann a.neum...@carto.net wrote: From: Andreas Neumann a.neum...@carto.net Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: jp.alcant...@geo-st.com, PostGIS Users Discussion postgis-users@postgis.refractions.net Cc: pcr...@pcreso.com Date: Saturday, May 9, 2009, 11:04 PM Hi Juan, First let me say the following: Please include the QGIS developers list in the further discussion, since this seem to be more QGIS related than Postgis related? In fact, the QGIS developers may have already changed the pkey behavior in more recent releases - so please include them in the discussion. Regarding OIDs: those aren't really stable. They increase until a certain limit is reached and then they start again at zero or one. Here is what the PostgreSQL manual says about the topic: The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. (http://www.postgresql.org/docs/8.4/static/datatype-oid.html) So to be on the safe side, I wouldn't use oids as primary keys. In addition, I don't know if oids are still the same if you dump your data and import it on another machine. After all, primary keys are used as unique, stable identifiers, which I don't think that oids can reliably deliver. Thanks, Andreas Juan Pedro Pérez Alcántara wrote: Hello all, thank you for all your responses, it is my first post to the list and I thank you all for your interest in my question. Don't get me wrong for what I'm going to say, for, as said, I appreciate all the ideas you say to me. Although I come from a GIS background and I'm a geographer myself, the center of all my processes and projects is and always will be the geographic relational model in PostGIS, so I fully understand the importance of PK and the technicalities of building and managing a relational model (in fact, this is a lecture I teach at the university here in Spain). This issue of working with PK in QGIS has been around for me for quite a long time, but, given that I've been always dealed with it with no problems (int4 PK) I haven't had the need to find a better solution. That's because I've been always able to find a int4 PK suitable for my tables, just being because I designed them or because I receive external data from, as you say, shapefiles-driven-minds :) of fellow geographers here at the university (no critics at all, I'm unable to perform the complex geophysical and geosocial analysis they perform either) and it were easy to adapt in the model. But now I'm receiving data from
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Hallo Juan Pedro I don't know enough about how OIDS are handled to discuss that, but I don't really understand why you can't use a column with unique values. As suggested earlier, if you use alter table yourTableName add column aColumnName serial UNIQUE ; then you will get unique values created for each row and new default values in the future. Then you don't redefine your PK. Then what is the problem? This new column will just be there to help QGis to keep things in order. I guess this is what is happening in the background in every application working with tables without primary keys, that they have som hidden unique index for the rows. I'm I missing something? Cheers Nicklas 2009-05-09 Juan Pedro P�rez Alc�ntara wrote: Hello all, thank you for all your responses, it is my first post to the list and I thank you all for your interest in my question. Don't get me wrong for what I'm going to say, for, as said, I appreciate all the ideas you say to me. Although I come from a GIS background and I'm a geographer myself, the center of all my processes and projects is and always will be the geographic relational model in PostGIS, so I fully understand the importance of PK and the technicalities of building and managing a relational model (in fact, this is a lecture I teach at the university here in Spain). This issue of working with PK in QGIS has been around for me for quite a long time, but, given that I've been always dealed with it with no problems (int4 PK) I haven't had the need to find a better solution. That's because I've been always able to find a int4 PK suitable for my tables, just being because I designed them or because I receive external data from, as you say, shapefiles-driven-minds :) of fellow geographers here at the university (no critics at all, I'm unable to perform the complex geophysical and geosocial analysis they perform either) and it were easy to adapt in the model. But now I'm receiving data from the government that are well designed, with a model-mind approach, so this is not always possible, due to the use of non-int4 PK or multi-field PK. I have experimented a little more with the OIDS approach and it seems that they don't interfere with the actual PK fields of the tables. It seems that although you created the table WITH OIDS, PK conflicts are still successfully detected, so it seems that Postgre don't think of the OID field as part of the PK fields and such. So, what do you think? Are my findings OK, or is there a, for me, unforeseen reason why I should not use OIDS? Again, thank you all. Juan Pedro Pérez Alcántara On Fri, 2009-05-08 at 14:09 -0700, pcr...@pcreso.com wrote: Hi, This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles. You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer explicit than implicit. They are a key reason for the success of the relational model, and relational databases. For me, the QGIS issue is not that it requires a PK, but that it can only use an int PK, wheras the DB supports PK's of any datatype, as well as composite keys using more than one column. (Also, there is at least one bug in QGIS when it comes to identifying a suitable column in a view, as in some unusual cases it fails to correctly identify a suitable column, but that is a separate issue, and only in particular circumstances) Generally this is not really a big issue, as QGIS can also use a unique index, and the DB supports multiple unique indexes on a table. So to work with QGIS, you can still use your non-int PK, or not use one at all, just add a new unique index on a suitable integer column for QGIS. A simple way to do this is: alter table add column qkey serial; this is an easy way of creating an integer column automatically populated as a sequence, so it is filled with unique values as it is created. To be used by QGIS, this column must have a unique index created on it once it is there. create unique index _qkey_idx on (qkey); You can, of course, use your own names for the table, column amp; index HTH, Brent Wood --- On Sat, 5/9/09, Ben Madinwrote: From: Ben Madin Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: PostGIS Users Discussion Date: Saturday, May 9, 2009, 12:23 AM Juan, Agreed that this can be annoying (especially in light of some other GIS packages which don't seem to care at all, or for instance shapefiles. I have had the same frustration when constructing geometries in a query or for a view, or any type of aggregate query really. This is also a problem for querymaps in MapServer. The alternative I settled with was creating a false key - for instance if the view was a makeline from points, making an id that was the gid
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Hi Juan, First let me say the following: Please include the QGIS developers list in the further discussion, since this seem to be more QGIS related than Postgis related? In fact, the QGIS developers may have already changed the pkey behavior in more recent releases - so please include them in the discussion. Regarding OIDs: those aren't really stable. They increase until a certain limit is reached and then they start again at zero or one. Here is what the PostgreSQL manual says about the topic: The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. (http://www.postgresql.org/docs/8.4/static/datatype-oid.html) So to be on the safe side, I wouldn't use oids as primary keys. In addition, I don't know if oids are still the same if you dump your data and import it on another machine. After all, primary keys are used as unique, stable identifiers, which I don't think that oids can reliably deliver. Thanks, Andreas Juan Pedro Pérez Alcántara wrote: Hello all, thank you for all your responses, it is my first post to the list and I thank you all for your interest in my question. Don't get me wrong for what I'm going to say, for, as said, I appreciate all the ideas you say to me. Although I come from a GIS background and I'm a geographer myself, the center of all my processes and projects is and always will be the geographic relational model in PostGIS, so I fully understand the importance of PK and the technicalities of building and managing a relational model (in fact, this is a lecture I teach at the university here in Spain). This issue of working with PK in QGIS has been around for me for quite a long time, but, given that I've been always dealed with it with no problems (int4 PK) I haven't had the need to find a better solution. That's because I've been always able to find a int4 PK suitable for my tables, just being because I designed them or because I receive external data from, as you say, shapefiles-driven-minds :) of fellow geographers here at the university (no critics at all, I'm unable to perform the complex geophysical and geosocial analysis they perform either) and it were easy to adapt in the model. But now I'm receiving data from the government that are well designed, with a model-mind approach, so this is not always possible, due to the use of non-int4 PK or multi-field PK. I have experimented a little more with the OIDS approach and it seems that they don't interfere with the actual PK fields of the tables. It seems that although you created the table WITH OIDS, PK conflicts are still successfully detected, so it seems that Postgre don't think of the OID field as part of the PK fields and such. So, what do you think? Are my findings OK, or is there a, for me, unforeseen reason why I should not use OIDS? Again, thank you all. Juan Pedro Pérez Alcántara On Fri, 2009-05-08 at 14:09 -0700, pcr...@pcreso.com wrote: Hi, This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles. You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer explicit than implicit. They are a key reason for the success of the relational model, and relational databases. For me, the QGIS issue is not that it requires a PK, but that it can only use an int PK, wheras the DB supports PK's of any datatype, as well as composite keys using more than one column. (Also, there is at least one bug in QGIS when it comes to identifying a suitable column in a view, as in some unusual cases it fails to correctly identify a suitable column, but that is a separate issue, and only in particular circumstances) Generally this is not really a big issue, as QGIS can also use a unique index, and the DB supports multiple unique indexes on a table. So to work with QGIS, you can still use your non-int PK, or not use one at all, just add a new unique index on a suitable integer column for QGIS. A simple way to do this is: alter table table add column qkey serial; this is an easy way of creating an integer column automatically populated as a sequence, so it is filled with unique values as it is created. To be used by QGIS, this column must have a unique index created on it once it is there. create unique index table_qkey_idx on table(qkey); You can, of course, use your own names for the table, column index HTH, Brent Wood --- On Sat, 5/9/09, Ben Madin b...@remoteinformation.com.au wrote: From: Ben Madin b...@remoteinformation.com.au Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: PostGIS Users
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Hi guys, Andreas is correct in that Postgres OIDs do not form a reliable primary key for a table, and has explained why. However, in terms of providing an ad hoc column that QGIS can use to uniquely identify a record, it is very unlikely that oids will not work. In theory it is possible that a given table may have two records with the same oid value, but the odds against it are enormous, just not quite impossible. I think it will be useful in this discussion to distinguish between a QGIS unique identifier a database primary key. They do not have to be the same thing, and often are not, but when appropriate convenient, a PK can also be a QGIS unique identifier. Brent Wood --- On Sat, 5/9/09, Andreas Neumann a.neum...@carto.net wrote: From: Andreas Neumann a.neum...@carto.net Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: jp.alcant...@geo-st.com, PostGIS Users Discussion postgis-users@postgis.refractions.net Cc: pcr...@pcreso.com Date: Saturday, May 9, 2009, 11:04 PM Hi Juan, First let me say the following: Please include the QGIS developers list in the further discussion, since this seem to be more QGIS related than Postgis related? In fact, the QGIS developers may have already changed the pkey behavior in more recent releases - so please include them in the discussion. Regarding OIDs: those aren't really stable. They increase until a certain limit is reached and then they start again at zero or one. Here is what the PostgreSQL manual says about the topic: The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. (http://www.postgresql.org/docs/8.4/static/datatype-oid.html) So to be on the safe side, I wouldn't use oids as primary keys. In addition, I don't know if oids are still the same if you dump your data and import it on another machine. After all, primary keys are used as unique, stable identifiers, which I don't think that oids can reliably deliver. Thanks, Andreas Juan Pedro Pérez Alcántara wrote: Hello all, thank you for all your responses, it is my first post to the list and I thank you all for your interest in my question. Don't get me wrong for what I'm going to say, for, as said, I appreciate all the ideas you say to me. Although I come from a GIS background and I'm a geographer myself, the center of all my processes and projects is and always will be the geographic relational model in PostGIS, so I fully understand the importance of PK and the technicalities of building and managing a relational model (in fact, this is a lecture I teach at the university here in Spain). This issue of working with PK in QGIS has been around for me for quite a long time, but, given that I've been always dealed with it with no problems (int4 PK) I haven't had the need to find a better solution. That's because I've been always able to find a int4 PK suitable for my tables, just being because I designed them or because I receive external data from, as you say, shapefiles-driven-minds :) of fellow geographers here at the university (no critics at all, I'm unable to perform the complex geophysical and geosocial analysis they perform either) and it were easy to adapt in the model. But now I'm receiving data from the government that are well designed, with a model-mind approach, so this is not always possible, due to the use of non-int4 PK or multi-field PK. I have experimented a little more with the OIDS approach and it seems that they don't interfere with the actual PK fields of the tables. It seems that although you created the table WITH OIDS, PK conflicts are still successfully detected, so it seems that Postgre don't think of the OID field as part of the PK fields and such. So, what do you think? Are my findings OK, or is there a, for me, unforeseen reason why I should not use OIDS? Again, thank you all. Juan Pedro Pérez Alcántara On Fri, 2009-05-08 at 14:09 -0700, pcr...@pcreso.com wrote: Hi, This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles. You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer explicit than implicit. They are a key reason for the success of the relational model, and relational databases. For me, the QGIS issue is not that it requires a PK, but that it can only use an int PK, wheras the DB supports PK's of any datatype, as well as composite keys using more than one column. (Also, there is at least
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Unlike a primary key, oids are not indexed (until/unless you explicitly index them). They are also not a good thing to depend on long-term, because the PgSQL dev team threatens to remove them complete from time to time. They are definitely a deprecated feature at this point. P On Sat, May 9, 2009 at 1:12 PM, pcr...@pcreso.com wrote: Hi guys, Andreas is correct in that Postgres OIDs do not form a reliable primary key for a table, and has explained why. However, in terms of providing an ad hoc column that QGIS can use to uniquely identify a record, it is very unlikely that oids will not work. In theory it is possible that a given table may have two records with the same oid value, but the odds against it are enormous, just not quite impossible. I think it will be useful in this discussion to distinguish between a QGIS unique identifier a database primary key. They do not have to be the same thing, and often are not, but when appropriate convenient, a PK can also be a QGIS unique identifier. Brent Wood --- On Sat, 5/9/09, Andreas Neumann a.neum...@carto.net wrote: From: Andreas Neumann a.neum...@carto.net Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: jp.alcant...@geo-st.com, PostGIS Users Discussion postgis-users@postgis.refractions.net Cc: pcr...@pcreso.com Date: Saturday, May 9, 2009, 11:04 PM Hi Juan, First let me say the following: Please include the QGIS developers list in the further discussion, since this seem to be more QGIS related than Postgis related? In fact, the QGIS developers may have already changed the pkey behavior in more recent releases - so please include them in the discussion. Regarding OIDs: those aren't really stable. They increase until a certain limit is reached and then they start again at zero or one. Here is what the PostgreSQL manual says about the topic: The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. (http://www.postgresql.org/docs/8.4/static/datatype-oid.html) So to be on the safe side, I wouldn't use oids as primary keys. In addition, I don't know if oids are still the same if you dump your data and import it on another machine. After all, primary keys are used as unique, stable identifiers, which I don't think that oids can reliably deliver. Thanks, Andreas Juan Pedro Pérez Alcántara wrote: Hello all, thank you for all your responses, it is my first post to the list and I thank you all for your interest in my question. Don't get me wrong for what I'm going to say, for, as said, I appreciate all the ideas you say to me. Although I come from a GIS background and I'm a geographer myself, the center of all my processes and projects is and always will be the geographic relational model in PostGIS, so I fully understand the importance of PK and the technicalities of building and managing a relational model (in fact, this is a lecture I teach at the university here in Spain). This issue of working with PK in QGIS has been around for me for quite a long time, but, given that I've been always dealed with it with no problems (int4 PK) I haven't had the need to find a better solution. That's because I've been always able to find a int4 PK suitable for my tables, just being because I designed them or because I receive external data from, as you say, shapefiles-driven-minds :) of fellow geographers here at the university (no critics at all, I'm unable to perform the complex geophysical and geosocial analysis they perform either) and it were easy to adapt in the model. But now I'm receiving data from the government that are well designed, with a model-mind approach, so this is not always possible, due to the use of non-int4 PK or multi-field PK. I have experimented a little more with the OIDS approach and it seems that they don't interfere with the actual PK fields of the tables. It seems that although you created the table WITH OIDS, PK conflicts are still successfully detected, so it seems that Postgre don't think of the OID field as part of the PK fields and such. So, what do you think? Are my findings OK, or is there a, for me, unforeseen reason why I should not use OIDS? Again, thank you all. Juan Pedro Pérez Alcántara On Fri, 2009-05-08 at 14:09 -0700, pcr...@pcreso.com wrote: Hi, This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles. You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
hm - what's wrong with having a primary key in every table? Good DB-Design requires primary keys. Other tools will refuse to work with your data if you don't use primary keys as well. As an example, pgadmin3 requires a primary key if you want to edit the data in the grid view. If you use the datatype serial or a sequence it is not complicated at all to use primary keys. Andreas On Fri, May 8, 2009 1:18 pm, Juan Pedro Pérez Alcántara wrote: Hello, perhaps this is a silly one, but I'm having a hard time dealing with primary keys restrictions in Quantum GIS. Not always is possible or desirable to put a, sometimes, artificial int4 PK in some tables only to be able to load them in QGIS. Those restrictions are very frustrating. I have been messing around for a solution, and I have experimented with OIDS. This seems to be enough for QGIS, but I fear the behavior of OIDS. I don't like the idea of non-controlled PK in my tables. So my question is simple: does creating a table WITH OIDS means that the OIDS will be part of the PK of the table like you it or not, or you have to specify that the OIDS are part of the PK in the ADD CONSTRAINT statement? My hope is that PostgreSQL uses internally OIDS without interfering with the true PK of the table, while QGIS is happy by having them around. OIDS will not play any role in my model other than that. Greetings, Juan Pedro Pérez Alcántara ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Andreas Neumann http://www.carto.net/neumann/ http://www.svgopen.org/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
as said earlier it's really not that hard to add a colunm with the datatype serial wich means it will get a unique new value as default. And if you have a textbased primary key or any other reason for not wanting to cunstruct a new primary key qgis will be satisfied with a unique value. So just do alter table theTable add column uniqueID serial UNIQUE ; then you acn add and remove records without caring about your unique value-field Hope that helps Nicklas 2009-05-08 Ben Madin wrote: Juan, Agreed that this can be annoying (especially in light of some other GIS packages which don't seem to care at all, or for instance shapefiles. I have had the same frustration when constructing geometries in a query or for a view, or any type of aggregate query really. This is also a problem for querymaps in MapServer. The alternative I settled with was creating a false key - for instance if the view was a makeline from points, making an id that was the gid of the first * 10 + the gid of the second. That may help. It may not - but there are better minds than mine hovering nearby, so I look forward to their suggestions. cheers Ben On 08/05/2009, at 7:36 PM, Andreas Neumann wrote: hm - what's wrong with having a primary key in every table? Good DB- Design requires primary keys. Other tools will refuse to work with your data if you don't use primary keys as well. As an example, pgadmin3 requires a primary key if you want to edit the data in the grid view. If you use the datatype serial or a sequence it is not complicated at all to use primary keys. Andreas On Fri, May 8, 2009 1:18 pm, Juan Pedro Pérez Alcántara wrote: Hello, perhaps this is a silly one, but I'm having a hard time dealing with primary keys restrictions in Quantum GIS. Not always is possible or desirable to put a, sometimes, artificial int4 PK in some tables only to be able to load them in QGIS. Those restrictions are very frustrating. I have been messing around for a solution, and I have experimented with OIDS. This seems to be enough for QGIS, but I fear the behavior of OIDS. I don't like the idea of non-controlled PK in my tables. So my question is simple: does creating a table WITH OIDS means that the OIDS will be part of the PK of the table like you it or not, or you have to specify that the OIDS are part of the PK in the ADD CONSTRAINT statement? My hope is that PostgreSQL uses internally OIDS without interfering with the true PK of the table, while QGIS is happy by having them around. OIDS will not play any role in my model other than that. Greetings, Juan Pedro Pérez Alcántara ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Andreas Neumann http://www.carto.net/neumann/ http://www.svgopen.org/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Ben Madin REMOTE INFORMATION t : +61 8 9192 5455 f : +61 8 9192 5535 m : 0448 887 220 Broome WA 6725 b...@remoteinformation.com.au Out here, it pays to know... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] OIDS, PostGIS and Quantum GIS
Hi, This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles. You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer explicit than implicit. They are a key reason for the success of the relational model, and relational databases. For me, the QGIS issue is not that it requires a PK, but that it can only use an int PK, wheras the DB supports PK's of any datatype, as well as composite keys using more than one column. (Also, there is at least one bug in QGIS when it comes to identifying a suitable column in a view, as in some unusual cases it fails to correctly identify a suitable column, but that is a separate issue, and only in particular circumstances) Generally this is not really a big issue, as QGIS can also use a unique index, and the DB supports multiple unique indexes on a table. So to work with QGIS, you can still use your non-int PK, or not use one at all, just add a new unique index on a suitable integer column for QGIS. A simple way to do this is: alter table table add column qkey serial; this is an easy way of creating an integer column automatically populated as a sequence, so it is filled with unique values as it is created. To be used by QGIS, this column must have a unique index created on it once it is there. create unique index table_qkey_idx on table(qkey); You can, of course, use your own names for the table, column index HTH, Brent Wood --- On Sat, 5/9/09, Ben Madin b...@remoteinformation.com.au wrote: From: Ben Madin b...@remoteinformation.com.au Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Saturday, May 9, 2009, 12:23 AM Juan, Agreed that this can be annoying (especially in light of some other GIS packages which don't seem to care at all, or for instance shapefiles. I have had the same frustration when constructing geometries in a query or for a view, or any type of aggregate query really. This is also a problem for querymaps in MapServer. The alternative I settled with was creating a false key - for instance if the view was a makeline from points, making an id that was the gid of the first * 10 + the gid of the second. That may help. It may not - but there are better minds than mine hovering nearby, so I look forward to their suggestions. cheers Ben On 08/05/2009, at 7:36 PM, Andreas Neumann wrote: hm - what's wrong with having a primary key in every table? Good DB-Design requires primary keys. Other tools will refuse to work with your data if you don't use primary keys as well. As an example, pgadmin3 requires a primary key if you want to edit the data in the grid view. If you use the datatype serial or a sequence it is not complicated at all to use primary keys. Andreas On Fri, May 8, 2009 1:18 pm, Juan Pedro Pérez Alcántara wrote: Hello, perhaps this is a silly one, but I'm having a hard time dealing with primary keys restrictions in Quantum GIS. Not always is possible or desirable to put a, sometimes, artificial int4 PK in some tables only to be able to load them in QGIS. Those restrictions are very frustrating. I have been messing around for a solution, and I have experimented with OIDS. This seems to be enough for QGIS, but I fear the behavior of OIDS. I don't like the idea of non-controlled PK in my tables. So my question is simple: does creating a table WITH OIDS means that the OIDS will be part of the PK of the table like you it or not, or you have to specify that the OIDS are part of the PK in the ADD CONSTRAINT statement? My hope is that PostgreSQL uses internally OIDS without interfering with the true PK of the table, while QGIS is happy by having them around. OIDS will not play any role in my model other than that. Greetings, Juan Pedro Pérez Alcántara ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users --Andreas Neumann http://www.carto.net/neumann/ http://www.svgopen.org/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Ben Madin REMOTE INFORMATION t : +61 8 9192 5455 f : +61 8 9192 5535 m : 0448 887 220 Broome WA 6725 b...@remoteinformation.com.au Out here, it pays to know... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
