[SQL] Order of evaluation in triggers for checks on inherited table partitions
I am trying to create a trigger on updates to a table that is
partitioned. The child tables are partitioned by month and include
checks on a timestamp field. I want the trigger on the updates to
call a function that replaces the update entirely. In order to do
this my trigger deletes the record from the parent table (which
deletes it from the appropriate child table) and then inserts into the
appropriate child table and returns NULL (thus skipping the actual
update). However when I try to update an existing record with a
timestamp that would place it in a child table different from the
child table it is in I get an error due to the check on the child
table it is currently in. My best guess as to what is happening is
that the trigger is evaluating the check before it evaluates the
trigger function and thus cannot tell that the update to the original
table should never take place. I have included an example below. The
error that results is "new row for relation "t_foo_2011_6" violates
check constraint "t_foo_2011_6_f_timestamp_check""
My questions:
Is the order of evaluation for the trigger causing this error?
If not what is?
Is there another way to update a record in a child table that would
move it to another child table before the update and skip the
evaluation of the check constraints on the current table?
Example code follows:
CREATE SCHEMA some_schema;
CREATE SCHEMA some_schema_children;
--master table
CREATE TABLE some_schema.t_foo (
f_id_foo serial,
f_timestamp timestamp,
f_text varchar(30)
);
CREATE OR REPLACE FUNCTION some_schema.foo_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_insert_foo_trigger
BEFORE INSERT ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_insert_trigger();
CREATE OR REPLACE FUNCTION some_schema.foo_update_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'DELETE FROM some_schema.t_foo WHERE f_id_foo='||NEW.f_id_foo;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_update_foo_trigger
BEFORE UPDATE ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_update_trigger();
INSERT INTO some
Re: [SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
Can procedural languages be used in rules? I didn't see any examples in the documentation that suggested something like this could be done using rules. --Kevin Crain On Mon, May 30, 2011 at 2:21 AM, Jasen Betts wrote: > On 2011-05-27, Kevin Crain wrote: >> I am trying to create a trigger on updates to a table that is >> partitioned. The child tables are partitioned by month and include >> checks on a timestamp field. > >> However when I try to update an existing record with a >> timestamp that would place it in a child table different from the >> child table it is in I get an error due to the check on the child >> table it is currently in. My best guess as to what is happening is >> that the trigger is evaluating the check before it evaluates the >> trigger function and thus cannot tell that the update to the original >> table should never take place. I have included an example below. The >> error that results is "new row for relation "t_foo_2011_6" violates >> check constraint "t_foo_2011_6_f_timestamp_check"" > > the problem is the check is running before the trigger. > perhaps you can use a rule instead of a trigger? > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
Okay, I figured out what is going on. Even though I was running the update on the master table the trigger was not being applied because it was actually being ran using the child table where the record to be updated resided. So the trigger function was being skipped and it was running as an ordinary update, hence the error. In order to get this to work I had to add a trigger for each child table as well to call my update function trigger. --Kevin Crain On Tue, May 31, 2011 at 6:40 AM, Kevin Crain wrote: > Can procedural languages be used in rules? I didn't see any examples > in the documentation that suggested something like this could be done > using rules. > > --Kevin Crain > > On Mon, May 30, 2011 at 2:21 AM, Jasen Betts wrote: >> On 2011-05-27, Kevin Crain wrote: >>> I am trying to create a trigger on updates to a table that is >>> partitioned. The child tables are partitioned by month and include >>> checks on a timestamp field. >> >>> However when I try to update an existing record with a >>> timestamp that would place it in a child table different from the >>> child table it is in I get an error due to the check on the child >>> table it is currently in. My best guess as to what is happening is >>> that the trigger is evaluating the check before it evaluates the >>> trigger function and thus cannot tell that the update to the original >>> table should never take place. I have included an example below. The >>> error that results is "new row for relation "t_foo_2011_6" violates >>> check constraint "t_foo_2011_6_f_timestamp_check"" >> >> the problem is the check is running before the trigger. >> perhaps you can use a rule instead of a trigger? >> >> -- >> ⚂⚃ 100% natural >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
Will you be using a full timestamp with that or are you only concerned about hours and minutes? If you want a full timestamp do you care about the seconds? For example, do you want to be able to do this for '2011-06-01 23:59:04' and '2011-06-02 00:04:04'? On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
It looks like maybe he is trying to fetch records that either have no previous entries or have another record with a timestamp 5 minutes before them at the time they are inserted... On Sat, Jun 4, 2011 at 4:45 AM, Jasen Betts wrote: > On 2011-06-03, [email protected] wrote: >> >> ID TS (HH:MM) >> --- >> 0 20:00 >> 0 20:05 >> 0 20:10 >> 1 20:03 >> 1 20:09 >> >> >> Does my question make sense? > > no, why is (1,20:04) excluded, but (0,20:05) included? > both records are 5 minutes from the newest. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
Why is (0,20:10) listed in your expected results when there is a (0,20:08)? On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
My approach would be to add a column for LAST_TS and place a trigger on insert that populates this new column. Then you have something you can put in your WHERE clause to test on. On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need help with some aggregation magic
Try this:
select user_id, project_id, date_trunc, sum(sum) FROM (select user_id,
project_id, date_trunc('day', ts), SUM(duration) FROM (select user_id,
project_id, a.ts, ((SELECT MIN(b.ts) FROM log b WHERE b.ts>a.ts AND
(date_trunc('day',a.ts)=date_trunc('day',b.ts)))-a.ts) AS duration
from log a order by user_id, project_id, ts) AS foo group by user_id,
project_id, ts) AS day_set group by user_id, project_id, date_trunc
order by user_id, project_id, date_trunc;
-Kevin Crain
On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
> hi,
> I have a log-table that stores events of users and projects like this
> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>
> I need an aggregated list of worktime per user, per project, per day.
>
> The users can switch projects during the day so I can't work this out with
> min(ts) and max(ts).
>
> Is there a clever way to get this with SQL ?
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
You don't need a loop there. Assuming your order id field is of type
varchar you can just build the first part of your string and then do a
count to get the last part using a LIKE comparison:
select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704 -%';
If you do this inside a function it will be like running it in a
transaction so you shouldn't have to worry about it being a multi-user
system.
On Tue, Jul 5, 2011 at 12:38 PM, Samuel Gendler
wrote:
>
>
> On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani wrote:
>>
>> Hi,
>>
>> I have a special need to create a sequence like function.
>>
>> "O-20110704 -2" which is
>> "O" for order (there are other types)
>> "20110704" is for July 4, 2011
>> '2' the second order of the day for July 4, 2011
>>
>> I of course can get the type and date. What I don't know is how to get is
>> the
>> last number. It would seem to be that I would need a loop to determine if
>> the
>> next number existed.
>>
>> LOOP
>> --Check to see if the string exist in a table
>> -- count = count +1
>> -- until I don't find the string
>> END LOOP;
>>
>> but then I thought I could do something like
>>
>> for $1 in (select string from sometable)
>> LOOP
>> count = count + 1
>>
>> or something like this
>>
>> for i in 1..999 LOOP
>> -- check for the existence of the string in a table using 'i'
>> -- there will never be 999 orders in one day.
>> END LOOP
>>
>>
>> So here is the question what would be the best way for a multi-user
>> system?
>> If someone has a better thought - it would be helpful.
>>
>> BTW I did NOT design the number - in fact it seems silly to me.
>
> I'd probably do the following. Create a table to hold the current date as a
> string appropriate for use in ids. I'd also create a sequence for each of
> the id types. I'd set up a cron job (or equivalent) to run at midnight which
> updates the date and resets all of the sequences to 1 within a transaction.
> You can probably do all of it in a single query.
> Then I'd do inserts which generate the id by concatenating the type initial
> with the date and a sequence, probably in an insert trigger on the table if
> you are ok with server generated ids. Otherwise, you could do insert with a
> subquery which generates the id:
> insert into order (id, x, y) values ((select 'O-' || d.datestring || '-' ||
> nextval('order_id_sequence') from date_table d), 'x_value', 'y_value');
> If you are using hibernate or some other ORM, you can surely use an insert
> trigger to generate the id and tell the ORM to use a server generated id.
> sequence documentation is here:
> http://www.postgresql.org/docs/8.1/static/functions-sequence.html
>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
My previous reply was intended for John.
On Tue, Jul 5, 2011 at 1:11 PM, Kevin Crain wrote:
> You don't need a loop there. Assuming your order id field is of type
> varchar you can just build the first part of your string and then do a
> count to get the last part using a LIKE comparison:
>
> select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704
> -%';
>
> If you do this inside a function it will be like running it in a
> transaction so you shouldn't have to worry about it being a multi-user
> system.
>
>
>
> On Tue, Jul 5, 2011 at 12:38 PM, Samuel Gendler
> wrote:
>>
>>
>> On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani wrote:
>>>
>>> Hi,
>>>
>>> I have a special need to create a sequence like function.
>>>
>>> "O-20110704 -2" which is
>>> "O" for order (there are other types)
>>> "20110704" is for July 4, 2011
>>> '2' the second order of the day for July 4, 2011
>>>
>>> I of course can get the type and date. What I don't know is how to get is
>>> the
>>> last number. It would seem to be that I would need a loop to determine if
>>> the
>>> next number existed.
>>>
>>> LOOP
>>> --Check to see if the string exist in a table
>>> -- count = count +1
>>> -- until I don't find the string
>>> END LOOP;
>>>
>>> but then I thought I could do something like
>>>
>>> for $1 in (select string from sometable)
>>> LOOP
>>> count = count + 1
>>>
>>> or something like this
>>>
>>> for i in 1..999 LOOP
>>> -- check for the existence of the string in a table using 'i'
>>> -- there will never be 999 orders in one day.
>>> END LOOP
>>>
>>>
>>> So here is the question what would be the best way for a multi-user
>>> system?
>>> If someone has a better thought - it would be helpful.
>>>
>>> BTW I did NOT design the number - in fact it seems silly to me.
>>
>> I'd probably do the following. Create a table to hold the current date as a
>> string appropriate for use in ids. I'd also create a sequence for each of
>> the id types. I'd set up a cron job (or equivalent) to run at midnight which
>> updates the date and resets all of the sequences to 1 within a transaction.
>> You can probably do all of it in a single query.
>> Then I'd do inserts which generate the id by concatenating the type initial
>> with the date and a sequence, probably in an insert trigger on the table if
>> you are ok with server generated ids. Otherwise, you could do insert with a
>> subquery which generates the id:
>> insert into order (id, x, y) values ((select 'O-' || d.datestring || '-' ||
>> nextval('order_id_sequence') from date_table d), 'x_value', 'y_value');
>> If you are using hibernate or some other ORM, you can surely use an insert
>> trigger to generate the id and tell the ORM to use a server generated id.
>> sequence documentation is here:
>> http://www.postgresql.org/docs/8.1/static/functions-sequence.html
>>
>>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
That's why you need to do this inside a function. Basically just make an insert function for the table and have it calculate the count and do the insert in one transaction. On Tue, Jul 5, 2011 at 5:41 PM, Samuel Gendler wrote: > > > On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani wrote: >> >> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote: >> > You don't need a loop there. Assuming your order id field is of type >> > varchar you can just build the first part of your string and then do a >> > count to get the last part using a LIKE comparison: >> > >> > select count(id_order) + 1 from sometable WHERE id_order LIKE >> > 'O-20110704 >> > -%'; >> > >> > If you do this inside a function it will be like running it in a >> > transaction so you shouldn't have to worry about it being a multi-user >> > system. >> > >> > >> > >> >> I like this - looks better than what I'm currently doing. Thanks >> Johnf >> > > It is simpler, but it will result in id collision if two inserts runs at the > same time, particularly if the count query takes a while to run, so be > prepared to handle that. Make sure you have an index which can satisfy that > count query quickly. If you are not using the C locale for your database, > that means you must create an index on that column that uses > text_pattern_ops or varchar_pattern_ops (depending on if it is text or > varchar column) so that postgresql can use the index for that comparison, > otherwise LIKE clauses will force a sequential scan of the whole table every > time. C locale does byte by byte text comparison, so the special index > isn't required. > http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
IF this field is unique you shouldn't get duplicates from a function; the transaction will either succeed or fail; the beauty of a function is that you can return an error message. I personally prefer to handle errors at the application level, but if you have admins running ad-hoc queries on the database level doing inserts then you definitely do need to handle that properly. Triggers are handy in that regard. You can do loops to check for collisions...I haven't done that so don't know the best way to code that though. On Wed, Jul 6, 2011 at 5:28 AM, Jasen Betts wrote: > On 2011-07-06, Kevin Crain wrote: >> That's why you need to do this inside a function. Basically just make >> an insert function for the table and have it calculate the count and >> do the insert in one transaction. > > you will still get duplicates, so include code in the function to > retry if there is an error. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
This is an unfortunate situation, you shouldn't be required to do this, the people generating your requirements need to be more informed. I would make damn sure you notify the stakeholders in this project that the data model is screwed and needs a redesign. I agree that you should split this table and do a join if you have no option of redesigning this. > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte wrote: >> Yes, sure. I mean, I can't change the whole process which creates columns >> dynamically. >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> wrote: >>> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >>> > Unfortunately It's an inherited data model and I can't make any change >>> > for >>> > now... >>> >>> but by adding columns you *are* making changes to it... >>> >>> Reinoud >>> -- >>> __ >>> "Nothing is as subjective as reality" >>> Reinoud van Leeuwen [email protected] >>> http://reinoud.van.leeuwen.net kvk 27320762 >>> __ >>> >>> -- >>> Sent via pgsql-sql mailing list ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] combining strings to make a query
You can do full-text search in postgres now using ts_vectors. I'd recommend going that route. Doing like comparisons is not a good idea if you don't know the first part of the string you are searching forIt appears to be much faster from my experience to search for ab% than it is to search for %ab%. On Tue, Jul 12, 2011 at 7:51 PM, Wes James wrote: > I'm using Erlang and postgresql to build a web interface. When I > create the query string I get something like: > > select * from table where field::text ilike '%%' > > But when I do that (if someone types in '\' for part of the text > search), I get a pg log entry to use E'\\' > > How would I use E'' with ilike '%%'. ilike E'%\\%' doesn't work. > > Thanks, > > -wes > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
I still can't imagine why you'd ever need this...could you explain what this does? I'm just curious now On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain wrote: > This is an unfortunate situation, you shouldn't be required to do > this, the people generating your requirements need to be more > informed. I would make damn sure you notify the stakeholders in this > project that the data model is screwed and needs a redesign. I agree > that you should split this table and do a join if you have no option > of redesigning this. > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte wrote: >> Yes, sure. I mean, I can't change the whole process which creates columns >> dynamically. >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> wrote: >>> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >>> > Unfortunately It's an inherited data model and I can't make any change >>> > for >>> > now... >>> >>> but by adding columns you *are* making changes to it... >>> >>> Reinoud >>> -- >>> __ >>> "Nothing is as subjective as reality" >>> Reinoud van Leeuwen [email protected] >>> http://reinoud.van.leeuwen.net kvk 27320762 >>> __ >>> >>> -- >>> Sent via pgsql-sql mailing list ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
How are you determining the data types for these columns? On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte wrote: > Hi, > Thanks for your interest. This app load scv files which change every day > (sometimes the columns too). The sizes of these files are in avg 15MB. So, > We load something like 100MB each day. We tried to find a better solution > but we couldn't, becouse one of the our requirement is not to use a lot of > space. Also, the app is used to consult these information, and for our > particular type of select's queries, we get the best performance if the > information is all into a same row. > > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote: >> >> I still can't imagine why you'd ever need this...could you explain >> what this does? I'm just curious now >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> wrote: >> > This is an unfortunate situation, you shouldn't be required to do >> > this, the people generating your requirements need to be more >> > informed. I would make damn sure you notify the stakeholders in this >> > project that the data model is screwed and needs a redesign. I agree >> > that you should split this table and do a join if you have no option >> > of redesigning this. >> > >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> > wrote: >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> columns >> >> dynamically. >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> wrote: >> >>> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >>> > Unfortunately It's an inherited data model and I can't make any >> >>> > change >> >>> > for >> >>> > now... >> >>> >> >>> but by adding columns you *are* making changes to it... >> >>> >> >>> Reinoud >> >>> -- >> >>> __ >> >>> "Nothing is as subjective as reality" >> >>> Reinoud van Leeuwen [email protected] >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >>> __ >> >>> >> >>> -- >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >>> To make changes to your subscription: >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
Is there any room for improvement in the data types? On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen [email protected] >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
Have you tried changing the block size? http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen [email protected] >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compile postgres with visual studio 2010
The documentation only mentions Visual Studio 2005 and Visual Studio 2008, but I see no reason why it shouldn't work. Check out the requirements listed in the documentation: http://www.postgresql.org/docs/9.0/interactive/install-windows-full.html On Wed, Jul 20, 2011 at 3:55 AM, Sofer, Yuval wrote: > Hi > > I would like to build Postgres from source with the visual studio 2010 > compiler > > Is it supported? Is there any document which describes the process of the > implementation? > > Thanks, > > > Yuval Sofer > BMC Software > CTM&D Business Unit > DBA Team > 972-52-4286-282 > [email protected] > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
