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
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');
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
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
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> > 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> Uh, no, that is certainly *not* the behavior you were getting in 8.1;
t> > t> 8.1's behavior corresponds to both switch
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> > 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
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
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
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
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
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
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
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
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
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> >>
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 (
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
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> 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
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
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
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> > 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
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
I have the following table:
gms=> \d jobclock
Table "public.jobclock"
Column| Type |
Modifiers
-++---
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
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
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
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>
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
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> 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 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
--
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
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> &
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
--
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
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
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>
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
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> > 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
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
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
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
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
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
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&
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
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>
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.
--
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'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
--
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.
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_
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
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
>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
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> >
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
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
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
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
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
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
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
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
70 matches
Mail list logo
