5

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.

antoyo
  • 11,097
  • 7
  • 51
  • 82
  • 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? –  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 –  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. –  Jan 13 '18 at 08:00

0 Answers0