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