This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-novice@postgresql.org
From: Andrew McMillan <andrew@catalyst.net.nz>
Subject: Re: [NOVICE] MySQL comparable syntax for PostgreSQL
Date: 18 Feb 2003 09:19:08 +1300
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 ...
===
To: pgsql-novice@postgresql.org
From: Bruno Wolff III <bruno@wolff.to>
Subject: Re: [NOVICE] MySQL comparable syntax for PostgreSQL
Date: Mon, 17 Feb 2003 14:58:01 -0600
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.
===
To: pgsql-novice@postgresql.org
From: Bruno Wolff III <bruno@wolff.to>
Subject: Re: [NOVICE] MySQL comparable syntax for PostgreSQL
Date: Mon, 17 Feb 2003 21:25:09 -0600
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.
===