pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT w/o variable names for a SERIAL type?
10+ messages / 6 participants
[nested] [flat]
* INSERT w/o variable names for a SERIAL type?
@ 2000-02-25 13:59 Emils Klotins <emils@mail.usis.bkc.lv>
0 siblings, 2 replies; 10+ messages in thread
From: Emils Klotins @ 2000-02-25 13:59 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello.
I am using, which is written for connecting to another server
and uses the AUTO_INCREMENT and BLOB fields (for storing
text). Apparently it was written for mysql(?).
I wanted to write a #define or two to change the relevant
places, such as
#define IDKEY "INTEGER AUTO_INCREMENT PRIMARY
KEY" // mysql version
#define IDKEY "SERIAL PRIMARY KEY" // postgres
and currently stumbled upon mysql version of inserting the
autoincrement value: NULL
ie.
CREATE TABLE mytable (id SERIAL PRIMARY KEY, textfield
text);
the mysql variant for inserting a row would be:
INSERT INTO mytable VALUES (NULL, 'textvalue');
and NULL would get translated into the real value.
Now, if I want to write a general #define, I'd need to have a way
to specify 'default' for SERIAL field, for, if I omit the id field in
VALUES, I need to specify all the rest of the fields explicitly.
Is there any value I could put in place of id in VALUES part, to
make it replaced with the next value in sequence?
Emils
-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.2 -- QDPGP 2.61a
Comment: http://community.wow.net/grt/qdpgp.html
iQA/AwUBOLZuld0sxa1MAPWHEQJtVACg3Nsd7YiH3q2aXkIMSM/mjaLD3fgAn0bu
S4NHXOsVsS26nntkkInqxBJw
=HodA
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-25 15:14 Alessio Bragadini <alessio@albourne.com>
parent: Emils Klotins <emils@mail.usis.bkc.lv>
1 sibling, 0 replies; 10+ messages in thread
From: Alessio Bragadini @ 2000-02-25 15:14 UTC (permalink / raw)
To: Emils Klotins <emils@mail.usis.bkc.lv>; +Cc: pgsql-sql
Emils Klotins wrote:
> I am using, which is written for connecting to another server
> and uses the AUTO_INCREMENT and BLOB fields (for storing
> text). Apparently it was written for mysql(?).
Most probably.
> I wanted to write a #define or two to change the relevant
> places, such as
>
> #define IDKEY "INTEGER AUTO_INCREMENT PRIMARY
> KEY" // mysql version
> #define IDKEY "SERIAL PRIMARY KEY" // postgres
Note that SERIAL is syntactic sugar for
INT4 NOT NULL DEFAULT NEXTVAL('<table>_<field>_SEQ')
> INSERT INTO mytable VALUES (NULL, 'textvalue');
> and NULL would get translated into the real value.
Please allow me not to comment on mysql behaviour...
> Now, if I want to write a general #define, I'd need to have a way
> to specify 'default' for SERIAL field, for, if I omit the id field in
> VALUES, I need to specify all the rest of the fields explicitly.
>
> Is there any value I could put in place of id in VALUES part, to
> make it replaced with the next value in sequence?
NEXTVAL('<table>_<field>_seq') should work.
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://www.sevenseas.org/~alessio
Nicosia, Cyprus phone: +357-2-750652
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 15:18 Peter Eisentraut <peter_e@gmx.net>
parent: Emils Klotins <emils@mail.usis.bkc.lv>
1 sibling, 2 replies; 10+ messages in thread
From: Peter Eisentraut @ 2000-02-27 15:18 UTC (permalink / raw)
To: Emils Klotins <emils@mail.usis.bkc.lv>; +Cc: pgsql-sql
Emils Klotins writes:
> Now, if I want to write a general #define, I'd need to have a way
> to specify 'default' for SERIAL field, for, if I omit the id field in
> VALUES, I need to specify all the rest of the fields explicitly.
>
> Is there any value I could put in place of id in VALUES part, to
> make it replaced with the next value in sequence?
INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 18:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2000-02-27 18:29 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql
Peter Eisentraut <peter_e@gmx.net> writes:
> Emils Klotins writes:
>> Now, if I want to write a general #define, I'd need to have a way
>> to specify 'default' for SERIAL field, for, if I omit the id field in
>> VALUES, I need to specify all the rest of the fields explicitly.
>>
>> Is there any value I could put in place of id in VALUES part, to
>> make it replaced with the next value in sequence?
> INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
I think that is legal SQL92 syntax, but Postgres doesn't accept it
at present.
The usual recommendation is to call out the columns you are loading
explicitly:
INSERT INTO my_table(a,b,d) VALUES (val-for-a, val-for-b, val-for-d);
The ones you don't load get their default values substituted instead.
This way is a shade more verbose, but it's good solid defensive
programming practice: the insert will do what it's supposed to
even if the table schema changes to add/delete/reorder columns.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 18:58 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2000-02-27 18:58 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Emils Klotins writes:
>
> > Now, if I want to write a general #define, I'd need to have a way
> > to specify 'default' for SERIAL field, for, if I omit the id field in
> > VALUES, I need to specify all the rest of the fields explicitly.
> >
> > Is there any value I could put in place of id in VALUES part, to
> > make it replaced with the next value in sequence?
>
> INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
I didn't think that worked, and it doesn't seem to work. Should it?
test=> create table j(x serial, y char(10))\g
NOTICE: CREATE TABLE will create implicit sequence 'j_x_seq' for SERIAL
column 'j.x'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'j_x_key' for
table 'j'
CREATE
test=> insert into j values (default,'yes');
ERROR: parser: parse error at or near "default"
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 19:04 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2000-02-27 19:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql
> > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
>
> I think that is legal SQL92 syntax, but Postgres doesn't accept it
> at present.
>
> The usual recommendation is to call out the columns you are loading
> explicitly:
>
> INSERT INTO my_table(a,b,d) VALUES (val-for-a, val-for-b, val-for-d);
>
> The ones you don't load get their default values substituted instead.
>
> This way is a shade more verbose, but it's good solid defensive
> programming practice: the insert will do what it's supposed to
> even if the table schema changes to add/delete/reorder columns.
The problem is when you are inserting >50 columns, it is a pain. The
use of DEFAULT would also allow SERIAL columns to get the proper
nextval(), rather than having specify the nextval() call specifically.
Added to TODO.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 19:44 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Ross J. Reedstrom @ 2000-02-27 19:44 UTC (permalink / raw)
To: pgsql-sql
On Sun, Feb 27, 2000 at 02:04:42PM -0500, Bruce Momjian wrote:
> > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
> >
> > I think that is legal SQL92 syntax, but Postgres doesn't accept it
> > at present.
> >
>
> The problem is when you are inserting >50 columns, it is a pain. The
> use of DEFAULT would also allow SERIAL columns to get the proper
> nextval(), rather than having specify the nextval() call specifically.
This, I think, is the best reason for doing it: even if you call
out the column names, as Tom rightly mentions is best, using DEFAULT
allows writing INSERTs without depending on the details of how SERIAL
is implemented in pgsql: i.e. knowing the algorithm for constructing
the sequenec name.
>
> Added to TODO.
Perhaps we should note which items on the TODO list are needed for what
level of SQL92 compliance. This one, for example, is not necessary for
ENTRY level, but required at INTERMEDIATE. Not that that should stop
anyone from implementing it: SQL92 Entry is a surprisingly limited
subset, and we have a lot of INTERMEDIATE and FULL features, already.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 00:00 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2000-02-28 00:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql
Tom Lane writes:
> > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
>
> I think that is legal SQL92 syntax, but Postgres doesn't accept it
> at present.
Oops!
We do support INSERT .. DEFAULT VALUES, so I guessed too far.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 00:19 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2000-02-28 00:19 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Tom Lane writes:
>
> > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
> >
> > I think that is legal SQL92 syntax, but Postgres doesn't accept it
> > at present.
>
> Oops!
>
> We do support INSERT .. DEFAULT VALUES, so I guessed too far.
We do? What does the DEFAULT do?
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 20:24 Peter Eisentraut <peter_e@gmx.net>
parent: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Eisentraut @ 2000-02-28 20:24 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: pgsql-sql
Ross J. Reedstrom writes:
> Perhaps we should note which items on the TODO list are needed for what
> level of SQL92 compliance. This one, for example, is not necessary for
> ENTRY level, but required at INTERMEDIATE. Not that that should stop
> anyone from implementing it: SQL92 Entry is a surprisingly limited
> subset, and we have a lot of INTERMEDIATE and FULL features, already.
On Feb 19 I sent out a list of things that are missing for SQL Entry Level
compliance. (See message on pgsql-hackers on that date.) The only major
items left are schemas and the correct transaction semantics. When I get
some time I'm planning on making this into a real document available
somewhere and I could add Intermediate as well.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-02-28 20:24 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-25 13:59 INSERT w/o variable names for a SERIAL type? Emils Klotins <emils@mail.usis.bkc.lv>
2000-02-25 15:14 ` Alessio Bragadini <alessio@albourne.com>
2000-02-27 15:18 ` Peter Eisentraut <peter_e@gmx.net>
2000-02-27 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-27 19:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-02-27 19:44 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-02-28 20:24 ` Peter Eisentraut <peter_e@gmx.net>
2000-02-28 00:00 ` Peter Eisentraut <peter_e@gmx.net>
2000-02-28 00:19 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-02-27 18:58 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox