The following construct does what I need but is there a better way of
expressing this?
select * from
(
SELECT DISTINCT
section.section_pk
FROM ITEM
LEFT JOIN product product ON Item.product_fk = product.product_pk
LEFT JOIN section section ON product.section_fk = section.section_pk
WHERE i
Andrew Sullivan wrote:
Define "crawling". Also, please post EXPLAIN and, if feasible,
Total runtime: 191430.537 ms
EXPLAIN ANALYSE output for your case.
On Sun, Mar 18, 2007 at 07:51:28PM +, T E Schmitz wrote:
The following self join of a table containing 5800 records i
T E Schmitz wrote:
T E Schmitz wrote:
Things improved hugely when I changed the JOIN clauses:
see explain analyze below - can this be improved further?
LEFT OUTER JOIN history AS past_month ON (past_month.stock =
history.stockAND past_month.day >= (history.day - 30)
T E Schmitz wrote:
The following self join of a table containing 5800 records is crawling:
SELECT
history.stock, history.day, history.high, history.low,
MAX(past_week.high) AS week_high,
MAX(past_month.high) AS month_high
FROM history
INNER JOIN history AS past_month ON
The following self join of a table containing 5800 records is crawling:
CREATE TABLE history
(
stock VARCHAR(30) NOT NULL,
day date NOT NULL,
open NUMERIC (6,1) NOT NULL,
high NUMERIC (6,1) NOT NULL,
low NUMERIC (6,1) NOT NULL,
close NUMERIC (6,1) NOT NULL,
volume NUME
William Leite Araújo wrote:
On 15/03/07, *T E Schmitz* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
(...)
Try join the tables.
SELECT present.day, present.low, (MIN(future.day)-present.day) as
days2fall FROM history AS present JOIN history
I am not getting to grips with the following query:
set-up: Postgresql 8.1
The table HISTORY contains stockmarket data:
DAY HIGHLOW
2007/02/28 6286.1 6166.2
2007/02/27 6434.7 6270.5
2007/02/26 6446.8 6401.5
I'd like to produce the following resul
T E Schmitz wrote:
Rodrigo De León wrote:
On 3/13/07, T E Schmitz <[EMAIL PROTECTED]> wrote:
This is pretty ingenious!
Just a few minor problems:
- how does COPY know which column is which?
- how do I specify DELIMITER as TAB?
See:
http://www.postgresql.org/docs/8.2/static/sql-cop
Rodrigo De León wrote:
On 3/13/07, T E Schmitz <[EMAIL PROTECTED]> wrote:
This is pretty ingenious!
Just a few minor problems:
- how does COPY know which column is which?
- how do I specify DELIMITER as TAB?
See:
http://www.postgresql.org/docs/8.2/static/sql-copy.html
Thank you f
D'Arcy J.M. Cain wrote:
On Tue, 13 Mar 2007 20:38:33 +0000
T E Schmitz <[EMAIL PROTECTED]> wrote:
psql -h lolek -U tes -d stockmarket -c "copy history from STDIN CSV" <
/tmp/FTSE.csv
Also, it's nopt happy about the date format : 2007/02/09
--
Regard
Jure Kodzoman wrote:
On Tue, 2007-03-13 at 20:38 +, T E Schmitz wrote:
I am trying to batch-load a tab-separated CSV file:
psql -h lolek -U tes -d stockmarket -c "copy history from
'/tmp/FTSE.csv' CSV";
ERROR: could not open file "/tmp/FTSE.csv"
D'Arcy J.M. Cain wrote:
On Tue, 13 Mar 2007 20:38:33 +0000
T E Schmitz <[EMAIL PROTECTED]> wrote:
I am trying to batch-load a tab-separated CSV file:
psql -h lolek -U tes -d stockmarket -c "copy history from
'/tmp/FTSE.csv' CSV";
ERROR: could not open file
I am trying to batch-load a tab-separated CSV file:
psql -h lolek -U tes -d stockmarket -c "copy history from
'/tmp/FTSE.csv' CSV";
ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or
directory
The file exists. Do I need to escape the quotes?
set-up: Postgres 8.1, De
Andrew Sullivan wrote:
On Mon, Feb 26, 2007 at 11:05:08AM +, T E Schmitz wrote:
I can't thank you enough for the above advice. The test above identified
3 records and once they were removed I was able to dump the DB.
You'll be wanting to make sure your hardware is fixed
Tom Lane wrote:
T E Schmitz <[EMAIL PROTECTED]> writes:
pg_dump: ERROR: timestamp out of range
pg_dump: SQL command to dump the contents of table "server_hit_bin"
failed: PQendcopy() failed.
You should treat this as a corrupt-data exercise: you need to identify
and fix
Apologies for cross-posting (already sent to pgadmin-support) but I am
totally stuck with this:
I run an ecommerce system on a webserver, which I want to move to a
different machine.
However, I am stalled because pg_dump fail
Joe Conway wrote:
T E Schmitz wrote:
Alvaro Herrera wrote:
T E Schmitz wrote:
I have written a shell script to export data:
psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '
How can I specify a TAB character with the -F option?
Try:
psql -A -t -U $DBUSER -d $DB
Alvaro Herrera wrote:
T E Schmitz wrote:
I have written a shell script to export data:
psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '
Currently, I am using spaces as field separator but what I really want
is tabs.
How can I specify a TAB character with the -F option?
I have written a shell script to export data:
psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '
Currently, I am using spaces as field separator but what I really want
is tabs.
How can I specify a TAB character with the -F option?
--
Regards,
Tarlika Elisabeth Schmitz
--
Michael Fuhr wrote:
On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote:
max (timestamptz, timestamptz) does not exist already. You need to
create a simple function in PLpgSQL something like
if a > b
return a;
else
return b;
Since PostgreSQL 8.1 you can use GREATEST:
test=> SELECT greates
I tried the following query but the query fails as
"function max (timestamp w. timezone,timestamp w. timezone) does not exist"
SELECT id,
MAX(last_updated,
(SELECT MAX (last_updated) FROM product_category_member WHERE
product_category_member.id = product_category.id))
FROM product_category
Emils wrote:
2006/7/7, T E Schmitz <[EMAIL PROTECTED]>:
valid entries would be:
"28mm F2.8" (prime lens)
"30-70mm F4" (zoom lens)
"30-70mm F2.8" (zoom lens)
"30-100mm F4.5-5.6" (zoom lens with variable speed)
If these are the cases, wouldn
Aaron Bono wrote:
On 7/7/06, *T E Schmitz* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
I am trying to come up with a semi-automatic solution to tidy up some
data. If it's got to be done manually via the GUI it would mean a lot of
dummy work [for the cus
Rodrigo De Leon wrote:
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
Sorry, but that would also capture something like
10-30-59mm
The pattern describes either a single length (120 millimeters) or a
range (30 to 70 millimetres), hence:
\\d+(-\\d+)?mm
The ? quantifier refers
Erik Jones wrote:
T E Schmitz wrote:
Gary Stainburn wrote:
On Friday 07 July 2006 14:51, T E Schmitz wrote:
I would like to split the contents of a column using substring with a
regular expression:
The column contains something like
"150mm LD AD Asp XR Macro"
I want to split
Aaron Bono wrote:
On 7/7/06, *Rodrigo De Leon* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
> But that takes me to the next problem:
>
> For the sak
Rodrigo De Leon wrote:
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
But that takes me to the next problem:
For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:
28mm
28-70mm
So the reg. expr. requires brackets:
substring (NAME, '^(\\d
Gary Stainburn wrote:
On Friday 07 July 2006 14:51, T E Schmitz wrote:
I would like to split the contents of a column using substring with a
regular expression:
The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD
I would like to split the contents of a column using substring with a
regular expression:
SELECT
substring (NAME, '^\\d+mm') as BASE_NAME,
substring (NAME, ??? ) as SUFFIX
FROM MODEL
The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR
Rod Taylor wrote:
Sequential despite the indices? Or is this because the tables of my test
DB are virtually empty?
This is it. PostgreSQL changes strategies with data load. Performance
testing must be done on an approximation of the real data (both values
and size).
Thanks for your responses
Milorad Poluga wrote:
Try to execute this modification of your query :
SELECT ITEM.ITEM_PK FROM ITEM
LEFT JOIN SERIAL_NO
ON ( SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
AND SERIAL_NO.NO ='WX1234' )
GROUP BY ITEM.ITEM_PK
SELECT ITEM.ITEM_PK FROM ITEM
LEFT JOIN SERIAL_NO O
I have two tables:
TABLE ITEM
(
ITEM_PK serial,
RETAIL_PRICE numeric (7,2) NOT NULL,
...
PRIMARY KEY (ITEM_PK)
)
TABLE SERIAL_NO
(
SERIAL_NO_PK serial,
NO varchar (20) NOT NULL,
NAME varchar (20),
ITEM_FK integer NOT NULL,
PRIMARY KEY (SERIAL_NO_PK)
);
common query:
SELECT ITEM.ITEM_PK FROM IT
Hello,
I *detest* British summertime. This year it took me two days to adjust.
Now I am realizing that my program might need some adjusting too:
Joking aside, I need some advice regarding TIMESTAMP colums and I can't
quite get my head round this at the moment:
I created a table TRANSAKTION with a
Bruno Wolff III wrote:
On Tue, Mar 29, 2005 at 14:21:15 +0100,
T E Schmitz <[EMAIL PROTECTED]> wrote:
If that's the case, I should come up with a different concept to obtain
a list of ORIGINs.
That may be a good idea, especially if there are lots of rows for each
origin value.
Th
Alvaro Herrera wrote:
On Tue, Mar 29, 2005 at 02:21:15PM +0100, T E Schmitz wrote:
Bruno Wolff III wrote:
On Tue, Mar 29, 2005 at 11:07:20 +0100,
T E Schmitz <[EMAIL PROTECTED]> wrote:
Would the "SELECT DISTINCT origin" always cause a sequential table
scan regardless whether ther
Bruno Wolff III wrote:
On Tue, Mar 29, 2005 at 11:07:20 +0100,
T E Schmitz <[EMAIL PROTECTED]> wrote:
Would the "SELECT DISTINCT origin" always cause a sequential table scan
regardless whether there is an index on the origin column or not?
It's worse than that, SELECT
Hello Scott,
Scott Marlowe wrote:
On Mon, 2005-03-28 at 15:43, T E Schmitz wrote:
How expensive would it be to maintain the following VIEW:
CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion
if there is in index on transaktion.origin; the table transaktion has
thousands of records and
Hello,
How expensive would it be to maintain the following VIEW:
CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion
if there is in index on transaktion.origin; the table transaktion has
thousands of records and there are only a few distinct origin?
--
Regards/Gruß,
Tarlika Elisabeth Sc
Hello Ragnar,
Ragnar Hafstað wrote:
On Mon, 2005-02-28 at 20:48 +, T E Schmitz wrote:
INSERT INTO item (name,retail_price) VALUES ('Cheese Soufflé',7.95,);
psql comes back with "invalid byte sequence for encoding "UNICODE": 0xe9"
might be the client encoding
Hello,
I am trying to insert the following record:
INSERT INTO item (name,retail_price) VALUES ('Cheese Soufflé',7.95,);
(I presume you see the accented character in *Soufflé*)
psql comes back with "invalid byte sequence for encoding "UNICODE": 0xe9"
If I do this via DbVisualizer, the record is in
Kai Hessing wrote:
Another question: Which software are you using to visualize your
database-structur. We're doing it with Quark, but are not very happy
with this.
DbVisualizer (free version)
http://www.minq.se/products/dbvis/index.html
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---
Hello Keith,
Thank you for your help.
Keith Worthington wrote:
T E Schmitz wrote:
Tables:
TRANSAKTION
---
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)
ITEM
TRANSAKTION_FK
ITEM_PK
RETAIL_PRICE
DISCOUNT
Desired result
Hello,
I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure whether
what I want to achieve is possible at all:
The problem in real-world terms: The DB stores TRANSAKTIONS - which are
either sales or refunds: each
Hello,
I need to be able to perform wildcard searches on a VARCHAR(100) column
of the form
SELECT * FROM item WHERE serial_no LIKE '%12345678%'
Would an index on serial_no do anything at all for this search?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---(end of broadcast)-
Hello Tom,
Tom Lane wrote:
T E Schmitz <[EMAIL PROTECTED]> writes:
This is *almost* what I need:
SELECT
BRAND.BRAND_NAME,
MODEL.MODEL_NAME,
min (ITEM.PRICE),max (ITEM.PRICE)
*min (CONDITION.POSITION),max (CONDITION.POSITION)*
FROM ITEM
left outer join MODEL on MODEL_PK =ITEM.MODEL_FK
left
Hello,
I'm sorry I couldn't think up a more precise subject line.
I would like to know whether the following can be implemented in SQL:
The example below joins 4 tables ITEM, BRAND, MODEL and CONDITION. In
human understandable terms: a [secondhand] Item is of a particular Model
and Brand. The Ite
Hello Mike/Tom/Richard,
Thank you for your replies.
Michael Fuhr wrote:
On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote:
Question: is it necessary/advisable to create an index for the ITEM_FK
column? Or is this redundantbecause this column is already one of the PK
columns?
However
Hello,
I have created the following join table: the two FKs are the PK of the
table. Typically, I will need to select rows for a given ITEM_FK.
Question: is it necessary/advisable to create an index for the ITEM_FK
column? Or is this redundantbecause this column is already one of the PK
columns
Hello again,
Martin Marques wrote:
El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
Is it possible to set up a table CHECK, which ensures that column A is
NOT NULL if column B = 'x' ?
CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)
I noticed a table constraint ca
Hola Martin!
Martin Marques wrote:
El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
Is it possible to set up a table CHECK, which ensures that column A is
NOT NULL if column B = 'x' ?
CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)
This is brilliant. Only detected th
Hello,
Is it possible to set up a table CHECK, which ensures that column A is
NOT NULL if column B = 'x' ?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(s
Hello Greg,
You have given me plenty of food for thought. Thank you for taking the
time.
Currently, the tables have such few records (350, 900, 1000) that
performance does not come into it, particularly seeing as this was only
needed for a one-shot report.
However, I have stached your examples a
Hi Rod,
Rod Taylor wrote:
On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
I figured it eventually. (The only thing I don't know is where to put
the ORDER BY.)
Try this:
SELECT brand_name, model_name
FROM (SELECT ... INTERSECT SELECT ...) AS t
ORDER BY ...
I figured it eventually. (The only thing I don't know is where to put
the ORDER BY.)
I want to select only those BRAND/MODEL combinations, where the MODEL
has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
I am not interested in MODELs with multiple TYPEs where none of them a
Hello,
I apologize in advance for this garbled message but I've been banging my
head against a brick-wall for a while and I just can't figure how to do
the following:
I have 3 tables BRAND,MODEL,TYPE which are related to each other:
BRAND
=
BRAND_PK
BRAND_NAME
MODEL
=
MODEL_PK
MODEL_NAME
Hello,
Greg Stark wrote:
Tom Lane <[EMAIL PROTECTED]> writes:
The paragraph continues:
"If the SELECT command included the clause WHERE phone NOT NULL,
PostgreSQL could use the index to satisfy the ORDER BY clause.
An index that covers optional (NOT NULL) columns will not be used to
speed table
Hello Tom,
Tom Lane wrote:
T E Schmitz <[EMAIL PROTECTED]> writes:
Greg Stark wrote:
Which book is it?
PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003
Hmm, I've heard of that book but never seen it. The authors are not
participants in the PG community
Hello Greg,
Greg Stark wrote:
T E Schmitz <[EMAIL PROTECTED]> writes:
Quote: "PostgreSQL will not index NULL values. Because an index will never
include NULL values, it cannot be used to satisfy the ORDER BY clause of a
query that returns all rows in a table."
You should just cros
Hello Tom,
Tom Lane wrote:
T E Schmitz <[EMAIL PROTECTED]> writes:
I read somewhere that an Index is not
going to improve the performance of an ORDER BY if the sort column
contains NULLs because NULLs aren't indexed?
Whatever you were reading had it pretty badly garbled :-(
I just
h
something that sorts BEFORE the first most value that your TO result
can return.
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
Fax: (416) 441-9085
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
Hello,
I am using PostgreSQL 7.4.2 and as I understand NULL values always sort
last.
However, I have a table from which select using two numerical sort keys
"FROM" and "TO". "TO" might be NULL and I would like to display those
rows first (without sorting the column in descending order).
Is the
on b1.BRAND_PK =BRAND_1_FK
left outer join BRAND as b2 on b2.BRAND_PK =BRAND_2_FK
Kind Regards,
Tarlika Elisabeth Schmitz
Adam Witney wrote:
On 2/5/04 5:23 pm, "T E Schmitz" <[EMAIL PROTECTED]> wrote:
Hello,
I have two tables SECTION and BRAND. SECTION is related to BRAND via two
forei
Hello,
I have two tables SECTION and BRAND. SECTION is related to BRAND via two
foreign keys. I would like to select ALL SECTIONs whether the FKs are
null or not and fetch the BRAND attributes in one SQL statement. In
other words I need a double outer join.
Is this possible at all?
The followin
63 matches
Mail list logo
