Thread: Incrementing a Serial Field

First off, I would like to take this opportunity to thank everyone who
worked on the new postgres v6.4.  I have it up and working here, and most
all appears well.

I do have two questions.  Firstly, I have one table that is updated from a
text file.  This table also has a serial field that is incremented with
each new record.  Is there a way to use the copy command to have that field
automatically updated - e.g. copy table_one from '/data/files/input.txt'
using delimiters '|' ?

So far, I can get this text file to import just fine when using a table
without a serial field, but as of yet not had any luck in importing it into
a table that has a serial field.

I suppose that I could parse the file and do an "insert into etc....  ",
but I fear that would be a little processor intensive for a 12000 line file.

The second question is that I noticed the ODBC bug (feature?) when linking
Postgres to MS Access still exists.  This bug occurs when linking a MS
Access table to a Postgres table, and identifying more than one field as
the unique record identifier.  This makes Postgres run until it exhausts
all available memory.  Does anyone know a way around this?  Enabling read
only ODBC is a feature I would like to make available, but I do not want
the possibility of postgres crashing because of an error on the part of a
MS Access user.

BTW - Having capability to be linked to an Access database is not an
option.  The current project I am working on calls for that, so it is a
necessary evil that I hav to live with.

Thanks in advance for any assistance.

Regards - Bob Kruger

At 19:07 +0200 on 9/11/98, Bob Kruger wrote:


>
> I do have two questions.  Firstly, I have one table that is updated from a
> text file.  This table also has a serial field that is incremented with
> each new record.  Is there a way to use the copy command to have that field
> automatically updated - e.g. copy table_one from '/data/files/input.txt'
> using delimiters '|' ?
>
> So far, I can get this text file to import just fine when using a table
> without a serial field, but as of yet not had any luck in importing it into
> a table that has a serial field.

Assuming the serial field is defined as a NOT NULL DEFAULT nextval( 'seq'
), the simplest way is to put nulls for that field in the text file. That
is, suppose it is the third field in your table, you write

data for field1|data for field 2|\N|data for field4...

The \N (capital N) stands for null, and will cause Postgres to use the
default value for that field.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

 

Bob Kruger wrote:

The second question is that I noticed the ODBC bug (feature?) when linking
Postgres to MS Access still exists.  This bug occurs when linking a MS
Access table to a Postgres table, and identifying more than one field as
the unique record identifier.  This makes Postgres run until it exhausts
all available memory.  Does anyone know a way around this?  Enabling read
only ODBC is a feature I would like to make available, but I do not want
the possibility of postgres crashing because of an error on the part of a
MS Access user.

BTW - Having capability to be linked to an Access database is not an
option.  The current project I am working on calls for that, so it is a
necessary evil that I hav to live with.
 

In the driver connection settings add the following line.

    SET ksql TO 'on';

Stands for: keyset query optimization.  This is not considered a final solution.  As such, it is undocumented.   Some time in the next day or so, we will be releasing a version of the driver which will automatically SET ksqo.

You will most likely be satisfied with the results.   One problem with this solution, however,  is that it does not work if you have any (some kinds of?) arrays in the table you are browsing.   This is a sideffect of the rewrite to a UNION which performs an internal sort unique.

Also, if you are using row versioning you may need to overload some operators for xid and int4.  I have included a script that will take care of this.

Bruce, can I get these operators hardcoded into 6.4.1- assuming there will be one.   The operators  necessitated by the UNION sideffects.
 

>
>
> Bob Kruger wrote:
>
> > The second question is that I noticed the ODBC bug (feature?) when linking
> > Postgres to MS Access still exists.  This bug occurs when linking a MS
> > Access table to a Postgres table, and identifying more than one field as
> > the unique record identifier.  This makes Postgres run until it exhausts
> > all available memory.  Does anyone know a way around this?  Enabling read
> > only ODBC is a feature I would like to make available, but I do not want
> > the possibility of postgres crashing because of an error on the part of a
> > MS Access user.
> >
> > BTW - Having capability to be linked to an Access database is not an
> > option.  The current project I am working on calls for that, so it is a
> > necessary evil that I hav to live with.
> >
>
> In the driver connection settings add the following line.
>
>     SET ksql TO 'on';
>
> Stands for: keyset query optimization.  This is not considered a final
> solution.  As such, it is undocumented.   Some time in the next day or so, we
> will be releasing a version of the driver which will automatically SET ksqo.
>
> You will most likely be satisfied with the results.   One problem with this
> solution, however,  is that it does not work if you have any (some kinds of?)
> arrays in the table you are browsing.   This is a sideffect of the rewrite to a
> UNION which performs an internal sort unique.
>
> Also, if you are using row versioning you may need to overload some operators
> for xid and int4.  I have included a script that will take care of this.
>
> Bruce, can I get these operators hardcoded into 6.4.1- assuming there will be
> one.   The operators  necessitated by the UNION sideffects.
>

Ths killer is that 6.4.1, if we have one, will not require a
dump/reload, because it is a minor release.  We can add the stuff, but
people who do not initdb as part of 6.4.1 will not see the changes.


> --   Insight Distribution Systems - System V - Apr 1998
> --   @(#)xidint4.sql    1.2 :/sccs/sql/extend/s.xidint4.sql 10/2/98 13:40:19"
>
> create function int4eq(xid,int4)
>   returns bool
>   as ''
>   language 'internal';
>
> create operator = (
>         leftarg=xid,
>         rightarg=int4,
>         procedure=int4eq,
>         commutator='=',
>         negator='<>',
>         restrict=eqsel,
>         join=eqjoinsel
>         );
>
> create function int4lt(xid,xid)
>   returns bool
>   as ''
>   language 'internal';
>
> create operator < (
>         leftarg=xid,
>         rightarg=xid,
>         procedure=int4lt,
>         commutator='=',
>         negator='<>',
>         restrict=eqsel,
>         join=eqjoinsel
>         );
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026