Thread: A couple of newbie questions ...
I've worked as a web developer on mostly small business websites for the
past seven years, and while I've had some limited experience with older
versions of PostgreSQL (7.* ??), I've mostly used MySQL all this time.
I now work for local govt and am building a large intranet-like system
which ultimately could provide storage for and various interfaces to a
significant proportion of my organisation's data including financial data.
I'm convinced that PostgreSQL's performance is not an issue (both
because it's improved and traffic will be relatively low anyway), and
that the benefits of PostgreSQL's advanced features are too good to
ignore. I'm hoping to shift quite a bit of data processing into the
database.
So anyway, life story aside, I have a couple of very newbie questions
after tinkering with PostgreSQL 8.1.9 for a day converting some
PHP/MySQL code:
1. Is a SEQUENCE what I use instead of auto_increment?
2. Does this work in PostgreSQL:
INSERT INTO table VALUES ('x','y','z')
or do I need to do this
INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
?
3. Does this work in PostgreSQL:
INSERT INTO table VALUES ('','y','z')
where the empty first item is intended for an auto_increment/SEQUENCE id
field?
If not, what is an alternative?
Thanks
Mick
On 23/07/2008 10:48, admin wrote:
> So anyway, life story aside, I have a couple of very newbie questions
> after tinkering with PostgreSQL 8.1.9 for a day converting some
> PHP/MySQL code:
Hi there,
You should consider upgrading to 8.3 if you can - there are significant
performance improvements.
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes. The easiest thing is to define the column as type SERIAL - this
will create the sequence for you and associate it with the column.
Alternatively, you can create the sequence by hand, create the column as
an integer type, and then set the default for the column as
nextval('<sequence name>'). Have a look in the docs for the gory details:
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
>
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
I personally tend to use the latter for safety, but the former will work
too as long as the columns have been defined in the order you're expecting.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
No - this will try to stuff a string value (here, an empty string) into
an integer column, which will cause an error.
If you've defined the first column to take a default value from a
sequence (as I described above), then just leave it out of the INSERT
statement altogether - this will mean you have to use a column list:
INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
HTH,
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On 23/07/2008 11:01, Raymond O'Donnell wrote: > On 23/07/2008 10:48, admin wrote: >> 1. Is a SEQUENCE what I use instead of auto_increment? > > Yes. The easiest thing is to define the column as type SERIAL - this > will create the sequence for you and associate it with the column. > Alternatively, you can create the sequence by hand, create the column as > an integer type, and then set the default for the column as I forgot to mention that you'll need to grant SELECT and UPDATE permissions on the sequence to the user that'll be accessing the table - this still catches me betimes. :-) Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
admin wrote:
> I'm convinced that PostgreSQL's performance is not an issue (both
> because it's improved and traffic will be relatively low anyway)
It's really rather solid in performance terms anyway, especially for
non-trivial workloads where data consistency and reliability are important.
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes. It's not quite the same, in that a sequence may have gaps if a
transaction acquires a value from the sequence and then rolls back (due
to disconnect, explicit ROLLBACK, etc).
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
Yes, but it's not recommended because it'll break if you add fields to
`table' or re-order fields.
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
The above is preferable.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
No. You are trying to insert the empty string into an integer auto
increment field, which is nonsensical and will be rejected.
Use DEFAULT, or omit the field.
INSERT INTO table VALUES (DEFAULT,'y','z')
or
INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
which is really doing:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
You could also explicitly acquire a value from the sequence:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (nextval('table_id_seq'),
'y','z')
... but there's not really any point. DEFAULT or just omitting the field
are much better options.
--
Craig Ringer
On Wed, Jul 23, 2008 at 07:18:15PM +0930, admin wrote:
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes. Perhaps better use it indirectly with (BIG)SERIAL:
create table foo (
pk (big)serial
);
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
Yes, as long as the values are intended for the first N
consecutive columns.
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
You don't have to but while it is a bit more typing it is
more future proof.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
No.
> If not, what is an alternative?
insert into table (auto_inc_col, a, b) values (DEFAULT, 'y', 'z');
insert into table (a, b) values ('y', 'z');
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
am Wed, dem 23.07.2008, um 19:18:15 +0930 mailte admin folgendes:
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes.
>
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
>
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
Both worked, but it is better to list the columns. If you change later
the table-design, the first (without column-list) insert fails.
>
> ?
>
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
> If not, what is an alternative?
Use 'default' instead (without the ') or omit this column in the
column-list.
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
admin wrote:
> So anyway, life story aside, I have a couple of very newbie questions
> after tinkering with PostgreSQL 8.1.9 for a day converting some
> PHP/MySQL code:
Here I have to ask the obvious thing: Why not a more current version?
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes. Even better, you can use the pseudo-type "serial" and "bigserial"
which is in reality "integer" and "bigint" with nextval(...) in DEFAULT.
The advantage over an explicitly created sequence is that with "serial",
the sequence will be dropped automatically if the table is dropped.
Consult the documentation for details!
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
>
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
The first will work as specified by the SQL standard.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
> If not, what is an alternative?
No, this won't work.
Use:
INSERT INTO table (fld_y, fld_z) VALUES ('y','z')
Yours,
Laurenz Albe
On Wed, Jul 23, 2008 at 3:48 AM, admin <mick@mjhall.org> wrote:
> I'm convinced that PostgreSQL's performance is not an issue (both because
> it's improved and traffic will be relatively low anyway), and that the
> benefits of PostgreSQL's advanced features are too good to ignore. I'm
> hoping to shift quite a bit of data processing into the database.
My experience has been that pgsql is more than a match for MySQL for
real workloads (i.e. ones with 600 concurrent users and millions or
billions of rows)
> So anyway, life story aside, I have a couple of very newbie questions after
> tinkering with PostgreSQL 8.1.9 for a day converting some PHP/MySQL code:
Umm, as mentioned by someone else, you should be running 8.3.3 not
8.1.9. That version is 2.5 years or so old. There's been a LOT of
improvements since then, and 8.3.3 is very fast. We just upgraded a
production server from 8.1.9 to 8.3.3 and the load on that machine
dropped from 20 to 30 to 4 or 5.
> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?
This is one of the many SQL bad habits you've likely picked up from
using MySQL. I'd highly suggest reading the pgsql users manual cover
to cover, you'll pick up a lot of good info on how to drive
postgresql. Other things that work in mysql but fail in pgsql include
inserting things that are out of range.
create table test (a int);
insert into test (a) values (123456789012345678);
will fail in pgsql, because that number won't fit. It won't get
chopped off at 2^31-1, it'll just not get inserted.
So will inserting a string that's too long. Dates like 2008-02-30 will fail.
A trick you'll want to learn in pgsql is putting as much into a
transaction together as is reasonable. If you're inserting 10,000
rows that should all go together or not, then wrap them all in begin;
commit; . If one insert fails, they all fail, and you can start again
without cleaning up. If they all go in, they all go in much faster
than if you did them individually.
Raymond O'Donnell wrote:
>> 1. Is a SEQUENCE what I use instead of auto_increment?
>
> Yes. The easiest thing is to define the column as type SERIAL - this
> will create the sequence for you and associate it with the column.
> Alternatively, you can create the sequence by hand, create the column
> as an integer type, and then set the default for the column as
> nextval('<sequence name>'). Have a look in the docs for the gory
> details:
>
> http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL
>
Of sorts - the correct definition would be the sequence is where the
auto_increment equivalent gets the next value. A straight conversion
would be replace auto_increment with DEFAULT nextval('this_table_seq')
after creating the sequence definition but these steps are automated by
using a data type of serial.
>> 3. Does this work in PostgreSQL:
>>
>> INSERT INTO table VALUES ('','y','z')
>>
>> where the empty first item is intended for an
>> auto_increment/SEQUENCE id field?
>
> No - this will try to stuff a string value (here, an empty string)
> into an integer column, which will cause an error.
>
> INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
Another way is INSERT INTO table VALUES (NULL,'y','z')
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Craig Ringer wrote:
> INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
>
> which is really doing:
>
> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
>
To be honest I hadn't seen the use of INSERT INTO table (fld_x,
fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')
is DEFAULT a better option than using NULL? or is it just a preference
to spell out the implied default entry?
I've only used DEFAULT in CREATE TABLE(...)
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Shane Ambler wrote:
>> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
> To be honest I hadn't seen the use of INSERT INTO table (fld_x,
> fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with
> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')
... which is not the same thing.
> is DEFAULT a better option than using NULL? or is it just a preference
> to spell out the implied default entry?
It's completely different - you're running into bad habits developed
from using MySQL in non-ANSI-compliant mode (though some of these might
also apply in strict mode). See below.
In PostgreSQL, like most databases, inserting NULL will in fact insert a
NULL value for that field. Using DEFAULT tells the database to pick the
default value for the field, or if unspecified insert NULL for that
field. You can't just use NULL when you mean DEFAULT.
With this schema:
CREATE TABLE t (
fld_x SERIAL PRIMARY KEY,
fld_y VARCHAR(255),
fld_z VARCHAR(255)
);
which actually behaves like:
CREATE SERIAL t_id_seq;
CREATE TABLE t (
fld_x INTEGER NOT NULL DEFAULT nextval('t_id_seq'),
fld_y VARCHAR(255),
fld_z VARCHAR(255),
PRIMARY KEY(fld_x)
);
this statement:
INSERT INTO t (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')
will fail with:
ERROR: null value in column "fld_x" violates not-null constraint
because NULL isn't valid in a PRIMARY KEY field.
On the other hand, if you write this:
INSERT INTO t (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
it'll succeed, because the DEFAULT will be evaluated as
nextval('t_id_seq') so it'll get the next value from the sequence from
the SERIAL primary key.
MySQL-isms:
'' is not the same as NULL. NULL essentially means "unknown/undefined",
whereas '' means a specific and known value, a zero-length string. They
mean different things, and will also compare non-equal.
In INSERT, NULL is not the same as DEFAULT. NULL means NULL. DEFAULT
means "evaluate the expression in the DEFAULT clause for this field in
the schema definition, or if none is specified use NULL".
NULL is not equal to NULL. The result of evaluating the expression:
NULL = NULL
is actually NULL, not true. If you want to test for nullity use IS NULL
and IS NOT NULL instead. If you really want NULL to compare as equal to
NULL and unequal to other values (instead of NULL when compared to other
values) use IS DISTINCT FROM. See the documentation for more details.
Note that if you really, really, really want to emulate auto_increment
from MySQL, you can do so with a trigger that replaces NULL values in a
given field with values selected from a counter table. Concurrency will
be very poor, though, as will performance in general, and it's a much
better idea to just use a proper sequence.
--
Craig Ringer
On 12:00 pm 07/23/08 Shane Ambler <pgsql@Sheeky.Biz> wrote:
> > INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
I believe that is the most common way of doing it.
> > which is really doing:
> > INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
Correct.
So either one should be fine.
Shane Ambler wrote:
> Raymond O'Donnell wrote:
>
...
>> INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
>
> Another way is INSERT INTO table VALUES (NULL,'y','z')
of course you meant:
INSERT INTO table VALUES (DEFAULT,'y','z')
since Null would be wrongly insert NULL value instead
of using the sequence value.
Regards
Tino
Attachment
> This is one of the many SQL bad habits you've likely picked up from > using MySQL. I'd highly suggest reading the pgsql users manual cover > to cover, you'll pick up a lot of good info on how to drive > postgresql. Other things that work in mysql but fail in pgsql include > inserting things that are out of range. You know, I bet as many of us came to PostgreSQL from a MySQL background as from Oracle. It might be good to have a section in the manual for how to transition from using the "tricks" in MySQL to a strict environment like Postgres.
On Wed, Jul 23, 2008 at 10:22 PM, Artacus <artacus@comcast.net> wrote: >> This is one of the many SQL bad habits you've likely picked up from >> using MySQL. I'd highly suggest reading the pgsql users manual cover >> to cover, you'll pick up a lot of good info on how to drive >> postgresql. Other things that work in mysql but fail in pgsql include >> inserting things that are out of range. > > You know, I bet as many of us came to PostgreSQL from a MySQL background as > from Oracle. It might be good to have a section in the manual for how to > transition from using the "tricks" in MySQL to a strict environment like > Postgres. And one for Oracle as well. I've seen a few people show up wondering how postgresql handles running out of rollback space... ahem.
Raymond O'Donnell wrote:
>> 1. Is a SEQUENCE what I use instead of auto_increment?
>
> Yes. The easiest thing is to define the column as type SERIAL - this
> will create the sequence for you and associate it with the column.
> Alternatively, you can create the sequence by hand, create the column
> as an integer type, and then set the default for the column as
> nextval('<sequence name>'). Have a look in the docs for the gory
> details:
>
> http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL
>
Of sorts - the correct definition would be the sequence is where the
auto_increment equivalent gets the next value. A straight conversion
would be replace auto_increment with DEFAULT nextval('this_table_seq')
after creating the sequence definition but these steps are automated by
using a data type of serial.
>> 3. Does this work in PostgreSQL:
>>
>> INSERT INTO table VALUES ('','y','z')
>>
>> where the empty first item is intended for an
>> auto_increment/SEQUENCE id field?
>
> No - this will try to stuff a string value (here, an empty string)
> into an integer column, which will cause an error.
>
> INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
Another way is INSERT INTO table VALUES (NULL,'y','z')
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Craig Ringer wrote:
> INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
>
> which is really doing:
>
> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
>
To be honest I hadn't seen the use of INSERT INTO table (fld_x,
fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')
is DEFAULT a better option than using NULL? or is it just a preference
to spell out the implied default entry?
I've only used DEFAULT in CREATE TABLE(...)
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jul 23, 2008, at 12:00 PM, Shane Ambler wrote: > To be honest I hadn't seen the use of INSERT INTO table (fld_x, > fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone > with INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z') > > is DEFAULT a better option than using NULL? or is it just a > preference to spell out the implied default entry? > > I've only used DEFAULT in CREATE TABLE(...) The semantics of using DEFAULT or NULL is completely different. If the column has a default value (for example, 0), then including DEFAULT in your insert list will give you the default value of zero. But if you include NULL in your insert list -- you'll get NULL, not zero. If nothing is included for the column in your insert list, you'll get the column default if it has one, otherwise NULL. John DeSoi, Ph.D.