Обсуждение: Delete coloumn
Please send to me how to delete coloumn in SQL. Thank's
> 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
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
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
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
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
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?
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