[GENERAL] null values in a view
Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; Thanks -Lauri Kajan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] null values in a view
try: CREATE OR REPLACE VIEW view1 AS SELECT name as a1, null::text as a2, 'test'::text as a3 FROM some_table; Στις Wednesday 05 October 2011 17:22:21 ο/η Lauri Kajan έγραψε: Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; Thanks -Lauri Kajan -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] null values in a view
On Wed, Oct 05, 2011 at 05:22:21PM +0300, Lauri Kajan wrote: Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? using normal cast: null::text as a2 or cast(null as text); Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] null values in a view
Lauri Kajan lauri.ka...@gmail.com writes: This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; FYI, the syntax typename 'literal' works *only* with string literals, not anything else. For any other target you have to write CAST(value AS typename) or equivalently value::typename. CAST is SQL-standard, :: is a Postgres-ism. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] null values in a view
This worked. Thank you all! I know the casting is quite basic operation but could this be added to the CREATE VIEW documentation? Now there is only an example: CREATE VIEW vista AS SELECT text 'Hello World' AS hello; The same syntax won't work with other type as Tom wrote. -Lauri On Wed, Oct 5, 2011 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lauri Kajan lauri.ka...@gmail.com writes: This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; FYI, the syntax typename 'literal' works *only* with string literals, not anything else. For any other target you have to write CAST(value AS typename) or equivalently value::typename. CAST is SQL-standard, :: is a Postgres-ism. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general