A thanks to everyone on this list and especially; Jeffery Rhines, Chris
Knight, Chris Bitmead, and Sevo Stille.

The solution turned out to be very simple.  After catching a SCSI BUS
speed mismatch problem which caused a NT Backup 'Restore' failure I
discovered that the actual data was in .mdb files!  Copied the files to a
system running MS Access (Office 97) and was able to export them to a
delimited format which went into PostgreSQL with very few problems.
Mostly there were split lines which the \copy command didn't like.  Hand
corrected them.

I was able to get the table format by using MS Access.  Only question left
is what is the corresponding field type in PostgreSQL for a memo field in
SQL Server/Access (varchar(nnnn))?

Again thanks for all the help,
Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814


Tue, 22 Aug 2000 09:37:42 -0700 (PDT)
"Roderick A. Anderson" <raanders@altoplanos.net>
[Solved] SQL Server to PostgreSQL
Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait :

)I was able to get the table format by using MS Access.  Only question left
)is what is the corresponding field type in PostgreSQL for a memo field in
)SQL Server/Access (varchar(nnnn))?

'text' type perhaps ?

Lionel


Tue, 22 Aug 2000 18:50:29 +0200 (CEST)
Tressens Lionel <tressens@etud.insa-tlse.fr>
Re: [Solved] SQL Server to PostgreSQL
I hate it when I do this.  See an answer I want and run with it rather
than find the real answer.

Turned out the data files (.mdb) _didn't_ belong to the database.  They
were a piece of the database that was used for a report.

Back to the old grind wheel.


Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814


Tue, 22 Aug 2000 10:19:17 -0700 (PDT)
"Roderick A. Anderson" <raanders@altoplanos.net>
I lied! [Solved] SQL Server to PostgreSQL
Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait :
> )I was able to get the table format by using MS Access.  Only question left
> )is what is the corresponding field type in PostgreSQL for a memo field in
> )SQL Server/Access (varchar(nnnn))?

> 'text' type perhaps ?

Uh ... what's wrong with varchar(n) ?

			regards, tom lane

Tue, 22 Aug 2000 13:52:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [Solved] SQL Server to PostgreSQL 
On Tue, 22 Aug 2000, Tom Lane wrote:

> Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> > Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait :
> > )I was able to get the table format by using MS Access.  Only question left
> > )is what is the corresponding field type in PostgreSQL for a memo field in
> > )SQL Server/Access (varchar(nnnn))?
> 
> > 'text' type perhaps ?
> 
> Uh ... what's wrong with varchar(n) ?

How big can our n be for varchar?  By looking at his description I'm
thinking SQL Server allows a large n. 

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Tue, 22 Aug 2000 15:02:50 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [Solved] SQL Server to PostgreSQL 
I've wondered that myself, actually.  What are the benefits and
drawbacks to going with one over the other, besides the obvious 255-char
field length limit for varchar?  The reason to stay away from "memo"
fields in other serious RDBMSs are typically more difficult maintenance,
significantly lower performance, and requiring special function calls to
get the data out.  Do any of those apply to PG?

Jeff

Tom Lane wrote:
> 
> Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> > Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait :
> > )I was able to get the table format by using MS Access.  Only question left
> > )is what is the corresponding field type in PostgreSQL for a memo field in
> > )SQL Server/Access (varchar(nnnn))?
> 
> > 'text' type perhaps ?
> 
> Uh ... what's wrong with varchar(n) ?
> 
>                         regards, tom lane

Tue, 22 Aug 2000 14:06:09 -0500
"Jeffrey A. Rhines" <jrhines@email.com>
Re: [Solved] SQL Server to PostgreSQL
"Jeffrey A. Rhines" <jrhines@email.com> writes:
>> Uh ... what's wrong with varchar(n) ?
>
> I've wondered that myself, actually.  What are the benefits and
> drawbacks to going with one over the other, besides the obvious 255-char
> field length limit for varchar?

AFAIK there has *never* been a 255-char limit on char or varchar in
pgsql ... you must be thinking of Some Other DBMS.

The limit for these datatypes in 7.0 and before is BLCKSZ less some
overhead --- ~8000 bytes in a default setup.  Beginning in 7.1 it's
an essentially arbitrary number.  I set it at 10Mb in current sources,
but there's no strong reason for that number over any other.  In theory
it could be up to 1Gb, but as Jan Wieck points out in a nearby thread,
you probably wouldn't like the performance of shoving gigabyte-sized
text values around.  We need to think about offering API functions that
will allow reading and writing huge field values in bite-sized chunks.

There's no essential performance difference between char(n), varchar(n),
and text in Postgres, given the same-sized data value.  char(n)
truncates or blank-pads to exactly n characters; varchar(n) truncates
if more than n characters; text never truncates nor pads.  Beyond that
they are completely identical in storage requirements.  Pick one based
on the semantics you want for your application.

			regards, tom lane

Tue, 22 Aug 2000 23:11:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [Solved] SQL Server to PostgreSQL 
I think the ODBC spec limits varchar to 255 bytes.
Some ODBC drivers enforce that limit.

Tom Lane wrote:

> "Jeffrey A. Rhines" <jrhines@email.com> writes:
> >> Uh ... what's wrong with varchar(n) ?
> >
> > I've wondered that myself, actually.  What are the benefits and
> > drawbacks to going with one over the other, besides the obvious 255-char
> > field length limit for varchar?
>
> AFAIK there has *never* been a 255-char limit on char or varchar in
> pgsql ... you must be thinking of Some Other DBMS.
>
> [snip]
>                         regards, tom lane


Tue, 22 Aug 2000 22:06:26 -0700
Craig Johannsen <cjohan@home.com>
Re: [Solved] SQL Server to PostgreSQL
*** Tom Lane <tgl@sss.pgh.pa.us> [Tuesday, 22.August.2000, 23:11 -0400]:
> There's no essential performance difference between char(n), varchar(n),
> and text in Postgres, given the same-sized data value.  char(n)
> truncates or blank-pads to exactly n characters; varchar(n) truncates
> if more than n characters; text never truncates nor pads.  Beyond that
> they are completely identical in storage requirements.  
[.rs.]

Does varchar(188) takes 188 bytes (+ bytes for length storage) every
time, no matter if it contains 'my text'  or 'my long 188 char text.....'
?


-- 
radoslaw.stachowiak.........................................http://alter.pl/

Fri, 1 Sep 2000 20:12:08 +0200
Radoslaw Stachowiak <radek@alter.pl>
Re: [Solved] SQL Server to PostgreSQL
>>>>> "Radoslaw" == Radoslaw Stachowiak <radek@alter.pl> writes:
Radoslaw> Does varchar(188) takes 188 bytes (+ bytes for length
Radoslaw> storage) every time, no matter if it contains 'my text' or
Radoslaw> 'my long 188 char text.....'  ?

The way I understand it varchar(n) is variable-length, while char(n)
is fixed-lenght. Thus the behaviour you describe above is that of
char(n).

Martin

-- 
GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt

Sat, 02 Sep 2000 21:05:45 GMT
Martin Christensen <knightsofspamalot-factotum@mail1.stofanet.dk>
Re: [Solved] SQL Server to PostgreSQL
Martin Christensen wrote:
> >>>>> "Radoslaw" == Radoslaw Stachowiak <radek@alter.pl> writes:
> Radoslaw> Does varchar(188) takes 188 bytes (+ bytes for length
> Radoslaw> storage) every time, no matter if it contains 'my text' or
> Radoslaw> 'my long 188 char text.....'  ?
>
> The way I understand it varchar(n) is variable-length, while char(n)
> is fixed-lenght. Thus the behaviour you describe above is that of
> char(n).

    Right for any pre-7.1 version.

    From  7.1  on  the  system  will  try  to  compress all types
    internally stored as variable length (char(), varchar(), text
    and  some  more). So the real amount of bytes for a char(188)
    will be "at maximum 192 - probably less".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Sat, 2 Sep 2000 18:33:48 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: [Solved] SQL Server to PostgreSQL
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

>     From  7.1  on  the  system  will  try  to  compress all types
>     internally stored as variable length (char(), varchar(), text
>     and  some  more). So the real amount of bytes for a char(188)
>     will be "at maximum 192 - probably less".

Don't variable-length records incur a performance overhead? In this case,
ought I be able to specify the length for a record if I know ahead of time
it will be the same in every case? :o

Ian Turner
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5sabgfn9ub9ZE1xoRAhayAKCwMjh/5tYlg8zZiAimJlgFSfCLsQCghBce
Gxx6X8sSwIACIHvdbxBsgGQ=
=bogc
-----END PGP SIGNATURE-----


Sat, 2 Sep 2000 18:18:22 -0700 (PDT)
Ian Turner <vectro@pipeline.com>
Re: [Solved] SQL Server to PostgreSQL
Ian Turner <vectro@pipeline.com> writes:
> Don't variable-length records incur a performance overhead?

Only to the extent that the system can't cache offset information for
later columns in that table.  While someone evidently once thought that
was worthwhile, I've never seen the column-access code show up as a
particularly hot spot in any profile I've run.  I doubt you could
actually measure any difference, let alone show it to be important
enough to be worth worrying about.

In any case, char(n) will still do what you want for reasonable-size
records.  The TOAST code only kicks in when the total tuple size exceeds
BLCKSZ/4 ... and at that point, compression is a good idea in any case.

Now that you mention it, though, doesn't TOAST break heapam's assumption
that char(n) is fixed length?  Seems like we'd better either remove that
assumption or mark char(n) nontoastable.  Any opinions which is better?

			regards, tom lane

Sun, 03 Sep 2000 01:07:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [Solved] SQL Server to PostgreSQL 
Tom Lane wrote:
> Now that you mention it, though, doesn't TOAST break heapam's assumption
> that char(n) is fixed length?  Seems like we'd better either remove that
> assumption or mark char(n) nontoastable.  Any opinions which is better?

    Is  the  saved overhead from assuming char(n) is fixed really
    that big that it's worth NOT to gain  the  TOAST  advantages?
    After  the  GB  benchmarks  we  know  that we have some spare
    performance to waste for such things :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Sun, 3 Sep 2000 04:03:31 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: [Solved] SQL Server to PostgreSQL
Tom Lane wrote:
> Now that you mention it, though, doesn't TOAST break heapam's assumption
> that char(n) is fixed length?  Seems like we'd better either remove that
> assumption or mark char(n) nontoastable.  Any opinions which is better?

    Is  the  saved overhead from assuming char(n) is fixed really
    that big that it's worth NOT to gain  the  TOAST  advantages?
    After  the  GB  benchmarks  we  know  that we have some spare
    performance to waste for such things :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





Sun, 3 Sep 2000 10:02:51 -0400
Jan Wieck <janwieck@Yahoo.com>
Re: [Solved] SQL Server to PostgreSQL
Ian Turner <vectro@pipeline.com> writes:
> Don't variable-length records incur a performance overhead?

Only to the extent that the system can't cache offset information for
later columns in that table.  While someone evidently once thought that
was worthwhile, I've never seen the column-access code show up as a
particularly hot spot in any profile I've run.  I doubt you could
actually measure any difference, let alone show it to be important
enough to be worth worrying about.

In any case, char(n) will still do what you want for reasonable-size
records.  The TOAST code only kicks in when the total tuple size exceeds
BLCKSZ/4 ... and at that point, compression is a good idea in any case.

Now that you mention it, though, doesn't TOAST break heapam's assumption
that char(n) is fixed length?  Seems like we'd better either remove that
assumption or mark char(n) nontoastable.  Any opinions which is better?

			regards, tom lane



Sun, 3 Sep 2000 10:03:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [Solved] SQL Server to PostgreSQL
Jan Wieck <janwieck@Yahoo.com> writes:
> Tom Lane wrote:
>> Now that you mention it, though, doesn't TOAST break heapam's assumption
>> that char(n) is fixed length?  Seems like we'd better either remove that
>> assumption or mark char(n) nontoastable.  Any opinions which is better?

>     Is  the  saved overhead from assuming char(n) is fixed really
>     that big that it's worth NOT to gain  the  TOAST  advantages?

No, I don't think so.  Instead of pulling out the code entirely,
however, we could extend the VARLENA_FIXED_SIZE macro to also check
whether attstorage = 'p' before reporting that a char(n) field is
fixed-size.  Then someone who's really intent on keeping the old
behavior could hack the attribute entry to make it so.

I seem to recall that your original idea for TOAST included an ALTER
command to allow adjustment of attstorage settings, but that didn't
get done did it?  Seems like it would be risky to change the setting
except on an empty table.

Not sure if any of this is worth keeping, or if we should just simplify
the code in heaptuple.c to get rid of the notion of "fixed size"
varlena attributes.  It's certainly not going to be a mainstream case
anymore, so I question whether the check has any hope of saving more
cycles than it costs.  Yet it seems a shame to wipe out this hack
entirely...

			regards, tom lane

Sun, 03 Sep 2000 20:37:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Viability of VARLENA_FIXED_SIZE()
> Not sure if any of this is worth keeping, or if we should just simplify
> the code in heaptuple.c to get rid of the notion of "fixed size"
> varlena attributes.  It's certainly not going to be a mainstream case
> anymore, so I question whether the check has any hope of saving more
> cycles than it costs.  Yet it seems a shame to wipe out this hack
> entirely...

Not sure if this is relevant (but when does that stop me ;):

The only truly "fixed length" string from a storage standpoint is for
single-byte encodings (and Unicode, I suppose). Eventually, we will need
the notion of both "octet length" *and* "character length" in our
backend code, and for non-ASCII encodings nothing will be of fixed octet
length anyway.

                    - Thomas

Mon, 04 Sep 2000 17:47:13 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Viability of VARLENA_FIXED_SIZE()
[ Charset ISO-8859-1 unsupported, converting... ]
> Ian Turner <vectro@pipeline.com> writes:
> > Don't variable-length records incur a performance overhead?
> 
> Only to the extent that the system can't cache offset information for
> later columns in that table.  While someone evidently once thought that
> was worthwhile, I've never seen the column-access code show up as a
> particularly hot spot in any profile I've run.  I doubt you could
> actually measure any difference, let alone show it to be important
> enough to be worth worrying about.

It clearly is a hot-spot.  That monster macro, fastgetattr(), in
heapam.h is in there for a reason. It accounts for about 5% for straight
sequential scan case, last I heard from someone who ran a test.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Sat, 14 Oct 2000 23:28:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [Solved] SQL Server to PostgreSQL
> Ian Turner <vectro@pipeline.com> writes:
> > Don't variable-length records incur a performance overhead?
> 
> Only to the extent that the system can't cache offset information for
> later columns in that table.  While someone evidently once thought that
> was worthwhile, I've never seen the column-access code show up as a
> particularly hot spot in any profile I've run.  I doubt you could
> actually measure any difference, let alone show it to be important
> enough to be worth worrying about.
> 
> In any case, char(n) will still do what you want for reasonable-size
> records.  The TOAST code only kicks in when the total tuple size exceeds
> BLCKSZ/4 ... and at that point, compression is a good idea in any case.

My logic is that I use char() when I want the length to be fixed, like
2-letter state codes, and varchar() for others where I just want a
maximum allowed, like last name.  I use text for arbitrary length stuff.
Tom is right that though there is a small performance difference, it is
better just to use the right type.

> 
> Now that you mention it, though, doesn't TOAST break heapam's assumption
> that char(n) is fixed length?  Seems like we'd better either remove that
> assumption or mark char(n) nontoastable.  Any opinions which is better?

I am sure Jan handled that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Sat, 14 Oct 2000 23:32:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [Solved] SQL Server to PostgreSQL
> Tom Lane wrote:
> > Now that you mention it, though, doesn't TOAST break heapam's assumption
> > that char(n) is fixed length?  Seems like we'd better either remove that
> > assumption or mark char(n) nontoastable.  Any opinions which is better?
> 
>     Is  the  saved overhead from assuming char(n) is fixed really
>     that big that it's worth NOT to gain  the  TOAST  advantages?
>     After  the  GB  benchmarks  we  know  that we have some spare
>     performance to waste for such things :-)

Oh, now I get it.  Some TOAST values may be out-of line.  Can we really
throw char() into TOAST?  I guess we can.  We have to record somewhere
that we have toasted that tuple and disable the offset cache for it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Sat, 14 Oct 2000 23:33:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [Solved] SQL Server to PostgreSQL