postgres: What is the query 'select * from user' actually doing?

Question

In psql, if one types 'select * from user' you'll get something like the following back:

 current_user 
--------------
 postgres

What is user in this context?

Answer 1

In this context, user is a reserved internal Postgres function that represents the current user logged in to the database.

This query can also be written as:

SELECT user;

Which should yield the same thing. Note, if you want to actually reference or create a table named user you'll have to use quotes, or fully qualify the schema it lives in. For example:

CREATE TABLE "user"
(
  id int2 not null
);

will work but:

CREATE TABLE user
(
  id int2 not null
);

Will yield an error.

Here's a reference for other system information functions:

http://www.postgresql.org/docs/9.0/static/functions-info.html

Answer 2

See the Postgresql Documentation on system functions.

Basically "select * from user" is one of the Postgresql-specific ways of finding the current user. It is functionally the same as using the current_user function eg: "select current_user()".

Other special functions that can be used as tables in queries include:

current_catalog
current_schema

Answer 3

If You are looking for list of users i should seek in pg_user table; SELECT * FROM pg_user;

Your Query get all data from result of special function named user. That function returns username of current_user.