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 Mar 22 '17 at 08:38 — Tom J Muthirenthi, Mar 22 '17 at 08:38

score 3 · Accepted Answer · answered Mar 22 '17 at 08:37

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

Vic · Answer 2 · 2020-03-31T09:14:32.273

3

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 '20 at 09:14

answered Mar 31 '20 at 09:08

Vic

1,778
3
19
37

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. – Jason Glez Jun 22 '21 at 19:38
Also this only works for adding new columns to the view. Removing columns requires a drop first. – villasv Mar 04 '22 at 14:11

score 2 · Answer 3 · answered Mar 22 '17 at 08:32

2

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 '17 at 08:32

Ray O'Donnell

759
4
11

score 0 · Answer 4 · answered Mar 22 '17 at 08:37

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.

Add a new column to a view in SQL

4 Answers4