Thread: Special table names
Hello everyone,
I googled I swear. And yet:
postgres=# select * from user;
current_user
--------------
postgres
(1 row)
postgres=# \c postgres
psql (8.4.2)
WARNING: Console code page (852) differs from Windows code page (1250)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "postgres".
postgres=# select * from user;
current_user
--------------
postgres
(1 row)
postgres=# \dt
No relations found.
OK so there's a table 'user' which is not located in 'postgres' db, so
where is it?
Other special tables?
Regards,
mk
In response to Marcin Krol : > OK so there's a table 'user' which is not located in 'postgres' db, so > where is it? > > Other special tables? http://www.postgresql.org/docs/8.4/static/functions-info.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 26 February 2010 14:21, Marcin Krol <mrkafk@gmail.com> wrote: > Hello everyone, > > I googled I swear. And yet: > > > postgres=# select * from user; > current_user > -------------- > postgres > (1 row) > > > postgres=# \c postgres > psql (8.4.2) > WARNING: Console code page (852) differs from Windows code page (1250) > 8-bit characters might not work correctly. See psql reference > page "Notes for Windows users" for details. > You are now connected to database "postgres". > > postgres=# select * from user; > current_user > -------------- > postgres > (1 row) > > > postgres=# \dt > No relations found. > > OK so there's a table 'user' which is not located in 'postgres' db, so where > is it? > > Other special tables? In addition to what Andreas said, try "\dS" (and "\?"). You should probably use "CREATE ROLE", "ALTER ROLE", "DROP ROLE" etc. instead of manipulating pg_user directly. -- Michael Wood <esiotrot@gmail.com>
Michael Wood wrote: > > In addition to what Andreas said, try "\dS" (and "\?"). Thanks, that's useful -- but that still doesn't let me tell where 'user' table (view? alias?) comes from. > You should probably use "CREATE ROLE", "ALTER ROLE", "DROP ROLE" etc. > instead of manipulating pg_user directly. I have no intention to do that; I just created test db via ORM called SQLAlchemy, with table named 'user'. Then I drop into psql, do 'select * from user' to see what's in there and I don't see what I expected to see: ts=# \c ts; ts=# select * from user; current_user -------------- postgres (1 row) So I'm wondering if there are other special table names I should avoid. Regards, mk
Marcin Krol <mrkafk@gmail.com> writes:
> Michael Wood wrote:
>> In addition to what Andreas said, try "\dS" (and "\?").
> Thanks, that's useful -- but that still doesn't let me tell where 'user'
> table (view? alias?) comes from.
It isn't a table. It's a function, equivalent to CURRENT_USER.
(Both of those are mandated by the SQL spec; we'd certainly never have
invented functions called without parentheses on our own desires.)
regards, tom lane
On 26 February 2010 16:51, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Marcin Krol <mrkafk@gmail.com> writes: >> Michael Wood wrote: >>> In addition to what Andreas said, try "\dS" (and "\?"). > >> Thanks, that's useful -- but that still doesn't let me tell where 'user' >> table (view? alias?) comes from. > > It isn't a table. It's a function, equivalent to CURRENT_USER. > > (Both of those are mandated by the SQL spec; we'd certainly never have > invented functions called without parentheses on our own desires.) Ah. So that's where that confusion comes from. -- Michael Wood <esiotrot@gmail.com>
On 26 February 2010 15:35, Marcin Krol <mrkafk@gmail.com> wrote:
> Michael Wood wrote:
>
>>
>> In addition to what Andreas said, try "\dS" (and "\?").
>
> Thanks, that's useful -- but that still doesn't let me tell where 'user'
> table (view? alias?) comes from.
>
>> You should probably use "CREATE ROLE", "ALTER ROLE", "DROP ROLE" etc.
>> instead of manipulating pg_user directly.
>
> I have no intention to do that; I just created test db via ORM called
> SQLAlchemy, with table named 'user'.
It seems SQLAlchemy lied to you about creating the table, or perhaps
you did not check an error code or something.
blah=> create table user (id int);
ERROR: syntax error at or near "user"
LINE 1: create table user (id int);
^
Note that it says "syntax error" and not "relation already exists".
> Then I drop into psql, do 'select * from user' to see what's in there and I
> don't see what I expected to see:
>
> ts=# \c ts;
>
> ts=# select * from user;
> current_user
> --------------
> postgres
> (1 row)
>
>
> So I'm wondering if there are other special table names I should avoid.
I suppose any function in the list Andreas pointed you at that don't
have parentheses. Also anything called pg_something. Not sure what
else.
--
Michael Wood <esiotrot@gmail.com>
Michael Wood wrote: > It seems SQLAlchemy lied to you about creating the table, or perhaps > you did not check an error code or something. > > blah=> create table user (id int); > ERROR: syntax error at or near "user" > LINE 1: create table user (id int); Apparently it did lie, bc I was able to write objects to that table without problems IIRC. > ^ > > Note that it says "syntax error" and not "relation already exists". SQLA typically does various stuff with table names, uses a lot of aliases etc. (well it has to considering potential conflicts) so I wouldn't be surprised if other DBs don't have smth like function or table 'user' available globally everywhere, so SQLA author had to do some PG-specific hack around that. > I suppose any function in the list Andreas pointed you at that don't > have parentheses. Also anything called pg_something. Not sure what > else. Thanks!
Tom Lane wrote: > It isn't a table. It's a function, equivalent to CURRENT_USER. > > (Both of those are mandated by the SQL spec; we'd certainly never have > invented functions called without parentheses on our own desires.) Curioser and curioser. That + SQLA hack == one confused developer trying to name a seemingly innocent table 'users'. :-) Thanks to everyone! Regards, mk
Marcin Krol <mrkafk@gmail.com> writes:
> Michael Wood wrote:
>> It seems SQLAlchemy lied to you about creating the table, or perhaps
>> you did not check an error code or something.
>>
>> blah=> create table user (id int);
>> ERROR: syntax error at or near "user"
>> LINE 1: create table user (id int);
> Apparently it did lie, bc I was able to write objects to that table
> without problems IIRC.
It's fairly likely that what SQLAlchemy actually did was to double-quote
"user" in the commands it issued for you. Observe:
regression=# create table user (id int);
ERROR: syntax error at or near "user"
LINE 1: create table user (id int);
^
regression=# create table "user" (id int);
CREATE TABLE
regression=# select * from user;
current_user
--------------
postgres
(1 row)
regression=# select * from "user";
id
----
(0 rows)
Without quotes, user is a reserved word that selects a SQL-standard
function. With quotes, it's just an identifier that you can use to
name a table if you choose.
regards, tom lane