Обсуждение: MySQL comparable syntax for PostgreSQL

Hi,

I'm trying out PostgreSQL as an alternate to MySQL.  Since MySQL seems a
little immature.  There are however some constructs I am used to, and
can't see a PostgreSQL alternate.  Can you let me know what the
comparable syntax is, if it exists...

The 'IF EXISTS' extension to CREATE and DROP TABLE.  Is there something
similar in PostgreSQL to alow scripts to create/amend table to parse
smoothly without errors? -- Or something which can be added to a script
to instuct PostgreSQL to not thow an error if duplicate tables submitted?

An UNSIGNED number.  Is this 'field INTEGER CHECK (field >= 0)', or is
there a direct UNSIGNED reference I have missed?

The AUTO_INREMENT extension to a Primary Key, as used in MuSQL.  What is
the official method for auto-incrementing fields?  I have found the
'DEFAULT NEXTVAL('...'), but I am not sure whether/how/if this can be
used to auto-increment a primary field?

The SET and ENUM data types, representing a set of, and enumerated data
respecitvelly.  Is there anything similar, expecially to ENUM data type,
in PostgreSQL, as I use this extensivelly at the moment...

If anybody can let me know on what I am sure are simple questions, I'd
love to know.

Ben

On Mon, Feb 17, 2003 at 14:47:01 +0000,
  Ben Clewett <B.Clewett@roadrunner.uk.com> wrote:
>
> An UNSIGNED number.  Is this 'field INTEGER CHECK (field >= 0)', or is
> there a direct UNSIGNED reference I have missed?

There is no unsigned type. You can use a check constraint to enforce this.

> The AUTO_INREMENT extension to a Primary Key, as used in MuSQL.  What is
> the official method for auto-incrementing fields?  I have found the
> 'DEFAULT NEXTVAL('...'), but I am not sure whether/how/if this can be
> used to auto-increment a primary field?

Yes, you want to use sequences as they are a lightweight way to generate
unique keys. The Serial types provide a simple way to do this. They
create a sequence, set the default to use nextval and add a not null
constraint. They don't create a unique index, but a primary key constraint
will do that.

On Tue, 2003-02-18 at 03:47, Ben Clewett wrote:
> Hi,
>
> I'm trying out PostgreSQL as an alternate to MySQL.  Since MySQL seems a
> little immature.  There are however some constructs I am used to, and
> can't see a PostgreSQL alternate.  Can you let me know what the
> comparable syntax is, if it exists...
>
> The 'IF EXISTS' extension to CREATE and DROP TABLE.  Is there something
> similar in PostgreSQL to alow scripts to create/amend table to parse
> smoothly without errors? -- Or something which can be added to a script
> to instuct PostgreSQL to not thow an error if duplicate tables submitted?
>
> An UNSIGNED number.  Is this 'field INTEGER CHECK (field >= 0)', or is
> there a direct UNSIGNED reference I have missed?
>
> The AUTO_INREMENT extension to a Primary Key, as used in MuSQL.  What is
> the official method for auto-incrementing fields?  I have found the
> 'DEFAULT NEXTVAL('...'), but I am not sure whether/how/if this can be
> used to auto-increment a primary field?
>
> The SET and ENUM data types, representing a set of, and enumerated data
> respecitvelly.  Is there anything similar, expecially to ENUM data type,
> in PostgreSQL, as I use this extensivelly at the moment...


For ENUM you can use a constraint like:

create table xyz ( a text check ( a IN ( 'a', 'b', 'c' ) ) );

At some point in the future you will be able to create a domain for this
sort of thing, but PostgreSQL does not currently support CHECK
constraints on domains :-(


For SET types you could either use a sub-relation (which is what I think
I would probably recommend, without understanding your full
requirements), or you could possibly use an array.


Also, you can use SERIAL as the type in your CREATE TABLE as a shortcut
to create a column with type INT + create a sequence + set the default
for the column to the nextval of that sequence.


There is no "IF EXISTS " on CREATE / DROP table, although you could do
some select on the metadata to ascertain this.  I suspect it would be
messy, however.  There is an equivalent syntax for functions and views,
which is CREATE OR REPLACE ...

Regards,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------

On Tue, Feb 18, 2003 at 09:19:08 +1300,
  Andrew McMillan <andrew@catalyst.net.nz> wrote:
>
> At some point in the future you will be able to create a domain for this
> sort of thing, but PostgreSQL does not currently support CHECK
> constraints on domains :-(

When 7.4 gets released check constraints on domains will be available.
The feature is already available in the 7.4 CVS.

----- Original Message -----
From: Bruno Wolff III <bruno@wolff.to>
Date: Monday, February 17, 2003 12:58 pm
Subject: Re: [NOVICE] MySQL comparable syntax for PostgreSQL

> On Tue, Feb 18, 2003 at 09:19:08 +1300,
>  Andrew McMillan <andrew@catalyst.net.nz> wrote:
> >
> > At some point in the future you will be able to create a domain
> for this
> > sort of thing, but PostgreSQL does not currently support CHECK
> > constraints on domains :-(
>
> When 7.4 gets released check constraints on domains will be available.
> The feature is already available in the 7.4 CVS.
>

Can someone tell me what domains are, and possibly point me towards some relevant documentation?

Thanks,

Doug Gorley | douggorley@shaw.ca

On Mon, Feb 17, 2003 at 13:05:29 -0800,
  douggorley@shaw.ca wrote:
>
> Can someone tell me what domains are, and possibly point me towards some relevant documentation?

They are a shorthand for a type plus constraints. This can make maintainance
easier when you have the same constraints in multiple locations.

You can look at the documentation for the create domain command to see
the syntax.