t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > I tried moving the last group of WHERE, GROUP BY, and ORDER BY before
t> > the UNION with the query it belongs to, but that results in a
t> > different syntax error.
t> I think that's probably what
M == matthew.hart...@krcc.on.ca writes:
M> > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
M> > > ow...@postgresql.org] On Behalf Of Brandon Metcalf
M> > > Sent: Friday, July 10, 2009 12:16 PM
M> >
M> > Change it to this:
M>
Is the following even possible? I keep getting a syntax error at the
last WHERE:
ERROR: syntax error at or near "WHERE"
LINE 20: WHERE p.part_id=379 AND t.machine_type_id=1
The SQL is
SELECT t.name AS machine_type_name,
j.workorder,
round(sum(EXTRACT
w == wmo...@potentialtech.com writes:
...
w> Your primary key can span multiple columns, i.e.
w> PRIMARY KEY(jobclock_id, employee_id, machine_id)
w> Could be more columns.
w> Keep in mind that this ensures that the combination of all those
w> columns is unique, which may or may not be wha
M == matthew.hart...@krcc.on.ca writes:
M> Just create a unique constraint on all of the columns.
Ah. Didn't realize you could specify more than one column as part of
a unique constraint.
Thanks.
--
Brandon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chan
I have the following table:
gms=> \d jobclock
Table "public.jobclock"
Column| Type |
Modifiers
-++---
I asked a question similar to this a couple of weeks ago, but the
requirement has changed a bit and I want to be sure I'm designing my
tables correctly.
I have the following table:
CREATE TABLE workorder (
numberVARCHAR(8),
quantity INTEGER,
generic BOOLEAN,
PRIMARY KEY
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > Is there a "\" command to show all tables in the current search path?
t> Even ones that are masked by earlier search_path entries? No.
Correct. Just wondering if there was something undocumented :)
t
r == r...@iol.ie writes:
r> On 11/06/2009 21:39, Brandon Metcalf wrote:
r> > Is there a "\" command to show all tables in the current search path?
r> \dt
r> \? is your friend
Nope. You didn't read the entire thread. If you do, you'll see why
\dt i
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > Something interesting I've noticed. If I have a table by the same
t> > name in two different schemas, say public and foo, and my search path
t> > is set to 'public, foo', \d without
Something interesting I've noticed. If I have a table by the same
name in two different schemas, say public and foo, and my search path
is set to 'public, foo', \d without an argument lists only the one in
public.
I see why from the SQL that \d generates, but just wondering why \d
doesn't generat
g == gryz...@gmail.com writes:
g> If you want to store period of time, why store it as varchar ?
g> just store two rows
g> create table foo(
g> n varchar,
g> val date
g> );
g> and store two rows:
g> "start", now(),
g> "end", now()+'something '::interval
g> Wouldn't that do, or is
g == gryz...@gmail.com writes:
g> 2009/6/9 Brandon Metcalf :
g> > I'm not sure I follow how this solves the problem.
g> Well, surely if you just need one row, you need single value per key.
g> And that's the, imo , better solution to that problem, than limiting
g == gryz...@gmail.com writes:
g> just change whatever you are storing to be in vertical structure,
g> instead of horizontal. so instead of create table foo(a int, b int, c
g> int, etc), try:
g> create table foo(name varchar, val int);
g> common mistake I've seen committed by people..
I'm
What would be the best way to maintain referential integrity in the
following situation? Let's say I have the following table
CREATE TABLE workorder (
workorder_id INTEGER NOT NULL,
part_id INTEGER DEFAULT NULL,
generic BOOLEAN DEFAULT FALSE,
PRIMARY KEY (
p == pgmaili...@codecraft.se writes:
p> On 4 jun 2009, at 22.17, Richard Broersma wrote:
p> > On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
p> > wrote:
p> >> Is there a way when creating a table to limit it to one row? That
p> >> is,
p> >>
r == richard.broer...@gmail.com writes:
r> On Thu, Jun 4, 2009 at 1:23 PM, Brandon Metcalf
r> wrote:
r> > Got it. ?Currently, it doesn't have a column for an ID, but I can add
r> > one if this is the only way.
r> Actually any column with a unique index on it
r == richard.broer...@gmail.com writes:
r> On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
r> wrote:
r> > Is there a way when creating a table to limit it to one row? ?That is,
r> > without using a stored procedure?
r> Sure just add a check constraint along the line
Is there a way when creating a table to limit it to one row? That is,
without using a stored procedure?
I searched the documentation, but didn't find anything.
--
Brandon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.pos
p == pavel.steh...@gmail.com writes:
p> Hello
p> use timestamp(0)
p> timestamp[0] means array of timestamps
Of course. I was reading the documentation wrong and taking the [] as
literal instead of it meaning an optional parameter as it always does.
Thanks.
--
Brandon
--
Sent via pgsql
b == bran...@geronimoalloys.com writes:
b> I need to create a table with two columns of type timestamp but I
b> don't want to store any fractional part of the seconds field. So,
b> I created a table with:
b> CREATE TABLE timeclock (
b> timeclock_id SERIAL,
b> employee_id I
I need to create a table with two columns of type timestamp but I
don't want to store any fractional part of the seconds field. So,
I created a table with:
CREATE TABLE timeclock (
timeclock_id SERIAL,
employee_id INTEGER,
clockin TIMESTAMP[0]NOT NULL,
clock
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS hours
FROM timeclock;
The clockin and clockout columns are of t
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > d == dal...@solfertje.student.utwente.nl writes:
t> > d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
t> > d> > The issue here is that these reduce back to my original problem.
For
t>
d == dal...@solfertje.student.utwente.nl writes:
d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
d> > j> option 2: case when '$length' = '' ...
d> >
d> > j> you can use case like this:
d> >
d> > j>UPDATE foo
j == ja...@xnet.co.nz writes:
j> On 2009-05-22, Brandon Metcalf wrote:
j> > Assume I have an UPDATE statement that looks like
j> >
j> > UPDATE foo
j> > SET
j> > pattern = '$pattern',
j> > shape = '$shape',
j&
Assume I have an UPDATE statement that looks like
UPDATE foo
SET
pattern = '$pattern',
shape = '$shape',
length = $length,
comment = '$comment'
WHERE foo_id = $foo_id
and length is defined as NUMERIC. Is there any kind of magic that
would allow me to use the
s == s...@samason.me.uk writes:
s> On Fri, May 22, 2009 at 08:41:46AM -0500, Brandon Metcalf wrote:
s> > I am looking for criteria on deciding whether or not to use a serial
s> > (auto-incrementing) key for rows in a table.
s> Wow, that's the second time today
g == gryz...@gmail.com writes:
g> you should use it, whenever you need db to keep its own key internally.
g> Advantage of sequence is also the fact, that you can have the sequence
g> value used on different columns/tables .
g> My rule of thumb is , in that case: as long as it is a short type
This may be better discussed in the pgsql-sql forum. Please let me
know if so.
I am looking for criteria on deciding whether or not to use a serial
(auto-incrementing) key for rows in a table. For example, if I have a
table of, say, the elements on the periodic table I could use the
atomic numbe
d == [EMAIL PROTECTED] writes:
d> Brandon Metcalf wrote:
d> >
d> > OK. I understand the Perl part of what is going on. What I don't
d> > understand is why $table in do_delete() hangs around. It seems this
d> > is more a characteristic of how triggers work
d == [EMAIL PROTECTED] writes:
d> Brandon Metcalf wrote:
d> > d == [EMAIL PROTECTED] writes:
d> >
d> > d> Brandon Metcalf wrote:
d> > d> > Yep, it seems that's the problem. If I pass in $table and use a
d> > d> > lexical variable
b == [EMAIL PROTECTED] writes:
b> d == [EMAIL PROTECTED] writes:
b> d> Brandon Metcalf wrote:
b> d> > Yep, it seems that's the problem. If I pass in $table and use a
b> d> > lexical variable defined inside do_delete(), the problem goes away.
b> d>
d == [EMAIL PROTECTED] writes:
d> Brandon Metcalf wrote:
d> > Yep, it seems that's the problem. If I pass in $table and use a
d> > lexical variable defined inside do_delete(), the problem goes away.
d> > So, this is where my understanding of how triggers work
d == [EMAIL PROTECTED] writes:
d> Brandon Metcalf wrote:
d> > Here is an example of the caching problem I described yesterday in a
d> > post. I have the following tables:
d> > And here is the SQL for the function and trigger definitions:
d> >
d>
Here is an example of the caching problem I described yesterday in a
post. I have the following tables:
db=> \d bmetcalf.foo1;
Table "bmetcalf.foo1"
Column | Type | Modifiers
---+--+---
country | text |
replicaID | text |
host | text |
replic
I'm just starting to investigate a problem where it seems data stored
in a variable from one invocation of a function is bleeding over to
the next invocation. For example, in a PL/PerlU function I'm getting
the table name with
my $table = $_TD->{relname}
and immediately print it out and all se
We have a number of automated jobs that connect to our pgsql DB and
I'm wondering what others are doing for authentication and securing
passwords. It's easy enough to hardcode a password, but is there
something specific to pgsql, perhaps, that would be a better solution?
Thanks.
--
Brandon
--
m == [EMAIL PROTECTED] writes:
m> On Tue, Oct 21, 2008 at 9:54 AM, Brandon Metcalf <[EMAIL PROTECTED]> wrote:
m> > m == [EMAIL PROTECTED] writes:
m> >
m> > m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <[EMAIL PROTECTED]>
wrote:
m> > m> &
m == [EMAIL PROTECTED] writes:
m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <[EMAIL PROTECTED]> wrote:
m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My
m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL
m> > t
I have a need to keep a PostgreSQL and MySQL table synchronized. My
thoughts are to use triggers on the pgsql side to manipulate the MySQL
table when data in the pgsql table is changed. I also plan on using
PL/Perl to write the functions.
Are there better ways to achieve this?
--
Brandon
--
b == [EMAIL PROTECTED] writes:
b> I've been able to find a couple of packages, but wondering if there is
b> a good system out there what will create an ER diagram of an existing
b> PostgreSQL DB. Open source would be nice.
Thanks for all that have responded so far. I'm looking at
Power*Arch
I've been able to find a couple of packages, but wondering if there is
a good system out there what will create an ER diagram of an existing
PostgreSQL DB. Open source would be nice.
Thanks.
--
Brandon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to yo
b == [EMAIL PROTECTED] writes:
b> t == [EMAIL PROTECTED] writes:
b> t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
b> t> > I just upgraded to 8.3.3 and taking advantage of the RETURNING clause
b> t> > which is really cool. I've found th
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > I just upgraded to 8.3.3 and taking advantage of the RETURNING clause
t> > which is really cool. I've found that with Pg.pm $r->resultStatus
t> > returns the int
I just upgraded to 8.3.3 and taking advantage of the RETURNING clause
which is really cool. I've found that with Pg.pm $r->resultStatus
returns the integer "2" when the RETURNING clause is used on an
insert.
Of course, without using RETURNING the status is the constant
PGRES_COMMAND_OK.
Is check
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > t == [EMAIL PROTECTED] writes:
t> > t> Uh, no, that is certainly *not* the behavior you were getting in 8.1;
t> > t> 8.1's behavior corresponds to both switch
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > t == [EMAIL PROTECTED] writes:
t> > t> Well, if your intent is to replicate 8.1's behavior, you should
instead
t> > t> frob the other switch.
t> > I
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > t == [EMAIL PROTECTED] writes:
t> > t> See standard_conforming_strings and escape_string_warning.
t> > Excellent. I had missed the standard_conforming_strings and
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
t> > handled completely differently now. For example,
t> See standard_conforming_strings and escape_s
b == [EMAIL PROTECTED] writes:
b> I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
b> handled completely differently now. For example,
It looks like the default for escape_string_warning is now "on".
However, it says in the docs that future versions will treat the
backslas
I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
handled completely differently now. For example,
db=# insert into junk (cifs) values ('\\f\bar');
WARNING: nonstandard use of \\ in a string literal
LINE 1: insert into junk (cifs) values ('\\f\bar');
I see that 8.2 has added a RETURNING clause option to the INSERT
command. Is there anyway to achieve the same thing in versions prior
to 8.2? Specifically, I need to return a default sequence number
generated from an INSERT.
Thanks.
--
Brandon
--
Sent via pgsql-general mailing list (pgsql-ge
m == [EMAIL PROTECTED] writes:
m> you didn't reference the table "replica"... this should work:
m> UPDATE model_timemap
m> SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica =
m> 32191 THEN 5739 ELSE -1 END, 1161642129, map)
m> FROM replica
m> WHERE replica.replica_id
Just upgraded to 8.1.5 and the following UPDATE causes the "missing
FROM-clause entry" error:
UPDATE model_timemap
SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica =
32191 THEN 5739 ELSE -1 END, 1161642129, map)
WHERE replica.replica_id = model_timemap.replica
I'm currently using version 1.9.0 of the old Pg interface with
PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg,
but we need to upgrade PostgreSQL before this is going to happen.
Does anyone know of any issues with continuing to use the old Pg
interface with newer versions of Post
k == kleptog@svana.org writes:
k> On Fri, Oct 06, 2006 at 09:10:33AM -0500, Brandon Metcalf wrote:
k> > >From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
k> > a timezone input string. However, this doesn't seem to work:
k> >
k> >
>From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
a timezone input string. However, this doesn't seem to work:
db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, seconds,
pseconds) VALUES ('10-05-2006 18:26:13 Europe/Moscow', 9407, 27362, 18516,
35361, 1
t == [EMAIL PROTECTED] writes:
t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
t> > What is the best way to handle timestamps with a timezone of IDT? I
t> > see that I could modify src/backend/utils/adt/datetime.c to support
t> > IDT, but what is the be
What is the best way to handle timestamps with a timezone of IDT? I
see that I could modify src/backend/utils/adt/datetime.c to support
IDT, but what is the best solution?
Basically, I have an application where I'm grabbing the timezone from
the output of date(1) and appending that to a timestamp
s == [EMAIL PROTECTED] writes:
s> On Thu, 2006-02-23 at 13:55, Brandon Metcalf wrote:
s> > What is the best way to store a timestamp if all I need to do is
s> > select rows where this timestamp is less than 60 minutes prior to the
s> > current time?
s> >
s> &g
c == [EMAIL PROTECTED] writes:
c> You can just save it as timestamp and try the following query.
c> select * from table where date < (now() - interval '1 hour');
Thanks.
--
Brandon
---(end of broadcast)---
TIP 4: Have you searched our list arc
What is the best way to store a timestamp if all I need to do is
select rows where this timestamp is less than 60 minutes prior to the
current time?
If I have a column called date with data type timestamp without time
zone I know I can use
SELECT * FROM table WHERE date < (now()::DATE - 7)::TIM
I figured pgsql-general would be a more appropriate forum for the
question below.
--
Brandon
-- Forwarded message --
Date: Mon, 26 Sep 2005 15:38:56 -0500 (CDT)
From: "Metcalf, Brandon [SC100:CM21:EXCH]" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: [SQL] add column i
p == pgman@candle.pha.pa.us writes:
p> Brandon Metcalf wrote:
...
p> > So, pg_autovacuum says it's going to sleep for 4886 seconds, but fires
p> > up again after just under 600 seconds.
p> >
p> > Can anyone explain what I'm seeing?
p> Yep, this
We're using pg_autovacuum with PostgreSQL 8.0.3 and it isn't sleeping
as long as the claims it will. For example,
...
[2005-09-20 15:40:23 CDT] INFO: last_analyze_count: 21735550;
last_vacuum_count: 21735009
[2005-09-20 15:40:23 CDT] INFO: analyze_threshold: 40676;
vacuum_
m == [EMAIL PROTECTED] writes:
m> On Thu, Aug 11, 2005 at 12:51:29PM -0500, Brandon Metcalf wrote:
m> > I'm looking for details on the new Perl server-side language
m> > introduced in 8.0. Specifically, I'm looking for changes between 7.4
m> > and 8.
I'm looking for details on the new Perl server-side language
introduced in 8.0. Specifically, I'm looking for changes between 7.4
and 8.0 and if there are any backward compatibility issues.
I checked the website and docs, but didn't find anything.
Thanks.
--
Brandon
--
d == [EMAIL PROTECTED] writes:
d> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
d> > I've been looking at the auto vacuum daemon pgavd and it looks like
d> > there hasn't been any development activity in a while. Does anyone
d> > know
I've been looking at the auto vacuum daemon pgavd and it looks like
there hasn't been any development activity in a while. Does anyone
know that status of pgavd?
Also, I'd be interested in hearing how well it works and if there are
any plans to include this daemon in the pgsql distribution.
--
70 matches
Mail list logo
