[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: OT: Calling SQL experts

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

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 );
cerberus=# create table bar_table ( id INTEGER );
cerberus=# CREATE INDEX foo_idx ON foo_table (id);
cerberus=# CREATE INDEX foo_idx ON bar_table (id);
ERROR:  relation "foo_idx" already exists
cerberus=# CREATE INDEX bar_idx ON bar_table (id);

Chris Boot

Reply to: