Postgres Pro
Downloads
Home   >   mailing lists

Thread: Delete coloumn

Delete coloumn

From
"Suhadi"
Date:
Please send to me how to delete coloumn in SQL.
Thank's

Re: Delete coloumn

From
Grant
Date:
> Please send to me how to delete coloumn in SQL.
> Thank's

http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/

Bookmark the above URL.

ALTER TABLE is what you are looking for:

http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-altertable.html

RE: Delete coloumn

From
"Robby Slaughter"
Date:
Unfortunately, there's no easy way to delete a column in a table in
PostgreSQL.
The standard SQL syntax is:
  ALTER TABLE tablename DROP COLUMN columnname;

But I repeat, this is NOT supported in postgresql.

If you really need to delete a column you can always just create a new
table with an identical definition but WITHOUT the offending column, and
then
SELECT INTO it. Example:

CREATE TABLE sample ( id   INTEGER, data TEXT, badcolumn DATE );

Now to delete the bad column table:

CREATE TABLE sample_copy ( id  INTEGER, data TEXT);

and then copy it all over:

SELECT id,data INTO sample_copy FROM sample;

and then you can DROP TABLE sample;

If you need the original table name, repeat the process of
creating a new table now and copying the data over.

Hope that helps!

-Robby Slaughter



-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Suhadi
Sent: Monday, August 06, 2001 11:16 PM
To: SQL
Subject: [SQL] Delete coloumn



Please send to me how to delete coloumn in SQL.
Thank's

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Delete coloumn

From
Grant
Date:
My response was incorrect! That's what you get for using phppgadmin all
day everyday. A transparent feeling that there is alter table drop column
support in postgresql :) Sorry.

> > Please send to me how to delete coloumn in SQL.
> > Thank's
> 
> http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/
> 
> Bookmark the above URL.
> 
> ALTER TABLE is what you are looking for:
> 
> http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-altertable.html

Delete Trigger Issue

From
"Sundararajan"
Date:
I am developing a db application in postgresql and i need to write a delete
trigger on one of the tables.

the environment is

table1

field1 varchar(64)
other fields.

table 2.

field1 varchar(64)
other fields

I need a delete trigger on the table 1, so that if I delete a row from table
1 , the corresponding rows from table 2 should also be deleted.

This is the code I have tried.

DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS 'BEGIN
delete from ports where appName=OLD.appName;RETURN OLD;
END;
'
LANGUAGE 'plpgsql';

Please help me with this, as my work is time bound.Even if the trigger is
written is SQL

Thanks
sundar

RE: Delete Trigger Issue

From
"Robby Slaughter"
Date:
Let me make sure I get this right:

CREATE TABLE table1( field1 varchar(64),   ... );

CREATE TABLE table2( field2 varchar(64),  ... );

and you want that whenever a row is deleted from table1
you want the SAME row to be deleted from table2?

here's what you want. First, a trigger:

CREATE TRIGGER update_table2
BEFORE DELETE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE update_table2_proc();

That trigger will make sure that each time a row is deleted
from table1, the proceudre update_table2_proc will
be called. And here is that procedure

CREATE FUNCTION update_table2_proc()
RETURNS opaque
AS
'BEGIN  DELETE FROM table2 WHERE field2 = new.field1;  RETURN new;END;'
LANGUAGE 'plpgsql';

That procedure just DELETEs all the rows in table2
that match up to field1 in the first table.
Of course, you might want to do a broader
LIKE matching if they are really VARCHAR fields.

Hope that helps!

-Robby Slaughter



-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Sundararajan
Sent: Tuesday, August 07, 2001 12:16 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Delete Trigger Issue


I am developing a db application in postgresql and i need to write a delete
trigger on one of the tables.

the environment is

table1

field1 varchar(64)
other fields.

table 2.

field1 varchar(64)
other fields

I need a delete trigger on the table 1, so that if I delete a row from table
1 , the corresponding rows from table 2 should also be deleted.

This is the code I have tried.

DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS 'BEGIN
delete from ports where appName=OLD.appName;RETURN OLD;
END;
'
LANGUAGE 'plpgsql';

Please help me with this, as my work is time bound.Even if the trigger is
written is SQL

Thanks
sundar


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Delete Trigger Issue

From
Stephan Szabo
Date:
On Tue, 7 Aug 2001, Sundararajan wrote:

> I am developing a db application in postgresql and i need to write a delete
> trigger on one of the tables.
> 
> I need a delete trigger on the table 1, so that if I delete a row from table
> 1 , the corresponding rows from table 2 should also be deleted.

Well, if you want the other semantics (not allow rows in 2 that don't
match rows in 1, etc) you could use a foreign key rather than an explicit
trigger.

> 
> This is the code I have tried.
> 
> DROP FUNCTION ApplicationsDeleteFn();
> CREATE FUNCTION ApplicationsDeleteFn()
> RETURNS OPAQUE
> AS '
>  BEGIN
> delete from ports where appName=OLD.appName;
>  RETURN OLD;
> 
>  END;
> '
> LANGUAGE 'plpgsql';

Are you actually making the trigger?  What does it do
when you try to make the trigger and then when you try
to delete?

RE: Delete coloumn

From
"Grigoriy G. Vovk"
Date:
Aug 6, 23:35 -0500, Robby Slaughter wrote:

> SELECT INTO it. Example:
>
> CREATE TABLE sample (
>   id   INTEGER,
>   data TEXT,
>   badcolumn DATE );
>
> Now to delete the bad column table:
>
> CREATE TABLE sample_copy (
>   id  INTEGER,
>   data TEXT);
>
> and then copy it all over:
>
> SELECT id,data INTO sample_copy FROM sample;

It is not correct. This statement used to _create_ _new_ table.
Correct is:
insert into sample_copy select id, data from sample;

>
> and then you can DROP TABLE sample;
>
> If you need the original table name, repeat the process of
> creating a new table now and copying the data over.
>
> Hope that helps!
>
> -Robby Slaughter

my best regards,
----------------
Grigoriy G. Vovk