On 8/2/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
>
> I have PostgresSQL database connection from server. Server ip is
172.16.5.179
>
>
>
> Now I want use lo_import and lo_export function for storing images into
the database.
>
>
>
>
On 8/4/06, David Garamond <[EMAIL PROTECTED]> wrote:
On 8/4/06, Richard Huxton wrote:
> David Garamond wrote:
> > Dear all,
> >
> > Please see SQL below. I'm already satisfied with everything except I
> > wish in
> > #4, mtime got automatically updated to NOW() if not explicitly SET in
> > UPDAT
On 8/10/06, Juliann Meyer <[EMAIL PROTECTED]> wrote:
I have a table with a column, lets call it identifier, that is defined
as varchar(8) that should never contain lower case letters. Its a large
table. Is there a way to query the table to see if any values in this
column are lower case and to
If it was inside a transaction, and the transaction is still open,
then just rollback.
Otherwise, no.
I'm not sure if there's any way of doing some kind of PITR, no familiar with it.
If not, best bet is to recover from the newest backup set you have.
Regards,
Rodrigo
On 8/16/06, Markus Schaber <[EMAIL PROTECTED]> wrote:
Hi, Kis,
Kis János Tamás wrote:
> So, if you send every insert, update, delete command to a
> logger-table, then you can to undo anything.
But this is just re-inventing the wheel, we already have Point-in-Time
recovery.
Or do I miss someth
SELECT CASE
WHEN TYPE = 'C'
THEN 'CREDIT'
END AS TYPE
FROM mytable
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On 2/24/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Hi lists,
Is there possible to change a bigint field (primary key) to a serial ?
If yes how it is possible ?
I was trying to do that but the pgadmin don't enable the change to sequence.
What is the problem my friends ?
Regards
On 2/26/07, Rommel the iCeMAn <[EMAIL PROTECTED]> wrote:
Hi list,
I wrote a database creation script that begins with commands to drop the
existing database (if it exists) and create it from scratch. These commands
execute fine, the problem is that all subsequent commands are executed on
the def
On 2/26/07, Rommel the iCeMAn <[EMAIL PROTECTED]> wrote:
Thanks for the swift responses,
I tried the \c command but I got the following error:
ERROR: syntax error at or near "\"
SQL state: 42601
Character: 520
Here's a code snippet ...
--
-- TOC entry 1685 (class 1262 OID 16453)
-- Name: tes
On 3/3/07, Eugenio Flores <[EMAIL PROTECTED]> wrote:
Hello. I would like to know how can I return a select expresion, and stored
in a variable, as cursors, but without the need of fetching it, and just to
get the hole resultset.
For example. I want to get this query: Select * from table1, and s
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Hi list,
It is possible to retrieve the time of a SQL statement leads to
execute ? I would like to put in my application how much time each
operation leads to finish.
Any suggestion ?
--
Ezequias Rodrigues da Rocha
http://ezequi
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Hi list,
It is possible to retrieve the time of a SQL statement leads to
execute ? I would like to put in my application how much time each
operation leads to finish.
Any suggestion ?
--
Ezequias Rodrigues da Rocha
http://ezequi
On 2 Mar 2007 01:17:33 -0800, Lars Gregersen <[EMAIL PROTECTED]> wrote:
I have a table that contains historical exchange rates:
date_time | timestamp
xrate | real
There is a maximum of one entry per day, but data are missing on
weekends and holidays. For these missing dates I must use the va
On 3/9/07, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote:
Hello
From my asp page i create a table
TableName = "CON01"
strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not
null);"
But the problem i have is that when i go and open my database in pgadmin the
table nam
On 3/9/07, Karthikeyan Sundaram <[EMAIL PROTECTED]> wrote:
Hi Everybody,
We are using postgres 8.1.0. I want to do some maintenance work.
Hence, I want to run postgres in single user mode so that external people
won't be able to access the database.
How can I run the postgres in singl
On 3/13/07, T E Schmitz <[EMAIL PROTECTED]> wrote:
This is pretty ingenious!
Just a few minor problems:
- how does COPY know which column is which?
- how do I specify DELIMITER as TAB?
See:
http://www.postgresql.org/docs/8.2/static/sql-copy.html
Also, it's nopt happy about the date format : 2
On 3/14/07, Pablo Barrón <[EMAIL PROTECTED]> wrote:
Hi!
I'm trying to order a list in which the first parameter to order is a
specific field, and the second should vary depending on a specific
condition. More explicit, I would like to do something like:
- ORDER BY a.column1, [b.column2 if c.
On 3/14/07, Wiebe Cazemier <[EMAIL PROTECTED]> wrote:
I discovered that removing the subselect (the entire second condition of
the join actually) is not the only thing that speeds it up. If I remove
the LIKE check on account.description, it's also a lot faster (152 ms
as opposed to 2915 ms), alth
On 3/29/07, Radhika Sambamurti <[EMAIL PROTECTED]> wrote:
Hello,
I have an interesing problem relating to sql and performance issues and am
looking at ways I can increase the performace from postgres.
Currently I have a view created from two tables. All the selects are being
done on the view -
On 4/7/07, Karthikeyan Sundaram <[EMAIL PROTECTED]> wrote:
Hi,
I am using 8.1.0 postgres and trying to write a plpgsql block. In that
I am inserting a row. I want to check to see if the row has been inserted
or not.
In oracle we can say like this
begin
insert into table_a
On 4/10/07, PostgreSQL Admin <[EMAIL PROTECTED]> wrote:
Hi,
I want to replace a title with dashes and also remove punctuation.
e.g, The blue fox's fur. -> The-blue-fox-fur
Thanks for any input,
J
SELECT translate('The blue fox''s fur.', ' .''', '-')
---(end of broa
On 4/19/07, Markus Holzer <[EMAIL PROTECTED]> wrote:
Hello.
I'm currently developing my first web app with Postgres and I have a
question.
How do I perform a conditional insert/update?
To clarify: I need to insert data into a table when the primary key is not
already in the table, or an update
On 4/20/07, RPK <[EMAIL PROTECTED]> wrote:
Thanks both of you,
I ran EXPLAIN command on above suggested query and got following result:
"Aggregate (cost=2.77..2.79 rows=1 width=10)"
" -> Seq Scan on studentfeespayment (cost=0.00..2.77 rows=1 width=10)"
"Filter: (date_part('month'::
On 4/20/07, Wei Weng <[EMAIL PROTECTED]> wrote:
Hi all.
How do I write a query that converts an interger to the interval type?
Like convert integer 10 to INTERVAL '10 seconds'?
The integer is a column in a table though, so it is more like convert
integer tbl.theInteger to INTERVAL 'tbl.theInte
On 5/2/07, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
I need a user defined function for the following purpose….
If I pass a string with comma ( , ) separated chars/values…. It should be
appear in next line…
Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
Output :
SK
rp
h
j
6
On 5/11/07, Collin Peters <[EMAIL PROTECTED]> wrote:
I have a need to convert an incorrectly typed serial column to a
regular integer column. Basically this just involves removing the
sequence. I am able to successfully remove the default value (DROP
DEFAULT) (which seems to use nextval) and no
On 5/12/07, Frank Bax <[EMAIL PROTECTED]> wrote:
At 07:40 PM 5/12/07, Andreas wrote:
>I've got a stack of tasks to show in a list.
>Every task has a timestamp X that may be NULL or a date. It contains the
>date when this tasks should be done.
>Sometimes it has date and the time-part, too.
>
>
>
On May 1, 10:47 pm, [EMAIL PROTECTED] wrote:
> Hello all,
> I have a table with 2 issues, issue_A and issue_B, a table with action
> items related to the issues,
>
> issue_A AI_A1
> issue_A AI_A2
> issue_A AI_A3
> issue_B AI_B1
> issue_B AI_B2
>
> and network elements that the issues affect:
>
> is
On 5/14/07, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote:
Is there a way to get a query (of function) to retrieve a n-ary tree: 1
parent node has n children and the tree is n levels deep?
See:
http://search.postgresql.org/search?q=tree&m=1&l=4&d=365&s=r
---(end of broad
On 5/14/07, Nemo Terry <[EMAIL PROTECTED]> wrote:
Look at this problem:
when
execute 'insert into lse_installations values(' || ||obj_id|| || ',' ||
||div|| || ',' || ||sub|| || ',' || ||obj_type|| || ','
|| ||obj_name|| || ',' || ||pstcd|| || ','
On 5/15/07, Nemo Terry <[EMAIL PROTECTED]> wrote:
But I must use it in function,so...
Do you have another solution?
So?
t=# CREATE TABLE d(i INT);
CREATE TABLE
t=# CREATE OR REPLACE FUNCTION add1(x INT) RETURNS INT AS
t-# $$
t$# BEGIN
t$# INSERT INTO
On May 15, 8:17 am, Paul251 <[EMAIL PROTECTED]> wrote:
> Hello...
>
> I am trying to validate a asset number (10 Characters) from one table
> to another table. Problem is they are in two different DB's and
> haven't done that before?
>
> Basically trying to take record 1 from Table 1/DB1 and valida
On 5/24/07, chester c young <[EMAIL PROTECTED]> wrote:
on lwn I read that pg is having problems releasing because of a want of
reviewers.
although my C is far too rusty I'd like to help out, perhaps with doc
or testing.
can someone direct me to the appropriate site?
See:
http://www.postgresq
On 5/24/07, Edward W. Rouse <[EMAIL PROTECTED]> wrote:
I have an audit table that I am trying to get a count of the number of
distinct entries per day by the external table key field. I can do a
select count(distinct(id)) from audit where timestamp >= '01-may-2007'
and get a total count. What
On 5/30/07, Trigve Siver <[EMAIL PROTECTED]> wrote:
Can you point me to some sources
or give me some examples, please?
CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$
BEGIN
RETURN NEXTVAL('ROWNUM_SEQ');
EXCEPTION WHEN OTHERS THEN
CREATE T
On 6/4/07, Joshua <[EMAIL PROTECTED]> wrote:
Hello,
I was hoping someone here may be able to help me out with this one:
Is there anything similiar to: SELECT current_date;
that will return the date of the first Monday of the month?
Please let me know.
Thanks,
Joshua
select (
select
ca
On 6/5/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote:
We should have a onliner contest. I love oneliners!!!
+1 on that
---(end of broadcast)---
TIP 6: explain analyze is your friend
On 6/11/07, Sabin Coanda <[EMAIL PROTECTED]> wrote:
I'd like to know when I create a primary key on a table, does postgres will
create automatically an index on that table related to the primary key
columns, or I have to create it explicitly ?
From http://www.postgresql.org/docs/8.2/static/ind
On Jun 13, 11:17 am, [EMAIL PROTECTED] (Bob Singleton) wrote:
> Any suggestions on how I can build a result set that would return
>
> ASSET 001 AAA 1:00 (1 hour)
> ASSET 001 BBB 0:10 (10 minutes)
> ASSET 001 CCC 0:08 (8 minutes)
> ASSET 001 DDD {difference between timestamp and now()}
> ASSET 002 A
On 6/14/07, Salman Tahir <[EMAIL PROTECTED]> wrote:
Any help on how best to structure such data would be mostly appreciated.
See: http://en.wikipedia.org/wiki/Database_normalization
*** Grossly oversimplified example follows ***
CREATE TABLE PEPTIDE(
NAME TEXT PRIMARY KEY
);
CREATE TABLE FR
Guillermo Arias ha escrito:
> Why it does not work???
> thanks for your help
What's wrong with:
CREATE OR REPLACE FUNCTION test1(
OUT art_cod character varying
, OUT art_descri character varying
, OUT exis_ubic character varying
, OUT exis_qty numeric
)
RETURNS SETOF record AS
$BODY$
SELECT a
On May 17, 8:19 am, [EMAIL PROTECTED] (Christopher Maier) wrote:
> Conceptually, all the exons for a given gene form a set, ordered by
> their "start" attribute. I need to add a new integer column to the
> table to store a counter for each exon that indicates their position
> in this ordering.
>
>
On Jun 22, 3:25 am, earthwormgaz <[EMAIL PROTECTED]> wrote:
> hello, i'm trying to do a simple (so i thought) sql insert operation,
> but i'm being told the insert violates unique constraint
> say i've got three columns, two text and one integer for the id, i'm
> doing the following
> INSERT INTO m
On 6/25/07, Joshua <[EMAIL PROTECTED]> wrote:
I have a column with the following values (example below)
5673
4731
4462
5422
756
3060
I want the column to display the numbers as follows:
56.73
47.31
44.62
54.22
7.56
30.60
I have been playing around with string functions but cannot seem to
figu
On 6/28/07, Reinoud van Leeuwen <[EMAIL PROTECTED]> wrote:
So the table I store the errormessages in has a format like this:
CREATE TABLE repport_history
(
rundate date,
errordescription character varying(255),
number bigint
)
And I would like an output with something like:
06
On Jun 28, 1:43 pm, "Bauhaus" <[EMAIL PROTECTED]> wrote:
> I have the following table Price:
>
> FuelID PriceDate Price
> LPG1/05/2007 0,2
> LPG13/05/2007 0,21
> SPS 2/05/2007 1,1
> SPS 15/05/2007 1,08
>
> And I have to make the following query:
>
> FuelID PriceDate_from Price
On 7/11/07, Radhika Sambamurti <[EMAIL PROTECTED]> wrote:
Is there a way to select data using EXECUTE?
FOR-IN-EXECUTE.
See:
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
---(end of broadcast)
On 7/13/07, Radhika Sambamurti <[EMAIL PROTECTED]> wrote:
Is there a way to explicitly make the planner use bitmap index scan on a
field?
See:
http://www.postgresql.org/docs/8.2/static/runtime-config-query.html
---(end of broadcast)---
TIP 5: do
On 7/17/07, Norm Garand <[EMAIL PROTECTED]> wrote:
/---
IF $7 IS NOT NULL THEN
AND f.id = pid.specific_location_cid AND f.long_desc = $7
END IF
---/
How about:
AND f.ID = pid.specific_location_cid
AND f.long_desc = COALESCE ($7, f.long_desc)
---
On 7/27/07, I wrote:
> On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote:
> > Something like:
> >
> > decade | average(salary)
> > ---+-
> >1940 | 69500
> >1950 | 5.33
> >1960 | 53000
> >1970 | 40333.33
>
> CREATE TABLE tester (
> birth_year integer,
>
On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote:
> Something like:
>
> decade | average(salary)
> ---+-
>1940 | 69500
>1950 | 5.33
>1960 | 53000
>1970 | 40333.33
CREATE TABLE tester (
birth_year integer,
salary numeric(10,2)
);
SELECT
SUBSTRING(T
On 7/31/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> Is there a way to make this more efficient with another construct, or
> INTERSECT the only way to accomplish the desired result?
SELECT f1.ID
FROM TEST f1 JOIN TEST f2 ON f1.ID = f2.ID
JOIN TEST f3 ON f2.ID = f3.ID
WHERE f1.FI
On Aug 6, 3:57 am, Kiran <[EMAIL PROTECTED]> wrote:
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
SELECT MAX(t1)
FROM t1
WHERE '9849' LIKE t1 || '%';
---(end of bro
On 8/9/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> Ooops, just fugured that out. But - it still doesn't use the index if I remove
> the "varchar_pattern_ops".
Huh?
CREATE INDEX person_lowerfullname_idx
ON person
((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '';
EXPL
On 8/10/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote:
> unfortunatelly this query will be hard to optimize.
Uh, how about
SELECT MAX(t1)
FROM t1
WHERE '9849' LIKE t1 || '%';
---(end of broadcast)---
TIP 6: explain analyze is your frie
On 8/13/07, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
> In the second example, is it possible to get the primary key of the row
> with the minimum expires time?
SELECT TYPE, MIN(expires), COUNT(*)
, (SELECT MIN(coupon_id)
FROM coupon
WHERE expires = MIN(c.expires)) A
On 8/15/07, Mike Diehl <[EMAIL PROTECTED]> wrote:
> Any hints would be much appreciated.
DDL + sample data, please...
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On 8/16/07, Joshua_Kramer <[EMAIL PROTECTED]> wrote:
> In the pg_users view - is there a way to differentiate between a role with
> SUPERUSER priveleges, and a user who merely has the CREATEUSER flag?
select * from pg_roles;
> If I want to create a role who can create other roles, but not have ot
On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote:
> Is it possible to run an insert,update, or delete and have it not launch
> a trigger like it normally would?
alter table disable trigger ...
http://www.postgresql.org/docs/8.2/static/sql-altertable.html
---(end of broa
On 9/2/07, L D <[EMAIL PROTECTED]> wrote:
> Should I set the latter table's FK also to PK?
It's sane.
> If so, do I need to set an index on the FK even though it's also the primary
> key?
No need to do so.
---(end of broadcast)---
TIP 9: In vers
On 9/11/07, Koen Bok <[EMAIL PROTECTED]> wrote:
> Anyone has a hint?
Another way:
SELECT i.*
FROM item i JOIN search_item s1 ON i.ID = s1.id_item
JOIN search_item s2 ON s1.id_item = s2.id_item
JOIN search_item s3 ON s2.id_item = s3.id_item
WHERE s1.id_search = 1
AND s2.id_sear
On 9/18/07, Philippe Lang <[EMAIL PROTECTED]> wrote:
> ... into this:
>
>
> serial dateL dateR
>
> 1 1 2
> 1 4
> 2 1 2
> 3 1 3
> 4 2 3
> 5 3
SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater
FROM t t1 LEFT JOIN
On Sep 21, 12:09 am, [EMAIL PROTECTED] wrote:
> Write the query (or queries if necessary) needed to count the number
> of employees in each employee's department who are paid more than
> their manager.
SELECT e.dept, COALESCE (SUM (1), 0) AS n
FROM employees e JOIN employees m
ON (e
On Oct 4, 4:45 am, Nicolas Boullis <[EMAIL PROTECTED]> wrote:
> I'd like to define a table with a "name", a "start_date" and a
> "stop_date" columns, with a constraint that ensures that 2 records with
> ovelapping dates don't share the same name. Is there a way to define
> such a constraint?
CREAT
On 10/16/07, Jamie Tufnell <[EMAIL PROTECTED]> wrote:
> I'm taking a subset of all my genres, and I want to get ONE row for each
> movie in the subset alongside its most appropriate genre (whichever has the
> highest relevance). In other words, the best fit.
You didn't provide the expected output
On 10/30/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote:
> Wreird enough to me, need some advice plz!
CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
DECLARE
RETURNVALUE VARCHAR;
BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
SELECT WORD
FROM WORDS WHERE PAGE_
On Nov 19, 2007 3:46 PM, Luiz K. Matsumura <[EMAIL PROTECTED]> wrote:
> If someone can give me some hint I will apreciate.
This is more of a normalization problem. See:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
and lookup Third Normal Form.
You have
On Nov 21, 2007 8:23 AM, Martin Marques <[EMAIL PROTECTED]> wrote:
> (BTW, which it that limit if it exists?).
"In any case, the longest possible character string that can be stored
is about 1 GB."
See:
http://www.postgresql.org/docs/8.2/static/datatype-character.html
> So I made the function to
On Nov 22, 2007 11:24 AM, Franklin Haut <[EMAIL PROTECTED]> wrote:
>num return
> --
>0 0
>null false
>1212
>a false
>12ab false
>
> it´s possible get these results ?
Try:
SELECT
NUM
, CASE
WHEN TRIM(NUM) ~
On Dec 12, 2007 1:39 AM, Paul Lambert <[EMAIL PROTECTED]> wrote:
> It's a financial application which needs to work using a concept of
> 'financial periods' which may not necessarily correspond to calendar
> months and it's much easier to manage in this way than it is to merge it
> all together usi
On 12/17/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote:
> I've got this ugly case statement that I'd like to hide in a function:
Why don't you hide the entire query in a VIEW?
---(end of broadcast)---
TIP 1: if posting/reading through Usenet,
71 matches
Mail list logo
