Re: MySQL comparable syntax for PostgreSQL

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/
---------------------------------------------------------------------