sequence primary key
sequence primary key
От:
Virginie Garcia <Virginie.Garcia@pmtg.u-bordeaux2.fr>
Дата:
Hi all,
my question is about sequences and primary key of a table.
More details : I create a sequence and a table with these lines :
CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1
CACHE 1;
CREATE TABLE "toto" (
"toto_id" integer PRIMARY KEY DEFAULT nextval('"my_id"'),
"db" text);
in a a sql file.
In fact, when I fill database I would like "toto_id" field to be filled
directly by sgdb by using sequence "my_id" but not by me.
This way doesn't work. It's perhaps a big mistake !!!
How is-it possible, so ?
Thanks a lot.
Virginie.
Re: sequence primary key
От:
Jason Earl <jdearl@yahoo.com>
Дата:
You are *very* close to having what you want. The
trick is to remember that the a "default" value is
simply the value that will be inserted into your new
record if you don't supply one yourself. In other
words, you need to make sure that you don't specify a
toto_id in your insert query. For example your query
should look something like:
INSERT INTO toto (db) VALUES ('SOME VALUE')
And then when you select from the table you will find
that the toto_id column was filled automagically.
processdata=> SELECT * FROM toto;
toto_id | db
---------+------------
1 | SOME VALUE
(1 row)
I hope this was helpful,
Jason
--- Virginie Garcia
wrote:
> Hi all,
>
> my question is about sequences and primary key of a
> table.
> More details : I create a sequence and a table with
> these lines :
>
> CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1
> MAXVALUE 2147483647 START 1
> CACHE 1;
>
> CREATE TABLE "toto" (
> "toto_id" integer PRIMARY KEY DEFAULT
> nextval('"my_id"'),
> "db" text);
>
> in a a sql file.
> In fact, when I fill database I would like "toto_id"
> field to be filled
> directly by sgdb by using sequence "my_id" but not
> by me.
> This way doesn't work. It's perhaps a big mistake
> !!!
> How is-it possible, so ?
> Thanks a lot.
>
> Virginie.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
Re: sequence primary key
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Jason Earl writes:
> ... you need to make sure that you don't specify a
> toto_id in your insert query. For example your query
> should look something like:
> INSERT INTO toto (db) VALUES ('SOME VALUE')
> And then when you select from the table you will find
> that the toto_id column was filled automagically.
> processdata=> SELECT * FROM toto;
> toto_id | db
> ---------+------------
> 1 | SOME VALUE
> (1 row)
One way to make this easier is to put the columns that are normally
filled by a DEFAULT at the end of the table, not the start. For
example, if you'd made "db" the first column then it'd have sufficed
to write
INSERT INTO toto VALUES ('SOME VALUE')
with the same results as above.
However, a lot of people say it is good practice to write an explicit
list of the column names you are supplying in *every* INSERT, whether
you think you are skipping over defaulted columns or not. This makes
your code less likely to break when you rearrange the database layout.
regards, tom lane
Confused about rights
От:
Perry The Cynic <perry@cynic.org>
Дата:
Greetings, I am just starting to use postgresql (7.0.2, on Debian), and I am rather confused about how to use its access rights (grant/revoke). Two questions in particular: (1) I created a database and table as a super-user, then started to GRANT away. The first thing I noticed is that UPDATE and DELETE rights are actually the same in postgresql - did I get that right? So I can't actually give the right to change anything in a table without also giving the right to completely erase all rows from it? (2) The COPY command documentation says that COPY ... FROM STDIN requires "insert OR update" rights to the table. I tried to set up a non-privileged user with just INSERT rights on a table, but COPY rejects an attempt to COPY unless I (also, or just) grant UPDATE rights to that user. I can use INSERT commands just fine with just INSERT rights, but not COPY. Is that a feature or a bug? (It is definitely annoying - I wanted to set up a "least privileged" account for a web server to just dump data into the table, so it can't possibly change or delete anything once it's stored there.) I tried to browse/search the mailing list archives and "interactive documentation", but I guess I don't know the magic keywords to look for... Thanks -- perry --------------------------------------------------------------------------- Perry The Cynic perry@cynic.org To a blind optimist, an optimistic realist must seem like an Accursed Cynic. ---------------------------------------------------------------------------
RE: sequence primary key
От:
"Robby Slaughter" <webmaster@robbyslaughter.com>
Дата:
Virginie,
Sounds like you could just user the SERIAL data type. Try:
CREATE TABLE toto (
toto_id SERIAL PRIMARY KEY
db TEXT);
Then whenver you do an insert...
INSERT INTO toto(db) VALUES ('and your little dog too!');
...you'll find that toto_id automagically increments for you.
Good luck. Hope this helps.
-Robby
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Virginie Garcia
Sent: Tuesday, July 17, 2001 10:44 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] sequence primary key
Hi all,
my question is about sequences and primary key of a table.
More details : I create a sequence and a table with these lines :
CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1
CACHE 1;
CREATE TABLE "toto" (
"toto_id" integer PRIMARY KEY DEFAULT nextval('"my_id"'),
"db" text);
in a a sql file.
In fact, when I fill database I would like "toto_id" field to be filled
directly by sgdb by using sequence "my_id" but not by me.
This way doesn't work. It's perhaps a big mistake !!!
How is-it possible, so ?
Thanks a lot.
Virginie.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
