Thread: please help me with arrays

Hi!
Would you please explain me how can I express in PostgreSQL the notion of
"any element of the array". Having a table like:

CREATE TABLE example (name text, slots text[]);

I want to be able to select, for example, all rows such that an element
of example.slots exists equal to example.name.
Something like:

SELECT name FROM example WHERE example.name = example.slots[*];

If I wrong from the very begining and such things should be done
in other way -- please tell me how.
Sorry, if my question is stupid or formulated incorrectly.
Thanks in advance,
  --bo

Bo Berkhaut writes:

> CREATE TABLE example (name text, slots text[]);
>
> I want to be able to select, for example, all rows such that an element
> of example.slots exists equal to example.name.

There are some operators for this in contrib/array.

However, I'd say that if you're trying to do this you better redesign your
database schema to make "slots" a separate table.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Hi!

Help me please to resolve my problem.
I cann't understand why my query returns very strange results.

select  datop  from mytable where date_trunc('month',datop)
  =date_trunc('month',abstime '22.05.00')

 it returns a strings with operations which were made in May,

but similar query

select  datop  from mytable where date_trunc('month',datop)
  =date_trunc('month',abstime '02.05.00')

 it returns operations which were made in february !

I'm running PGSQL 7.0 ,
SET DATESTYLE ='GERMAN'
SET TIME ZONE 'EUROPE/MOSCOW'

Tell me please , what I made wrong?


Thanks for any suggestions.

Igor.

Igor Khanjine <igor_kh@mailru.com> writes:
> select  datop  from mytable where date_trunc('month',datop)
>   =date_trunc('month',abstime '02.05.00')
>  it returns operations which were made in february !

> I'm running PGSQL 7.0 ,
> SET DATESTYLE ='GERMAN'
> SET TIME ZONE 'EUROPE/MOSCOW'

FWIW, I do not see this in 7.0.3-to-be:

play=> SET DATESTYLE ='GERMAN';
SET VARIABLE
play=> select abstime '02.05.00';
          ?column?
----------------------------
 02.05.2000 00:00:00.00 EDT
(1 row)

play=> select date_trunc('month',abstime '02.05.00');
         date_trunc
----------------------------
 01.05.2000 00:00:00.00 EDT
(1 row)

Either it's been fixed since 7.0 release, or there is something peculiar
about the datetime support on your platform (which you didn't specify).

            regards, tom lane

  I'm running 7.02 on RedHat 6.0
and these queryes work with error result,
but I found the solution -
  select abstime '2000-05-02'
and
  select date_trunc('month',abstime '2000-05-02')
both work properly.

 May be I have to install the next release of PG?
Tell me please what is the last available?
 (I think 7.02 - is it right?)


 Thanks for help!

Regards.

Igor

play=>> SET DATESTYLE ='GERMAN';
TL> SET VARIABLE
play=>> select abstime '02.05.00';
TL>           ?column?
TL> ----------------------------
TL>  02.05.2000 00:00:00.00 EDT
TL> (1 row)

play=>> select date_trunc('month',abstime '02.05.00');
TL>          date_trunc
TL> ----------------------------
TL>  01.05.2000 00:00:00.00 EDT
TL> (1 row)

TL> Either it's been fixed since 7.0 release, or there is something peculiar
TL> about the datetime support on your platform (which you didn't specify).