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
Re: [GENERAL] NULL values seem to short-circuit my unique index
On Sep 29, 2008, at 4:59 AM, Chris wrote: Matthew Wilson wrote: I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? When you think of null as unknown, it makes sense. Does an unknown value equal another unknown value? Also, you wouldn't be able to put a UNIQUE constraint on foreign keys with a 0..1 to 1 relation if two NULL values would be considered not unique. That UNIQUE constraint is what makes it a 0..1 to 1 relation (as would a PRIMARY KEY constraint). Without it it would be a * to 1 relation. If two NULLs would be considered not unique, only one NULL key reference would be allowed and all following ones would result in a unique constraint violation! Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49157dd89507271520953! -- 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 seem to short-circuit my unique index
You can always add a constraint that these columns cannot be NULL themselves. That removes this problem. On Sep 28, 2008, at 11:17 PM, Klint Gore [EMAIL PROTECTED] wrote: Matthew Wilson wrote: I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058 Last paragraph just above 5.3.4. What's the deal with NULL? NULL = NULL is not true, it's null NULL NULL is not false, it's null It's the normal SQL 3 state logic (true/false/null) with only the true value from the comparison causing the constraint violation. Think of the unique constraint check like does this value equal any other value already recorded. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 seem to short-circuit my unique index
Matthew Wilson wrote: I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? When you think of null as unknown, it makes sense. Does an unknown value equal another unknown value? http://www.postgresql.org/docs/8.3/interactive/functions-logical.html http://www.postgresql.org/docs/8.3/interactive/functions-comparison.html -- Postgresql php tutorials http://www.designmagick.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 seem to short-circuit my unique index
Matthew Wilson [EMAIL PROTECTED] writes: It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. This is per SQL spec and quite well-documented in our manual ... 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 seem to short-circuit my unique index
Matthew Wilson wrote: I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058 Last paragraph just above 5.3.4. What's the deal with NULL? NULL = NULL is not true, it's null NULL NULL is not false, it's null It's the normal SQL 3 state logic (true/false/null) with only the true value from the comparison causing the constraint violation. Think of the unique constraint check like does this value equal any other value already recorded. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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 and string
Sergey Karin schrieb: Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? I'm doing something like this: create function func(int4) returns varchar as' declare num_value alias for $1; string_value varchar; begin string_value := \'input value = \' || num_value; return string_value; end 'language 'plpgsql'; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; You can use COALESCE() create function func(int4) returns text as $$ declare num_value alias for $1; begin return 'input value = ' || COALESCE(num_value,'NULL'); end $$ language 'plpgsql'; (Id rather use more descriptive name for your function) Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NULL values and string
Sergey Karin wrote: Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? Null isn't a real value. Try not to think of it as a value. http://archives.postgresql.org/pgsql-sql/2003-01/msg00222.php num_value alias for $1; string_value := \'input value = \' || num_value; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Because NULL means unknown. A string with an unknown string appended to it is itself unknown. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; But it is not laconic... Try something like: string_value := ''input_value = '' || COALESCE(num_value, 'a null'); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NULL values and string
Richard Huxton wrote: Sergey Karin wrote: Are there any abilities to represent NULL values as string? Null isn't a real value. Try not to think of it as a value. That being said, and with due credit elsewhere (http://www.varlena.com/varlena/GeneralBits/84.php), what I do is CREATE OR REPLACE FUNCTION public.textcat_null(text, text) RETURNS text AS 'SELECT textcat(COALESCE($1, ), COALESCE($2, ));' LANGUAGE sql' VOLATILE; CREATE OPERATOR public.||+( PROCEDURE = public.textcat_null, LEFTARG = text, RIGHTARG = text); This goes against proper form, considering what NULL is designed for, but it sure is convenient. Regards, Berend Tober ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] NULL values
On Tue, Jul 10, 2001 at 09:40:31AM +0100, Mark Muffett wrote:
Is there a Postgresql equivalent to the Oracle NVL( ) function,
which allows a SELECT statement to fill in default values if a
column is NULL?
Yes.
Use the DEFAULT keyword while creating the table.
That is:
CREATE TABLE account (
nameCHAR(20),
balance NUMERIC(16,2) DEFAULT 0,
active CHAR(2) DEFAULT 'Y',
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Then :
INSERT INTO account (name)
VALUES ('Federated Builders');
will leave the inserted rows with no nulls, but the balance will be
zero, and the created field will have the date/time of the insert.
Mark Muffett
-crl
--
Chad R. Larson (CRL22)[EMAIL PROTECTED]
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
