[SQL] date arithmetic
Hi ALL, I want to do date arithmetic in SQL with a column that is integer. example create table bob ( col1 timestamp , col2 int4 ; where col2 represents a number of minutes. I want to do something like select col1 + interval col2 minutes from bob This is doable with most other RDBMS (DB2, Oracle MS SQL) what am I missing?? I know one option is to create col2 as interval, but does not work for me. How can I cast int4 to "interval minutes" -- View this message in context: http://www.nabble.com/date-arithmetic-tf2075353.html#a5715425 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] error with mor than 1 sub-select
Hi, I am fairly new to PostgreSQL but not to SQL. Is this a bug or a limitation... The first query works fine, but when a second sub-query is added I get a syntax error... psql protocal2 -U p2user << EOF1 select * from serv_res SR where serv_key = 10 and not exists (select 1 from reservation R ) -- and not exits --(select 1 -- from interval R1 --) order by 1 ; select * from serv_res SR where serv_key = 10 and not exists (select 1 from reservation R ) and not exits (select 1 from interval R1 ) order by 1 ; EOF1 The output is as follows: Password for user p2user: serv_key | res_key | start_off_min | duration_min --+-+---+-- (0 rows) ERROR: syntax error at or near "select" at character 192 LINE 9: (select 1 ^ Note: This is not the actual query I want to run but just kept removing complexity until the problem (multiple sub-selects) seemed obvious. -- View this message in context: http://www.nabble.com/error-with-mor-than-1-sub-select-tf2149934.html#a5936948 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] error with mor than 1 sub-select
thanks, stupid user error. i guess the pointer on the error message led me astray Michael Fuhr wrote: > > On Tue, Aug 22, 2006 at 06:47:51PM -0700, chrisj wrote: >> The first query works fine, but when a second sub-query is added I get a >> syntax error... >> >> psql protocal2 -U p2user << EOF1 >> select * >>from serv_res SR >>where serv_key = 10 >> and not exists >> (select 1 >> from reservation R >> ) >> -- and not exits > > Does the real query have "exits" instead of "exists"? I created > some test tables and fixed that typo and then both queries worked. > > -- > Michael Fuhr > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > > -- View this message in context: http://www.nabble.com/error-with-more-than-1-sub-select-tf2149934.html#a5937248 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Assigning a timestamp without timezone to a timestamp with timezone
Hi Does any one have any ideas for the following problem? Two tables both the have open and close columns that are timestamp or timestamp with time zone. One row in first table represents the corporate office default open and close times for all stores relative to the store?s own time zone for a particular day. The second table represents the specific open and close time for a specific store for a specific day, occasionally a store?s hours can be different from the corporate default. Table1: open_time timestamp close_time timestamp Table2: store_number int open_timetimestamp with timezone close_time timestamp with timezone I would like to be able to initialize table 2 from table 1. Suppose I had a store table that contained Store_table: Store_number int Store_tz char(03) I would like to do something like: Insert into Table2 Select S.store_number ,cast(T1.open_time as timestamp with timezone at S.Store_tz) ,cast(T1.close_time as timestamp with timezone at S.Store_tz) from Store_table S, Table1 T1 -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6613652 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Andrew, I do appreciate your reply and we agree on two things timestamp without timezone should be avoided and the timestamps in table 2 should definately be "with timezone". I have no problem changing the timestamps in table 1 to "with timezone", but I do not see how this solves my problem (maybe I am just thick). the timestamps in table 1 are not the open and close times for the corporate location, but they are the directive to all store locations saying: "In the context of the timezone your store is located in, these are the hours you should be open. For example the corporate office may be on the east coast and they are saying that on December 24,2006 you should open at 9am and close at 1pm. Stores in California should open at 9:00am Pacific time and stores in New York should open at 9am EDT. If I did not appreciate the full implication of your answer please be patient with me sometimes I am slow but I usually get there. Andrew Sullivan wrote: > > On Mon, Oct 02, 2006 at 08:15:56PM -0700, chrisj wrote: >> Two tables both the have open and close columns that are timestamp or >> timestamp with time zone. > > I think the best answer is to convert the one table to timestamptz, > and always enter explicitly the time zone with it (since you're going > to know the corporate timezone anyway, right?). This way, you don't > have to worry about the client's timezone setting, and you always get > the right answer. For instance: > > test=# SHOW TimeZone ; > TimeZone > -- > EST5EDT > (1 row) > > test=# SELECT '2006-10-03 09:00:00-00'::timestamptz; > timestamptz > > 2006-10-03 05:00:00-04 > (1 row) > > This has the other advantage that if an office moves, its "open time" > in history doesn't need to change, and you don't need external > knowledge about what the office time zone is, because that's encoded > in the timestamp. > > In general, I think timestamps without timezones are just a bad > idea. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > I remember when computers were frustrating because they *did* exactly what > you told them to. That actually seems sort of quaint now. > --J.D. Baldwin > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6621346 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Brilliant, elegant and simple !! I can't wait to try it (don't have access to Postgres 9-5 EDT) !! thank-you !! Andrew Sullivan wrote: > > On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote: >> location, but they are the directive to all store locations saying: "In >> the >> context of the timezone your store is located in, these are the hours >> you >> should be open. > > Ah. Well, then, right, it _does_ have to be timezone free. That's > actually the only case I'd use that. Sorry, I'm dim, and didn't > understand properly what you were doing. (I read the "relative to > the store's own time zone" to refer to the corporate office. No, I > don't know why, either. Told you I'm dim.) > > Anyway, here's something that worked for me (expanding this into your > case ought not to be too tricky): > > testing=# SELECT * from storetz ; > id | timezone > +-- > 1 | -03 > (1 row) > > testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03 > 09:00'||"timezone" as timestamp from storetz where id = 1) as a; >timestamp > > 2006-10-03 12:00:00+00 > (1 row) > > A > -- > Andrew Sullivan | [EMAIL PROTECTED] > When my information changes, I alter my conclusions. What do you do sir? > --attr. John Maynard Keynes > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6622976 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Hector, It would probably better to get the explanation from Andrew, but I will do the best I can. You asked about the 1 and -3. The 1 would be the store number in my original scenario and -3 would be the representation of the timezone (three hours behind Universal Coordinate Time). I still have not had a chance to implement the solution into my application, but I am assuming the -3 could also be a mnemonic such as "EDT" I live in Toronto EDT is Eastern Daylight-savings Time. As for the syntax of the select, it is simply casting a character representation of a timestamp concatenated with a character representation of timezone to timestamptz. In hindsight it is so simple I can't believe I could not come up with it myself. Hector Villarreal wrote: > > Hi > I am also interested in this type of setup. However, in the example > below > I am a little confused as to why the table entry is 1, -3 > And the subsequent select statement . I would appreciate an explanation > on the select statement. I do not understand the syntax. > Thanks in advance > Hector Villarreal > SELECT a.timestamp::timestamptz from (SELECT '2006-10-03 > 09:00'||"timezone" as timestamp from storetz where id = 1) as a; >timestamp > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan > Sent: Tuesday, October 03, 2006 7:52 AM > To: chrisj > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp > > On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote: >> location, but they are the directive to all store locations saying: > "In the >> context of the timezone your store is located in, these are the hours > you >> should be open. > > Ah. Well, then, right, it _does_ have to be timezone free. That's > actually the only case I'd use that. Sorry, I'm dim, and didn't > understand properly what you were doing. (I read the "relative to > the store's own time zone" to refer to the corporate office. No, I > don't know why, either. Told you I'm dim.) > > Anyway, here's something that worked for me (expanding this into your > case ought not to be too tricky): > > testing=# SELECT * from storetz ; > id | timezone > +-- > 1 | -03 > (1 row) > > testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03 > 09:00'||"timezone" as timestamp from storetz where id = 1) as a; >timestamp > > 2006-10-03 12:00:00+00 > (1 row) > > A > -- > Andrew Sullivan | [EMAIL PROTECTED] > When my information changes, I alter my conclusions. What do you do > sir? > --attr. John Maynard Keynes > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667349 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Andrew, If only all time zones were fixed offset timezones life would be so much simpler. Unfortunately the main area of deployment of my app will beToronto which is on EDT which is not a fixed offsets timezone. I hope/assume your solution works with "EDT" instead of "-3", I will test it soon. Andrew Sullivan wrote: > > On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote: >> Hi >> I am also interested in this type of setup. However, in the example >> below >> I am a little confused as to why the table entry is 1, -3 > > The 1 is an artificial key (it's the criterion in the WHERE clause). > The -03 is the time zone offset. The most reliable way to handle > time zone offsets, I find, is to use the numeric offset from UTC. > That's the way PostgreSQL shows them in some cases, too. On my > system, for instance, I get this for SELECT now() (at the moment): > > now > --- > 2006-10-05 14:21:51.507419-04 > (1 row) > >> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03 >> 09:00'||"timezone" as timestamp from storetz where id = 1) as a; >>timestamp > > So what this does is > > SELECT > > the column named "timestamp" from relation "a" > cast to timestamp with time zone (the :: is a shorthand for > cast in Postgres) > > FROM > > a relation called "a" > constituted as (this is that "as a" on the end) > > SELECT > the literal string '2006-10-03 09:00' > concatenated to (that's what "||" means) > the column "timezone" > [and call that whole thing "timestamp" > FROM > a relation called "storetz" > WHERE > the storetz row has an id of 1. > > So, what you get is a timestamp with a time zone that is built up > from the combination of a timestamp without time zone and some time > zone data that you have. > > What's _really_ cool in Postgres about the time handling is that you > can also change your time zone, and find that the data nicely > represents your new time zone too. You can see this in my original > example: I was using GMT, but inserted a timestamp in -03. When I > selected the answer, though, I got one back in GMT (==UTC). So > that's why you see this: > >> >> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03 >> 09:00'||"timezone" as timestamp from storetz where id = 1) as a; >>timestamp >> >> 2006-10-03 12:00:00+00 >> (1 row) > > 2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00 > > Hope that helps, > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > The fact that technology doesn't work is no bar to success in the > marketplace. > --Philip Greenspun > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667446 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Thanks for the heads up, I definately need EST5EDT you saved me twice!! Andrew Sullivan wrote: > > On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote: >> If only all time zones were fixed offset timezones life would be so much >> simpler. > > Indeed. > >> Unfortunately the main area of deployment of my app will beToronto which >> is >> on EDT which is not a fixed offsets timezone. I hope/assume your >> solution >> works with "EDT" instead of "-3", I will test it soon. > > Should do, although you'll need more than EDT. EDT is also fixed: > it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need > to improve your schema, though, because you had char(3) there, and > not all time zones are 3 characters long). But to answer your > question, yes, it works. I just tried it. > > A > > > -- > Andrew Sullivan | [EMAIL PROTECTED] > Information security isn't a technological problem. It's an economics > problem. > --Bruce Schneier > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6668169 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Andrew, Finally got around to trying to implement your solution. It works fine with fixed offset timezones, but when I try it with EST5EDT I get the following: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 09:20:00 EST5EDT" when I change timezone_ch to EST it works like a charm: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; start_datetime | timestamptz + 2006-07-13 09:20:00-04 | 2006-07-13 10:20:00-04 (1 row) Any thoughts? On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote: > If only all time zones were fixed offset timezones life would be so much > simpler. Indeed. > Unfortunately the main area of deployment of my app will beToronto which > is > on EDT which is not a fixed offsets timezone. I hope/assume your solution > works with "EDT" instead of "-3", I will test it soon. Should do, although you'll need more than EDT. EDT is also fixed: it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need to improve your schema, though, because you had char(3) there, and not all time zones are 3 characters long). But to answer your question, yes, it works. I just tried it. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6815181 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Did not seem to help: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 09:20:00 America/New_York" Tom Lane-2 wrote: > > chrisj <[EMAIL PROTECTED]> writes: >> It works fine with fixed offset timezones, but when I try it with EST5EDT >> I get the following: >> ERROR: invalid input syntax for type timestamp with time zone: >> "2006-07-13 >> 09:20:00 EST5EDT" > > Try it with "America/New_York". The datetime parser seems to think that > a timezone name shouldn't contain digits ... which is bogus, but we'll > have to think carefully about how to improve it ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6827636 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Thanks Tom that's great!!
When I first saw your solution I thought it was logically going to do
(notice the parentheses):
select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
which does not help
So I was not hopeful, but when I tried it it did exactly what I needed which
is:
select '2006-07-13 09:20:00'::(timestamp at time zone 'EST5EDT');
My adjusted SQL is:
select start_datetime
, cast(start_datetime as timestamp(0) without time zone)::timestamp at
time zone B.timezone_ch
from reservation A
, location B
where A.appt_key = 7
and B.locn_key = 102;
thank-you so much
Tom Lane-2 wrote:
>
> chrisj <[EMAIL PROTECTED]> writes:
>> Did not seem to help:
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "2006-07-13
>> 09:20:00 America/New_York"
>
> Sorry, I was thinking in terms of CVS HEAD ... the ability to use a full
> timezone spec in timestamptz input is new for 8.2. You might be able to
> use this, which does work in 8.1:
>
> select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT';
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
>
--
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6847852
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Tom,
Thanks again, I did not appreciate the dual function of "AT TIME ZONE" when
the input is timestamptz then the function converts from one timezone to
another (not what I wanted),
but when the input is timestamp the function acts more like a cast than a
convert (exactly what I wanted)
I must disagree with your assertion about the redundancy of:
> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch
what I am doing is taking a timestamptz, discarding its timezone, and then
casting it to another timezone
for example from 2006-10-03 09:00:00 NZST to 2006-10-03 09:00:00 EST5EDT
If I am missing a much easier way to accomplish this please let me know.
Tom Lane-2 wrote:
>
> chrisj <[EMAIL PROTECTED]> writes:
>> When I first saw your solution I thought it was logically going to do
>> (notice the parentheses):
>> select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
>> which does not help
>
> Well, actually, that's exactly what it does. AT TIME ZONE is an
> operator that converts timestamp without time zone to timestamp with
> time zone (or vice versa). I guess you could easily get confused
> here, but AT is not WITH.
>
>> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
>> time zone B.timezone_ch
>
> That's redundant --- you're casting the result of the cast to timestamp
> (implicitly without time zone), then applying the AT TIME ZONE operator.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
--
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6863766
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] recursive SQL and with clause
I was just wondering when we could reasonably expect recursive SQL to be added to Postgres? I saw some posts from 2004 that made it sound like it was imminent, but I guess something went wrong? I believe the WITH clause is a pre-requisite for recursive SQL, however I do find the WITH clause also very useful in decomposing very complex SQL into understandable chunks. -- View this message in context: http://www.nabble.com/recursive-SQL-and-with-clause-tf2638088.html#a7363779 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Aggregate function to build 2-d array
Hi Steven,
I believe I saw something about a fix to array_append in the release notes
for V8.2. Not sure if this helps.
Steven Murdoch-2 wrote:
>
> I would like to aggregate several rows of a query, maintaining the
> relative order. Is there an other way to achive the same result? I
> have an alternative construction, but I am not convinced it will work
> in all cases.
>
> For example, with the setup below:
>
> -- Concatenate elements of type t into array of type t[]
> CREATE AGGREGATE aconcat (
> BASETYPE = anyelement,
> SFUNC = array_append,
> STYPE = anyarray,
> INITCOND = '{}'
> );
>
> -- Sample table
> CREATE TABLE a (
> id INT PRIMARY KEY,
> k TEXT NOT NULL,
> v1 TEXT NOT NULL,
> v2 TEXT NOT NULL);
>
> -- Initialize data
> COPY a(id,k,v1,v2) FROM STDIN DELIMITER '|';
> 1|Alice|A|a
> 2|Bob|B|b
> 3|Charlie|C|c
> 4|Alice|A|a
> 5|Charlie|C|c
> \.
>
> This query is what I would like to run:
> SELECT aconcat(ARRAY[v1, v2]), k FROM a GROUP BY k;
> Which gives the result
> "ERROR: could not find array type for data type text[]"
>
> I would have expected:
> aconcat |k
> ---+-
> {{C,c},C,c}} | Charlie
> {{A,a},{A,a} | Alice
> {{B,b},{B,b}} | Bob
>
> The problem I am hitting appears to be that the array_append()
> function does not accept 2-d arrays[1].
>
> SELECT array_append(ARRAY[ARRAY[1,2],ARRAY[2,3]], ARRAY[3,4]);
> ERROR: function array_append(integer[], integer[]) does not exist
>
> The operator "||" does but I don't know how to use this to make a
> custom aggregate. Is there some way to do so, or achive the same
> result?
>
> SELECT ARRAY[ARRAY[1,2],ARRAY[2,3]] || ARRAY[3,4];
>?column?
> -
> {{1,2},{2,3},{3,4}}
>
> An alternative works in my test case:
> SELECT aconcat(v1), aconcat(v2), k FROM a GROUP BY k;
> aconcat | aconcat |k
> -+-+-
> {C,C} | {c,c} | Charlie
> {A,A} | {a,a} | Alice
> {B} | {b} | Bob
>
> However I can't find any assurance that the order that each aggregate
> is formed will be the same in each column. Is this currently the case,
> and is it likely to remain so?
>
> Thanks in advance,
> Steven.
>
> [1] http://www.postgresql.org/docs/8.2/interactive/arrays.html
>
> --
> w: http://www.cl.cam.ac.uk/users/sjm217/
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
--
View this message in context:
http://www.nabble.com/Aggregate-function-to-build-2-d-array-tf2638930.html#a7369425
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] can someone explain confusing array indexing nomenclature
given the following table:
protocal2=> select * from sal_emp ;
name | pay_by_quarter | schedule
---+---+---
Bill | {1,1,1,1} |
{{meeting,lunch},{training,presentation}}
Carol | {2,25000,25000,25000} |
{{breakfast,consulting},{meeting,lunch}}
(2 rows)
why do the following two queries yield different results??
protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
schedule
--
lunch
(1 row)
protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
schedule
---
{{meeting,lunch}}
(1 row)
--
View this message in context:
http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a8971770
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] can someone explain confusing array indexing nomenclature
Thanks Achilleas,
I see what you are saying, but if we consider just the index "[2]" for a
moment,
it means something different depending upon the context (in one case it
means "2" and in the other case it means "1:2") and the context is
determined by the format of indexes on other dimensions.
I believe I understandbut incredibly confusing.
- chris
Achilleas Mantzios wrote:
>
> Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
>> given the following table:
>>
>> protocal2=> select * from sal_emp ;
>> name | pay_by_quarter | schedule
>> ---+---+---
>> Bill | {1,1,1,1} |
>> {{meeting,lunch},{training,presentation}}
>> Carol | {2,25000,25000,25000} |
>> {{breakfast,consulting},{meeting,lunch}}
>> (2 rows)
>>
>> why do the following two queries yield different results??
>>
>> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> --
>> lunch
>> (1 row)
>>
>> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> ---
>> {{meeting,lunch}}
>> (1 row)
>
> The [n:m] notation denotes a slice of the array (not element).
> So schedule[1][2] is the Array element on 2nd col of 1st row,
> while schedule[1:1][2] could mean
> the second row of the subarray schedule[1:1][1:2].
> So these two are foundamentally different things.
> In my 7.4 even if you gave
> SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> you would still get {{meeting,lunch}} as a result.
> (Right or wrong is another story).
> Anyway the first time you query for a "text",
> the second time you query for a "text[]", so you should expect
> different results.
> --
> Achilleas Mantzios
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
>
--
View this message in context:
http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a8989242
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] can someone explain confusing array indexing nomenclature
I am quite sure the [2] is not discarded, easy enough to test but I don't
have access to PG at the moment.
Achilleas Mantzios wrote:
>
> Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
>> Thanks Achilleas,
>>
>> I see what you are saying, but if we consider just the index "[2]" for a
>> moment,
>> it means something different depending upon the context (in one case it
>> means "2" and in the other case it means "1:2") and the context is
>> determined by the format of indexes on other dimensions.
>>
>> I believe I understandbut incredibly confusing.
>>
>
> Now that i think about it again, i speculate that the [2] is discarded.
>
>> - chris
>>
>> Achilleas Mantzios wrote:
>> > Ξ£ΟΞΉΟ Ξ�Ξ΅ΟΞ¬ΟΟΞ· 14 ΦΡβΟΞΏΟ
Ξ¬ΟΞΉΞΏΟ 2007 21:31, ΞΏ/Ξ·
>> chrisj
> ΞΞ³ΟΞ±ΟΞ΅:
>> >> given the following table:
>> >>
>> >> protocal2=> select * from sal_emp ;
>> >> name | pay_by_quarter | schedule
>> >>
>> ---+---+
>> >>--- Bill | {1,1,1,1} |
>> >> {{meeting,lunch},{training,presentation}}
>> >> Carol | {2,25000,25000,25000} |
>> >> {{breakfast,consulting},{meeting,lunch}}
>> >> (2 rows)
>> >>
>> >> why do the following two queries yield different results??
>> >>
>> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> >> schedule
>> >> --
>> >> lunch
>> >> (1 row)
>> >>
>> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
>> >> schedule
>> >> ---
>> >> {{meeting,lunch}}
>> >> (1 row)
>> >
>> > The [n:m] notation denotes a slice of the array (not element).
>> > So schedule[1][2] is the Array element on 2nd col of 1st row,
>> > while schedule[1:1][2] could mean
>> > the second row of the subarray schedule[1:1][1:2].
>> > So these two are foundamentally different things.
>> > In my 7.4 even if you gave
>> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
>> > you would still get {{meeting,lunch}} as a result.
>> > (Right or wrong is another story).
>> > Anyway the first time you query for a "text",
>> > the second time you query for a "text[]", so you should expect
>> > different results.
>> > --
>> > Achilleas Mantzios
>> >
>> > ---(end of
>> broadcast)---
>> > TIP 3: Have you checked our extensive FAQ?
>> >
>> >http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
>
--
View this message in context:
http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a9009934
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] can someone explain confusing array indexing nomenclature
I guess you could say the [2] is discarded since the value "2" is at the top
or beyond the top of the range.
Achilleas Mantzios wrote:
>
> Στις Παρασκευή 16 Φεβρουάριος 2007 20:35, ο/η chrisj έγραψε:
>> I am quite sure the [2] is not discarded, easy enough to test but I don't
>> have access to PG at the moment.
>
> Well it should, since
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
> text
> -
> {{meeting}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as
> text[][]))[1:1][1000];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=#
>
>>
>> Achilleas Mantzios wrote:
>> > Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
>> >> Thanks Achilleas,
>> >>
>> >> I see what you are saying, but if we consider just the index "[2]" for
>> a
>> >> moment,
>> >> it means something different depending upon the context (in one case
>> it
>> >> means "2" and in the other case it means "1:2") and the context is
>> >> determined by the format of indexes on other dimensions.
>> >>
>> >> I believe I understandbut incredibly confusing.
>> >
>> > Now that i think about it again, i speculate that the [2] is discarded.
>> >
>> >> - chris
>> >>
>> >> Achilleas Mantzios wrote:
>> >> > Ξ£ΟΞΉΟ Ξ�Ξ΅ΟΞ¬ΟΟΞ· 14 ΦΡβΟΞΏΟ
Ξ¬ΟΞΉΞΏΟ 2007 21:31, ΞΏ/Ξ·
>> >>
>> >> chrisj
>> >
>> > ΞΞ³ΟΞ±ΟΞ΅:
>> >> >> given the following table:
>> >> >>
>> >> >> protocal2=> select * from sal_emp ;
>> >> >> name | pay_by_quarter | schedule
>> >>
>> >>
>> ---+---+
>> >>
>> >> >>--- Bill | {1,1,1,1} |
>> >> >> {{meeting,lunch},{training,presentation}}
>> >> >> Carol | {2,25000,25000,25000} |
>> >> >> {{breakfast,consulting},{meeting,lunch}}
>> >> >> (2 rows)
>> >> >>
>> >> >> why do the following two queries yield different results??
>> >> >>
>> >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> >> >> schedule
>> >> >> --
>> >> >> lunch
>> >> >> (1 row)
>> >> >>
>> >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name =
>> 'Bill';
>> >> >> schedule
>> >> >> ---
>> >> >> {{meeting,lunch}}
>> >> >> (1 row)
>> >> >
>> >> > The [n:m] notation denotes a slice of the array (not element).
>> >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
>> >> > while schedule[1:1][2] could mean
>> >> > the second row of the subarray schedule[1:1][1:2].
>> >> > So these two are foundamentally different things.
>> >> > In my 7.4 even if you gave
>> >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
>> >> > you would still get {{meeting,lunch}} as a result.
>> >> > (Right or wrong is another story).
>> >> > Anyway the first time you query for a "text",
>> >> > the second time you query for a "text[]", so you should expect
>> >> > different results.
>> >> > --
>> >> > Achilleas Mantzios
>> >> >
>> >> > ---(end of
>> >>
>> >> broadcast)---
>> >>
>> >> > TIP 3: Have you checked our extensive FAQ?
>> >> >
>> >> >http://www.postgresql.org/docs/faq
>> >
>> > --
>> > Achilleas Mantzios
>> >
>> > ---(end of
>> broadcast)---
>> > TIP 3: Have you checked our extensive FAQ?
>> >
>> >http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>
--
View this message in context:
http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a9138745
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] can a insert with a returning clause be subquery ?
I am trying to accomplish this:
update p2user.party set ADDR_KEY =
(insert into p2user.address ( street_no, street_name)
values ('22', 'Geek St.')
returning addr_key )
where party_key = 22 ;
But I am getting error:
ERROR: syntax error at or near "into"
LINE 1: update p2user.party set ADDR_KEY = (insert into p2user.addre...
^(is actually under the
"into")
The insert by itself works fine and returns a valid integer.
I know I could do this easily with a stored proc,
but it seems like an obvious use of the returning clause
Am I missing some syntax error
--
View this message in context:
http://www.nabble.com/can-a-insert-with-a-returning-clause-be-subquery---tp15248628p15248628.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
