postgres

kimi

Hi..

What is the suitable data type in postgres to used for student matricno?
The matricno will start with A1#### (# - Number). This number will be auto-increment.

Please help.

Sxooter

The correct type is a composite default type, like so:

create sequence ls;
create table lt (nam text, d date, id text default 'AI'||nextval('l')::text);

Now, did that number need to be padded out with 0 characters? If so, then things get a little more difficult.

kimi

Hi there

For your info my matric no will begin with A1 0001, A1 0002 , A1 0003 ...
How bout this?

TQ.

chriskl

Then just put in a space:

create sequence ls;
create table lt (nam text, d date, id text default 'AI '||nextval('l')::text);

Chris

kimi

I'm still confius about this, now how would i know the next number(sequence)?

Additional here.
I dont want A1 to be the default value because it will change for every year.
2002 - A1/2
2003 - B1/2
2004 - C1/2
and 1/2 is for current semester.

I have do something like this
create sequence ls increment 1 START 1;
create table lt (nam text, d date, id text default 'A1'||nextval('ls')::text)
My id will be A11 and all i want it to be A10001.

Please help.
Thanks.

chriskl

RTFM!!

create table lt (nam text, d date, id text default 'A1'|| ltrim(to_char(nextval('ls'), '0000')))

Also, don't think about trying this trick of leaving off inserting the serial value at the end of the table. This will not work in postgres 7.3

Chris

[deleted]

IF you want to use more prefixes than A1, then you should not store the prefix in the same column, just create a new column and put 'A1' or 'C1' in there.
Then use a 'serial' kolumn to create the number that goes after 'A1'.

Sxooter

Also, don't think about trying this trick of leaving off inserting the serial value at the end of the table. This will not work in postgres 7.3

Really, then how come this just worked:

Welcome to psql 7.3b1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# create table test (nam text, id serial);
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
CREATE TABLE
postgres=# insert into test values ('abc');
INSERT 16984 1

[deleted]

"Really, then how come this just worked: "

Try it again with the ID in the first column. Doesn't work anymore does it? 🙂

Anyway, does anybody really care about that?

It is extremely bad practice to insert data this way, so just don't do it.

Sxooter

It may be bad practice, but it is defined by the SQL 92 standard as acceptable. (look it up, I did.)

also, you can use the DEFAULT keyword in place of a value, like so...

insert into table values (DEFAULT,'abc');

[deleted]

" but it is defined by the SQL 92 standard as acceptable. (look it up, I did.)"

I saw your post, doesn't look much like what you are saying.

kimi

I'm trying to get the next sequence value by using nextval.
I have try this on console
sql=# select (to_char(nextval('z1'), '0000'));

to_char

0084

How to do it in php?
This is what i do.
$res= pg_exec("SELECT (to_char(nextval('z1'), '0000'))");
When i echo $res it will give me Resource id #4.
How the right syntax?

Thanks.

kimi

After a while i got it. It will be like this
$result = pg_exec("select (to_char(currval('ls'), '0000'));");
if($result) {
$num = pg_result($result,0,0);
echo $num;
echo "<p>";
}

pg_freeresult($result);

And now i'm trying to use setval to set the sequence back to 1.
select setval ('ls', 1);

But when i do insert into lt (nam, d) values ('A','18-09-2002');
the id become A10002.
I want the id begin with A10001.
Is there any possibilities to do this?

Sxooter

You're very very close. Just set it like this:

select setval ('seq',1,false)

and the next number selected will be 1.

See section 4.11 of the user's guide to postgresql for the sequence manipulating functions