[SQL] domains and serial

[BillR]

Is it not possible to set up a domain using this construct?  I will have 
a lot of tables using integer and big integer types as primary key 
identifier fields.  It would make things simpler if I can use the serial 
construct in a domain instead of using an int or bigint in the domain, 
and then change each field in each table requiring a serial or bigserial 
construct.


I just tried to create a domain using a bigserial type. 


CREATE DOMAIN Identifier_DM AS bigserial;

I received the error message:

ERROR: type "bigserial" does not exist
SQL state: 42704

I tried to run this in both pgadminIII and in data architect 3.5.

Thanks.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values

[BillR]

Peter Eisentraut wrote:

D'Arcy J.M. Cain wrote:
  

   SELECT * FROM table WHERE column IS NULL;
   SELECT * FROM table WHERE column = NULL;

The latter violates the SQL spec and is not allowed by PostgreSQL
without setting a special flag.



It doesn't violate any spec and it's certainly allowed by PostgreSQL 
without any flags.  It's just that the result is not what some people 
expect.


  


"= NULL" violates the SQL-92 Specification.  Relevant section posted below.
Additionally *none of the example code* in the SQL-92 specification 
document uses the expression "WHERE #value# = NULL"
*All* the example code in the specification use the expression as:  
"WHERE #value# IS NULL"



8.6  

Function

Specify a test for a null value.

Format

 ::=  IS [ NOT ] NULL


Syntax Rules

   None.

Access Rules


   None.

General Rules

1) Let R be the value of the .

2) If all the values in R are the null value, then "R IS NULL" is
   true; otherwise, it is false.

3) If none of the values in R are the null value, then "R IS NOT
   NULL" is true; otherwise, it is false.

   Note: For all R, "R IS NOT NULL" has the same result as "NOT
   R IS NULL" if and only if R is of degree 1. Table 12, " semantics", specifies this behavior.

Table_12-_semantics

  R ISR IS NOT  NOT R IS  NOT R IS NOT
_Expression___NULLNULL__NULL__NULL_

   | degree 1: null | true  | false   | false  |  true|
   ||   | ||  |
   | degree 1: not  | false | true| true   |  false   |
 null

   | degree > 1:| true  | false   | false  |  true|
   | all null   |   | ||  |
   ||   | ||  |
   | degree > 1:| false | false   | true   |  true|
   | some null  |   | ||  |
   ||   | ||  |
   | degree > 1:| false | true| true   |  false   |
   |_none_null__|___|_||__|
   ||   | ||  |
   |Leveling Rules  |   | ||  |
   ||   | ||  |
218  Database Language SQL



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org