(Note: catching up on a severe list backlog, thought I'd complete this
thread for the archives)
Brian -
In case Boris never sent anything directly, I'll extend his example and
show a solution. The usual problem w/ namespaces is getting your head
wrapped around the fact that they're local aliases:
On Sat, Jun 18, 2011 at 08:51:55PM -0700, Samuel Gendler wrote:
> Actually, you need the array_agg() function to aggregate multiple rows into
> a single array, and that is discussed on the aggregate functions page,
> here: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
>
> You
On Wed, May 18, 2011 at 05:06:36PM -0600, Eric McKeeth wrote:
> On Wed, May 18, 2011 at 2:20 PM, Emi Lu wrote:
>
> > Hello,
> >
> > To save pdf files into postgresql8.3, what is the best column type?
> >
> > bytea, blob, etc?
> >
> > Thank you,
> > Emi
> >
>
> Everyone else has pointed out reaso
On Wed, May 11, 2011 at 11:11:07PM +0200, Leif Biberg Kristensen wrote:
>
> although it's a little above me why you would want to select firstname in the
> first place when you proceed to replace it with nothing.
Nah, he's replacing double-quote-character " with nothing.
An attempt to protect a
On Wed, May 11, 2011 at 04:51:05PM -0400, Tony Capobianco wrote:
> Ok, I think I found it:
>
> select translate(firstname,'"','') from members;
>
> gives me what I want.
Yup, you beat me to the answer. For the archives, if this was a
compatability question (for example, you've got framework code
On Mon, Mar 07, 2011 at 02:08:10PM -0800, bricklen wrote:
> On Mon, Mar 7, 2011 at 1:20 PM, Piotr Czekalski
> wrote:
> > Hello pgsql community,
> >
> > Is there any string function (other than regex / scan & compare loop) to
> > obtain a list (or even a count) of characters within a string?
> > s
On Wed, Jul 07, 2010 at 04:25:13PM -0500, Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on h
On Wed, Sep 02, 2009 at 04:28:34PM +0200, aymen marouani wrote:
> Hi for all,
> What is the possible sources of the SQLState 55000 "OBJECT NOT IN
> PREREQUISITE STATE" ?
> The error 55000 was triggered when I executed the following query :
> "select currval('"BatchTreatment_batch_treatment_id_seq"'
On Thu, Jan 30, 2003 at 11:03:43PM -0800, joe.guyot wrote:
> greetings all!
>
>
> and continually get different errors:
> "bad date external representation 'createdate'"
> or
> "bad timestamp external representation 'createdate'"
>
> i'm sure this has an obvious solution but i can't seem
On Sat, Feb 01, 2003 at 12:39:50AM -0600, Bruno Wolff III wrote:
> On Fri, Jan 31, 2003 at 23:47:27 +1100,
> Matthew Horoschun <[EMAIL PROTECTED]> wrote:
> >
> > Is there any effective method for controlling access to a SEQUENCE? or
> > should I do something like in the view:
>
> You can limit
On Wed, Jan 15, 2003 at 03:19:38PM -0500, Matthew Nuzum wrote:
> > -Original Message-
> > From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 15, 2003 3:00 PM
> > To: Matthew Nuzum
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: [SQL] lost on self joins
> > >Finaly, a tabl
On Wed, Jan 15, 2003 at 09:19:47PM +0300, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote:
>
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
> >
>
> Use OpenFTS (openfts.sourceforge.net) for relev
Quoting from http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html :
PL/pgSQL can be used to define trigger procedures. A trigger procedure
is created with the CREATE FUNCTION command as a function with no
arguments and a return type of OPAQUE. Note that the function must be
On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote:
>
> any anyone explain whats wrong with the replace based solution to this problem
> which i posted earlier?
>
> did i misunderstood anything?
Probably just overkill - I'm sure it would work, but, based on how the
question was
On Wed, Jan 08, 2003 at 09:02:47AM -0500, Randy D. McCracken wrote:
> I apologize for having to resort to sending what is most likely a simple
> tech support question regarding PostgreSQL to this list but I have not
> been able to find the answer in the documentation.
Hey, this is Open Source: tha
On Fri, Nov 29, 2002 at 02:39:50PM +, Gary Stainburn wrote:
> I've worked out a way of doing it by vreating a view for the tally info as:
>
> create view link_tally as
> select lklid, lktype, count(*) from links group by lklid, lktype;
>
> and then doing:
>
> select r.rtid, r.rtname, l.cou
Hey all -
I'm working on the query interface to a system that stores a bunch of
(realtively) small text documents, with titles and keywords, etc.
For the basic interface, we have the user types some words into a textbox,
then just search 'everything'. I'm weighting the results, so that hits
on th
Another way to approach this would be to add a trigger to your table
in the database, that rejects updates that don't change any values.
You'd basically have to hard code that same logic (new.a != old.a or
new.b != old.b ...) and it'd fire on every update, so you're talking
about trading computatio
On Wed, Oct 30, 2002 at 10:25:17AM +0100, James Adams wrote:
> Yea it would be easier to have everything in one table filling unused with
> nulls, but I was trying to avoid that because of the wasted space.
> But I think I'll do it that way after all :~]
> Thanks for your help
Don't fret too
On Tue, Sep 24, 2002 at 10:33:51AM +0200, Manfred Koizar wrote:
>
> The people who wrote the specification knew about transactions. If
> they had wanted what you describe above, they would have written:
>
> 3) If a transaction generally contains more than one reference
> to one or more s
On Tue, Sep 24, 2002 at 08:05:59AM -0700, Josh Berkus wrote:
>
> This looks fine to me, as a search-and-replace on current_timestamp is
> easy. However, we need to do a better job of warning people about the
> change than we did with interval() to "interval"().
>
> Actually, can I make the
On Tue, Sep 24, 2002 at 10:55:41AM -0400, Roland Roberts wrote:
> > "Martijn" == Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
>
> Martijn> Well, what I would suggest is that when you wrap several
> Martijn> statements into a single transaction with begin/commit,
> Martijn> t
On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote:
> Yudie wrote:
> Good question. The only easy answer I have is the creation of a temp
> table with a SERIAL column:
>
> CREATE TEMP TABLE out (cnt SERIAL, other_cols...);
> INSERT INTO out SELECT ... ORDER BY col;
Hmm,
Beth -
Take a look at pgsql's sequences. They do pretty much exactly what
you want, here. There's even a 'pseudo type' serial that sets up
an int column with a default value coming from a sequence.
Sequences are more loosely couple to 'auto increment' columns than
in mysql or other database sys
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
>
> Adam Erickson wrote:
>
> > Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> > Triggers are carried out after the SQL is executed and the data is
> modified.
> > So, to answer your question, I think rules
On Tue, Sep 10, 2002 at 08:35:27AM -0500, Josh Jore wrote:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>
> > id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
>
> > ERROR:ExecAppend: rejected due to CHECK constraint table_name1_id1
> >
> > I tested preceding regular expression w
On Mon, Sep 09, 2002 at 11:02:27PM +0200, Reinoud van Leeuwen wrote:
> On Mon, 9 Sep 2002 18:16:07 + (UTC), [EMAIL PROTECTED] (Charles
> Hauser) wrote:
>
> >I am trying to port a Sybase table create script to one usable for
> >postgreSQL.
> >
> >(note I am not a DBA)
> >
> >In particular I am
Richard -
Your analysis of this looks right on, to me. With current code,
if you put in explicit JOINS, the table get joined in that order,
no questions. By specifying an all JOIN version, you've made the
optimizers job very easy: only one plan to consider.
Your point about realistic data and co
On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote:
> Hi!
>
> If I want to be able to search for stringmatches using LIKE, doing
> something like the following:
>
> select id, name from table1 where lower(name) like 'somestring%';
>
> Actually I will be joining with some other table on
On Tue, Oct 02, 2001 at 09:47:09AM -0700, Josh Berkus wrote:
> Folks,
>
> Q: Are brand-new rows included in PostgreSQL indexed immediately?
>
A: Yes, and that's why you don't add indices willy-nilly to all possible
column combos on any given table: INSERTs get real slow.
Ross
---
On Mon, May 21, 2001 at 07:39:06PM +0530, Gurudutt wrote:
> Hello pgsql-sql,
>
> I am the new member for the postgres mailing list. Actually I have
> been working with mysql, php and perl for a very long time now, and
> offlate shifted to pgsql. I have many technical difficulties
>
> 2.
On Sat, Sep 01, 2001 at 09:44:19AM -0700, Josh Berkus wrote:
> Ross,
>
> > Have you seen "Database Design for Mere Mortals" by Michael
> > Hernandez?
>
> Yeah, that's one I've been loaning out a lot. However, while it does
> cover a lot of good stuff about how to design a database, it never get
On Fri, Aug 31, 2001 at 08:29:21AM -0700, Josh Berkus wrote:
>
> That's why we have the list. Personally, I'm still looking for a
> comprehensive introductory SQL book to recommend. The ones I know are
> either too simple and not that accurate (SQL for Dummies), proprietary
> (MS SQL Server in
Well, a short answerto your direct question would be:
Select id from T where name = 'bleh'
UNION ALL
Select id from T where description = 'bleh';
But since you described what your trying to do, not just how
your trying to do it, doesn't this do it for you?
SELECT id from T where name =
On Tue, Jul 24, 2001 at 10:50:50AM -0300, Roberto Jo?o Lopes Garcia wrote:
> Hi
>
> Is there any way, possible an SQL or pgsql command, to get the server version?
>
template1=# select version();
version
-
Best to ask your questions on the list, so others may find them,
with (hopefully) helpful answers in the archives in the future.
so, you've got a table with indistinguishable rows. I'm afraid you've
got to use an non ANSI extension. Every DB I've ever used has something
equivelant. In PostgreSQL,
I did something similar, but pu the subselect into a view, in the WHERE clause.
SELECT * FROM modules m WHERE (m.module_ident = (SELECT
max(modules.module_ident) AS max FROM modules WHERE (m.moduleid =
modules.moduleid) GROUP BY modules.moduleid));
The equivalent for you would be something like:
On Fri, Jun 22, 2001 at 06:31:03PM +0200, Kristoff Bonne wrote:
> Greetings, (and also Alex)
>
> On Fri, 22 Jun 2001, Josh Berkus wrote:
> > > Excuse my lack my 'database'-jargon, but what is a 'tuple'?
>
> > Also known as a "Record", or a "Row". The word "tuple" is used because
> > it can refe
On Fri, Jun 22, 2001 at 12:58:46PM -0400, Tom Lane wrote:
> "Ross J. Reedstrom" <[EMAIL PROTECTED]> writes:
> > And here's the working example: not the need to GROUP BY, and <=
> > to get the current payment.
>
> > select cust_id,invoice_id,v
And here's the working example: not the need to GROUP BY, and <=
to get the current payment.
select cust_id,invoice_id,val,paid, (select (sum(val) - sum(paid))
from invoices_not_paid where cust_id= i.cust_id and invoice_id <=
i.invoice_id group by cust_id) as balance from invoices_not_paid i;
a
On Wed, Jun 20, 2001 at 07:37:39PM +, sbelow wrote:
> getting this error "Transaction cannot start while in firehouse mode." I
> can't find in the books what this is trying to tell me.
> new at dbs.
That message is not coming from PostgreSQL, I've grepped the source tree:
$ find . -name \*.[
On Thu, Jun 21, 2001 at 01:32:10AM -0400, Tom Lane wrote:
> "Ross J. Reedstrom" <[EMAIL PROTECTED]> writes:
> >> could [a fork] potentially happen?
>
> > Sure, we're BSD, so they're under no obligation to share any code changes
> > with us
On Wed, Jun 20, 2001 at 03:28:34PM -0700, clayton cottingham wrote:
> hello:
>
> i thought id find out what people think about this:
> http://www.zdnet.com/zdnn/stories/news/0,4586,2778706,00.html?chkpt=zdnn_rt_latest
>
> which basically points out that redhat will be making their own database
>
On Wed, Jun 06, 2001 at 06:13:18PM -0300, Marcos Vaz - ( NewAge Group ) wrote:
> I have one table with thwo columns, user and text, why sum all the text
> fields of the one user ?
I assume you mean 'how' not 'why'?
You don't give a lot of detail, like what you mean by 'sum all the text'. One
int
On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote:
numeric casts>
Tom Lane has already addressed the 'why is it this way' question. I'll
address your proposed work arounds.
>
> the only solution i can think of, if it is possible, is to have a script
> that the client would need to
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote:
> On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> > Deleting is broken if it deletes more than one row. The rule is executed
> > only one time each delete command, and not one time each deleted row.
> >
> > It's this OK ??? Am i
On Tue, Apr 17, 2001 at 01:52:16PM -0500, John Hasler wrote:
> Ross writes:
> > But seriously, numeric(10,2) (or whatever precision and scale is correct
> > for your application) is the standard answer.
>
> Floats are fine for money as long as you only add and subtract and don't
> deal in amounts
On Tue, Apr 17, 2001 at 10:31:49AM -0500, Mark Stosberg wrote:
> Peter Eisentraut wrote:
> >
> > Mark Stosberg writes:
> >
> > > I'm curious to know if there is a function available in Postgres 7.0.3
> > > (or 7.1) that will format a float style number as "money". I understand
> > > that the m
On Mon, Apr 09, 2001 at 06:53:13PM +0200, Peter Eisentraut wrote:
> Ross J. Reedstrom writes:
>
> > FYI, I can't find an occurance of '::' that's not part of '::=' in either
> > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got.
>
On Mon, Apr 09, 2001 at 05:57:45PM +0200, Peter Eisentraut wrote:
> Cedar Cox writes:
>
> > When would one want to use cast()? What is the difference between cast
> > and :: ? After a quick look in the documentation I couldn't find
> > anything..
>
> cast() is SQL, :: is traditional Postgres.
On Wed, Mar 21, 2001 at 08:20:22AM -0500, Bruce Momjian wrote:
> > version 7.0.3
> >
> > binary_data=# select day, date_part('day', day) AS day from test;
> > day | day
> > +-
> > 02/04/2000 | 2
> > 01/04/2000 | 1
> > 03/04/2000 | 3
> > (3 rows)
> >
> > binary_d
On Mon, Mar 19, 2001 at 11:23:45AM +0100, [EMAIL PROTECTED] wrote:
> Hi there
>
> For testing, I have a Microsoft Windows NT Server with IIS 4.0 running in
> my DMZ. Currently it is not possible to migrate this server to LinuX. Is
> there a way to move an existing postgreSQL 7.0.3 db (with views/
On Fri, Mar 16, 2001 at 12:15:28PM +, Egbert Ellenkamp wrote:
> All,
>
> Is there a way I can set the sequence of a table equal to highest row
> ID?
> For example something like:
> select setval('mytable_myrowid_seq',select max(myrowid) from mytable);
So close!
select setval('mytable_myrowi
On Wed, Mar 14, 2001 at 09:17:33PM +, Richard H wrote:
>
> > I need to produce a new table that coalesces immediately successive
> > operations on a run into one, e.g.:
>
> > run | start | done
> > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > 1415| 2001-02-14 07:
On Fri, Mar 09, 2001 at 11:05:38AM -0800, Alder wrote:
> I'm pretty new to SQL and can't figure out how to write what should be a
> simple query of two tables. Could someone here possibly help me out?
>
> Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE
> Table 2 also has two f
On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> Andrew Perrin writes:
>
> > I'm trying to write what should be a simple function that returns the
> > minimim of two integers. The complication is that when one of the two
> > integers is NULL, it should return the other; and wh
On Thu, Feb 22, 2001 at 11:49:30PM +0100, Bjørn T Johansen wrote:
> I am trying to do a simple update (or at least I thought it was
> simple), but I just keep getting a parse error, saying:
>
> Error executing query
>
> Update "Config" Set "Wave" = 'F:\wav\BTJ.wav',"Answer" = 20,
> "Recordwav" =
On Mon, Feb 12, 2001 at 03:54:39PM +0100, Giovanni Biscuolo wrote:
> I set up a simple database in wich all works well, exept
> one *very* strange (to me) result.
>
> Some time ago I created this views:
>
> CREATE VIEW mag_scaricati_view AS
> SELECT s.id_carico, SUM(s.qta_scaricata) AS Scaricat
Brice -
What does EXPLAIN say for that query? With empty tables, I get two index scans,
a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal
sort strategy happening somehow, given the four fold ORDER BY clause.
Ross
Here's the empty version:
NOTICE: QUERY PLAN:
Sort
On Mon, Feb 05, 2001 at 05:15:47PM +0100, Karel Zak wrote:
>
> test=# select date_part('dow','2001-02-11'::timestamp);
> date_part
> ---
> 0
>
> test=# select to_char('2001-02-11'::timestamp, 'D');
> to_char
> -
> 1
>
>
> date_part is based on zero - use range 0-
Rick -
Thanks for checking that. I should've done it myself. Sorry I didn't
respond when you asked for confirmation.
Ross
On Thu, Feb 01, 2001 at 05:38:12PM -0500, Rick Delaney wrote:
> Rick Delaney wrote:
> >
> > "Ross J. Reedstrom" wrote:
> > >
&
y referentially-related tables. Can/should I get
> > around this? A somewhat contrived example:
> >
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
On Tue, Jan 16, 2001 at 06:44:18PM +0200, Hannu Krosing wrote:
>
> To get a feel you could use MS Access visual query builder and then view
> the source.
> I have not checked it lately, but it very likely produces SQL92
> compliant outer joins.
>
I fired up MS-Access 97SR1, just to see, and her
; here is the query:
>
> select * from av34s1 where chromat ~~ ('%' || sample || '%');
>
>
> ERROR: parser: syntax error at or near "||"
>
> I have also tried using LIKE
>
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
;";'
from pg_class c,
pg_attribute a,
pg_attrdef d
where c.oid=d.adrelid and
a.attrelid=c.oid and
d.adnum=a.attnum and
d.adsrc ~ ('nextval\\(\'"
27;,
max("' || attname || '")) FROM "' || relname || '";' from pg_class c, pg_attribute a,
pg_attrdef d where c.oid=d.adrelid and a.attrelid=c.oid and d.adnum=a.attnum and
d.adsrc ~ ('nextval\\(\''||relname);
mydb=# \o
mydb=# \i sequence
On Fri, Dec 01, 2000 at 04:25:36PM -0700, Roberto Mello wrote:
> Tom Lane wrote:
> >
> > It kinda sounds like your wife created those tables in template1.
> >
> > Duplicating template1's contents into new databases isn't a bug,
> > it's a feature ;-)
>
> I've seen this behaviour too. Yest
On Tue, Nov 28, 2000 at 12:39:50PM +0100, Hans-Jürgen Schönig wrote:
> How can I retrieve empty columns from a table?
> The affected column is int4, some row do have values, others are empty -
> how can those empty ones be retrieved and updated to 0
UPDATE foo_table SET bar_column = 0 WHERE bar_c
On Mon, Nov 20, 2000 at 01:24:32PM +1000, Sean Weissensee wrote:
> Why does this error come up
>
> ionsol=> update quickstock set ItemDesc = '1',Qty = 0.0,salesprice = 0
> .0 where StockID = '8597' \g
> ERROR: Relation 'quickstock' does not have attribute 'itemdesc'
>
> when ItemDesc do
On Fri, Nov 17, 2000 at 10:06:17AM -0600, Roberto Mello wrote:
>
> I have this on the way. I started creating such document a
> couple months ago when I was porting stuff from Oracle to PostgreSQL and
> stumbled on the few examples on the documentation. I'd be glad to finish
> it up, add mo
On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote:
> Hello,
>
> I would like to insert a bunch of rows in a table in a transaction. Some of
> the insertions will fail due to constraints violation. When this happens,
> Postgres automatically ends the transaction and rolls bac
ptimal'?
>
> select m.miles, m.date, sum(d.miles) from mileage m, mileage d
> where...
>
This is the traditional way to do a self join, yes.
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Insti
term from your WHERE
clause? "survey_id =" seems to be missing in the functional form. I
din't see how that would make it run slowly, however.
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
n the region table any more.
>
> How can I do that?
>
SELECT * FROM city WHERE region_id NOT IN (SELECT region_id FROM region)
should do it.
BTW, the syntax on your defaults for the id's seems a bit odd. Any reason
your casting the text literal to text? Have we got a bug som
't' AND code = office_code);
>
How does the output of the above differ from:
SELECT name FROM office, office_application
WHERE code = office_code
AND active != 't';
Without knowing the table structures (which tables to active, code,
and office_code belong to?) it's
ed about doing an all in one
bulk load, skip the split.
After this is all loaded, be sure to set the sequence associated with
the serial filed you loading into:
SELECT setval('table_field_seq',max(field)) from table;
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
r',datums_) and date_part('year',datetime datums_) doesnt
> work also!
Right, that's the syntax for a datetime literal, not a cast. How about:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND
date_part('year', datetime(datums_)) = 2000 LIMIT 1;
By the
to
> use them
> in postgreSQL 7.0 I'v got error.
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
RROR: Unable to identify an operator '<' for types 'unknown' and
> 'unknown'
> You will have to retype this query using an explicit cast
> playpen=>
>
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
79 matches
Mail list logo
