I have a table containing tasks completed in a game I'm playing. The game
includes an extra BINGO Challenge where each cell of standard BINGO card
contains a particular task to be completed. The goal is score a BINGO
(row, column, diagonal) by completing five (or more) tasks from the BINGO
cards.
Hi
Le 22/05/2012 19:13, Carlos Mennens a écrit :
Hello everyone! I wanted to ask the list a question about the 'bytea'
data type& how I can picture this in my head. I've been reading SQL
for about a few months now and since then, I've only been working with
textual data. Basically I'm familiar
The fastest way is to create a ref table with all possible entries,
ordered with an additionnal numerical column, indexing it and make a
join from your table to this ref table.
A +
Le 17/12/2011 11:33, Richard Klingler a écrit :
Morning...
What is the fastest way to achieve natural ordering
Actullay there is no transaction support in internal PG routines.
So the code you posted is not translatable in PG PL/SQL because it
involve a transaction inside the process.
A +
Le 30/01/2012 07:42, Rehan Saleem a écrit :
hi , how i can convert this store procedure to PostgreSQL function,
e
I am answering to myseilf...
the good syntax is something like :
CREATE RULE R_U_MSR_BEFORE2000x
AS
ON UPDATE TO T_MESURE_MSR
WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
(
-- rows does not change partition :
UPDATE T_MESURE_BEFORE2000_MSR
SET MSR_ID = NEW.MSR_ID,
MSR_
Hello,
I have a problem to find the good syntax for a rule for rows going for
one partition to the other in cas of an update.
Let me give the conditions :
1 - having a mother table
CREATE TABLE T_MESURE_MSR
(
MSR_ID INT NOT NULL,
MSR_DATEDATE NOT NULL,
MSR_MESURE FLOAT NOT
I write a paper on this topic comparing queries for PG, SQL Server and
MySQL.
Can you read french ?
http://blog.developpez.com/sqlpro/p9821/langage-sql-norme/agregation-d-intervalles-en-sql-1/
The worst query is the RECURSIVE one !
A +
Le 15/06/2011 17:23, Jira, Marcel a écrit :
Hi!
Althou
Hi there
I am trying to get an example of SET BASED trigger logic with FOR EACH
STATEMENT, but I cannot find any example involving the pseudo table NEW
(or OLD) in the trigger function SQL statement.
Let me give you a real life example.
Suppose we have the above table :
CREATE TABLE T_PRODUIT
Hello,
i'm using a pl/sql procedure and I prevent inserting duplicate tuples using
an exception for example:
BEGIN
INSERT INTO "Triples"(id, subject, predicate, "object")
VALUES (id, sub_i, pred_i, obj_i);
* EXCEPTION WHEN unique_violation THEN
--do som
Hello,
I am trying to migrate from postgresql-8.1.11-1.el5_1.1 (i386) to
postgresql-8.3.4-1.fc10.x86_64. But I can not.
Database uses ltree and tsearch and the problem seems to be this.
I am using,
pg_dump in first computer and psql in second computer to execute script.
First error:
psql:inform
Does anyone think this is the correct behavior?
adaps_db=# select * from upc_usage order by 1 ;
usage
--
53E
ABC
CYPHER
_GENERAL
H66
HAWK
_JOE
RSRA
S61
S65
S70
S76
S92
XWING
(14 rows)
It appears to be ignoring the underscore!
Database has LATIN1 encoding and was recently
Here is a Perl function a friend of mine wrote to help with this...
Problems I ran into were errors because of NON NUMERIC Characters in the
String...
Casting would fail
It doesn't catch all cases but it has been quite helpful to me
$x = $_[0];
$x =~ s/^[^-\d\.]*//;
$x =~ s/\,//g;
$x = $x * 1;
No response from the pgsql-admin list so I though I would try cross
posting here:
pgsql-sql@postgresql.org
pgsql-general@postgresql.org
I just know I am not the first to try and do this
Jim
*
Can anyone point me in a direction that may help me populate in real
time a table
eters available.
http://developer.postgresql.org/docs/postgres/plpgsql.html#PLPGSQL-
OVERVIEW
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Open Source Solutions. Optimized Web Development.
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-63
What indexes do you have on these tables?And have you ANALYZEd all three recently? --Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCOpen Source Solutions. Optimized Web Development.http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-515061
hit upon one of your options.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
On Sep 29, 2005, at 1:16 PM, Mario Spl
ccommodate arbitrary lists, I can't imagine how you'd do it
without using a PL, but you could still build your IN clause
programmatically.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th
Well, browse through this list:
http://www.postgresql.org/docs/books/
I can't make any recommendations, as I am fairly familiar with the
online documentation, which, when supported by the community, seems
to be pretty good.
--
Thomas F. O'Connell
Co-Founder, Information Architec
Matt,
In PostgreSQL 8.0.3, I see:
postgres=# select nullif( '1', '' );
nullif
1
(1 row)
postgres=# select nullif( '', '' ) is null;
?column?
--
t
(1 row)
What behavior are you expecting?
--
Thomas F. O'Connell
Co-Founder, Inform
y that you
know to be safe or passing in safe values generated elsewhere in the
application, you're less likely to need to quote your variables
explicitly or to build queries dynamically.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source
Is there a reason not to build it in as a sub-query?E.g., if you have a function get_count( int ): SELECT count(b_column)FROM some_tableWHERE some_field_1 in ( SELECT a_column FROM a_table WHERE some_condition)AND some_field_2 = $2; --Thomas F. O'ConnellCo-Founder, Inform
This doesn't give me quite what I'm looking for because I need there
to be only one of each possible value of seq2 to be returned for each
value of fkey.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.si
ayer_idThis seems like it works, but it is abominably slow, running on the order of days across 1.5 million rows rather than the seconds (or preferably milliseconds) I'd prefer.I have this annoying feeling that I'm overlooking a reasonably efficient in-between query.-- Thomas F. O'Conne
le;
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql';
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashvi
You should be able to use the CURRENT_DATE function in place of sysdate.
You might need to cast the 1 explicitly to an interval.
As in:
CURRENT_DATE - '1 day'::interval
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Aven
Yup. Got it. Wasn't thinking clearly about what expression meant.
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb 23, 2005, at 2:33 PM, Bruno Wolff
As far as I know, you didn't post your actual table definitions (or
full queries) earlier, so I'm not exactly sure what you mean.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 372
This sounds like a perfect candidate for a LEFT OUTER JOIN. See:
http://www.postgresql.org/docs/7.4/static/queries-table-
expressions.html#QUERIES-FROM
Yours would looks something like:
SELECT *
FROM ...
LEFT JOIN candidate AS c
ON <...>.omcr_id = c.omcr_id
AND ...
-tfo
--
Thomas F. O'
Have you looked at the documentation on triggers?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jan 23, 2005, at 11:23 AM, Klaus W. wrote:
Hi!
Because i got no answ
her tables.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 17, 2004, at 8:52 AM, Stephan Szabo wrote:
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
Hmm. I wa
?
Furthermore, is there a better way of performing this sort of operation
in postgres (or just in SQL in general)?
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260
This is untested, but it might be enough to get you started:
SELECT namecounter
FROM name n
WHERE NOT EXISTS (
SELECT 1
FROM name
WHERE hh > 0
AND famnu = n.famnu
)
GROUP BY famnu
HAVING birthdate = min( birthdate );
What I'm trying to do here is grab all families th
I think the OUTER JOIN version is probably more efficient, but EXPLAIN
would tell you.
-tfo
On Oct 8, 2004, at 8:02 AM, CHRIS HOOVER wrote:
Just curious, what is wrong with the first way of coding the solution?
--( Forwarded letter 1 follows )-
Date: Fri, 8 Oct
A query that should get the job done is:
SELECT registration_id
FROM registrations r
WHERE NOT EXISTS (
SELECT 1
FROM receipts
WHERE registration_id = r.registration_id
);
There might be a more efficient version with JOINs that don't require a
subquery, but this should get
One way to do this is to use the column_info database handle method.
Here's a little perl script that accepts a table name as an argument
and returns the column names:
#!/usr/bin/perl
use DBI;
use strict;
my( $database, $table ) = @ARGV;
my $dbh = DBI->connect( "dbi:Pg:dbname=$database", 'postgre
I was afraid someone was going to ask that :)
Okay, I'll do my best at explaining where I'm coming from
I'm working on a mapping application it is user-configurable. What this means (as it pertains to this disucssion) is that the through a configuration file, the user is able to define the ru
Hello,
I already tried this same basic question with no response maybe I was too wordy. So here it is simplified what's the best way to write this query? I'm open to using stored procedures, but even then I don't know how I would conditionally populate a resultset (refcursor). Notice th
Hello,
This is probably an odd request, but I have my reasons :) Basically, what I need to do is return *multiple* values each for a number of criteria. Imagine being able to return multiple values for each CASE statement... that's what I'm trying to do. I can solve this with subqueries, but as
id int2
foo_id int2 references foo( id )
);
imagine that the tables are populated.
now, consider the query
select b.foo_id
from bar b
where b.id =
and
exists(
select *
from foo f
where b.foo_id = f.id
and b.id =
);
now consider the same query with "selec
fetch the n
desired rows
example
SELECT * FROM test_table LIMIT 100 OFFSET 100;
this will skip to row number 100 and the fetch the next 100 rows
[]´s
-
Joao Pedro M. F. Monoo
Infortrade Information Systems
#183816 Linux Registered User
Slackware 7.1 r
from the docs, i know that if you have two tables, foo and bar, you can
write a query such as
select f.bling
from foo f
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
);
what i'm wondering is if you need that subquery in two pl
template1 and on project
database too. Thank's for help!
[]'s
Edipo Elder F. de Melo
We using PostgreSQL 7.0.3. under Conectiva Linux 5.0. I'm
sending the ASCII script attached.
PS.: Sara, sorry if you receive more than one copy of this mensage, but
I was uncertain about you correct e-mail address.
Thank's and "Feliz Natal" to all,
[]'s
Edipo Elder F. de Melo
TESTE.ZIP
4ADT (I live in Brazil, Natal city).
>
>Hm. On a RedHat Linux box, with pgsql 7.0.2:
>
>regression=# show TimeZone ;
>NOTICE: Time zone is AST4ADT
>SHOW VARIABLE
>regression=# select '2000-01-01'::date::timestamp;
>?column?
>----
> 2000-01-01 00:00:00-04
[]'s
Edipo Elder F. de Melo
I'm using OS/2 with Fix Pack 12, Postgresql 7.0.2.
Can any one explain this?
[]'s
Edipo Elder F. de Melo
I'm doing something wrong? if the date is '2000-04-01', the
"frac-date-select" returns day: 31, month: 03, year 2000. More over:
'2000-01-01', splited: 31, 12, 1999.
[]'s
Edipo Elder F. de Melo
46 matches
Mail list logo
