The following bug has been logged online:
Bug reference: 1324
Logged by: Tzvetan Tzankov
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0 Beta
Operating system: Debian
Description: create domain strange behaviour
Details:
I will post a sort test
test=# create schema test;
CREATE SCHEMA
test=# set search_path to test;
SET
test=# CREATE DOMAIN session_id AS character(9) CONSTRAINT
ch_session_id_field CHECK (value ~ '[0-9]{4}-[0-9]{4}-[0-9]{4}-[0-9]{4}');
CREATE DOMAIN
test=#
test=# CREATE OR REPLACE FUNCTION generate_session_id() RETURNS session_id
AS $$
test$# begin
test$# return lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0');
test$# end;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
test=# CREATE TABLE session (
test(# id session_id NOT NULL default generate_session_id() PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"session_pkey" for table "session"
CREATE TABLE
test=# insert into session default values;
INSERT 2272280 1
test=# select * from session;
id
---------------------
3199-4274-8097-7843
(1 row)
test=# insert into session values ('3199-4274-8097-7842');
ERROR: value too long for type character(9)
test=# select version();
version
---------------------------------------------------------------------------
----------------------
PostgreSQL 8.0.0beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4
(Debian 1:3.3.4-13)
(1 row)
well this was first of all, a bug in my domain creating statement, which I
should have done character(19) insetead of 9, but instead of generating
error on default value it accepted it (when I try to insert a random value
by hand it gives however an error)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
