ybe help. Right now
we can't try this because all the FK checks where created with default
settings. We'd like to avoid taking the database down for recreating
foreign keys.
Regards,
Frank
---(end of broadcast)---
TIP 5: Have you
On Wed, Mar 30, 2005 at 11:48:31AM +0200, PFC wrote:
>
> Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ...
ALTER CONSTRAINT? I did check for that, and it does not appear to
exist?! That's why I asked ...
Rgds, Frank
---(end o
hi_shift - lo_shift) as ovrhrs from timesheet
where earncode not in ('R', 'C', 'X') order by emp
will give me all the overtime hours.
How do I combine these to get one result set with emp, reghrs, ovrhrs on
each row.
Frank
___
LSE
(hi_shift-lo_shift) END) AS ovrhrs
FROM timesheet
GROUP BY emp
ORDER BY emp;
At 09:34 AM 9/11/00 -0400, Frank Bax wrote:
>I've got a table containing some timesheet data.
>Fields are emp, earncode, lo_shift, hi_shift.
>
>SELECT emp, sum(hi_shift - lo_shift) as reghrs fr
n't this give the same result (without order by):
> select , (select min(ID) from table where id > this.id) as
next_id
Frank
Select count(*) from Table
At 04:58 AM 10/20/00 +, Craig May wrote:
>Hi,
>
>How do I get a row count, like "Select [COUNT] from Table" ??
>
>Regards,
>Craig May
>
>Enth Dimension
>http://www.enthdimension.com.au
>
>
to the problem.
Comments?
- Frank
. That looks like my solution. I had actually seen it someplace
before, but didn't make the connection with my problem.
Ta, Frank
On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote:
> Frank,
>
> Please look in the list archives. About 2 months ago this topic came
> up and was discussed extensively (including a creative solution by yours
> truly).
Hm, neither my archives nor a search on the
e than one tuple returned by a subselect used as an expression.
mpi=#
This is a recursive query, on a single table. Is that not possible?
I am using 7.1.
- Frank
Christof Glaser wrote:
[ . . . ]
> since the where clause compares just one field for equality. You might
> try IN instead of =, ie
>
> update index set level = 2 where parentid in ( select id from
> index where level = 1 );
That was it! Thanks, Frank
E (CategoryID is not null) LOOP
Run SQL statement to get the next higher-up node's CategoryID and
increment a counter.
END LOOP;
Return counter and insert value into level column.
This seems to feasible but not really as straightforward as one might
hope. Is there an easier way?
- Frank
?!! :)) the gap
or
update an entire half of the tree to close it . . . then my brain started to hurt and I
gave up.
> I can send it to you if you like (please allow a few days since I
> have several exams at the university this week).
Sure, I'd like to have a look at it!
Thanks, Frank
> Michael Ansley wrote:
>
> Hi, Frank,
>
> This is exactly why there are alternative solutions for trees. The mechanism that
>you
> are using traded input speed for 'queryability', and this is where you start to run
>into
> problems. Either you need to s
gt; as one might
> > hope. Is there an easier way?
>
> Hmmm. I don't know, Frank. That strikes me as a really
> good, straightforward workaround to your problem. I'm not
> sure what you could do that would be simpler. This is
> practically a textbook exampl
be wrong. Can
anyone enlighten me?
Ta, Frank
e
content
These two queries get me what I want from each table:
select unique id from table1 where lastname='morton';
select unique id from table2 where ((type = "pie") and (content = 'apple));
What is the best way to combine these into one query?
Thanks.
Frank
my problem?
Cheers, Frank
or interpret as what they essentially are, i.e. foreign keys
(depending on your philosophical outlook, that is, whether you consider
the essence of your foreign keys to be a set of triggers, or vice versa
;-)).
Regards, Frank
. This will log every query
in detail. It's very convenient while you're developing and testing,
especially if you run a separate window with
tail -f /wherever/you/write/your/postgres.log
Regards, Frank
alues
from the result set. Is this behaviour correct and if so, why?
I am using 7.1 beta 4.
Regards, Frank
d
automagically with the table), as I get errors on trying to update those
tables.
Trouble is that the foreign keys show up in a schema dump as
triggers (AFAIK there is no other way to display foreign key
constraints) which I don't know how to drop. Here's an example:
\connect - frank
-
On Thu, Mar 08, 2001 at 04:42:46PM +0100, Frank Joerdens wrote:
> I've created quite a few foreign key constraints in the database that I
> am currently working on, and now that I've altered the structure and
> dropped a table that had a foreign key reference to a couple of oth
On Thu, Mar 08, 2001 at 05:06:29PM +0100, Frank Joerdens wrote:
> On Thu, Mar 08, 2001 at 04:42:46PM +0100, Frank Joerdens wrote:
> > I've created quite a few foreign key constraints in the database that I
> > am currently working on, and now that I've altered the st
e default is 32. There is also a compiled-in hard upper limit
on this value, which is typically 1024 (both numbers can be altered when
compiling the server). This parameter can only be set at server start.
Regards, Frank
---(end of broadcast)---
r to just use entire email address
in second key rather than extract before the '@' character.
Frank
At 08:37 PM 5/10/01 +0200, you wrote:
>Hi,
>
>I just want to order by a listing of email address by domain like :
>
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>[EMAIL PRO
read the doc section datatype!!!
serial just create a sequence on a standard way, if you dont like it you'll
have to create your sequence.
bye
--
--
"L'idea di base è estremamente semplice..."
©opyright Frank "Pazzooo" Contrepois (schiavista francese del '900)
look at some doc for it. this is the way
Relational DB works
bye
--
--
"L'idea di base è estremamente semplice..."
©opyright Frank "Pazzooo" Contrepois (schiavista francese del '900)
---(end of broadcast)---
TIP 3: if po
read this:
http://www.postgresql.org/idocs/index.php?sql-createtrigger.html
bye
--
--
"L'idea di base è estremamente semplice..."
©opyright Frank "Pazzooo" Contrepois (schiavista francese del '900)
---(end of broadcast)
If you use index on this table it's better to:
1) drop all index on the table
2) do the insert
3) (re)create the indexes
--
--
"L'idea di base è estremamente semplice..."
©opyright Frank "Pazzooo" Contrepois (schiavista francese del '900)
-
Perhaps 'limits' should be part of FAQ, not separate entity?
Also a reference (or link) to 'limits' from other sections such as
mentioned below may be more appropriate than duplicating the information.
Frank
At 08:56 AM 10/16/01 -0400, you wrote:
>> >>&
I want to store a long article in the Postgresql in Linux, how can I put the
content into it and redraw it back to show? urgent. Thanks.
I use JSP.
I note that all database systems are very dull in BLOB. Why?
Frank Zhu.
---(end of broadcast
it.
thanks
Frank Zhu.
"Bruce Momjian" <[EMAIL PROTECTED]> дÈëÏûÏ¢ÐÂÎÅ
:[EMAIL PROTECTED]
> > On Wed, 10 Oct 2001, Aasmund Midttun Godal wrote:
> >
> > > I am sure this question has been answered in some form or another
> > &g
For the SQL gurus, a query where I'm not getting
the expected
results. Trying to write it using sql compatible with both postgres
and mysql.
There are two tables:
table = profile
int id
char name
table = attribute
int id
int containerId
char name
char value
Multiple attribute rows corre
"VisitorExtra"."ID"= 325903;
to
WHERE "VisitorExtra"."ID"="Visitor"."ID" AND
"Visitor"."ID"= 325903;
have any effect?
Why do you use "From VisitorExtra" in first select when no r
At 10:36 PM 12/11/02, ksql wrote:
This is vital for my, please send me an example
about like I can make me to support my database with name WAREHOUSE
pg_dump -o -c warehouse | gzip > warehouse.gz
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/backup.html
lse 0 end as flow4,
case when point = 5 then flow else 0 end as flow5,
case when point = 6 then flow else 0 end as flow6
from samples
There have been several messages recently about this - search on crosstab
or pivot - a couple of other options were presented.
Frank
---(e
At 04:33 PM 2/14/03, [EMAIL PROTECTED] wrote:
When I run select to_date('20030212','MMDD') the output is 2/12/03
if I run select to_date( to_char(20030212,),'MMDD'); the
output is 6/23/05
How can I convert from integer into date format correctly?
Same as the first one, except l
pe | Modifier
+--+--
columnname | text |
fbax=# \q
pg_dump -o -c -s -t tablename fbax | sed "s:\"columnname\"
text:\"columnname\" varchar(2):" | psql
Frank
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
At 10:59 AM 6/6/03, Damien Dougan wrote:
I was wondering if it is possible to create a table view based on a table
which is effectively an "attribute list".
For example, suppose I have two tables:
CREATE TABLE user
(
userid integer,
username character varying,
userpassword character varying,
The two functions do not group data the same way, so a common index is not
possible.
urlhost would put pgsql.org and pgsql.com close together.
urltld would but pgsql.com and xyz.com close together.
Frank
At 01:36 PM 7/15/03, David Olbersen wrote:
Hello all,
I have a function (urlhost) which
Two tables - employee and timesheet simple enough. Each table is more
complex than example before. timesheet contains multiple rows per day
because the "other" fields are different for various rows on same
day/employee. My selection criteria actually uses some of these other
fields. lo_shift
I have a table called a "profile" that has company addresses as well
as individual contact information. Simpifying:
id (the primary key)
parentId (any profile can have a parent ie, contact parent is a company)
address (for this example, will just have one piece of the address)
useParentAddress
If "
ould use DBD::CSV for reading
and DBD::Pg for
writing. Or you read the file with Perl´s standard functions, treat the data to be
PostgreSQL-insert-query-compatible (regarding apostrophes for example) and then use
DBD::Pg (that´s
what I usually do). If you have some kind of DBase files, you can use DBD::X
hou < 10) {$hou="0".$hou;}
if ($mday < 10) {$mday="0".$mday;}
$mon++;
if ($mon < 10) {$mon="0".$mon;}
$yea=$yea+1900;
my $t=$yea."-".$mon."-".$mday." ".$hou.":".$min.":".$sec;
return $t;
rrences of what came before (digits in
this case)
$ matches end of string
The ^ and $ are important - if they were left out, the pattern would match
a string containing both numeric and non-numeric data.
You can change the + to * if you decide that an empty string should be
consid
rn (last-day-of-month - dow1 - 7 + dow2)
else
return (last-day-of-month - dow1 + dow2)
I'm no good at coding pgsql functions, so I'm not going to attempt proper
syntax.
Frank
---(end of broadcast)---
TIP 5: Have you checked
.
This third step I am struggling with - can anyone suggest a query that
might achieve the writing back of the primary key to the original table?
Do all three steps in one command:
create table newtable as (select key1, key2, money from oldtable);
Frank
---(end of broadcast
Check your version with:
select version();
In the meantime, you could try
date_part('epoch', submittime)
which will return a unix timestamp, you could then use functions within
your scripting language to convert to human readable date formats.
Frank
At 11:53 PM 7/20/04,
gt; false", you will only get the fields containing true, not the NULLs.
Regards, Frank.
On Wed, 11 Aug 2004 13:22:00 +1200 "Traci Sumpter" <[EMAIL PROTECTED]>
sat down, thought long and then wrote:
> A team developer has chosen the lazy way of not checking if a variable
&g
t I only found the '-l'-way to this this.
The -e switch of psql will display queries behind commands. So use this
option, the use 'l' as a command instead of a switch.
Frank
---(end of broadcast)---
TIP 8: explain analyze is your friend
At 12:11 AM 12/11/04, Josh Berkus wrote:
Wei,
> insert into table temp (tempname, tempdate)
> select distinct 'tempname', null from some_other_relevant_table;
I don't think you're reporting the error exactly as it happened. Try cutting
and pasting your actual PSQL session into your e-mail.
Perhaps
At 10:28 AM 4/14/05, Dinesh Pandey wrote:
How can
I
Write a prepared query and
Set values at run time and
Execute that query for different values.
Depends on what language you are using - try interfaces mailing
list.
frank habermann
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
hello again.
ok. i can connect over tcp!!! this works! my problem is that i only can connect
with trust! if i use password i cant connect. with trustmode i need the right
password for the user!
frank
-- Original-Nachricht --
Von: "Frank Habermann" <[EMAIL PROTECTED]>
At 01:55 PM 4/23/05, Tadej Kanizar wrote:
Ok, so I installed the latest version of Postresql (8.02) on a WinXP SP2
machine..
My question is why won't the statement SELECT * FROM table work, whereas
the statement SELECT * FROM »table« works!
And, to explain a bit more, here's another example:
IN
is a nice trick, but not useful in this case because all rows for
one client are being retrieved anyway for the other three data items.
Frank
---(end of broadcast)---
TIP 8: explain analyze is your friend
r storing large blobs and
such?)
/Frank H
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
On 7/14/05, Richard Huxton wrote:
> Frank Hagstrom wrote:
> > Hello
> >
> > I've been thinking on a potential problem I might get in a distant
> > future, but once I started thinking on it I just as well had to
> > check...
> >
> > Is it possibl
where (in table criteria)
I have a feeling it is possible but I need the right syntax
//Frank
This message was sent using IMP, the Internet Messaging Program.
---(end of broadcast
/ utf_decode.
We had a similiar problem, since we encode both, webpages and database, as
utf8, there is no longer such a problem. We use php 4.4.x, postgresql 8.0.3 and
pear.
Regards, Frank.
On Sat, 06 Aug 2005 22:58:40 +0200 Nicolas Cornu <[EMAIL PROTECTED]> thought
long, then sat down and
At 05:30 AM 8/17/05, Kretschmer Andreas wrote:
[EMAIL PROTECTED] <[EMAIL PROTECTED]> schrieb:
> Hi,
>
>I have a varchar column, and I need to
>
>1) check the value in it is an integer
>2) get the integer value (as integer)
test=# update foo set n = substring(t , '[0-9]')::int;
I
At 03:43 PM 10/6/05, Hector Rosas wrote:
Hello, I'm trying to select records in a table not present in a related
table, in example, I've a table with message information (subject,
message, date, etc) and another (usermessages) with where user(s) has that
message, its state, etc. Records in thi
I have a table with only 434 rows in it. Two important columns are
"itemid" and "locn". Each item must be in one of seven locations. We need
to create a "combo" by selecting one item from each of seven locations;
then determine which "combo" is the "best" according to our analysis (see
below
At 08:29 AM 10/11/05, Richard Huxton wrote:
Frank Bax wrote:
I have a table with only 434 rows in it. Two important columns are
"itemid" and "locn". Each item must be in one of seven locations. We
need to create a "combo" by selecting one item from each of se
At 09:00 AM 10/12/05, Daryl Richter wrote:
Richard Huxton wrote:
Frank Bax wrote:
Are you saying that you WANT to generate a cross-join, score the
millions of results and then pick the best 10? It's doing what you
want, but you'd like it to be faster.
Or are you saying that you
At 09:04 AM 10/13/05, Daryl Richter wrote:
Frank Bax wrote:
[snip]
Richard, you've summed it up nicely.
Splitting locations into subsets (like 2,2,3) doesn't work because it is
possible that low values in one location can be offset by high values in
another location, and still re
At 02:00 PM 10/27/05, Abhishek wrote:
I have a table "TABLE1" which has
Callguid | digits | type
123 'a'
345
How do you extract postgresql column names, types and comments in tabular form,
using an SQL command?. I know they are stored in one of the system tables, but
I don't know which.
R Church
This message was sent using IMP, the Inter
If my database has column containing a filename, can I use sql to present
this filename and datemodified (as output from 'ls -l' or from mtime()
fuction) or *must* it be done after the query in interface such as php or perl?
---(end of broadcast)
At 03:03 PM 11/11/05, Greg Sabino Mullane wrote:
At 08:57 AM 11/11/05, Frank Bax wrote:
> If my database has column containing a filename, can I use sql to present
> this filename and datemodified (as output from 'ls -l' or from mtime()
> fuction) or *must* it be done
Hi guys,
What is the command change database owner in PostgreSQL 7.4?
This message was sent using IMP, the Internet Messaging Program.
---(end of broadcast)---
TIP 9: In versions b
At 11:20 AM 11/28/05, Mauricio Fernandez A. wrote:
Can any of you help me with this query?, I need to retrieve the
sum(units) and sum(cost) for each client in every month in the same
row, something like this:
client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...|UnDec|CostDec
- -
Quoting Andreas Kretschmer <[EMAIL PROTECTED]>:
> frank church <[EMAIL PROTECTED]> schrieb:
>
> > Hi guys,
> >
> > What is the command change database owner in PostgreSQL 7.4?
>
> ALTER DATABASE foo OWNER TO blob;
I tried that and it didn't work. T
At 07:53 PM 12/10/05, Havasvölgyi Ottó wrote:
I would like to select the count of distinct rows in a table.
SELECT COUNT(DISTINCT *) FROM mytable;
This does not work. How can I do it with Postgres?
select count(*) from (select distinct * from mytable) as x;
--
Does VACUUMing reorder tables on clustered indices or is it only the CLUSTER
command that can do that?
/ r church
This message was sent using IMP, the Internet Messaging Program.
---(end of broadcast)
At 06:58 PM 12/28/05, Collin Peters wrote:
The following query will return me all the latest dates, but I can't
return the note_id or subject with it.
SELECT n.user_id, max(n.modified_date)
FROM notes n
GROUP by n.user_id
ORDER BY n.user_id
Is this simpler than I am making it?
No, it's not "s
feature that allows which allows the same performance as
transactions, without causing the whole process to fail, like a delayed updates
or write mechanism of some sort.
It is something I would like to set in that particular data looad.
Frank
Today's your lucky day (I think), because I was looking for (and used) the
aggregate function mentioned below just before reading your question.
At 11:03 AM 1/7/06, Tomas Vondra wrote:
1) How to create a "table" in the form
documtent_id | word_1 | word_2 | ... | word_n
-
I repeatedly get this error whenever I try to backup a database
The command used is:
pg_dump -Fc -O -U username tablename > tablename.20060122
pg_dump: ERROR: invalid memory alloc request size 4294967290
pg_dump: SQL command to dump the contents of table "cc_ratecard" failed:
PQendcopy() faile
At 04:10 PM 2/8/06, Ken Hill wrote:
I need some help with a bit of SQL. I have two tables. I want to find
records in one table that don't match records in another table based on a
common column in the two tables. Both tables have a column named 'key100'.
I was trying something like:
SELECT co
At 05:16 AM 4/20/06, Markus Schaber wrote:
Hi, Louise,
Louise Catherine wrote:
> Could anyone explain, why the field length must be add by 4 :
> result 1 : 10 + 4 =14
> result 2 : 5 + 4 = 9
I guess that it is because all variable length datatypes (and text types
are such) internally contain a
At 08:53 AM 6/11/06, Leif B. Kristensen wrote:
I've got two tables:
CREATE TABLE events (
event_idINTEGER PRIMARY KEY,
tag_fk INTEGER NOT NULL REFERENCES tags (tag_id),
place_fkINTEGER NOT NULL REFERENCES places (place_id),
event_date CHAR(18) NOT NULL DEFAULT '000
At 10:55 AM 6/21/06, Worky Workerson wrote:
I'm having a bit of a brain freeze and can't seem to come up with
decent SQL for the following problem:
I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
VARCHAR" and would like to create a query that outputs something of
the form "fi
At 11:06 AM 6/21/06, [EMAIL PROTECTED] wrote:
returns:
id | nick
--+--
22192 | A
22192 | T
(2 rows)
I'd like to get the result in only one row:
id | nick
--+--
22192 | A,T
This question is in the archives (probably more than once). The answer is...
Read the online docs
At 02:24 PM 6/21/06, Richard Broersma Jr wrote:
> >I'd like to get the result in only one row:
> >id | nick
> >--+--
> >22192 | A,T
> This question is in the archives (probably more than once). The answer
is...>
> Read the online docs about aggregate functions. There is an exampl
At 10:19 AM 7/11/06, [EMAIL PROTECTED] wrote:
control:
controller_id pk;
datapack:
controller_id fk;
I need to get all entries from the table control that are not listed in
datapack.
select controller.controller_id from controller
left join datapack on contr
Richard Huxton wrote:
Christophe wrote:
Now, since this database has been production since 7.2 days, cruft has
crept in: in particular, there are duplicate email addresses, some
with mismatched attributes. The policy decision by the client is that
the correct row is the one with the earliest
chester c young wrote:
Le 20/07/09 15:19, chester c young a écrit :
within a trigger need to know if the UPDATE statement
set a column. the column might be set to the old value
or a different value.
(want to make sure the app is sending all necessary
values)
thanks
If the column to test i
Raj Mathur wrote:
On Saturday 05 Sep 2009, bilal ghayyad wrote:
I have an sql script function that take one text parameter "funct
(text)", what I need to do is the following:
If the parameter name is string and its value was for example "abcd"
then I need to do a query based on ab and then base
Gavin McCullagh wrote:
SELECT time, to_timestamp(time) AS ts, EXTRACT('months',to_timestamp(time))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
Try replacing extract('month',value) with extract('months' from value
Gavin McCullagh wrote:
On Thu, 17 Sep 2009, Frank Bax wrote:
Gavin McCullagh wrote:
SELECT time, to_timestamp(time) AS ts,
EXTRACT('months',to_timestamp(time)) FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('
I'm not quite sure how to ask for the query I want, so let's start with
data:
create table t1 (i int, val varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,'G H I J');
create table t2 (q varchar, z varchar);
insert into t2 values('A','vowel
Osvaldo Kussama wrote:
2010/6/25 Frank Bax :
I'm not quite sure how to ask for the query I want, so let's start with
data:
create table t1 (i int, val varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,
Osvaldo Kussama wrote:
2010/6/25 Frank Bax :
I'm not quite sure how to ask for the query I want, so let's start with
data:
create table t1 (i int, val varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,
Create some tables; then add some data:
create table t1 (i int, v varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,'G H I J');
insert into t1 values(4,'E');
create table t2 (q varchar, z varchar);
insert into t2 values('A','vowel');
insert
Osvaldo Kussama wrote:
2010/7/1 Frank Bax :
Create some tables; then add some data:
create table t1 (i int, v varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,'G H I J');
insert into t1 values(4,'
John Hasler wrote:
How does PostgreSQL react to time being stepped at bootup? My Chrony
NTP package might cause it to do so on rare occasions when the hardware
clock is way off. This would only happen during bootup.
My ntp client changes clock (by small amount) at any time:
Jul 25 05:29:38
John Hasler wrote:
Frank writes:
My ntp client changes clock (by small amount) at any time:
Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by
Steve wrote:
Original-Nachricht
Datum: Sat, 11 Sep 2010 11:08:00 -0400
Von: Lew
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Question regarding indices
On 09/11/2010 08:29 AM, Steve wrote:
I have a small question about the order of values in a query.
Assume I have a tab
1 - 100 of 150 matches
Mail list logo
