Re: OT: Calling SQL experts

[Chris Boot <bootc@bootc.net>]

Alan Chandler wrote:

> Can someone tell me the scope of the various SQL names.  I have googled around 
> all afternoon looking for a document that defines it and can find none.
>
> In particular, I am trying to convert the textpattern package to use a 
> postgres database rather than the mysql one.
>
> One of the characteristics of textpattern is that it allows all table names to 
> have a (setup defined) prefix so that two instances of textpattern can run in 
> the same database.  Some of these tables have columns which form indexes.
>
> In mysql these are declared implicitly within the CREATE TABLE statement.  For 
> Postgres I need to explicitly declaire them with a CREATE INDEX statement.  
> Without knowing the scope of the INDEX name identifier, I can't tell whether 
> I have to add the prefix on to all index names.
>
> Can someone point me at a SQL tutorial that describes to what extent (across 
> what surrounding concept (database, table, ...) various names have to be 
> unique
>
> TIA
>  
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
      \h for help with SQL commands
      \? for help on internal slash commands
      \g or terminate with semicolon to execute query
      \q to quit

cerberus=# create table foo_table ( id INTEGER );
CREATE TABLE
cerberus=# create table bar_table ( id INTEGER );
CREATE TABLE
cerberus=# CREATE INDEX foo_idx ON foo_table (id);
CREATE INDEX
cerberus=# CREATE INDEX foo_idx ON bar_table (id);
ERROR:  relation "foo_idx" already exists
cerberus=# CREATE INDEX bar_idx ON bar_table (id);
CREATE INDEX
cerberus=#

--
Chris Boot
bootc@bootc.net
http://www.bootc.net/