pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedempty arrays
3+ messages / 3 participants
[nested] [flat]
* empty arrays
@ 2002-12-30 17:40 floyds@4peakstech.com
0 siblings, 2 replies; 3+ messages in thread
From: floyds@4peakstech.com @ 2002-12-30 17:40 UTC (permalink / raw)
To: pgsql-sql
using: psql (PostgreSQL) 7.2.1
why does an empty array return an array of length 1 rather than array of
length 0? one would think that the results below would have returned { }
instead of {0}.
simple test using psql:
# create table test_table ( test_column integer[] );
CREATE
# insert into test_table (test_column) values ( '{ }' );
INSERT 43475 1
# select * from test_table;
test_column
------------
{0}
(1 row)
i want to be able to store a zero-length array field in the database. how do
i specify this with sql?
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 916.404.7125
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: empty arrays
@ 2003-01-02 06:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: floyds@4peakstech.com
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-01-02 06:04 UTC (permalink / raw)
To: floyds@4peakstech.com; +Cc: pgsql-sql
<floyds@4peakstech.com> writes:
> # insert into test_table (test_column) values ( '{ }' );
Try it without the space:
insert into test_table (test_column) values ( '{}' );
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: empty arrays
@ 2003-01-02 19:06 Steve Crawford <scrawford@pinpointresearch.com>
parent: floyds@4peakstech.com
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2003-01-02 19:06 UTC (permalink / raw)
To: floyds@4peakstech.com; pgsql-sql
Caution!
In 7.2.x your statement is interpreted by the parser to be a single element
with an empty string which is converted to a zero. If you do this instead:
create table test_table ( test_column integer[], another_column integer );
CREATE
steve=# insert into test_table (another_column) values (1);
INSERT 7140977 1
steve=# select * from test_table;
test_column | another_column
-------------+----------------
| 1
(1 row)
You will see that there is no item in the array which you can confirm with:
select array_dims(test_column) from test_table;
array_dims
------------
(1 row)
(Which seemingly oddly shows nothing instead of 0 since there is not yet
really any array to have dimensions. Per the docs, an array can be null but
it cannot contain SQL null elements. What the docs don't say is that
requesting an element beyond either end of an array does return a SQL null.)
Here's the gotcha: when you upgrade to 7.3.x your statement will generate an
error since an empty string is no longer arbitrarily assumed to be a zero
(either in integer fields or arrays) so if you attempt the same you will get:
create table test_table ( test_column integer[], another_column integer );
CREATE TABLE
tati=> insert into test_table (test_column) values ( '{ }' );
ERROR: pg_atoi: zero-length string
If you want to insert a null array you are best off saying so:
insert into test_table (test_column) values (null);
INSERT 17053 1
tati=> select * from test_table;
test_column | another_column
-------------+----------------
|
(1 row)
Cheers,
Steve
On Monday 30 December 2002 9:40 am, floyds@4peakstech.com wrote:
> using: psql (PostgreSQL) 7.2.1
>
> why does an empty array return an array of length 1 rather than array of
> length 0? one would think that the results below would have returned { }
> instead of {0}.
>
> simple test using psql:
>
> # create table test_table ( test_column integer[] );
> CREATE
> # insert into test_table (test_column) values ( '{ }' );
> INSERT 43475 1
> # select * from test_table;
>
> test_column
> ------------
> {0}
> (1 row)
>
>
> i want to be able to store a zero-length array field in the database. how
> do i specify this with sql?
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 916.404.7125
> EMAIL: FloydS@4PeaksTech.com
> ICQ #: 161371538
>
> Shackelford Motto: ACTA NON VERBA - Actions, not words
>
> Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
> Rights
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-02 19:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-30 17:40 empty arrays floyds@4peakstech.com
2003-01-02 06:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-02 19:06 ` Steve Crawford <scrawford@pinpointresearch.com>
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