Re: [GENERAL] Why is cast array integer[] <--> text[] is not immutable.
Thanks I'm having trouble finding any reference to array_out and array_in in the documentation. Is there a way to set a different cast for an array? Regards On 9 December 2011 15:09, Tom Lane wrote: > Phil Couling writes: >> I'm struggling to understand why this casts is not immutable: > >> integer[]::text[] >> text[]::integer[] > > Because it's implemented via array_out/array_in rather than any more > direct method, and those are marked stable because they potentially > invoke non-immutable element I/O functions. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why is cast array integer[] <--> text[] is not immutable.
Hi I'm struggling to understand why this casts is not immutable: integer[]::text[] text[]::integer[] The following are all immutable: integer::text text::integer integer[]::float[] integer::float I hit on this while trying to make a gin index which cast from one to the other. Why does the encapsulation of an array suddenly make this not immutable? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error: timestamp with timezone + interval is not immutable while creating index
On 14 October 2011 00:49, Steve Crawford wrote: > On 10/13/2011 04:32 PM, Tom Lane wrote: >> >> Phil Couling writes: >>> >>> main=> create index foo_next_update on foo( (last_updated + >>> update_cycle) ) ; >>> ERROR: functions in index expression must be marked IMMUTABLE... >> >> timestamptz + interval is not immutable because the results can vary >> depending on timezone. For instance, in my zone (America/New_York): >> > So it seems like a potential workaround, depending on the nature of your > data and applications, would be to convert the timestamptz into a timestamp > at a reference TZ: > > steve=# create table testfoo (a_timestamptz timestamptz, an_interval > interval); > CREATE TABLE > steve=# create index testfoo_index on testfoo ((a_timestamptz at time zone > 'UTC' + an_interval)); > CREATE INDEX > > You will have to be sure you are getting the results you want in the > vicinity of DST changes and if you are handling multiple timezones. > > Cheers, > Steve > > Thanks all That makes a lot of sense. For some reason I'd thought that having the timezone would make it immutable (since it represents an absolute point in time) whereas without it would not be (since the point in time it *actually* represents is dependant on time zone...). Guess I hadn't thought that through very well. Kudos to Postgres for pointing out a flaw in my design! I'll be adding in the timezone to the table (or at least a table it references). The new index looks more like this: create index foo_next_update on foo ( ((first_update + (update_cycle * update_count)) at time zone update_region) ) I'm not sure timezone will ever be anything but 'GB' in this case, but there's nothing like future proofing. Regards All -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error: timestamp with timezone + interval is not immutable while creating index
Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an error though: main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; ERROR: functions in index expression must be marked IMMUTABLE Does anyone know why adding two fields like this results in anything other than an immutable function? Under what circumstances could it return a different result? Thanks very much for any help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error: timestamp with timezone + interval is not immutable while creating index
Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an error though: main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; ERROR: functions in index expression must be marked IMMUTABLE Does anyone know why adding two fields like this results in anything other than an immutable function? Under what circumstances could it return a different result? Thanks very much for any help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
I think you need to get the full list of change dates first. Assuming
you're searching over a time period between "period_from" and
"period_to":
SELECT change_time, sum(diff) as total_diff FROM (
SELECT starttime as change_time, 1 AS diff FROM t WHERE starttime >
period_from AND endtime < period_to
UNION ALL
SELECT endtime as change_time, -1 AS diff FROM t WHERE endtime >
period_from AND endtime < period_to
) a
GROUP BY change_time
HAVING sum(diff) <> 0
ORDER BY change_time asc
I used this in a pgplsql function to produce a very simular result to
what you were looking for. You need to start by finding how many time
periods overlapped period_from, then accumulatively add on
"total_diff" for each row you process.
Hope this helps.
2011/10/5 Filip Rembiałkowski :
>
>
> 2011/10/5 thomas veymont
>>
>> hello,
>>
>> let's say that each rows in a table contains a start time and a end
>> time ("timeinterval" type),
>
> there is no such type ( no result for select * from pg_type where typname ~
> 'timeinterval' ).
> can you show exact table structure (output of psql "\d" or better, CREATE
> TABLE command)?
>
>
>> but the index are not ordered nor consecutive, e.g :
>>
>> $ select * from T order by starttime
>>
>> index | starttime | endtime
>> -+-+-
>> 3 | t1 | t2
>> 1 | t3 | t4
>> 18 | t5 | t6
>> 12 | t7 | t8
>>
>> I want a result that shows time gaps and overlaps in this table, that is :
>>
>> delta
>> -+
>> t3 - t2 |
>> t5 - t4 |
>> t7 - t6 |
>>
>> how would I do that ? I guess this could be done with window function and
>> lag()
>> function but I don't know exactly how. Any suggestion ?
>>
>
>
> -- assuming that you actually want lag compared to previous starttime - try
> this:
> select index, starttime, endtime, starttime - lag(endtime) over(order by
> starttime asc) as delta from test;
>
>
> PS. this question should probably go to "pgslq-sql mailing list more than
> "pgsql-general". also please give more details next time. Thanks.
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to select one column into another in same table?
I don't this this is possible as postgres.
There is something simular with:
alter table table_name alter column column_foo using column_bar
But I don't think there's any performance advantage over a simple
update and the using clause doesn't appear to have an equivalent in an
add column statement.
You could.
alter table table_name rename column_foo to column_bar;
alter table table_name add column_foo foo_data_type default =
nextval('new_foo_sequence');
This has your best chance of success since renaming a column should
not have to touch every row of the table.
Regards
On 4 October 2011 20:21, J.V. wrote:
> What I need to do is to save the id column for future use and then modify
> the id column resetting all values from another sequence.
>
> So I need to select the id column or somehow get the data into another
> column in the same table.
>
> And then I can update the id column (after dropping the constraint).
>
> J.V.
>
> On 10/4/2011 1:09 PM, Scott Marlowe wrote:
>>
>> On Tue, Oct 4, 2011 at 12:24 PM, J.V. wrote:
>>>
>>> Currently I can select one column into another with two statements:
>>>
>>> alter table add column id_old int;
>>> update set id_old = id;
>>>
>>> Is there a way to do this in one statement with a select into? I have
>>> tried
>>> various select statements but want the new column (with the same data) to
>>> be
>>> in the same table and to have it execute much more quickly that the two
>>> statements currently do.
>>
>> Do you need another column or do you just want to alter a column that
>> already exists? If so you can alter a column from one type to another
>> and throw a using clause at it to convert the data in some way. I
>> think we need to know a bit better what you're trying to do.,
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add quto increment to existing column
Hi
Dropping the column is a bit drastic if you already have data in there.
You could just set the default on the column:
alter table my_table alter hist_id set default nextval('hist_id_seq')
Also considder setting the sequence owner:
alter sequence hist_id_seq owned by my_table.hist_id;
This will mean if the table or collumn gets dropped so will the
sequence and if the table is moved between schemas, so to will the
sequence be moved.
Regards
On 4 October 2011 14:38, marc_firth wrote:
> If you use the SERIAL (this is the auto-incrementing function that creates
> sequences in the bankground for you) datatype you can accomplish it in one
> go.
>
> So:
> DROP sequence hist_id_seq; -- Get rid of your old sequence
>
> ALTER TABLE my_table DROP COLUMN hist_id; -- Remove id column
>
> ALTER TABLE my_table ADD COLUMN hist_id SERIAL PRIMARY KEY; -- Recreate it
> as Primary Key and quto-incrementing.
>
> Btw: have you tried the http://www.pgadmin.org/ pgadmin gui for
> postgres? It will help you do tasks like this and show you the SQL to do it
> on the command line :)
>
> Cheers,
> Marc
>
>
>
> Robert Buckley wrote:
>>
>> Hi,
>>
>> I have a column in a table called hist_id with the datatype "integer".
>> When I created the table I assigned this column the primary key constraint
>> but didn´t make it an auto-increment column.
>>
>> How could I do this to an the already existing column?
>>
>> I have created the sequence with the following command but don´t know how
>> to change the existing column to auto-increment.
>>
>>
>> $ create sequence hist_id_seq;
>>
>> thanks for any help,
>>
>> Rob
>>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Add-quto-increment-to-existing-column-tp4868404p4868544.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert data into horizontal from vertical form
Hi Adarsh You say you need this to be done dynamically. I assume that by this you're looking for a way to have 1 query produce an increasing number of columns as you increase the number of rows in your table. This really isn't possible and doesn't fit with the model SQL was designed for. The concept of tables is that each table represents a set of items of a single type with a set of known properties (the possible properties are known before the item itself). An item is represented by a row and a property is represented by a column. You are trying to create a query with an unknown set of properties. If the data must be represented as you've shown then you will need to get your front end application to transform the data for you. Regards On 19 May 2011 11:15, Adarsh Sharma wrote: > Dear all, > > I am not able to insert data into a table in horizontal form. > > The data is in below form : > > A show a small set of data :- > > c_id f_name f_value > 2 k1 v1 > 2 k2 v2 > 2 k3 v3 > 2 k4 v4 > 3 a1 b1 > 3 a2 b2 > 3 a3 b3 > 3 a4 b4 > 3 a5 b5 > 1 c1 d1 > 1 c2 d2 > 3 a1 e1 > 3 a2 e2 > 3 a3 e3 > 3 a4 e4 > 3 a5 e5 > > Now i want to show the above data in horizontal form as per c_id , fore.g if > a user enters c_id 3 then output is : > > c_id a1 a2 a3 a4 a5 > 3 b1 b2 b3 b4 b5 > 3 e1 e2 e3 e4 > e5 > > i.e f_name entries became the columns of the table & f_value become the rows > > > I research on crosstab function but i don'e think it is useful because we > have to give column names in the command. > I want to show it dynamically . I try to create a procedure & also attach > it. > > A user enters only c_id & output is shown fore.g if a user enters c_id 1 > then output is > > c_id c1 c2 > 1 d1 d2 > > I show the data in simple way bt there r 1 of rows & 100 of c_id's. > > > Please let me know if it is possible or any information is required. > > > Thanks > > > > create function user_news_new(text) returns void as $$ > declare > name text; > cat_name alias for $1; > begin > CREATE TEMPORARY TABLE temptest(category_id INTEGER,category_name text); > /* create a temp table to hold all the dynamic schemas*/ > for name in select label_name from category_new where category_id = (select > category_id from category where category_name=cat_name) loop > execute 'alter table temptest add column ' || name || ' text'; > end loop; > end; > $$ language plpgsql; > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] find the greatest, pick it up and group by
Hi The method you're using is functionally correct and quite efficient if a little on the verbose side. Other non-postgres variants of SQL have a "DECODE" function which comes in very handy. I dont believe postgres has any equivalent. (Postgres decode() does something entirely differnt). I often use nested queries in the from clause for this purpose. SELECT a, b, c, x, y, z, case when gr = x then 'x' when gr = y then 'y' when gr = z then 'z' end FROM ( Select distinct a,b,c, x,y,z, greatest(x,y,z) as gr from foo ) Regards On 17 May 2011 01:26, Ivan Sergio Borgonovo wrote: > On Mon, 16 May 2011 20:05:45 -0400 > "David Johnston" wrote: > >> When asking for help on non-trivial SELECT queries it really helps >> to tell us the version of PG you are using so that responders know >> what functionality you can and cannot use. In this case >> specifically, whether WINDOW (and maybe WITH) clauses available? > > Unfortunately I'm on 8.3 so no WINDOW. > > I didn't even think of using them and I can't think of any way to > use WINDOW/WITH but if there is a more readable solution that use > them I'd like to see it even if I won't be able to use it. > Of course I'm more interested to know if there is any cleaner > solution for 8.3. > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extract (Recover) data from a cluster built on a different architecture (ARM).
Hi I'm looking for a way to extract the data from a PostgreSQL 8.3.14 database (cluster) that was built using an an ARM/Linux server. The problem is that the hardware itself is a brick and the replacement hardware will be X86/AMD64. Sadly my backups are all copies of the DB files and don't include a recent text based dump (fail!). All attempts so far to start up a server using the original files have failed with a couple of different errors (Failed to parse...). I'm rapidly coming to the conclusion that this is to do with endianness. X86 is little endian whereas ARM is primarily big endian. Are there any tools for recovering data from a database built with a different architecture or is my data toast unless I can lay my hands on an ARM box? Thanks so much for your time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Do TEMP Tables have an OID? Can this be a problem if used too frequently?
Hi I've just written a search function which creates a temp table, preforms some reasoning on it returning results then drops it again. I'm using temp tables in an attempt to gain efficiency (not repeating work between one section of the function and another). However I'm worried that there may be some pit falls in doing this. I'm especially worried about OIDs. Does creating a temp table assign an OID to the table? If so am I right to assume that, if the function is used too frequently, it could cause the database to crash by wraping OIDs? Thanks very much for your time Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
