When I join tables; I will sometimes get multiple rows back as in this
example.
create table class(name varchar, p point, d int);
insert into class values( 'All', point(50,50), 100 );
insert into class values( 'NE70', point(70,70), 20 );
insert into class values( 'NE75', point(75,75), 20 );
inse
James Cloos wrote:
I have a table which includes a text column containing posix-style
paths. Ie, matching the regexp "^[^/]+(/[^/]+)*$".
I need to do a query of a number of columns from that table, plus the
count of rows which are "children" of the current row.
The query:
SELECT count(*) AS
On 09/15/11 19:40, Andreas wrote:
Hi,
is there a clever way to check overlapping time intervals ?
An option named n should be taken from date y to y.
The same name is ok for another interval.
e.g. table : mytab ( d1 date, d2 date, n text, v text )
There should be a constraint to provide no row
R BY pernr, eindt DESC;
> ---
>
> In this case the ORDER BY does not work: I will get the same person
> data, either with DESC as with ASC, even when this should change.
>
> Does anyone have an explanation for this?
Don't you sort just the part at EXCEPT?
Cheers,
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
On 03/20/12 06:14, Bèrto ëd Sèra wrote:
So as a dirty and quick hack to
make sure our failure filter works I wanted to have an external process
kill and relaunch the filter from cron each 30 minutes.
Is there anyway I can mark the process running the filter, maybe using
the update_process_title
Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and how I can fix it?
Looks a bit like wrong statistics. Are the statistiks for your tables
correct?
Cheers,
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to y
At 04:14 PM 10/9/06, Daniel Drotos wrote:
What is the best way to do something like:
delete from tablename where something order by somefield...
You cannot, because it doesn't make sense. The "order by" clause is not
valid on delete statement. Queries from other processes that start while
At 07:23 PM 10/27/06, beau hargis wrote:
I am aware of the double-quote 'feature' which indicates that an element
should be treated in a case-sensitive way. This as been the 'answer' to every
question of this sort. This 'feature' does not solve the problem and
introduces other problems.
If you
At 10:42 AM 1/16/07, Mario Behring wrote:
Thank you for your advise..I was thinking about doing exactly
that, I wasn't sure on how to do it though, meaning, considering the info
below, how should I use the CREATE INDEX command to create these indexes??
This might provide a clue:
At 11:27 AM 1/16/07, Frank Bax wrote:
At 10:42 AM 1/16/07, Mario Behring wrote:
Thank you for your advise..I was thinking about doing exactly
that, I wasn't sure on how to do it though, meaning, considering the info
below, how should I use the CREATE INDEX command to create
At 11:04 AM 2/9/07, Sabin Coanda wrote:
I have two queries:
1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS d ) x
ORDER BY i
2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d
UNION SELECT 51 AS i, true AS d ) x ORDER BY i
The first returns the colum
At 12:22 PM 3/9/07, Ezequias Rodrigues da Rocha wrote:
Does someone have statistcs from PostgreSQL ? Numbers from the list,
performance statistics. I must argue with another person the idea of do
not put Oracle in our organization.
Performance should not be the *only* consideration when compa
At 12:54 PM 3/9/07, Ezequias Rodrigues da Rocha wrote:
Is it a simple action to convert a database from PostgreSQL to Oracle ?
I mean a simple database with
33 tables
8 functions
31 sequencies
2 triggers
1 type
3 views
Has someone any idea ?
Depends on what's actually in the above objects;
At 11:39 AM 3/14/07, Wiebe Cazemier wrote:
Consider this scenario of three (simplified) tables:
people
- id
- name
accounts
- id
- owner_id REFERENCES people
account_co_owners
- co_owner_id REFERENCES people
- account_id REFERENCES accounts
I need a query that allows the user to search for ac
At 07:40 PM 5/12/07, Andreas wrote:
I've got a stack of tasks to show in a list.
Every task has a timestamp X that may be NULL or a date. It contains the
date when this tasks should be done.
Sometimes it has date and the time-part, too.
The list should be like this:
1) X sometime today s
Andreas wrote:
I'd like to update a table efficiently where the relevant select-info
is in another table that is foreign-linked.
Stupid example. 2 tables:
things (thing_id integer, name varchar(100), color varchar(100))
inventory (item_id integer, thing_fk integer references things
(thing_id
Gary Stainburn wrote:
Hi folks.
I've got a problem with regex_replace.
The function is regexp_replace - you misspelled it.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
nventory(item_id,purchase_date)
values(1,"2008 7 22 {} {} {} {DD MONTH }")
I would change it to something like:
insert into tbl_inventory(item_id,purchase_date)
values(1,regexp_replace('2008 7 22 {} {} {} {DD MONTH }','{.+}
*','&#x
Sofer, Yuval wrote:
I need to extract table DDL (create script) from database connection
(using sql, by retrieving system table info or by activating some pg
function)
This command should output the SQL you need...
echo '\d tablename' | psql -E
---(end of broadcast
Aarni Ruuhimäki wrote:
Check my work, but I think the sum part of the query simply becomes:
sum (
(
date_smaller(res_end_day, '2008-02-29'::date) -
date_larger(res_start_day, '2008-01-31'::date)
) * group_size
)
Basically remove the "+1" so we don't include both start and end dates
but
Frank Bax wrote:
Aarni Ruuhimäki wrote:
Anyway, I have to rethink and elaborate the query. I know that it will
usually be on a monthly or yearly basis, but a reservation can
actually be any of the following in relation to the given (arbitrary)
period:
1. start_day before period_start
Aarni Ruuhimäki wrote:
Thanks Frank,
astart_day before period_start, end_day before period_start
This I don't have to care about as it is not in the period we are looking at.
bstart_day = period_start, end_day = period_start
Is zero days/nights, ignored. Not even possib
Aarni Ruuhimäki wrote:
So the WHERE clause would go like:
group_id = 1 AND res_start_day < '$date1' AND res_end_day >= '$date1' [AND
region_id = $region_id] [AND company_id = $company_id] [AND product_id =
$product_id]
OR
group_id = 1 AND res_start_day = '$date1' AND res_end_day >= '$date1' [
Nacef LABIDI wrote:
is there a better method to retrieve all
the rows with dates in the current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
--
Sent via pgsql-sql mailing list (pgsql-sql@p
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in the
current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
Sorry; I was not thinking
Craig Ringer wrote:
> seiliki wrote:
>> I expect the SELECT to return two rows. Would some kind
>> soul explain for me why it gives only one row?
>
> Without having read the post in detail I'll make a guess: Because NULL =
> NULL results in NULL, not true, and the outer (or any other) join
> cond
David Garamond wrote:
Dear SQL masters,
The query for "latest price for each product" goes like this (which I
can grasp quite easily):
SELECT * FROM price p1
WHERE ctime=(SELECT MAX(ctime) FROM price p2 WHERE p1.product_id=p2.product_id)
or:
SELECT * FROM price p1
WHERE NOT EXISTS (SELECT
x27;;
and I doubt that there is special code to handle case where length of
argument is exactly the same as column. However; ~* '^a' which anchors
search to first character is perhaps more likely to use an index scan.
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[EMAIL PROTECTED] wrote:
On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote:
Have you tried something where you read in all those "IN id's" and then
group them into blocks (of say 1,000 or 10,000 or whatever number works
best)? Then execute:
DELETE FROM a WHERE a.b_id in ([static_
[EMAIL PROTECTED] wrote:
On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote:
If you're really desperate; is it possible to alter table 'a' to add column
b_id; populate it; delete your rows without a join; then drop the column?
I thought of something similar, but UPDA
Kevin Duffy wrote:
Within my table there is a field DESCRIPTION that I would like to parse
and split out into other fields.
Within DESCRIPTION there are spaces that separate the data items.
String_to_array(description, ‘ ‘) does the job very well.
I need something like this to work.
Kevin Duffy wrote:
Noticed that string_to_array does not handle double spaces very well.
If there are double space between the tokens, there is "" (empty string)
in the array returned. Not exactly what I expected.
Try regexp_replace
http://www.postgresql.org/docs/8.3/interactive/functions-st
Kevin Duffy wrote:
Just testing the regexp_string_to_array
This SQL
select description, regexp_string_to_array(description::text , E'\\s+' )
as optdesc, securitytype
from xx where type = 'B' order by 1
produced this error:
ERROR: function regexp_string_to_array(text, text) does n
Kevin Duffy wrote:
No looks like I have 8.2
This works on 8.2:
String_to_array(regexp_replace(description,E'\\s+',' ','g'),' ')
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[EMAIL PROTECTED] wrote:
Hi,
Is it possible to select the current UTC time as type timestamp with time
zone?
select localtimestamp;
2008-09-14 21:55:24.367796
select localtimestamp at time zone 'UTC';
2008-09-15 09:55:42.3478+12
(not sure if what this result is)
select current_timesta
Karl Grossner wrote:
I've thrashed at this transform for quite a while and come up empty. The
crosstab() functions, and the documented examples, all do something more
complex than I need. I can do this after the fact trivially in python with
the 'zip()' function, but I need it real-time from a vi
t3 on t3.stop::date=series.date::date
where open is not null or closed is not null
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Jamie Tufnell wrote:
If someone uses a search query on my site like this:
"abc def"
I would like to return all results for 'abc & def' first, followed by
all results for tsquery 'abc | def' is there some way to express this
in one tsquery? What's the most efficient way to go about this? The
s
the question; but I
still don't know what you want. You're initial question indicated you
were trying to calculate the proper discount for each customer. This
example mentions neither customers nor discounts at all and appears to
be looking for a specific transaction instead.
Frank
novice wrote:
2008/11/20 Rodrigo E. De León Plicet <[EMAIL PROTECTED]>:
On Wed, Nov 19, 2008 at 10:03 PM, novice <[EMAIL PROTECTED]> wrote:
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
ivan marchesini wrote:
Hi to all...
I need to create a db that contain link to some pdf files..
At the moment these are simple links (to the files that are stored into
the file system) storing paths into a column of a dbf table...
I need to manage this data considering that the db I'm going to c
Tom Lane wrote:
"Matthew T. O'Connor" writes:
I want the following:
select column_to_english_list( select towns from towns_table );
to return:
'town1, town2 and town3'
I wonder though if it wouldn't be better to recast the problem as an
aggregate:
select column_to_english_list(towns) from
iewer_id", "viewed_id")
VALUES (94039, 60701) RETURNING id;
ERROR: cannot perform INSERT RETURNING on relation "userstats_profileview"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a
RETURNING clause.
-- snap --
Is there any way to make this work?
Regards,
sathiya psql wrote:
Is there any big difference between selecting the data type as
varchar(N) and text. What is the difference of it when seeing from the
performance side ?
Check the manual. There is no difference!
http://www.postgresql.org/docs/8.3/static/datatype-character.html
--
Sent
M, which is filtering Person by
properties on the related user table, then it will create a join for
each of those it seems.
Our Django experts are telling me that it is probably not practical to
fix in the ORM, as it seems to be structural (anyway not fixable for
us in the near term). Hence I am wondering if anyone has an idea as to
how to make the planner smarter about such weirdness (or brokenness);
you might argue that the 2nd join there is merely syntactic bloat
which the planner might just recognize as such?
We are using:
woome_video=# select version();
version
-
PostgreSQL 8.3.5 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)
Regards,
Frank
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
I think you meant:
select * from blah where stamp >= now() - '7days'::interval;
You can also try:
select * from blah where age( now(), stamp ) < '7days'::interval;
Frank
At 09:07 AM 7/11/00 +0200, you wrote:
>Hi Rob,
>
>Try this:
>
>select * from
(PersistentObject.java:356)
at PersistentObject.query(PersistentObject.java:286)
at TestFormatter.main(TestFormatter.java:88)
java.lang.NullPointerException
at TestFormatter.main(TestFormatter.java:91)
I am using postgresql 7.0.2.
How to get array data ? Please help me.
Thanks in advance.
Frank
parent_order_id) as foo
where order_id = parent_order_id
and order_price != sum_price
This should prove to be as efficient as it gets ;)
--
Best,
Frank.
---(end of broadcast)---
TIP 4: Have you searched our list archives
Hi
You need to make the call_handler and create the language plpgsql.
Assuming postgres is installed in /usr/local/pgsql
Example:
-- Setup -- define the languages and the associated handlers
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C
On Wed, 7 Jun 2000, Gustavo Henrique wrote:
Ad 1)
SQL = Structured Query Language :)
TSQL is an extention of SQL that includes support for queries against
temporal databases. VALID/TRANSACTION TIME and SEQUENCED/NONSEQUENCED
queries. If you would like more info on temporal databases try
ht
101 - 150 of 150 matches
Mail list logo
