>> try this:
>> select mytime from mytable order by abs(extract(epoch from
>> (usertime-mytime))) asc limit 5;
>> SELECT ts_fld2,abs(extract(epoch from '2011-03-25
>> 14:15:25-07'::timestamptz)-extract(epoch from ts_fld2)) from timestamp_test
>> order
>> by abs(extract(epoch from '2011-03-25
>
> How about something like:
> test(5432)aklaver=>SELECT ts_fld2,now()-ts_fld2 from timestamp_test order
by
> now()-ts_fld2 limit 5;
Thanks Adrian,
Let me explain the problem better. Say my table has 24 entries, one for
each
hour, midnight through 11 pm. If the user enters "6:30 PM", I want to
Hello,
I have a table with a list of times. When the user provides my application
with a
desired time, I want to show them the 5 times from the table that are
closest to their
input. I expected to do this using abs() like such:
select mytime from mytable order by abs(usertime-mytime) asc l
Merlin Moncure wrote:
On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich wrote:
Hello,
There is an existing function which takes an integer and returns a record.
I need to call this function with every integer in a table. Is there a
simple shortcut for doing this?
I'm looking for something
Hello,
There is an existing function which takes an integer and returns a
record. I need to call this function with every integer in a table. Is
there a simple shortcut for doing this?
I'm looking for something like:
select f.*
from function(t.value) f, table t
Thanks,
Adam
--
Sent vi
This seems like a simple question that would have come up, but I'm not
able to find an answer in google, PG docs, or PG mailing list archives.
How do I prevent a user from being able to list all databases in my
cluster? I want to restrict them to seeing just the databases they have
connect
Hello,
Please reference these explain plans. This is Pg 8.4.1
http://explain-analyze.info/query_plans/4032-query-plan-2745
http://explain-analyze.info/query_plans/4033-query-plan-2746
First, could somebody explain what is leading the first query to choose
a different plan that's much slower?
Shane R. Spencer wrote:
I work in VoIP. HMS (Hour/Minute/Second) format appears to be the rule
when working with call time totals. I admit it makes some reports
easier to read.
The method I used to convert a int/bigint to HMS (or the standard
representation of an interval type) is as follows:
Neil Saunders wrote:
Hi all,
I maintain an online property rental application. The main focus of the
UI is the search engine, which I'd now like to improve by allowing
filtering of the search results shown on some criteria, but provide a
count of the number of properties that meet that criter
Hello,
I'm building a new server on RHEL 5.3 and was wondering if there was an
optimized build guide published somewhere with guidelines on disk
partitioning, filesystems, etc? For example, do you recommend putting
the data on an ext2 partition mounted noatime, and the logs on ext3? Or
shoul
Willy-Bas Loos wrote:
Hi,
I'm trying to figure out to generate a auto-increment column in a view.
There is no physical column to base it on, the view contains a group
by clause, which renders that impossible.
In a normal query i can create a sequence for that purpouse and drop
it afterwards, but
David Kerr wrote:
On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
- In Oracle, the way we handle audit triggers is by using Package
- Variables. We emulate some of that functionality in postgresql by
- adding a custom variable to the configuration file:
-
- custom_variable_classes
> Most of the time, my application will set the edited_by field to
> reflect an application username (i.e., the application logs into the
> database as a database user, and that's not going to be the
> application user) So I log into my application as "Dave", but the
> application connects to the
From: vinny
Subject: Re: R: [GENERAL] Field's position in Table
To: "Sam Mason"
Cc: pgsql-general@postgresql.org
Date: Monday, August 24, 2009, 2:38 PM
On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote:
> On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote:
> > I can't really think of any r
Juan Backson wrote:
Hi,
In my table, I have fieldA and fieldB. At any point in time, only one
of these fields can have data in it. The other is NULL.
Instead of "select fieldA, fieldB from table", I want it to return
either fieldA or fieldB depends on whether it is NULL or not.
The re
Andre,
See this PHP page:
http://www.php.net/manual/en/function.pg-last-notice.php
Andre Lopes wrote:
Hi,
I'm developing a function with some checks, for example... to check if
the e-mail is valid or not.
If the e-mail is not valid I put a line with RAISE NOTICE 'E-mail not
valid'.
I n
Tim Keitt wrote:
I am combining query results that I know are disjoint. I'm wondering
how much overhead there is in calling union versus union all. (Just
curious really; I can't see a reason not to use union all.) (cc me
please; not subscribed...)
THK
I think you can test this one yourself p
Guy Flaherty wrote:
On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <mailto:ada...@sbcglobal.net>> wrote:
Hello,
I have a table with a DATE field "birth_date". The data obviously
contains various dates in the past, such as 07/04/1970. In my
query, I need to re
Hello,
I have a table with a DATE field "birth_date". The data obviously
contains various dates in the past, such as 07/04/1970. In my query, I
need to retrieve the person's "next" birthday. In other words, for the
example date 07/04/1970, the query should return 07/04/2009 for the
current
APseudoUtopia wrote:
Hey list,
I have a table with user IDs, among other information. I also have a
table of comments that users can place on a page.
CREATE TABLE "users" (
"id" SERIAL PRIMARY KEY,
...
);
CREATE TABLE "comments" (
"id" SERIAL PRIMARY KEY,
"userid" INTEGER REFERENCES "use
Emanuel Calvo Franco wrote:
>
> Executing 'select * from datos limit 1 offset 15' two times i
have different
> result sets.
> When i execute 'explain analyze verbose ' i see that (as
> expected) the seq scan
> is occurring.
>
>
> That's correct? Is logical that if the scan is sequential in
Carlo Stonebanks wrote:
I think the ORDER BY is free to update the rows in any order it needs
to. The key is to put the sequence further down. How about this?
Adam - thanks. Unless I hear otherwise I will assume that you mean the
UPDATE is free to update the rows in any way it wants - irrega
Carlo Stonebanks wrote:
(FOR PG VERSION 8.3.6 running on Windows 2003 Server x64)
We have a function that assigns unique ID's (to use as row identifiers)
to a table via an UPDATE using nextval(). This table is imported from
another source, and there is a "sequencing" field to let the query kno
>
>
> > This query makes little sense. Why are you trying to convert a
> > timestamp to a timestamp? Is this a bizarre substitute for
> date_trunc()?
>
> The "from_datetime" column is of type "timestamp" but I want to check
> only the date, not the time.
> In this example I want to retrieve al
>
>
> > OK, so you want to see if a timestamp is greater than now()? Why not
> > just compare them?
> >
> > where a.from_datetime >= now()
>
> No, not the whole timestamp. I dont want to check the time.
> So I had to truncate the datetime with:
>
> date_trunc('day', a.from_datetime) >= date_tr
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Sharma, Sid
> Sent: Tuesday, February 24, 2009 12:47 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Query question
>
> Hi,
>
> I am new to Postgres and
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Richard Yen
> Sent: Monday, February 09, 2009 4:18 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] trying to make sense of deadlocks
>
> Hi,
>
> I'm tryin
>
> I have the following sql:
>
> SELECT * from table order by dato asc limit 20 offset 0
>
>
> This gives me different rows than the 20 first rows when running the
> following sql:
>
> SELECT * from table order by dato asc
>
>
> Shouldn't the 20 first rows in the second sql statment be the
> On Wed, Feb 04, 2009 at 09:34:56AM -0500, Raymond C. Rodgers wrote:
> > You don't need to depend on an external library for this
> functionality;
> > it's built right into Postgres. Personally, in my own apps I write in
> > PHP, I use a combination of sha1 and md5 to hash user passwords,
> > wit
> >> I would like to create a new table where one of the field would be a
> >> user password. Is there any data type for supporting this
> >> functionality? Something like Password DataType. I've taken a look
> of
> >> the available data types in PgAdmin Application and there is nothing
> >> simila
> On Saturday 31 January 2009 8:47:28 pm Adam Rich wrote:
> > On Thu, 29 Jan 2009 13:16:17 +
> >
> > Gregory Stark wrote:
> > > So, what do people say? Is Postgres perfect in your world or does
> it
> > > do some things which rub you the wrong
On Thu, 29 Jan 2009 13:16:17 +
Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it
> do some things which rub you the wrong way?
I see all the major ones have already been mentioned, so here's some
minor ones.
- lack of system-level and DDL triggers
> > Hello,
> >
> > I have a column with a small number of distinct values, indexing this
one
> > with a standard BTree is useless. How do I can index this column
> > efficiently? I searched and it seems that pg doesn't support the
creation of
> > persistent bitmap indexes... Is that feature planne
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Tuan Hoang Anh
> Sent: Tuesday, December 30, 2008 10:49 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to use index in strpos function
>
> I have ta
>
> Hi all,
>
> I'd like to make a single query that returns a number of rows using a
> 'WHERE id IN ()' condition, but I'd like the rows to be
> returned in the order in which the ids are given in the list.
>
> Is this possible?
>
Depending on how many IDs you have in your list, you can accom
> if i have a "column" that is a calculation, say a bank balance -> sum
> of all the debits and credits...is it more efficient to make a view
> that executes the underlying calc query doing the math, or to create a
> table that has a column called balance that is updated for each
> transaction?
>
> On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote:
> > I need to add some complex constraints at the DB.
> >
> > For example.
> >
> > Do not allow a line item of inventory to be changed if it does not
> > result in the same number of joints originally shipped.
> >
> > These will involve
> >
> > When we get windowing functions, a lot of this pain will go away :)
> >
>
> Yes! Hope it won't be too long now. The patch seems to behave like it
> should
> now :)
> Hopefully we'll see it commited for 8.4.
>
> Though this does not look too much cleaner at least it's standard SQL:
>
> A
> I'm seeing a lot of plans in my database that look like this:
> It seems very strange for the planner to decide to build an in-memory
> hash table on a column that is already indexed (the primary key, no
> less!). But this is happening A LOT - I often see plans where a
> majority of the joins ar
> >> sorry I get nothing :(
> >
> > Of course not. None of the dates you gave in the example overlap.
> >
>
> But it should still have the 1st entry with the name Ben? Am I
> missing something?
Ben's issue dates are in the year 2008. The first call entry is
in the year 2007. There are no cust
> Now user Ben has passed his mobile to user Josh and we issued Josh his
> mobile on 2008-10-15.
>
> 1. Is it possible for me to write a query that will have the fields
>
> call.call_id,
> call.datetime,
> mobile_custodian.user_id,
> call.mobile_no
> call.charge
>
> that will use call.datetime a
I traced a bug in our application down to this basic operation:
set timezone to 'US/Eastern';
select '11/02/2008'::timestamptz, '12:10'::time,
'11/02/2008'::timestamptz + '12:10'::time;
I have a date and a time stored separately and I want to combine them,
and use them in some timezone-aware cal
> This query from the console:
>
> select * from stats order by start_time;
>
> takes 8 seconds before starting its output. Am I wrong in assuming that
> the index on start_time should make ORDER BY orders of magnitude
> faster?
> Or is this already fast enough? Or should I max up some memory (bu
>
> Hi, I have a table of the form
>
> aid pid nmol
> - --- ---
> 123 34
> 245 3445
> 323 100
> 478 12
> 545 14
> 645 200
> 7null null
>
> In general, aid is unique, pid and nmol are non-unique.
>
> What I'm trying to do is to select those rows w
>
> Hello,
>
> I'm developing a autocomplete Feature using php and PostgreSQL 8.3.
> To fill the autocomplete box I use the following SQL Statement:
> select * from _table_ where upper( _field_ ) like '%STRING%';
>
> This SQL Statement takes 900 ms on a Table with 300.000 entries.
>
> What can
>
> childrensjustice=# create table petition_bail like petition_white;
> ERROR: syntax error at or near "like"
> LINE 1: create table petition_bail like petition_white;
>
> Huh?
>
> Yes, the source table exists and obviously as postgres superuser
> ("pgsql") I have select permission on the par
>
> Dear All,
>
> I have the following problem with grouping: I want to know the maximum
> in a group as well as the maximal element. Example:
>
> I have a table products_providers:
> product | provider | criteria_1 | criteria_2
>
> I have a number of products, each of them from a several
>
> I'm building a shift-scheduling app. I want to make a constraint in my
> database that prevents one human from being assigned to work two
> different jobs at the same time.
>
> In other words, if I schedule John Doe to mop bathrooms from 10 AM
> until
> 4 PM, some other manager will not be a
>
> "Bob Duffey" <[EMAIL PROTECTED]> writes:
> > I'm seeing some query plans that I'm not expecting. The table in
> question
> > is reasonably big (130,000,000 rows). The table has a primary key,
> indexed
> > by one field ("ID", of type bigint). Thus, I would expect the
> following
> > query
>
> What I want to do is present the results of a query in a web page, but
> only 10 rows at a time. My PostgreSQL table has millions of records and
> if I don't add a LIMIT 10 to the SQL selection, the request can take
> too
> long. The worst case scenario is when the user requests all records
>
> 1. I have heard of problems arising from compiling PostGreSQL (8.3) on
> 64-bit
> processors. What sort of problems am I likely to encounter and how
> should I fix
> them? We are will run Linux Redhat 5 on a Dell PE2950 III Quad Core
> Xeon E54
> 2.33 GHz, and a Dell PE2950 III Quad Core Xeon
>
> Can you describe, or point me to somewhere which describes, all the
> things you can do with a rule that you can't do with a trigger? The
> only examples of rules in the manual are (1) logging, which I've just
> been told is much better done with a trigger, and (2) making update,
> insert, an
> -Original Message-
>
> Hi all,
>
> I have been using IN clause almost exclusively until recently I tried
> to use EXISTS and gained significant performance increase without
> changing/creating any indexes:
>
> SELECT ... FROM a WHERE a.ref IN (SELECT b.id WHERE ...)
>
> vs
>
> SELECT
> -Original Message-
> The small table is a listing of county fips codes, their name, and the
> geometry for the county. Each fips is only listed once. The big table
> is multiple emissions for each county, the parameter for the emission,
> and the source code for the emission (scc). Each
> > I am not familiar with the python library, but that looks correct
> > to me. You can always test it by adding a sleep between your two
> > queries and modifying the database from a console connection
> > during the sleep.
>
> > Note that I'm assuming your 5 queries are all read-only selects.
> >> I need that 5 queries, fired from the same ajax request to a web
> >> python application, see the same database snapshot. The driver is
> >> psycopg2.
> >>
> >> Since postgresql 8.2 functions can't return multiple result sets
> >> what would be the best aproach?
> >>
> > You want to set your
> I need that 5 queries, fired from the same ajax request to a web
> python application, see the same database snapshot. The driver is
> psycopg2.
>
> Since postgresql 8.2 functions can't return multiple result sets what
> would be the best aproach?
>
You want to set your transaction isolation
> I've implemented Depesz's running total function
> (http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-
> sum-in-
> one-query/) in my DB, which works great.
> Now what I want to do is get the running total for a certain statement
> and
> then do a subselect on that result so to
>
> I need to be able to do queries that restrict my result set to items
> belonging to a specified site and ignore all nodes that belong to
> different sites. To determine the ID of the site an item belongs to I
> wrote a stored procedure:
>
> This returns the ID of the root node for non-root
> I have two tables that have identical index fields, maplot and
> unitno, (both indexes span two columns) and I want to find all the
> records in the commcost table that don't have a corresponding record
> in the bldg file.
>
> The SQL I've tried is:
>
> select commcost.maplot, commcost.unitno
>
> Is it possible to do this?
>
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;
>
> What I want is to return a boolean, but when I tried SELECT
> COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to
> boolean.
>
How about this? Logic
al expresses are alrea
>
> It worked Thanks!! But there is definitly something wrong with the
> error
> message I got (right?):
>
> reference=# alter table "IN" add column INDESCS VARCHAR[];
> ERROR: column "indescs" of relation "IN" already exists
>
I don't know, what do you see when you \d "IN" ?
When you use d
> Could you just have a look to the output below? I try to add a column
> to a
> table named "in" (I know "in" is a reserved keyword but the table
> exists and
> I cannot change it). Postgresql complains that the column already
> exist but
> it didn't. Am I doing something wrong ?
Try: ALTER T
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- ");
Ideally, you'd use this:
pg_query_params('select id from table1 where a=$1', a
> I hope this isn't a FAQ, but does anyone have any
> suggestions as to
> how to make a query that selects using:
> "where in()"
> secure from an sql injection point of view?
>
> As the length of the comma delimited list is highly
> variable I don't
> think I can use a prepared query to increase
> I have just created a table using SELECT INTO however the PK was
> supposed to be a serial. It is now an integer. To make it a serial I
> just create the seq and set the default to be the nextval() of that
> sequence right? is there anything else I need to do?
You'll want to do this:
ALTER SE
>> Does Postgres allow updates based on the context of a sub-query,
something like the sample below ?
Yes,
Update real_tab
set real_tab.data_desc = temp_tab.data_desc
>From temp_tab
Where real_tab.keyID = temp_tab.keyID
(don't repeat your updated table in the "from" list unless you
Mean to self
> I have a table that has 3 date columns :
>
> create table xyz (
> xyz_id integer,
> date1 timestamp,
> date2 timestamp,
> date3 timestamp
> )
>
>
> I want to select in a query the xyz_id and the max date column for
> each row
> something like :
> create table temp2 as select xyz_id (max
> Hi all,
> I am trying to backup a large table with about 6 million rows. I want
> to
> export the data from the table and be able to import it into another
> table
> on a different database server (from pgsql 8.1 to 8.2). I need to
> export the
> data through SQL query 'cause I want to do a gr
> > Oh, then there should have been some options in the survey along the
> > lines of "things are fine how they are."
>
> Oh, a bit of answer-forcing wasn't beneath him.
Ummm... Isn't that what Option A is about ?
1) What type of names do you prefer?
---
a) old no
> Thanks Richard. Is there a way to do it without changing the INSERT
> command? As I mentioned, there are many more columns of different
> types, so finding and replacing the VALUES would be very difficult.
Can you import the data into a holding table (with columns defined
as integer) first, an
>
> Please let us know your meaning,
>
> thanks Zdenek Kotala
>
1. c
2. a
3. other = "pginitdb", to be consistent with pgcreatedb,etc
4. a
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mai
> By the way, I have just inserted a duplicate. Then I have run the
> select statement with distinct and I got an error.
>
> How one can solve this?
>
Does this work?
select distinct name
from ( select name from t order by replace(name, '.', 'a')) as t2
--
Sent via pgsql-general mailing li
> The short answer is that Adam's statement is wrong, or at least
> misleading.
Sorry Tom, I wasn't trying to do either. Joshua Drake (who I understand
to be a reliable source of postgresql information) said that applying
a sequence to a column after creation created issues, versus using the
seri
> No, you need compat-3, not compat-4. For example:
>
> [EMAIL PROTECTED] ~]# yum install php-pgsql
> --> Processing Dependency: libpq.so.3 for package: php-pgsql
I have applications that depend on libpq.so.4
Where do I get that, if not compat-postgresql-libs-4-2 ??
--
Sent via pgsql-general
> Pick your OS/Arch from this list, and click to it:
>
> http://yum.pgsqlrpms.org/rpmchart.php
>
> Then click to "C" at the top, and download the compat package.
Devrim,
I clicked on my OS (RHEL/CentOS 4 - x86) Then on "C" as you said,
But the RPM list still only contains the compat-postgresql-l
> > I am not sure about 8.3 but certainly earlier releases of PostgreSQL
> > would have specific dependency issues when a sequence was applied to
> a
> > a column after the fact, versus using the serial or bigserial
> > psuedo-types.
I'd like to point out that using pg_dump does in fact apply sequ
Thanks to Pavan for the answer regarding HOT. Does anybody have an
answer regarding the postgres role or compat lib ?
>
> * From: "Adam Rich"
> * To:
> * Subject: 8.3.0 upgrade
> * Date: Mon, 17 Mar 2008 02:13:55 -0500
>
>
> I just fini
I just finished upgrading my production DB to 8.3.0. Everything went
smoothly, but I thought of a few questions.
After the upgrade, while restoring my backup to the new version,
I got this error message:
ERROR: role "postgres" already exists
I assume this is nothing to be concerned about. B
> Is there a query to pg_catalog tables to find out which table/column
> has
> the stat level not at default in 1 sweep?
Try this:
select c.relname, a.attname, attstattarget
from pg_catalog.pg_attribute a, pg_catalog.pg_class c,
pg_catalog.pg_namespace n
where a.attrelid = c.oid and c.relnamespac
> I need to convert postgres timestamp to date format -mm-dd in a
> sql statement.
> pt.created_date below is timestamp format
>
> i.e ... WHERE pt.created_date >= '2008-01-21'
>
> Any help would be greatly appreciated.
Try this:
WHERE pt.created_date >= '2008-01-21'::date
--
Sent v
> I'm not married to using SQL: are there other efficient solutions to
> store directed graphs? Could I hack something up in Perl or Ruby and
> then serialize my in-memory graph to a file (for efficient
> saving/reloading)?
As far as a perl solution, I would suggest posting your problem on
perlmon
> "The data types of all the result expressions must be convertible to a
> single output type."
The type of the field pg_class.relkind appears to be "char" which is
described in the notes as:
The type "char" (note the quotes) is different from char(1) in that it
only uses one byte of storage. It
> I wanted to use the following statement to "translate" the relkind
> column to a
> more descriptive value:
>
> select c.relname
> case
> when c.relkind in ('t','r') then 'table'
> when c.relkind = 'i' then 'index'
> when c.relkind = 'S' then 'sequence'
>
> Can you provide more details? pg_locks, pg_stat_activity, the deadlock
> message?
>
> (Hmm, it would be helpful if the deadlock checker were to save the
> pg_locks contents and perhaps pg_stat_activity in a file, whenever a
> deadlock is detected.)
Great idea! As somebody who's spent hours tr
> I would instead queue messages (or suitable information about them) in
> a table, and have a process outside PostgreSQL periodically poll for them
Why poll when you can wait?
http://www.postgresql.org/docs/8.2/interactive/sql-notify.html
---(end of broadcast)-
> > I have two options for storing this data: As BYTEA or as large objects.
Is it true that if you update a row containing a large BYTEA value, (even if
you're not updating the BYTEA field itself, just another field), it requires
the entire BYTEA value to be copied to a new row (because of MVCC)
> It seems to do the job, but how good is it in the long run? Any way I
> could tweak it?
I think this form will work the best:
SELECT u.login, MAX(s.stop_time) AS last_use_time
FROM users u, stats s
WHERE u.id=s.user_id
AND u.status='3' AND u.next_plan_id IS NULL
GROUP BY u.login
HAVING MAX(s.
Gregory,
> Are you using it just to avoid retyping a complex subquery? Or do you
> expect
> that the feature will reduce the execution time by avoiding re-
> executing the
> subquery for each call site in the query?
The only situation where I rely on this currently is when my main/outer
query re
Are there any plans to support materialized subquery / factoring (sql-99
WITH)
in Postgresql? I am spoiled with this feature in oracle, and find myself
wishing I had it in postgresql more and more. It *seems* to an outsider
like a relatively easy addition.
I searched the archives but only fou
> how can I dump a schema with all tables, but without the functions? Is
> there a way to do it, or do I have to manually drop the functions
> later when having used the pg_restore?
Stef,
You can edit the data between dump and restore, to comment out the
function references. Or, you can use the "-
;seq2') as s, * from (select client_id, arbnum
from arb_test order by client_id, arbnum OFFSET 0)as z OFFSET 0) as b
on a.s=(b.s-1) where a.client_id=b.client_id;
> -Original Message-
> From: Willem Buitendyk [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:48 PM
&
Hi Willem,
> for some reason the order by's aren't working.
Could you provide more details? Do you get a specific error message?
> only returning 658 rows instead of the 750K.
You should not expect the same row count in both source table and
result set. Even in your example -- you provided 8
> > I'm trying to replicate the use of Oracle's 'lag' and 'over
> > partition by' analytical functions in my query. I have a table
> > (all_client_times) such as:
> > and I would like to create a new view that takes the first table and
> > calculates the time difference in minutes between each row
> I tried this function but it keeps returning an error such as:
>
> ERROR: invalid input syntax for integer: "2007-05-05 00:34:08"
> SQL state: 22P02
> Context: PL/pgSQL function "lagfunc" line 10 at assignment
Whoops, this line:
> > client_id := thisrow.datetime;
Should be:
clien
> and I would like to create a new view that takes the first table and
> calculates the time difference in minutes between each row so that the
> result is something like:
>
> client_id,datetime, previousTime, difftime
> 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
> 455,2007-05-01 12:03:00, 20
> Resulting in 4 columns in the ResultSet like:
>
> count(*)_from_table2_between_fromdate1_and_todate1 = X
> count(*)_from_table2_between_fromdate2_and_todate2 = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*)_from_table3_between_fromdate2_and_todate2 = V
>
> Is thi
> I'd like to implement some simple data logging via triggers on a small
> number of infrequently updated tables and I'm wondering if there are
> some helpful functions, plugins or idioms that would serialize a row
If you're familiar with perl, you can try PL/Perl.
http://www.postgresql.org/docs
> In my database, I have a core table that nearly all other tables
> key against. Now I need to adjust all of those foreign keys to
> add a "on update cascade" action. Is there a way to alter the
> existing keys? (it didn't jump out at me in the manual)
>
Would it be possible to modify confupdt
In my database, I have a core table that nearly all other tables
key against. Now I need to adjust all of those foreign keys to
add a "on update cascade" action. Is there a way to alter the
existing keys? (it didn't jump out at me in the manual)
If not, is there a serious issue preventing this
1 - 100 of 131 matches
Mail list logo
