Обсуждение: 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