Add a new column to a view in SQL

Question

I have a database and I've made a view with the data I want. The data looks something like this:

    n     |   value |
----------+---------+
 50404791 |     112 | 
  5034591 |     164 |
 50280287 |      31 |

I want to add a column of text like this:

    n     |   value | status |
----------+---------+--------+
 50404791 |     112 |default | 
  5034591 |     164 |biggest |
 50280287 |      31 |smallest|

I tried Alter Table Table1 Add Column status text ; but it seems like text is not a data type. Any suggestions what to do?

Alter Table Table1 Add status text ; Can you try this?

Tom J Muthirenthi
– Tom J Muthirenthi

2017-03-22 08:38:40 +00:00
Commented Mar 22, 2017 at 8:38 — Tom J Muthirenthi
– Tom J Muthirenthi, Commented Mar 22, 2017 at 8:38

Vic · Accepted Answer · 2020-03-31 09:14:32Z

7

In Postgres you can use CREATE OR REPLACE without dropping the view and do the following

    CREATE OR REPLACE VIEW View1 AS
        SELECT value, status FROM Table 1;

Altering the original table will just alter that table without changes to the view.

edited Mar 31, 2020 at 9:14

answered Mar 31, 2020 at 9:08

Vic

1,9284 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jason Glez Over a year ago

Just to add extra information to this answer, the new column should be the last one, otherwise, it will throw some errors when executing the query.

villasv Over a year ago

Also this only works for adding new columns to the view. Removing columns requires a drop first.

Simon Logic Over a year ago

changing field type also requires view to be dropped.

etsa · Accepted Answer · 2017-03-22 08:37:04Z

4

Tested on Rextester.com (remember, avoid anyway to use SELECT * in a view)

CREATE TABLE TABLE1 (ID INTEGER);
CREATE VIEW V1 AS SELECT * FROM TABLE1;
INSERT INTO TABLE1 VALUES (0);

ALTER TABLE TABLE1 ADD COLUMN STATUS TEXT;    
INSERT INTO TABLE1 (ID, STATUS) VALUES (1, 'aaa');

SELECT * FROM V1;
DROP VIEW V1;

CREATE VIEW V1 AS SELECT * FROM TABLE1;
SELECT * FROM V1;

Output:

    id
1   0
2   1

    id  status
1   0   NULL
2   1   aaa

answered Mar 22, 2017 at 8:37

etsa

5,0501 gold badge9 silver badges18 bronze badges

Comments

Ray O'Donnell · Accepted Answer · 2017-03-22 08:32:56Z

3

You'll need to drop and re-create the view - you can't alter an existing view. And yes, text IS a data type.

answered Mar 22, 2017 at 8:32

Ray O'Donnell

7814 silver badges12 bronze badges

Comments

Piotr Rogowski · Accepted Answer · 2017-03-22 08:37:36Z

I found answer on postgresql.org

You can do from pgAdmin by right-clicking on the view and select CREATE SCRIPT uncomment: DROP VIEW ; and edit the CREATE VIEW to drop the column.

However if the view is used in other views you have to drop them all and recreate in sequence.

Alternatively you can delete the columns from the pg_catalog.pg_attribute table. Make sure however that you know you deleting only the one's you want to delete and not other tables columns... using the below:

delete from pg_attribute where attrelid = regclass 'yourviewname' and attname = 'columnnametodrop'

Best to first do couple of selects until you have the selection correct:

select attrelid::regclass as whatever, * from pg_attribute where attname = 'columnnametodrop'

Johan Nel.

Social Idiot · Accepted Answer · 2023-11-29 17:43:08Z

1

The way I do it is in pgAdmin, I right click on the view and click on "Scripts" -> "CREATE Script".

This gives me the create/replace structure which I add to:

CREATE OR REPLACE VIEW schema.view_name
 AS
 SELECT a."COLUMN1",
    a."COLUMN2",
    b."COLUMN1",
    b."COLUMN2",
    b."NEWCOLUMN1"
    FROM schema."TABLEA" a
    LEFT JOIN schema."TABLEB b ON a."ID" = b."ID"

ALTER TABLE shcema.view_name
    OWNER TO postgres;

Add the columns (ex: b."NEWCOLUMN1") and run. If I have a lot, I'll run a python script to format the columns to paste in.

Always save the original so you can revert!

answered Nov 29, 2023 at 17:43

Social Idiot

92 bronze badges

Collectives™ on Stack Overflow

Add a new column to a view in SQL

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related