Обсуждение: Table definitions using CHECK and DEFAULT

I am adding PostgreSQL support to some software that I've been
developing and I've hit a few snags.
I've found that MySQL's "ENUM" can be emulated with PostgreSQL's
"CHECK".  Unfortunately it appears that I cannot use both the DEFAULT
modifier and the check modifier at the same time.

This line:
address_type VARCHAR(30) DEFAULT 'unknown' NOT NULL CHECK(address_type
IN ("unknown", "forged", "proxy", "temporary", "permanent"))

gives the error:
psql:IRDB_POSTGRES:69: ERROR:  Attribute 'unknown' not found

If I write the definition without DEFAULT it works fine, if I write it
without the CHECK statement it works fine too..
(I've also split up the column definition and the check definition, i.e.
address_type VARCHAR(30) DEFAULT 'unknown' NOT NULL,
CHECK(address_type IN ("unknown", "forged", "proxy", "temporary", "permanent"))
)

Is the SQL wrong?  Is it just not possible to do this? Or is this a
possible bug?

Any help is appreciated!

--
Matthew Wirges
  Developer::CERIAS Incident Response Database
  https://cirdb.cerias.purdue.edu/
  wirges@cerias.purdue.edu

On Sun, 4 Nov 2001, Matthew L. Wirges wrote:
>This line:
>address_type VARCHAR(30) DEFAULT 'unknown' NOT NULL CHECK(address_type
>IN ("unknown", "forged", "proxy", "temporary", "permanent"))
>gives the error:
>psql:IRDB_POSTGRES:69: ERROR:  Attribute 'unknown' not found
[snip]
>Is the SQL wrong?  Is it just not possible to do this? Or is this a
>possible bug?

No, the problem is that you're specifying string constants with
double-quotes, not single-quotes. ;) Double-quotes are for identifiers,
not constants.

Change it to read like this, and you'll be set:

address_type VARCHAR(30)
  DEFAULT 'unknown'
  NOT NULL
  CHECK(address_type IN
        ('unknown', 'forged', 'proxy', 'temporary', 'permanent'))


Regards,
Jw.
--
by way of pgsql-general@commandprompt.com

"Matthew L. Wirges" <wirges@cerias.purdue.edu> writes:
> address_type VARCHAR(30) DEFAULT 'unknown' NOT NULL CHECK(address_type
> IN ("unknown", "forged", "proxy", "temporary", "permanent"))
> gives the error:
> psql:IRDB_POSTGRES:69: ERROR:  Attribute 'unknown' not found

You must use single quotes not double quotes for literal strings.
Double-quotes are for names that don't follow standard identifier
rules.

            regards, tom lane