Thread: ALTER TABLE for field modify...

ALTER TABLE for field modify...

From
MG
Date:
Hi!

How can I modify a field of table with ALTER TABLE? I want to modify the
constraint. The TABLE is now:
"ID"    int8    NOT NULL, UNIQUE

But I want this:
"ID" int8       REFERENCE "table2"


THanx!
Bye!
-----------------
Linux RedHat 7.1
-----------------


Re: ALTER TABLE for field modify...

From
"Nick Fankhauser"
Date:

alter table <tablename> drop constraint ID unique;
alter table <tablename> drop constraint ID not null;

alter table <tablename> add constraint ID references table2 ;

Here is where you can find the documentation for this:
The "alter":
http://www.postgresql.org/idocs/index.php?sql-altertable.html
The constraints:
http://www.postgresql.org/idocs/index.php?sql-createtable.html

regards,

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of MG
> Sent: Thursday, May 02, 2002 3:28 PM
> To: PostgreSQL List
> Subject: [ADMIN] ALTER TABLE for field modify...
>
>
>
> Hi!
>
> How can I modify a field of table with ALTER TABLE? I want to modify the
> constraint. The TABLE is now:
> "ID"    int8    NOT NULL, UNIQUE
>
> But I want this:
> "ID" int8       REFERENCE "table2"
>
>
> THanx!
> Bye!
> -----------------
> Linux RedHat 7.1
> -----------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: ALTER TABLE for field modify...

From
MG
Date:
On 03-May-2002 Nick Fankhauser wrote:
> alter table <tablename> drop constraint ID unique;
> alter table <tablename> drop constraint ID not null;
>
> alter table <tablename> add constraint ID references table2 ;
>

Hi!

It not works... :(
How can I change the properties of a field?

Bye!
-----------------
Linux RedHat 7.1
-----------------

Re: ALTER TABLE for field modify...

From
MG
Date:
On 03-May-2002 Nick Fankhauser wrote:
> alter table <tablename> drop constraint ID unique;
> alter table <tablename> drop constraint ID not null;
>
> alter table <tablename> add constraint ID references table2 ;
>

Hi!

It not works. :(
I try it:
proba=# create table "tabla1" ("ID" int8 NOT NULL UNIQUE, "nev" varchar(20) );
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tabla1_ID_key' for
table 'tabla1'
CREATE
proba=# create table "tabla2" ("ID2" int8 NOT NULL UNIQUE, "nev2" varchar(20) );
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tabla2_ID2_key' for
table 'tabla2'
CREATE

proba=# alter table tabla1 drop constraint ID not null;
ERROR:  parser: parse error at or near "not"
-------------------------------------------

What is the problem?


Bye!
-----------------
Linux RedHat 7.1
-----------------

failed to start up pgsql

From
Tao Wan
Date:
Hi, all,

pgsql7.1.2 is running on Sun solaris. When I start pgsql
server, I got start-up failed, error below:

IpcMemoryCreate: shmget(key=5432001, size=1777664, 03600)
failed: Invalid argument

please help me to figure it out. thanks

tony



Re: ALTER TABLE for field modify...

From
David Stanaway
Date:
On Tue, 2002-05-14 at 11:24, MG wrote:
>
> On 03-May-2002 Nick Fankhauser wrote:
> > alter table <tablename> drop constraint ID unique;
> > alter table <tablename> drop constraint ID not null;
> >
> > alter table <tablename> add constraint ID references table2 ;
> >
>
> Hi!
>
> It not works. :(
> I try it:
> proba=# create table "tabla1" ("ID" int8 NOT NULL UNIQUE, "nev" varchar(20) );
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tabla1_ID_key' for
> table 'tabla1'

In this case the row is ID (Case sensitive) so whenever you refer to it
you must refer to it as "ID"


> CREATE
> proba=# create table "tabla2" ("ID2" int8 NOT NULL UNIQUE, "nev2" varchar(20) );
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tabla2_ID2_key' for
> table 'tabla2'
> CREATE
>
> proba=# alter table tabla1 drop constraint ID not null;

Here you refer to it as ID which is case folded to id and does not refer
to the column named ID.


I don't know if that is your problem, but it is one problem with your
code. and it will no doubt show up in many more places.



--
David Stanaway

Attachment

Re: ALTER TABLE for field modify...

From
MG
Date:
David Stanaway <david@stanaway.net> wrote:
> In this case the row is ID (Case sensitive) so whenever
> you refer to it you must refer to it as "ID"
> ...
>
> > proba=# alter table tabla1 drop constraint ID not null;
>
> Here you refer to it as ID which is case folded to id and
> does not refer to the column named ID.
>

I tried it with "ID", but it doesn't work neither. :(
Why? What should I do?

THanx!
Bye!