nuB: How to add a sequence to an already created table? 1

chedder123 · Jan 2, 2003

Ok, this is my first jump into SQL/Perl stuff and I'm a little lost. I'm running PostgreSQL and already have a populated table, however I need to have an incrementing variable every time a new row is submitted. I also need a time stamp. My question is, once I already have the table created, how can I modify the variable's attributes so as to start a sequence, or tell it to do a timestamp, or add a value to a variable everytime that a new row is added. I imagine I could update table on the command line using the ALTER command, but I'm not sure of the exact syntax. If someone wouldn't mind just throwing it out at me, I'd greatly appreciate it.
Three variables:

"leadid" int4
"created" timestamptz
"export_counter" int4

I need a sequence on the leadid so it just increments the number, the timestamptz to be added everytime, and the export_counter just needs the value "0" assigned to it. Simple question. I would just create a new table, but the table already has a LOT of data already populated and I would like to just modify it without erasing the data or having to transfer all the data to a new table. Thanks

-Chad Sanders

Leland123 · Jan 2, 2003

Hi nestorjb,

Refer to the link below as to how to create a sequence.

http://www.postgresql.org/idocs/index.php?sql-createsequence.html

Once a sequence has been create, there is no need to refer to the init4 or init8 key field in you insert command. Postgres will automatically handle this for you via a default value. I'm not sure whether creating a sequence in an after the fact table is exactly like creating a serial field during table creation. I have a table called clintmst. It has a primary key field called clientno. When I created the table using phpPgAdmin, I defined the field clientno as SERIAL. PhpPgAdmin created the sequence and added the following as the default value for the clientno field of the clintmst table. It also created a sequence named clintmst_clientno_seq.

Defalut value for the clientno field of the clintmst table:

nextval('"clintmst_clientno_seq"'::text)

Use 'next' as the value of the timestap field when doing an insert. This will create a current timestamp in the timestamp field.

Below is some perl example code:

############################### Connect to the database.
##############################

$dbh = DBI->connect("DBI

g:dbname=$the_db;host=$the_host", $the_user,
$the_pass_wd, {'RaiseError' => 1});

$dbh->do("INSERT INTO smorders
(date_it,first,last,company,address,city,state,postal,phone,
email,pass_key,payby,cardtype,cardholder,cardnumber,cardmont
h,cardyear,amount,processed) VALUES
('now',$lfirst,$llast,$lcompany,$laddress,$lcity,$lstate,$lp
ostal,$lphone,$lemail,$lpass_key,$lpayby,$lcardtype,$lcardho
lder,$lcardnumber,$lcardmonth,$lcardyear,$lamount,$lprocesse
d)&quot

;

############################################################
$sth = $dbh->prepare("SELECT currval
('smorders_cno_seq')&quot

;

$sth->execute;

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {
@temp = @$row;
}

$leland=$temp[0];
##########################################################

$sth = $dbh->prepare("SELECT cno,first,last,company,email
FROM smorders WHERE cno=$leland AND first=$lfirst AND
last=$llast AND company=$lcompany AND email=$lemail&quot

;

$sth->execute;

############################################################
#####################
print "<html>";

print "<head>";

print "<title>Software - Master (TM) Metacharacter</title>\n";

print "</head>";

print "<body TEXT='black' bgcolor='white'>";

print "<h3><B>";

print "<TABLE bgcolor=$the_color ALIGN='center'
cellpadding='0' cellspacing='0'
bordercolorlight=$the_borderlight
bordercolordark=$the_borderdark BORDER='7' WIDTH='100%'>\n";

print "<CAPTION><H1>Sales Order Update</H1></caption>";

print "<TR bgcolor=$headcolor> $thetitle</TR>";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

@$therow = @$row;

print sprintf "$thesprintf", @$therow;
}

$dbh->disconnect();

print "</TABLE>";
if ($leland>0) {

print "<h3><b>Enter the registration number below
into your program.</b></h3>\n";

print "<H1>$factor</H1>";

print "<h3><b>Thank You\! Your Card Has Passed
Validation. It will now be submitted for Charge
Authorization.</b></h3>\n";

print "</body>\n</html>\n";

# Remember mail to purchaser

&send_mail;

# Remember mail to Seller

&send_mailx;

} else {

print "<h3><b>Your Submission did not update our
database. Please go <FONT COLOR='\#FF0000\'>Back</font>
and resubmit your order. Thanks.</b></h3>\n";

print "</body>\n</html>\n";

}

exit;

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

Leland123 · Jan 3, 2003

Hi chedder123,

I made a mistake when I indicated 'next' as a value in an insert statement for a timestamp field would insert the current time. I should have said 'now'. Sorry about that.

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

nuB: How to add a sequence to an already created table? 1

chedder123

Programmer

Leland123

Programmer

Leland123

Programmer

Similar threads

Part and Inventory Search

Sponsor