PostgreSQL equivalent to SQLite ROWID

Asked Jan 06 '18 at 16:39

Active Jan 13 '18 at 07:53

Viewed 1,397 times

With the new IDENTITY type in Postgresql 10, I wonder if there is anything similar to ROWID in SQLite which is a pseudo-column to get the primary key.

I want to do something like:

SELECT ROWID FROM Table

in Postgresql and get the primary key.

Edit: This is not a duplicate of this question because ROWID in Oracle does not give you the primary key.

edited Jan 13 '18 at 06:20

asked Jan 06 '18 at 16:39

antoyo

9,835
3
43
77

aw you beat me by 4 seconds lol – udbhavs Jan 06 '18 at 16:43
I somehow beat myself also. – speciesUnknown Jan 06 '18 at 16:45
Sorry to all of you, but Oracle's `ROWID` is different than SQLite's `ROWID`. – antoyo Jan 06 '18 at 16:51
So what does SQLite's `rowid` do? – a_horse_with_no_name Jan 06 '18 at 17:37
@a_horse_with_no_name It gives the primary key while `ROWID` in Oracle is the address of the row (which is not the primary key). – antoyo Jan 06 '18 at 21:54
A primary key is a constraint on one or more columns, not a function – a_horse_with_no_name Jan 06 '18 at 22:51
That's why the DBMS that supports such a pseudo-variable have a different behavior when the primary key is compound (look at `$IDENTITY` in SQL Server). – antoyo Jan 06 '18 at 23:32
@a_horse_with_no_name If you don't know the answer, please don't post such comments. – antoyo Jan 06 '18 at 23:33
1

Postgres (like many other DBMS) does not automatically create a primary key. You need to declare it based on one or more columns. If you don't do that. `ctid` is the closest thing you get: a value that uniquely identifies a row (but might change over time for one specific row). So if you want a primary key, declare one. If you need the values to be created automatically use a `serial` or `identity` column. – a_horse_with_no_name Jan 13 '18 at 08:00

PostgreSQL equivalent to SQLite ROWID

0 Answers0