Thread: Some MySQL features
Hi! 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; - the index auto incrementation the prevents from having to specify the value of the primary key each time one adds a row. I'd also like to know if the java driver for pgsql really works under linux as I haven't been able to make the MySQL one work and this thing is really getting on my nerves... Thanks! Vincent. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
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)
> - 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'),
...
);
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Fri, 3 Dec 1999, vincent leycuras wrote: > Hi! > 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; yes > - the index auto incrementation the prevents from having to specify the > value of the primary key each time one adds a row. yes > > I'd also like to know if the java driver for pgsql really works under linux > as I haven't been able to make the MySQL one work and this thing is really > getting on my nerves... yes > Thanks! np mazek
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
vincent leycuras wrote: > > Hi! > 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; > - the index auto incrementation the prevents from having to specify the > value of the primary key each time one adds a row. > This is a question that could be an answer. What do y'all think about using the OID to get the same functionality (unless he really needs a sequence of nice small numbers)?
hi.. > This is a question that could be an answer. What do y'all think about > using the OID to get the same functionality (unless he really needs a > sequence of nice small numbers)? OIDs don't survive dump/reloads and sequences already provide this functionality, surviving dump/reloads -- Aaron J. Seigo Sys Admin
and, generally, those low-level non-sql86/92 things should be treated with caution. On Fri, 3 Dec 1999, Aaron J. Seigo wrote: > hi.. > > > This is a question that could be an answer. What do y'all think about > > using the OID to get the same functionality (unless he really needs a > > sequence of nice small numbers)? > > OIDs don't survive dump/reloads and sequences already provide this > functionality, surviving dump/reloads > > -- > Aaron J. Seigo > Sys Admin > > ************ >
"Aaron J. Seigo" wrote: > > This is a question that could be an answer. What do y'all think about > > using the OID to get the same functionality (unless he really needs a > > sequence of nice small numbers)? > > OIDs don't survive dump/reloads and sequences already provide this > functionality, surviving dump/reloads The postgres SERIAL type, which is a sequence, is fairly well-suited to primary key usage. See PG documentation for details... Cheers. Ed Loehr
Hello! I try to make a PostgreSQL based GUI aplication. I find a lot of GUI libraries, but none of them have 'high level' classes for accessing and displaying dates from databases in a more convenient mode. (like TTable, TDBGrid in Delphi) Is there something like that? Thanks, Karesz.