[SQL] Creating sequences
Is it possible to have a sequence (or something like it) when it increments alpha-numeric? Say the first value is set at A01 and it will increment to A02, A03 by default. Also, it would be good if it could be made the primary key. Adam Lang Systems Engineer Rutgers Casualty Insurance Company
Re: [SQL] Creating sequences
This is what I wanted to do. When I can have a primary key that is meaningful AND won't change, I would like to try to use it, otherwise I'd probably use an OID. So... I'm developing a database that will have the primary key like a "case number" (like when you call for tech support). Say your call center has 3 products: Word processor, Spread Sheet, Database. When you open a case for a product, that product's code will be the beginning of the case number. Say, Word processor will be WP1, Spreadsheet will be SS1, etc. (Each product would be in its own table). Each time a case is opened, it would increment to the next number. Granted, I could probably just code something where I set up a sequence and when I do an insert, it concatenates the prefix to the number and then inserts the value, but I was curious if there was a way where you can set the initial value to alphanumeric and it will increment from there. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Gannon, Terry" <[EMAIL PROTECTED]> To: "'Adam Lang'" <[EMAIL PROTECTED]> Sent: Friday, August 18, 2000 4:15 PM Subject: RE: [SQL] Creating sequences > Adam -- suggest that a way to do this would be to use a stored procedure > that would read the sequence number associated with the table, and build the > variable as you require. You would then setup a trigger that would launch > the stored procedure everytime you insert a new record into the table? If > that's a little foggy, then I would suggest that if you can part with the > definition of the table that I could put together a little example to > illustrate...regards... > > Terry Gannon > [EMAIL PROTECTED] > > > -Original Message- > > From: Adam Lang [SMTP:[EMAIL PROTECTED]] > > Sent: Friday, August 18, 2000 12:25 PM > > To: [EMAIL PROTECTED] > > Subject: [SQL] Creating sequences > > > > Is it possible to have a sequence (or something like it) when it > > increments > > alpha-numeric? > > > > Say the first value is set at A01 and it will increment to A02, A03 by > > default. Also, it would be good if it could be made the primary key. > > > > Adam Lang > > Systems Engineer > > Rutgers Casualty Insurance Company
[SQL] Copy To - fixed width
Is it possible to copy from a text file that has the fields as fixed width with no delimiter to a table? The other option is a CSV file... but then how do I handle if there is a comma in one of the fields? I'm transfering information from an AS/400 ... if it is into a text file, it is fixed width, if I use Excel, it is Comma separated.
Re: [SQL] Copy To - fixed width
Sort of nevermind, can I can save it tab delimited, but I am still curious if it can be done using fixed width fields. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Adam Lang" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, August 23, 2000 10:38 AM Subject: [SQL] Copy To - fixed width > Is it possible to copy from a text file that has the fields as fixed width > with no delimiter to a table? > > The other option is a CSV file... but then how do I handle if there is a > comma in one of the fields? > > I'm transfering information from an AS/400 ... if it is into a text file, it > is fixed width, if I use Excel, it is Comma separated.
[SQL] copy from
I need additional help involving the copy from command I have a database with several fields: id, name, address, zip id is the primary key and I use nextval to get a value. The file I want to upload is in comma separated value, that does not have a primry key (or an id) field. How would I use copy from and assign the correct id number? Adam Lang Systems Engineer Rutgers Casualty Insurance Company
Re: [SQL] copy from
I get it. I upload to the temp table, then for the select/insert I just do the nextval(seq_id) for the primary key field, correct? Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Adam Lang" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 14, 2000 5:17 PM Subject: Re: [SQL] copy from > > One way would be to copy the data into a temporary table that doesn't > have the id field, and then > insert into table (fields) select * from temptable; > > Stephan Szabo > [EMAIL PROTECTED] > > On Mon, 14 Aug 2000, Adam Lang wrote: > > > I need additional help involving the copy from command > > > > I have a database with several fields: id, name, address, zip > > > > id is the primary key and I use nextval to get a value. > > > > The file I want to upload is in comma separated value, that does not have a > > primry key (or an id) field. > > > > How would I use copy from and assign the correct id number? > > > > Adam Lang > > Systems Engineer > > Rutgers Casualty Insurance Company > >
Re: [SQL] 8K Limit, whats the best strategy?
I believe you are correct. Version 7.1 will supposedly eliminate the row size. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Thomas Swan" <[EMAIL PROTECTED]> To: "Keith Wong" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, August 15, 2000 11:37 AM Subject: Re: [SQL] 8K Limit, whats the best strategy? > At 06:29 AM 8/15/2000, Keith Wong wrote: > >Hi everyone, > > > >Just wondering what strategies people have used to get around the 8K row > >limit in Postgres. > >If anyone has been troubled by this limitation before and has a nice > >solution around it, I would love to hear it. > > > >My application is a web-based system in which needs to store large amounts > >of text. > >The text stored needs to searchable as well. > > You can you the type LZTEXT which is compressed text so your mileage will vary. > > The other option is to go in and recompile for a larger row size. Most of > the people I have talked to had no problems with this route. I move the > size limit on an x86 Linux box to 32K (the max) and have not had any > problems. In the next release, I don't believe the row size limit will > exist (correct me if I'm wrong). > > Let me know the system your on, and I will try to help if I can. > > - > - Thomas Swan > - Graduate Student - Computer Science > - The University of Mississippi > - > - "People can be categorized into two fundamental > - groups, those that divide people into two groups > - and those that don't."
Re: [SQL] copy from
Hmmm... well, I don't think I have an "explicit" nextval. I created the table and then I did a create sequence broker_id; Are you implying that I can set the field to automatically create a nextval? Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Adam Lang" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, August 15, 2000 12:34 PM Subject: Re: [SQL] copy from > Well, if you define your main table to have a serial, or an explicit > default nextval(seqid), you won't even need to do that. Just leave > the column off the insert list and the default should fill it for you. > > Stephan Szabo > [EMAIL PROTECTED] > > On Tue, 15 Aug 2000, Adam Lang wrote: > > > I get it. I upload to the temp table, then for the select/insert I just do > > the nextval(seq_id) for the primary key field, correct?
Re: [SQL] copy from
Gotcha. Now, if I do an insert and just don't specify that field at all, it
will, use the default value, correct? As long as I don't "touch" the field
with anything it uses the default.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 2:00 PM
Subject: Re: [SQL] copy from
>
> Sort of. You can give the field a default value of
> nextval() which means that if you do not specify
> the column in an insert, it automatically gets the default
> value which should be the next value in the sequence.
> Note, that not putting the column is different from inserting a
> NULL into the field.
>
> (Example:
> sszabo=# create sequence xyzseq;
> CREATE
> sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
> int);
> CREATE
> sszabo=# insert into xyzseqtest (b) values (2);
> INSERT 172188 1
> sszabo=# insert into xyzseqtest (b) values (3);
> INSERT 172189 1
> sszabo=# select * from xyzseqtest;
> a | b
> ---+---
> 1 | 2
> 2 | 3
> (2 rows)
> )
>
> There are issues about this dealing with rules and triggers where another
> row may be inserted or the default may be evaluated a second time where
> you want to get the value you just inserted back, but in general it works.
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > Hmmm... well, I don't think I have an "explicit" nextval. I created the
> > table and then I did a create sequence broker_id;
> >
> > Are you implying that I can set the field to automatically create a
nextval?
