Jump to content

Generate Unique ID


ILMV

Recommended Posts

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

Link to comment
Share on other sites

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.

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.