ILMV Posted March 20, 2009 Share Posted March 20, 2009 I am creating an overview (query) in pgAdmin on PostgreSQL. I need to generate a unique ID, it can be totally random but really I want something simple like the row number. Does anyone know any way of implementing this? ILMV Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/ Share on other sites More sharing options...
btherl Posted March 21, 2009 Share Posted March 21, 2009 What does the id uniquely identify? Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-789992 Share on other sites More sharing options...
ILMV Posted March 22, 2009 Author Share Posted March 22, 2009 Nothing... but I need to generate a unique ID field to satisfy the PHP framework we are using. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791181 Share on other sites More sharing options...
trq Posted March 22, 2009 Share Posted March 22, 2009 maybe Ive missed something but, are you looking for an auto increment type field for postgres? Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791196 Share on other sites More sharing options...
jackpf Posted March 22, 2009 Share Posted March 22, 2009 Excuse me for swaying slightly off topic, but what's Postgresql like? All I've ever used is mysql, just wondering what else is out there. Are there really any advantages to using it? Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791208 Share on other sites More sharing options...
ILMV Posted March 23, 2009 Author Share Posted March 23, 2009 Yes it is off topic, please start your own thread. Here's the deal... I am creating a query within postgreSQL (call it a query, view, overview, whatever)... It is ripping information from two tables, there are no fields that, when combined in the query are unique. I could make a unique field by concatenating lots of other fields together, but that looks crap, and uses a lot of code. I need to generate in this query, a unique number, auto incremented would be good, the number could be for example the row number, this is a feature used within Oracle and I believe is defined as rownum. I need to have a unique ID on the query because without it the framework we are using pukes up, it will not allow us to order or page the results of the query. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791368 Share on other sites More sharing options...
trq Posted March 23, 2009 Share Posted March 23, 2009 Sounds like a sequence might be waht your looking for. man. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791376 Share on other sites More sharing options...
btherl Posted March 23, 2009 Share Posted March 23, 2009 An example would help a lot. Is it something like this: Table a aid integer adata string Table b bdata string bdata2 string SELECT * FROM a JOIN b ON (adata = bdata) The tables are combined, but there's no obvious unique identifier. Is that the kind of situation you're looking at? Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791431 Share on other sites More sharing options...
ILMV Posted March 23, 2009 Author Share Posted March 23, 2009 Yes I believe so, we can create a unique identifier by concatinating several fields, but this looks ugly, and takes up a lot of code. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791582 Share on other sites More sharing options...
btherl Posted March 23, 2009 Share Posted March 23, 2009 I'm pretty sure postgres doesn't have this unfortunately. You could simulate it by creating a temporary table with a unique identifier. But there's no idea of a persistent query which has row numbers associated with it in postgres. There are cursors though .. if your setup allows you to use them (not sure how that would work with multiple page views), then a query using a cursor has specific row numbers for the result. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-791606 Share on other sites More sharing options...
rivan Posted June 6, 2009 Share Posted June 6, 2009 You should use sequence to solve this (like somebody mentioned) - here is example create sequence blahahaha; -- sequence creation - do this only once then always append this nextval part to your query (to generate column with unique values) select nextval('blahahaha') as unique_column, * from some_table Regards Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-850625 Share on other sites More sharing options...
btherl Posted June 7, 2009 Share Posted June 7, 2009 Rivan, if you do that then you won't get the same sequence numbers each time. That means it's not a unique identifier for a result row. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-850953 Share on other sites More sharing options...
rivan Posted June 7, 2009 Share Posted June 7, 2009 Well, I didn't understand that he needs unique IDs across runs - if that is the case then it is better to have unique column with sequence in every table and then to concatenate only that columns (I think that wouldn't look so ugly) Anyway it sounds pretty meaningless to have persistent non-existent-in-database id of the row - what for you can use it if you are not storing it somewhere? Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-851102 Share on other sites More sharing options...
btherl Posted June 8, 2009 Share Posted June 8, 2009 Well he said this: I need to generate a unique ID field to satisfy the PHP framework we are using. I'm assuming that means the ids need to stay the same when he runs the query again. Maybe it doesn't, I don't know exactly how he's using them. It might be that even random ids are good enough, as long as he doesn't reference them again later (ie they are purely to satisfy the framework, and aren't actually used by the framework). I'm thinking now that concatenating might be the best solution. Then you don't need to worry about matching ids to rows, as the rows themselves give the id. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-851615 Share on other sites More sharing options...
rivan Posted June 9, 2009 Share Posted June 9, 2009 I agree wth you if persistance is needed (why original poster doesn't clear that :-) ) Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-852317 Share on other sites More sharing options...
the_oliver Posted June 22, 2009 Share Posted June 22, 2009 When createing the table you can create an id field setting it to use the sequence automaticatly for its next id. (no need to do this through your table every time you insert)... ALTER TABLE test ALTER id SET DEFAULT nextval('inc_test'); Then you will never need to touch it again. Alternativly you can tern on oid's in your config, and it will be done for you. The only problem with oid's is that they will not be the same if you dump and restore, or duplicat the database. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-861425 Share on other sites More sharing options...
btherl Posted June 23, 2009 Share Posted June 23, 2009 the_oliver, the issue here is that he wants a unique id for a VIEW, not for a table. A sequence won't work as a view is generated dynamically, so the sequence numbers would be different every time the view was queried. Quote Link to comment https://forums.phpfreaks.com/topic/150343-generate-unique-id/#findComment-861614 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.