Обсуждение: help with serial type
Hi , I'm a postgreSQL newbie. I have a table called "atable" that has
the columns:
title varchar(20)
name varchar(20)
id serial
if I do:
INSERT INTO TABLE atable VALUES('SQL1','Jason')
the 'id' gets updated with a new number automatically. I then later
added a new column called 'date'. Now if I do an insert with:
INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
the id will update the first time to '0', but using this command again:
INSERT INTO TABLE atable VALUES('SQL3','Toy','',date('now'))
it won't let me update because there are duplicate 'id's.
I also tried
INSERT INTO TABLE a(title, name, date) VALUES('SQL3','Toy',date('now'))
but it won't automatically update 'id' also. How can I make the insert
command automatically update the 'id' to the next highest number? or
how can I rearrange the columns so that 'id' is the last column and
'date' comes before 'id' so that way 'id' will automatically update.
thanks in advance.
Jason Toy
> if I do:
> INSERT INTO TABLE atable VALUES('SQL1','Jason')
> the 'id' gets updated with a new number automatically.
Correct, the default value is the next value in the sequence.
> I then later
> added a new column called 'date'. Now if I do an insert with:
> INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
> the id will update the first time to '0'
That is because you are specifying a value for the id field, which means
that it *doesn't* use the default value (which is the next number in the
sequence). I'm surprised that this will work... I suppose that it is casting
the empty string to zero, which is the value it is inserting.
> , but using this command again:
> INSERT INTO TABLE atable VALUES('SQL3','Toy','',date('now'))
> it won't let me update because there are duplicate 'id's.
That is because it thinks you are explicitly using a zero value again... and
when you use the explicit value, it ignores the default.
> I also tried
> INSERT INTO TABLE a(title, name, date) VALUES('SQL3','Toy',date('now'))
> but it won't automatically update 'id' also.
I'm surprised that works at all... the name of the table is "atable", isn't
it? Try this:
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
Greg
On Wed, 25 Apr 2001, Jason wrote:
> Hi , I'm a postgreSQL newbie. I have a table called "atable" that has
> the columns:
> title varchar(20)
> name varchar(20)
> id serial
> if I do:
> INSERT INTO TABLE atable VALUES('SQL1','Jason')
> the 'id' gets updated with a new number automatically. I then later
> added a new column called 'date'. Now if I do an insert with:
> INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
Use:
INSERT INTO atable
(title,name,date)
VALUES('SQL2','Toy',date('now'));
I don't know if you can name a column "date" because I think it's a
reserved word.
Poul L. Christiansen
Jason writes:
> Hi , I'm a postgreSQL newbie. I have a table called "atable" that has
> the columns:
> title varchar(20)
> name varchar(20)
> id serial
> if I do:
> INSERT INTO TABLE atable VALUES('SQL1','Jason')
> the 'id' gets updated with a new number automatically. I then later
> added a new column called 'date'. Now if I do an insert with:
> INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
> the id will update the first time to '0',
This is not really valid. What you are telling PostgreSQL is to insert a
value of '' (empty string) into the id column. This gets converted to 0
(zero) by the implicit type converter. The serial type only generates a
sequence number if you do not override it explicitly with a different
value. So what you want is something like this:
INSERT INTO TABLE atable (title, name, date_field)
VALUES ('SQL2', 'Toy', current_date);
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> > I'm surprised that works at all... the name of the table is "atable",
isn't
> > it? Try this:
> >
> > INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
> >
> > Greg
>
> Thanks for the help. I wrote the command out wrong in the post, I did try
the
> one that you wrote out, and it didn't update. I used:
>
> INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
> Still not sure how to fix it.
I just did this on 7.1 (PostgreSQL 7.1 on i386-unknown-freebsd4.2, compiled
by GCC 2.95.2) and it worked fine:
-----
CREATE TABLE atable (
title VARCHAR(20),
name VARCHAR(20),
id SERIAL PRIMARY KEY
);
ALTER TABLE ADD COLUMN date DATE;
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy2',date('now'));
-----
After that I tried SELECT currval('atable_id_seq'); and got the correct
value (2).
> Is it possible to rearrange columns? I looked
> in the tutorial, but didn't find anything useful.
In relational database design, the order of the columns should not matter,
only the data layout of the database (which columns are in which table). To
the best of my knowledge, the only way to reorder the columns physically
would be to recreate the table... you can do a SELECT id,title,name,date
INTO btable FROM atable, and then rename that first table I suppose. Or
rename the table first and then do the SELECT INTO.
> I don't know if you can name a column "date" because I think it's a > reserved word. Oddly enough, it *does* work (at least on my version of 7.1), although I would recommend against doing it if for no other reason than it's confusing. Greg
On Thu, 26 Apr 2001, Gregory Wood wrote: > > I don't know if you can name a column "date" because I think it's a > > reserved word. > > Oddly enough, it *does* work (at least on my version of 7.1), although I > would recommend against doing it if for no other reason than it's confusing. If you wrap them in double-quotes, you can use most reserved words as system identifiers. But I wouldn't -- some cheesy client implementation might choke on them, and better to not find that out later. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Ahhh, but I didn't use a double quote identifier. This statement worked fine for me: CREATE TABLE atable ( title VARCHAR(20), name VARCHAR(20), id SERIAL PRIMARY KEY, date DATE); Greg ----- Original Message ----- From: "Joel Burton" <jburton@scw.org> To: "Gregory Wood" <gregw@com-stock.com> Cc: "Poul L. Christiansen" <poulc@cs.auc.dk>; "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: Thursday, April 26, 2001 2:45 PM Subject: Re: help with serial type > On Thu, 26 Apr 2001, Gregory Wood wrote: > > > > I don't know if you can name a column "date" because I think it's a > > > reserved word. > > > > Oddly enough, it *does* work (at least on my version of 7.1), although I > > would recommend against doing it if for no other reason than it's confusing. > > If you wrap them in double-quotes, you can use most reserved words as > system identifiers. But I wouldn't -- some cheesy client implementation > might choke on them, and better to not find that out later. > > -- > Joel Burton <jburton@scw.org> > Director of Information Systems, Support Center of Washington > >
Try this command at the psql command line:
CREATE SEQUENCE atable_id_seq;
ALTER TABLE atable ALTER COLUMN id SET DEFAULT
nextval('"atable_id_seq"'::text);
Jeff
-----Original Message-----
How can I make the insert
command automatically update the 'id' to the next highest number?
> Sorry, I got it to work now, I think it had to do with the syntax, so what
> I was trying to do was the right command, I just wrote it out wrong.
Thanks
> for the help.
> I have 1 more question, if I use the INSERT command without telling it
which
> colmuns I want to update, can I still make the 'id' update automatically?
> so I do something like:
>
> INSERT INTO atable VALUES('SQL3', 'whatever command i need to make id
upadte
> automatically',date('now'));
>
> instead of using:
> INSERT INTO atable(column1,column2,column3) VALUES('a','b','c'));
You could explicit use the default of the serial column. i.e.:
INSERT INTO atable VALUES ('SQL3',nextval('atable_id_seq'),date('now'));
Just to clear up the mystery of the SERIAL datatype... it's actually just a
shortcut for the following:
1. Make the serial column an integer type (specifically int4).
2. Add a NOT NULL constraint to that column.
3. Create a sequence called 'tablename_serialfield_seq'.
4. Assign the serial column a default value of
nextval('tablename_serialfield_seq').
5. Create a UNIQUE index on that column.
What this means is that you can:
o Omit the column and have an autoincremented value inserted implicitly
o Add the default value sequence value explicitly
o Use whatever value you want (as long as it's an integer, not NULL, and is
unique to that column).
Greg