Обсуждение: schemas and paths with the alter statement

I have to first admit that I am very green at this. I
thought that one could refer to a table in a fully
qualified path... public.testtable

I am fooling around with webobjects and EOModeler. at
one point it creates the sql to create my table
structure and it does something like:

ALTER TABLE public.test ADD CONSTRAINT public.test_PK
PRIMARY KEY (test);

I assumed that it would work. Did I miss something in
the docs that say you can not do this? Does "ALTER
TABLE" not work with 'public.' before the table name?

on 7.4.6

aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
public.test_PK PRIMARY KEY (test);
ERROR:  syntax error at or near "." at character 46

but it works if I remove the 'public.' from both
places

aswebtest=# ALTER TABLE test ADD CONSTRAINT test_PK
PRIMARY KEY (test);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create
implicit index "test_pk" for table "test"
ALTER TABLE


Ted

    
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

Theodore Petrosky <tedpet5@yahoo.com> writes:
> ALTER TABLE public.test ADD CONSTRAINT public.test_PK
> PRIMARY KEY (test);

Constraints don't have schema-qualified names.  Try

ALTER TABLE public.test ADD CONSTRAINT test_PK PRIMARY KEY (test);

The error message was pointing to the correct place BTW.
        regards, tom lane

Theodore Petrosky wrote:
> I assumed that it would work. Did I miss something in
> the docs that say you can not do this? Does "ALTER
> TABLE" not work with 'public.' before the table name?
> 
> on 7.4.6
> 
> aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
> public.test_PK PRIMARY KEY (test);
> ERROR:  syntax error at or near "." at character 46

Are you sure it's not the constraint that is causing the problem (he 
says, counting 46 characters in). I think a table constraint is by 
definition in the same schema as its table.

--   Richard Huxton  Archonet Ltd

On Tue, 2005-02-22 at 11:06 -0800, Theodore Petrosky wrote:
> I have to first admit that I am very green at this. I
> thought that one could refer to a table in a fully
> qualified path... public.testtable
> ...
> ALTER TABLE public.test ADD CONSTRAINT public.test_PK
> PRIMARY KEY (test);
> ...
> aswebtest=# ALTER TABLE public.test ADD CONSTRAINT
> public.test_PK PRIMARY KEY (test);
> ERROR:  syntax error at or near "." at character 46

it not the first public that is the problem, but the
second one, as you might discover if you count 46
characters.

you cannot put the constraint in a different schema than
the table, it seems.

gnari

Wow...in less than 5 minutes....

Thanks

Ted

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Theodore Petrosky <tedpet5@yahoo.com> writes:
> > ALTER TABLE public.test ADD CONSTRAINT
> public.test_PK
> > PRIMARY KEY (test);
> 
> Constraints don't have schema-qualified names.  Try
> 
> ALTER TABLE public.test ADD CONSTRAINT test_PK
> PRIMARY KEY (test);
> 
> The error message was pointing to the correct place
> BTW.
> 
>             regards, tom lane
> 

    
__________________________________ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo