Postgres: What Is the Query 'Select * from User' Actually Doing

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

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

Illegal argument to function in Postgres when doing select on a view with pgp_sym_decrypt() expression

So my issue was that it turns out that stage environment migrations that added the view weren't provided with encryption password (second argument to pgp_sym_decrypt), which meant that view got compiled with null value as password, which is why it gives "Illegal argumen to function".

Would've been much easier to fix if pgcrypto didn't give such cryptic error messages.

Avoid multiple duplicate records due to JOIN needed for filtering

You can just use GROUP BY "user".id (because it is its table's primary key, all other column in its table functionally depends on it).

select    id, name
from      "user"
left join item on item.user_id = "user".id
left join foob on foob.user_id = "user".id
where     item.key = 'my_key' and foob.value_t = 'vtz'
group by  "user".id

BTW I'm not sure if those are the names of your real tables. Funny things happen if you select * from user.

php - fetching associative array of pg_execute() prepared statement returns false

Thanks to Don't Panic's comment I renamed my user table to utiliser and everything worked as it should have.

It should also be noted that this could be circumvented by using double quotes on the table name (according to this answer), however, double quotes are evil so better to just stay away.

Here's the full table of reserved words to avoid as table/column names.

Uncaught PDOException: column does not exist

In PostgreSQL, user is a system information function, equivalent to current_user that returns the "user name of current execution context" (quoted from the documentation.) You can check this question for more details.

That's why you had to quote it when creating the table (CREATE TABLE "user" instead of CREATE TABLE user.)

The userid column from your table obviously doesn't exist in the return of that function. You may be able to get it to work by quoting "user" in your SELECT query, but I think the best solution is to just change the name of the table to something that doesn't already mean something to your database. Maybe users instead?

---

Related Topics