how to change a table column datatype in a view in postgresql

Question

))Hi all, I have an 'Interval' datatype column in mytable that I would like to change to 'character varying' datatype in mytableview.

I know I can change it using...

to_char(interval, 'yy-mm-dd HH24:MI:SS.MS');

But I would like to leave that column datatype as 'interval' in mytable, and make a view of that table changing the 'interval' column to 'character varying' datatype in mytableview.

was so easy...

CREATE TABLE mytable (length INTERVAL);

INSERT INTO mytable (length) VALUES (INTERVAL '1 minute');

CREATE VIEW myview AS
  SELECT to_char(length, 'yy-mm-dd HH24:MI:SS.MS') AS length
  FROM mytable;

Is that possible? Thanks Advanced.

Yes, quite possible. Just use the code you are showing in the query you use to build the view. Have you tried that? — Politank-Z
– Politank-Z, Commented Jul 2, 2015 at 15:36

Kristján · Accepted Answer · 2015-07-02 15:39:57Z

1

You can specify your view just like you'd specify any other query without needing to change the underlying table types. Here's a Fiddle with your example.

CREATE TABLE mytable (length INTERVAL);

INSERT INTO mytable (length) VALUES (INTERVAL '1 minute');

CREATE VIEW myview AS
  SELECT to_char(length, 'yy-mm-dd HH24:MI:SS.MS') AS length
  FROM mytable;

SELECT * FROM mytable;
-- 0 years 0 mons 0 days 0 hours 1 mins 0.00 secs

SELECT * FROM myview;
-- 00-00-00 00:01:00.000

answered Jul 2, 2015 at 15:39

Kristján

19k5 gold badges55 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

how to change a table column datatype in a view in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related