Re: [GENERAL] Some MySQL features

On Fri, Dec 03, 1999 at 03:02:42PM +0100, Herbert Liechti wrote:
> vincent leycuras wrote:
>
> > I'd like to know if pgsql supports the two excellent features that exist in
> > MySQL:
> > - the possibility to load the database with ASCII formatted file containing
> > the data we want to put;
>
> Sure. This is the copy routine. But it is also easy to load data with
> programs (perl DBI/DBD)

COPY has its own little quirks as regards representing NULLs and
such. Check the docs.  Works fine for me from the unix command line.
Be careful about delimiters and quoting: if a bulk load fails, the actual
error usually goes flying past, and the ultimate killing error says
something about the command buffer overflowing, or command to long, or
so. Usually a result of quoting getting off by one. Hmm, I just realized
that the work to allow unlimited command buffers is going to change this
failure mode.

>
> > - the index auto incrementation the prevents from having to specify the
> > value of the primary key each time one adds a row.
>
> Sure. You may define a sequence by doing this:
>
> create sequence xxx_id_seq START 1;
> create table xxy
> (
>    xxx_Id                    INTEGER       NOT NULL
>          DEFAULT NEXTVAL('xxx_id_seq'),
>    ...
> );


Or even easier:

test=> create table xxy (xxx_id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'xxy_xxx_id_seq' for SERIAL column 'xxy.xxx_id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'xxy_xxx_id_key' for table 'xxy'
CREATE
test=> \d xxy
Table    = xxy
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| xxx_id                           | int4 not null default nextval('" |     4 |
+----------------------------------+----------------------------------+-------+
Index:    xxy_xxx_id_key

test=>


As you can see, 'serial' is implemented exaclty that way: it creates a
sequence, and sets the default for the field to nextval, and makes it not null
and unique (with the index).

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005