Thread: 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
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
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/
Lauri Kajan <lauri.kajan@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
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 <tgl@sss.pgh.pa.us> wrote: > Lauri Kajan <lauri.kajan@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 >