Hello
2013/4/20 JORGE MALDONADO
> Let´s suppose that I have a SELECT statement that joins more than one
> table and such a statement is order by fields that belong not only to the
> table in the FROM but also by fields in the tables that are part of the
> JOIN´s. How does indexes should be cons
I'm sorry to prove that daft. :(
generate_series needs the startdate of every project to generate the
specific list of monthnumbers for every project.
To join against this the list needs to have a column with the project_id.
So I get something like this but still I cant reference the columns
Create a CTE with the project code and starting month of the project. Left join
to month series CTE.
Sent from my smartphone
- Reply message -
From: "Andreas"
To: "Alexander Gataric"
Cc: "Filip Rembiałkowski" , "jan zimmek"
,
Subject: [SQL] need some magic with generate_series()
Date
I would create a common table expression with the series from Filip and left
join to the table you need to report on.
Sent from my smartphone
- Reply message -
From: "Andreas"
To: "Filip Rembiałkowski"
Cc: "jan zimmek" ,
Subject: [SQL] need some magic with generate_series()
Date: Tue,
select id, regexp_replace(category, (E'\\|{2,}'), E'\|', 'g') as category from
akb_articles limit 100
Backslash in regex doubled. Added global modifier to replace all occurrences.
- Reply message -
From: "Johann Spies"
Date: Thu, Jul 28, 2011 8:20 am
Subject: [SQL] Help with regexp-quer
Would
regexp_replace(src_str, '[\{\}\[\]\(\)\.', '')
at
http://www.postgresql.org/docs/9.0/static/functions-string.html
help?
- Reply message -
From: "Andreas"
Date: Thu, Jun 30, 2011 4:28 pm
Subject: [SQL] How to remove a set of characters in text-columns ?
To:
Hi,
how can I re
WITH foo AS
(
SELECT column1::integer id, column2::timestamp ts
FROM (VALUES
(0, '1-Jan-2010 20:00'),
(1, '1-Jan-2010 20:03'),
(1, '1-Jan-2010 20:04'),
(0, '1-Jan-2010 20:05'),
(1, '1-Jan-2010 20:05'),
(0, '1-Jan-2010 20:08'),
(1, '1-Jan-2010 20:09'),
(0, '1-Jan-
SELECT a.*
FROM foo a
INNER JOIN foo b
ON b.id = a.id
AND b.ts >= a.ts + '5 minutes'
GROUP BY a.id, a.ts
ORDER BY a.id, a.ts
Not clear yet on the needs, but this may give a start.
- Reply message -
From: lists-pg...@useunix.net
Date: Sat, Jun 4, 2011 4:15 pm
Subject: [SQL] selecti
Have you looked at
http://en.m.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance
- Reply message -
From: "Tarlika Elisabeth Schmitz"
Date: Wed, May 25, 2011 6:13 pm
Subject: [SQL] extracting location info from string
To:
On Wed, 25 May 2011 09:25:48 -0600
Rob Sargent wrote:
SELECT
A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2
FROM a
INNER JOIN B
ON a.id = b.a_id
ORDER BY a.field2 ASC, b.field1 ASC ;
- Reply message -
From: "R. Smith"
Date: Fri, May 13, 2011 12:00 pm
Subject: [SQL] Sorting data based fields in two linked
While there is insufficient information provided (a couple of table snippets),
you may consider and experiment with the snippet below to get you started.
SELECT
ids.id,
f1.value AS value1,
f2.value AS value2,
f3.value AS value3
FROM
( SELECT DISTINCT id FROM foo ) AS ids
LEFT JOIN foo
Another approach to consider is using strings. E.g. The function assembles the
underlying query as a string and returns the results of executing the string.
Passing the groups in a string may be straightforward in your application, or
not.
There was a thread in the novice forum last year abou
On Saturday 11 April 2009 00:41:54 Jasen Betts wrote:
> On 2009-04-02, Alvaro Herrera wrote:
> > James Kitambara wrote:
> >> Dear Srikanth,
> >> You can solve your problem by doing this
> >>
> >> THE SQL IS AS FOLLOWS
> >> ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE
On 2009-04-02, Alvaro Herrera wrote:
> James Kitambara wrote:
>> Dear Srikanth,
>> You can solve your problem by doing this
>>
>> THE SQL IS AS FOLLOWS
>> ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE
>> TABLE NAME time_interval
>>
>> COUNT (*) FROM
>> (select
On 2009-03-17, Srikanth wrote:
> Dear all,
>
> I have a table that records User Login Sessions with two timestamp fields. =
> Basically Start of Session and End of a Session (start_ts and end_ts). Each=
> row in the table identifies a session which a customer has used.=A0=20
>
>
> I have to find
Oliveiros Cristina wrote:
> Escreve a tua dúvida em inglês, por favor, isto é uma lista de mailing em
> ingles.
Isn't there a portuguese mailing list? Should one be created?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt,
Escreve a tua dúvida em inglês, por favor, isto é uma lista de mailing em
ingles.
He wants to do a report with the composition of each foreign key and primary
key, not just their names.
He wants to know where postgres keeps that in catalog.
Best,
Oliveiros
- Original Message -
From
sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
Namens seb JACK
Verzonden: vrijdag 13 maart 2009 13:49
Aan: pgsql-sql@postgresql.org
Onderwerp: [SQL] RE : [SQL] Convert text from UTF8 to ASCII
Hi
Perhaps you should try to set your db client_encoding to UTF8.
How do you know yo
Hi
Perhaps you should try to set your db client_encoding to UTF8.
How do you know you have 'patiënt' instead of 'patiënt'?
I mean i also deals with UTF8 databases and all i store is correctly stored.
But i can't check it with command line psql as my shell is configured with
iso-8859-1 charse
Thx, I could really use a good laugh!
Bart van Houdt
Syfact International B.V.
Database developer
T +31 (0)35 688 8209
M+31 (0)6 4938 8131
IMPORTANT: The information contained in this message is privileged and
confidential, and protected from disclosure. If you are n
Hello Fernando,
Thank you for the quick response.
When setting the parameter, the output changed to:
psql:d:/tmp/pg/migrate.sql:4: NOTICE: Joeho!
write_line
(1 row)
Which looks a lot better already! And you have given me new hope and a new
angle to look at :)
On Fri, 2008-08-22 at 17:10 -0400, Emi Lu wrote:
>
> Would you please give me an example?
>
> I have two tables like the following:
> T1 (col1 varchar, col2 varchar, primary key (col1, col2))
> T2 (col1 varchar, col2 varchar, primary key (col1, col2))
>
>
> Query I have is:
> =
Hi Luigi,
you will have problems if table 1 and table 2 have the same names to col1 e
col2. For example, table1 has col1=parcel and col2=area and table 2 has
col1=country and col2=area then, in that case you will have ambiguity.
Would you please give me an example?
I have two tables like the
Hi Edward,
Just a guess, but it seems to me that since the join is using col1 and col2
there is no ambiguity. They should be the same no matter which table it
comes from.
Not always the same; "Left join" may return:
table2.col1,col2 = null,
while table1.col1,col2 is not null
-Origina
Just a guess, but it seems to me that since the join is using col1 and col2
there is no ambiguity. They should be the same no matter which table it
comes from.
Edward W. Rouse
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Emi Lu
Sent: Friday, August 22
Eduardo, creio que o problema decorre do jogo de caracteres usado...
No meu caso, uma vez que vivo em Portugal, uso o jogo LATIN9 quando crio uma
nova base de dados.
Simplificando:
CREATE DATABASE xpto
WITH OWNER = postgres
ENCODING = 'LATIN9'
TABLESPACE = pg_default;
Hélder M.
M
To: Joel Fradkin
Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Subject: Re: [SQL] RE: [SQL] trrouble inserting stuff like é
On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote:
> How do I tell the connection to use Unicode?
Try
SET client_encoding TO 'UNICODE';
http://www.postgr
On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote:
How do I tell the connection to use Unicode?
Try
SET client_encoding TO 'UNICODE';
http://www.postgresql.org/docs/8.0/interactive/sql-set.html
But it should default to the database encoding, so I'm not sure if that
is the problem.
John DeSoi, Ph.D
Looking deeper into the matter (which I admit I am a bit ignorant on) I
think you hit the nail on the head.
Coming from MSSQL which is using Latin I may have to use Latin1.
It works ok as SQL_ASCHII, but lower does not work, so hopefully using
LATIN1 I can store the chars and also get lower to work
I dont think it has anything to do with the data base as I can add fine
from pgadmin its an odbc question.
How do I tell the connection to use Unicode?
It worked ok using SQL_ASCHII also with the driver, but I thought if we get
a Chinese client down the road I couldnt store those chars unless i
There's no sense waiting as 7.3 is about to go RC1, and only the most
improbable circumstances would lead to a system catalog change at this
point. (If you do make a diagram, please post it to the group )
Robert Treat
On Wed, 2002-11-13 at 16:28, Jean-Luc Lachance wrote:
> Thanks, I know about t
Thanks, I know about that.
I was just hoping for a nice diagram.
I guess I will have to wait for 7.3 anyhow if I do not want to waste my
time doing one for 7.2
JLL
Paul Ogden wrote:
>
> It's not ERD but I've found the information in the Developer's Guide
> regarding system catalogs to be use
It's not ERD but I've found the information in the Developer's Guide
regarding system catalogs to be useful in the past.
This http://www.postgresql.org/idocs/index.php?catalogs.html will
get you started.
Thanks,
Paul Ogden
Claresco Corporation
> -Original Message-
> From: [EMAIL PROTECT
Hi Penny,
I hope someone proves me wrong, but I don't think you can modify a
constraint like thatI believe you have to drop it and recreate it.
Steve
penny wrote:
> Hi all,
>
> I am using Oracle8i Enterprise Edition in window 2000
>
> my problem as following
>
> ~
>
> S
Stephen Bell <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> Hi Penny,
>
> I hope someone proves me wrong, but I don't think you can modify a
> constraint like thatI believe you have to drop it and recreate it.
>
> Steve
>
> penny wrote:
>
> > Hi all,
> >
> > I am using
Here's one approach:
create view vw_maxrain as select max(rain) as rain, date_part('year',day) as
year from meteo group by year;
select day, meteo.rain from meteo, vw_maxrain where
meteo.rain=vw_maxrain.rain;
"Salvador Mainé" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL
On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
>
> -Brice
On many systems (linux at least) there is a command do
On Thu, 08 Feb 2001 05:38, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
This little heiroglyph will convert all text files rescued from that man's
syste
I believe the cr/lf was the problem. I reran the original query:
SELECT
tblFDBMono.SequenceNumber,
tblFDBMono.SectionCode,
tblFDBMono.LineText
FROM
tblPEMDrugLink,
tblFDBMono
WHERE
(tblPEMDrugLink.MonographId = tblFDBMono.MonographId) AND
(VersionId = 'FDB-PE') AND
(Cat
On Wed, 7 Feb 2001, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
perl -pi -e 's/\cM\\g'
will do the trick, assuming you have Perl instaleld on your sy
heya
there are a couple of good example on how to do this in the perl
cookbook
the trim function in chapter one might help
the source code from the book is avail at ora.com
On Wed, 7 Feb 2001, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
perl -pi~ -e 's/\r//g' file1 file2 ... fileN
--
Dominic J. Eidson
Brice Ruth <[EMAIL PROTECTED]> writes:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?
You could strip out CRs entirely with
tr -d '\015'
regards, tom lane
Is there a simple (unix) command I can run on text files to convert
cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
mention time consuming).
-Brice
Tom Lane wrote:
>
> Brice Ruth <[EMAIL PROTECTED]> writes:
> > Here's my latest tactic: I'm guessing that the data 'corrupt
Brice Ruth <[EMAIL PROTECTED]> writes:
> Here's my latest tactic: I'm guessing that the data 'corruption' has
> something to do with the way the data was exported from the original
> database by the third party ... probably something with the cr/lf
> linebreaks or something to that effect (the dat
Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last fiel
You could try this to see if it makes a difference
UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Wed, 7 Feb 2001, Brice Ruth wrote:
> I believe we're getting close
Brice Ruth <[EMAIL PROTECTED]> writes:
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
> I get 5 returned to me, even though the string is only 4 ('2008').
Okay, so you've got some invisible character in there, but not a space
(else the trim() woulda got rid of it). Tab
I believe we're getting closer to the problem here. When I run the
first query below, I get 0 results, which should be wrong. When I run
the query:
SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
I get 5 returned to me, even though the string is only 4 ('2008').
However
Run the following query:
SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
3;
is anything returned? If not, that is your problem (no rows exists with
matching monographid's). If information is returned,
Unfortunately ... that didn't seem to help :( I used btrim on all the
fields that were part of an equals (=) statement and reran the select
and got the same result (0 rows). After I was in the process of
updating the tables, I thought that this may fail ... since again, the
'manual join' of thes
Ross,
Thanx for the heads up on this. The select did indeed return something
other than four: 5. I updated as you suggested, but that alone didn't
fix the problem. I'm updating tblFDBMono now with the same type of
'fix' to see if this is the root of the problem. Is '=' handled
differently bet
In psql you can try \g or \o.
-Original Message-
From:[SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, January 23, 2001 10:03 PM
To: [EMAIL PROTECTED]
Subject:SQL Query Results
I want to show a certain person the commands I type and the query results,
and I want to save them i
(Aplolgies to Mr. McCoy, to whom I mailed this awnser in error)
Mr. Bajerski,
> > I've got answer from Postgres
> > "Illegal attributes or non-group column"
> >
> > Is it error in query or in parser ?
It's your query. In a GROUP BY query, all named columns must either
contain and aggregate fun
Tom Lane wrote:
>Hmm. If you do the same commands without wrapping them in an SQL
>function, they operate as expected. I'll bet there's some nasty
>interaction between the FOR UPDATE support and the way that SQL
>functions twiddle the current-command counter. Sigh, another bug.
>
>You might tr
55 matches
Mail list logo
