[SQL] postgre2postgre
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:informatica.sql:24: ERROR: no se encuentra la función «gtsvector_in» en el archivo «/usr/lib64/pgsql/tsearch2.so» Anyone know any way to migrate? -- -- Publicidad http://www.pas-world.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Returning Multiple Values from CASE statement?
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 you'll see it's *really* ugly, and I'm sure there's a better way (performance is key). The other way obviously is to repeat the CASE statement for each value I want to return but that seems inefficient also. So I was wondering if someone could take a look at my query and slap me up side the head and set me straight? Here's the kind of query that would *like* to be able to do. select (case when column1 = column2 then column5,column6) as alias5,alias6, (case when column3 = column4 then column7,column8) as alias7,alias8from mytable; and here's the only ugly way I know how to do it.. select a.*, (case a.alias2 = true then b.column5) as alias5, (case a.alias2 = true then b.column6) as alias6, (case a.alias4 = true then b.column7) as alias7, (case a.alias4 = true then b.column8) as alias8from (select (case when column1 = column2 then true else false end) as alias2, (case when column3 = column4 then true else false end) as alias4 from mytable) a, mytable bwhere a.id = b.id; Thanks!! Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] SQL Help
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 that in the first three cases, the _expression_ is the exact same, only the return value is different. This seems inefficient select (case when column1 = column2 then column3 end) as alias1, (case when column1 = column2 then column4 end) as alias2, (case when column1 = column2 then column5 end) as alias3, (case when column6 = column7 then column8 end) as alias4from mytable; Any ideas? Thanks! Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: [SQL] CASE returning multiple values (was SQL Help)
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 rules that the application will use to determine which geometries *and* attributes to pull from PG at various scales. These 'rules' that the user defines also contain other specifics such as how to symbolize the geometry, how to label the geometry etc. All of these parameters can either be hard coded by the user into the configuration file, or they can define an _expression_ that will be used to dynamically pull it from the database. On top of all of this, we have two more levels of queries. So... at the very top level, the user can define an _expression_ that will determine that everything queried from this table will match these criteria... this is my WHERE clause. Then below this level, various rules can be defined... each rule can have another definition that evaluates into a SQL _expression_. Now, I could make each rule an entirely separate query, but for one, they all share the exact same top level WHERE clause, and two, there could potentially be many many rules which I would think would cause severe performance issues. Let me give you an example... Let's say we're mapping cities of the United States based on population... In other words, I want to symbolize the cities on the map based on population (larger symbol for larger populations, smaller symbol for smaller populations, etc). I also want to show the city names of the larger cities *only*. So, what the client application (client to PostgreSQL) needs is; the city location, which rules evaluate to true, and the city names of those larger cities (defined by a rule). We have a table of cities of the world. So the top level filter (that all rules will share) is, "COUNTRY = 'USA'". Rule 1 says that cities with a population over 1,000,000 will have a large symbol and be labeled with the city name. So the sql could look like this... select longitude, latitude, city_name from city where country = 'USA' and population > 100; ... seems easy enough, but remember we can have an infinite number of rules (not really inifinite, but you get the point). So Rule2 says that cities with a population under 1,000,000 will have a small symbol (note, we do not care about the city name here). So, by itself, the SQL could look like this... select longitude, latitude from city where country = 'USA' and population < 100; Okay, for this simple example, I would have no problem doing two different queries (this example is extremely simplified compared to what is possible/likely). But what if the user wanted to give a different symbol for every population in 100,000 increments? If our range of populations was 100,000 to 5,000,000 that would be 50 queries! Not only would it be 50 queries, but it would be 50 queries using a nearly identical WHERE clause. So I thought it would be more efficient to combine the queries into something like the following... select longitude, latitutde, (case when population > 100 then true else false end) as rule1, (case when population > 100 then city_name end) as label1, (case when population < 100 then true else false end) as rule2 where country = 'USA' ; I could just only concern the SQL with the boolean values for the rules, and return all city names, and let the application simply discard them, but that seems like not a good thing to do for very large resultsets (and again, this is overly simplified, we could have many such columns full of uncessary data being returned). And by the way, that query cannot be written as something like... (case when population > 100 then 'rule1' when population < 100 then 'rule2' end) as rules ... because the rules are NOT mutually exclusive, there can many positives. Anyway, hopefully I didn't leave anything important out. It sounds like there's no obvious solution to avoiding multiple evaluations of the test expressions. The rules are relatively static once the config files are read in, so I could conceivably create stored procedures with a bunch of IF statements at that time. However, I'm not sure if in PG there is a way to dynamically populate the resulting recordset on the fly. I can think of 10 different ways accomplish what I'm trying to do, but hopefully someone has some ideas on what would be the best performing. Sorry if it's information overload, but you tried to answer my questions, so I thought I should at least try to answer yours :) Any thoughts much appreciated. You could write a set returning function, but you'd just end up doing the same thing. Can you explain what it is you're trying to acheive - real fields/schemas etc?-- Richard Huxton Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] Get id of a tuple using exception
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 something. *In some cases I have interest in getting the id of the tuple that was already in the table when the exception is triggered. Is there a way for the EXCEPTION to return that id instead of using a select to know wich was the id of the triple already existing in the table? Thanks, Filipe
[SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values
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. My task table contains more tasks completed than the one included in the BINGO challenge. SELECT task, CASE WHEN task='Task27' THEN 'R1C1' WHEN task='Task32' THEN 'R1C2' ... WHEN task='Task94' THEN 'R5C5' END AS bingo FROM tasks WHERE bingo IS NOT NULL; This query will retrieve all tasks related to the BINGO that I have completed and present them in a simple list. I would like to arrange the tasks as a BINGO card; so that I can easily see my progress on various rows & columns working toward a BINGO. Any suggestions? BONUS points will be awarded if the query displays a row with 5 NULL values if no tasks are completed in that row.
[SQL] using top-level aggregate values in subqueries
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 places in a query if there's some way to cache it at the top level. for instance, if i were shooting for select f.id from foo f, ola o where f.id = ( select max( b.id ) from bar b where b.bling = "i kiss you!" ) and o.id != ( select max( b.id ) from bar b where b.bling = "i kiss you!" ) is there some way to grab the value returned by the subquery in the superquery and use the value instead of running the subquery twice? i'm not looking for an optimized version of my example (unless it answers the question of the bigger picture); i'd rather know if there's some way to access top-level aggregates from within a subquery. or find out that postgres is smart enough to recognize bits of SQL in a query that are identical and do its own internal caching. generically stated, my question is: is there some way, without writing a function, to calculate an aggregate value in a query that is used in multiple subqueries without needing to run an aggregating query multiple times? i know it only amounts to syntactic sugar, but, as such, it would be pretty sweet. thanks. -tfo ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] the optimizer and exists
i think i might've stumbled across a tiny defect in the optimizer.
unfortunately, i haven't the knowledge of the code to know where to
begin looking at how to address this problem.
anyway, consider the following:
create table foo(
id int2
);
create table bar(
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 "select " in place of "select
*" in the EXISTS subquery.
explain analyze indicates that the constant version always runs a little
bit faster. shouldn't the optimizer be able to determine that it isn't
necessary actually to read a row in the case of EXISTS? i'm assuming
that's where the overhead is coming into play.
i realize this is minutiae in comparison to other aspects of
development, but it is another small performance boost that could be
added since i imagine many people, myself included, find it more natural
to throw in "select *" rather than "select ".
i didn't see this on the current lists or TODO, but if it's a dupe, i
apologize for the noise. i also apologize for not being able to patch
it, myself!
-tfo
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Extracting fieldnames from a TABLE
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", 'postgres' );
my $sth = $dbh->column_info( undef, 'public', $table, '%' );
$sth->execute;
while( my @row = $sth->fetchrow_array ) {
print join( ' ', $row[ 3 ] ), "\n";
}
$sth->finish;
$dbh->disconnect;
This could be easily modified to stick the contents of $row[ 3 ] into
an array. You'd have to modify the user and schema as appropriate for
your database.
The fourth parameter to column_info is a wildcard so you get everything.
-tfo
On Sep 1, 2004, at 10:14 AM, Erik Wasser wrote:
Hi community,
I would like to retrieve all the fieldnames of a given table. In the
perl module Tie::DBI[1] i found the following fragment:
$dbh->prepare("LISTFIELDS $table");
in the case the DB supports this (Tie::DBI thinks so for Pg) or the
alternative is:
$dbh->prepare("SELECT * FROM $table WHERE 0=1");
The first one doesn't work in my PostgreSQL 7.4.3:
% LISTFIELDS foobar;
ERROR: syntax error at or near "LISTFIELDS" at character 1
%
and the seconds one looks ugly. Is there a solution for the problem?
Greetings
[1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm
--
So long... Fuzz
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL confusion
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 that don't have a head of household, group them by family, and get only the namecounter corresponding to the minimum birthdate for that family. If I recall, I've had some trouble using HAVING with min/max in ways that seem intuitive to me, but this might help get you started. -tfo On Oct 9, 2004, at 3:39 PM, Andrew Ward wrote: I'm trying to figure out how to do a particular query, and I'm beating my head against a wall. Here's my situation: I'm running postgres 7.3.2 on linux, and making my requests from Perl scripts using DBD::Pg. My table structure is as follows (irrelevant cols removed) CREATE TABLE name ( namecounter integer NOT NULL, firstmiddle character varying(64) NOT NULL, lastname character varying(64) NOT NULL, birthdate date, hh smallint, famnu integer, ); Each row represents a person with a unique namecounter. Families share a famnu, and usually one person in a family is marked as head of household (hh>0), with everyone else hh=0. However, there are a few families with nobody marked as hh, and I'd like to elect one by age. The query I'm trying to do is to pull one person from each household, either the head of household if available, or the eldest if not. I want them sorted by last name, so I'd prefer to find them all in one query, no matter how ugly and nested it has to be. I can pull the list with hh>0 easily enough, but I'm not sure how to pull out the others. I realize that this could be done through some looping in the Perl script, but I'd like to avoid pulling the whole list into memory in case the list gets long. My preference is to just handle one record at a time in Perl if possible. Help? Andrew Ward [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] help on a query
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 you started. -tfo On Oct 7, 2004, at 10:03 PM, Michelle Murrain wrote: Hi all, This is one of those things I know I should know, but it's not coming to me. It's probably really simple. I have two related tables, registrations and receipts, related by the field registration_id. So registrations looks kinda like: registration_id bigint (primary key) foo varchar(10) bar varchar(20) and receipts looks like: receipt_id bigint (primary key) registration_id bigint (foreign key) amount float baz varchar(10) If someone has paid, there is a row in the receipts table for that registration ID#. I need to find a list of the registration IDs that *don't* have an entry in the receipts table. Thanks in advance!!! -- .Michelle -- Michelle Murrain mmurrain at dbdes dot com 413-222-6350 ph 617-889-0929 ph 952-674-7253 fax <--- new Page: [EMAIL PROTECTED] AIM:pearlbear0 ICQ:129250575 Skype: pearlbear Jabber: [EMAIL PROTECTED] "I see all the different religious traditions as paths for the development of inner peace, which is the true foundation of world peace. These ancient traditions come to us as a gift from our common past. Will we continue to cherish it as a gift and hand it over to the future generations as a legacy of our shared desire for peace?" - His Holiness the Dalai Lama ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help on a query
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 2004 08:44:23 +0400 To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: Re: [SQL] help on a query On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: 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 ); Don't, PLEASE, don't !!! drive this way : SELECT r.registration_id FROM registrations AS r LEFT OUTER JOIN receipts AS rec ON rec.registration_id = r.registration_id WHERE rec.registration_id IS NULL; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Counting Distinct Records
I am wondering whether the following two forms of SELECT statements are logically equivalent: SELECT COUNT( DISTINCT table.column ) ... and SELECT DISTINCT COUNT( * ) ... If they are the same, then why is the latter query much slower in postgres when applied to the same FROM and WHERE clauses? 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-0005 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Counting Distinct Records
The specific problem I'm trying to solve involves a user table with some history. Something like this: create table user_history ( user_id int event_time_stamp timestamp ); I'd like to be able to count the distinct user_ids in this table, even if it were joined to other 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 was more interested in using COUNT( * ) than DISTINCT *. I want a count of all rows, but I want to be able to specify which columns are distinct. I'm now a bit confused about exactly what you're looking for in the end. Can you give a short example? That's definitely an interesting approach, but testing doesn't show it to be appreciably faster. If I do a DISTINCT *, postgres will attempt to guarantee that there are no duplicate values across all columns rather than a subset of columns? Is that right? It guarantees one output row for each distinct set of column values across all columns. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to update dependent tables AND design considerations
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 answer in pgsql.novice, i'll try it here. But maybe it
was even too easy for .novice?
Lets say there are three tables:
CREATE TABLE c (id SERIAL, data VARCHAR(20));
CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data
VARCHAR(20));
CREATE TABLE a (id SERIAL, b_id INTEGER REFERENCES b, data
VARCHAR(20));
Now i have to insert some data into this table structure.
In my old mysql days i would have inserted into c, look after
the id, insert it into b, look after the id, insert into a...
Of course this could be done here too, but i think it's the worst
case.
Another idea was to create a VIEW which is updatable and
insertable by RULES. But this solution simply shifts the Problem
to the rule definition.
Next idea was using a pl/pgsql function. But still the same
problem: how to do?
Idea:
INSERT INTO TABLE c VALUES (DEFAULT, 'asdfasdfasfd');
INSERT INTO TABLE b VALUES (DEFAULT, currval('c_id_seq'),
'asdfasfasf');
Good Idea? Is this the normal way?
But what about this case:
There is the following table:
CREATE TABLE old (data_a VARCHAR(20), data_b VARCHAR(20), data_c
VARCHAR(20);
containig Data that should be incorporated to the above mentioned
table structure. With my solution i have to read the data
with an application and split it into subsequent INSERT
statements. This could not be a good Idea.
Of course i could define the already mentioned VIEW, write
some rules for updating and inserting and INSERT the data
from old table into the VIEW. But is this the
usual way?
Isn't there something available like an INSERT to multiple
tables?
With real updates this should be easier, because the
datasets are already existing and can be joined
within FROM of the UPDATE Statement. But what about
this case:
I get a dataset: ('data a', 'data b', 'data c'). But
the corresponding subset in table a and b already exists.
Do i have to check in my application wheather the
Dataset in table c exists or not and do an seperate
INSERT myself? What about if the Data is coming from
another table and not from an application?
Should i make my UPDATE rule of a possible VIEW
doing this JOB?
Unfortunately the available tutorials cover only quite
simple cases. But my projekt may have up to five or more
dependency layers. It would be nice to have a
readable, manageable and extensible solution.
But i'm afraid my ideas so far are not.
Thank you in advance!
Klaus
---(end of
broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] plpgsql functions and NULLs
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'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 30, 2005, at 1:41 PM, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; IF FOUND ... Is there a way to do the lookup in one statement?? This could get ugly quick. I'm using v7.4. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] plpgsql functions and NULLs
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 37203-6320 615-260-0005 On Jan 31, 2005, at 3:06 PM, Don Drake wrote: My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
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 III wrote:
On Wed, Feb 23, 2005 at 13:54:50 -0600,
"Thomas F.O'Connell" <[EMAIL PROTECTED]> wrote:
How would one know from the reference material that it is possible to
include IS NOT NULL in an ORDER BY clause?
Similarly, other than the FAQ, I've never been able to tell from the
SELECT documentation why ORDER BY random() works.
From the SELECT command documentation:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
Notice that for ORDER BY you can supply an expression. That should be
a big
clue why you can use IS NOT NULL and random() in an ORDER BY clause.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] query
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 Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 17, 2005, at 4:57 AM, Chandan_Kumaraiah wrote: Hi, In oracle we write sysdate-1 For example,we write a query (select * from table1 where created_date>=sysdate-1).Whats its equivalent in postgre? Chandan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] DROP IF ...
The following function takes a table name as a parameter and drops
the table and returns true if there are zero rows (otherwise, it
returns false):
CREATE OR REPLACE FUNCTION dropzero( varchar ) RETURNS bool AS '
DECLARE
zerotable ALIAS FOR $1;
zerocurs refcursor;
rowcount int;
BEGIN
OPEN zerocurs FOR EXECUTE ''SELECT COUNT( * ) FROM '' ||
zerotable;
FETCH zerocurs INTO rowcount;
CLOSE zerocurs;
IF rowcount = 0 THEN
EXECUTE ''DROP TABLE '' || zerotable;
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
Nashville, TN 37203-6320
615-260-0005
On May 24, 2005, at 12:44 PM, CG wrote:
PostgreSQL 7.4 ...
I'm trying to find a way to drop a table via SQL if it contains 0
rows. Here
was my thought:
CREATE OR REPLACE FUNCTION dropif(text, bool)
RETURNS bool AS
'DECLARE
tblname ALIAS FOR $1;
condition ALIAS FOR $2;
BEGIN
IF (condition) THEN
EXECUTE(\'DROP TABLE "\' || tblname || \'";\');
END IF;
RETURN \'t\'::bool;
END;'
LANGUAGE 'plpgsql' VOLATILE;
... then ...
BEGIN;
CREATE TABLE testtbl (i int4);
SELECT dropif('testtbl',(SELECT count(*)::int4 FROM testtbl) = 0);
ERROR: relation 286000108 is still open
CONTEXT: PL/pgSQL function "dropif" line 6 at execute statement
... It makes sense. The select is still open when the table is
going to be
dropped. I need a different strategy.
Please advise!
CG
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Grouping Too Closely
I have a table that looks like this:CREATE TABLE my_table ( pkey serial PRIMARY KEY, fkey int NOT NULL REFERENCES my_other_table( pkey ), uid int NOT NULL REFERENCES user( pkey ), seq1 int, seq2 int);Basically, for each fkey that exists in my_table, there is a sequence represented by seq1, which covers every record corresponding to a given fkey. Then there is a subset of records covered by seq2, which increments over the course of a given fkey, but might span multiple records.E.g.,pkey | fkey | uid | seq1 | seq2---1 | 1 | 1 | 1 | 12 | 1 | 2 | 2 | 1...What I'd like to be able to do is select all records corresponding to the minimum value of seq1 for each value of seq2 corresponding to a given fkey (with a lower bound on the value of seq2).My first attempt looked like this:SELECT fkey, uid, seq2FROM my_tableWHERE seq2 > 2GROUP BY fkey, seq2, uid, seq1HAVING seq1 = min( seq1 )but this groups too closely to return the desired results.My next attempt looked like this (where I use the shorthand for min in the subquery):SELECT fkey, uid, seq2FROM my_table AS mt1WHERE mt1.seq2 > 2AND ( mt1.uid, hh1.seq1 ) IN ( SELECT mt2.player_id, mt2.order_no FROM my_table AS mt2 WHERE mt2.fkey = mt1.fkey AND mt2.seq2 = mt1.seq2 GROUP BY mt2.seq1, mt2.uid ORDER BY mt2.seq1 ASC LIMIT 1)GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_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'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-260-0005
Re: [SQL] Grouping Too Closely
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.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jun 24, 2005, at 11:22 PM, Greg Sabino Mullane wrote: What I'd like to be able to do is select all records corresponding to the minimum value of seq1 for each value of seq2 corresponding to a given fkey (with a lower bound on the value of seq2). I'm not sure how uid figures in, but would this do what you want?: SELECT fkey, uid, seq2, min(seq1) FROM my_table WHERE seq2 > 2 GROUP BY fkey, uid, seq2 ORDER BY 1,2,3; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sql function: using set as argument
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, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax) On Aug 10, 2005, at 9:30 AM, Akshay Mathur wrote: Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay ---Akshay MathurSMTS, Product VerificationAirTight Networks, Inc. (www.airtightnetworks.net)O: +91 20 2588 1555 ext 205F: +91 20 2588 1445
Re: [SQL] Numerical variables in pqsql statements
Michael, PL/pgSQL variable interpolation works similarly to that in other popular programming languages. If you have a statement -- whether it's PERFORM, SELECT INTO, or EXECUTE -- a variable will get interpolated during parsing if not escaped in a string. Per the documentation, dynamic values in dynamic queries require special handling since they might themselves contain quotes: http://www.postgresql.org/docs/8.0/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN But your temp table statement below is fine if you know that there will never be quotes in refer_num. And it would work similarly if you built it dynamically via string concatenation as a query to be executed with quote_literal escaping for refer_num. Typically, you need to protect yourself against user input to a function. If you're computing values in the function body 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: 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 Aug 21, 2005, at 7:43 PM, Michael Schmidt wrote: Folks, I'm sure this is dumb, but I'm a little confused about use of numerical variables in pqsql. It was my impression one had to use EXECUTE on a concatenated string including quote_literal() for the variable containing the value. This can be quite a bit of trouble. I just wrote a function that included the statement : CREATE TEMPORARY TABLE author_names AS SELECT ra.ref_auth_key, a.last_name, a.first_name FROM ref_auth ra INNER JOIN authors a ON (ra.author_num = a.author_key) WHERE ra.ref_num = refer_num; where refer_num is integer. This worked (surprisingly, to me). So, what is the rule regarding variables? Would this query work if I concatenated a string together, including quote_literal (refer_num) and then EXECUTEd it? Thanks and sorry to be so stupid. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] question
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, 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 Aug 24, 2005, at 12:05 AM, Matt A. wrote:
I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating level.
Lastly there is a null option for n/a. We use null for
n/a so it's excluded from the AVG() calculations.
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the "not a value"
accordingly.
Is there a workaround for this so it doesn't break our
rating system? We cannot always enter a value for a
integer column. Is there any other way to accomplish
this? Please help.
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Numerical variables in pqsql statements
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 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 Aug 29, 2005, at 4:05 PM, Michael Schmidt wrote: Mr. O'Connell, Thanks so much for the insights. Sorry about the basic nature of the question - perhaps a "PostgreSQL for Dummies" book would help me! Michael Schmidt ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Searching for results with an unknown amount of data
On Sep 2, 2005, at 2:40 PM, DownLoad X wrote: Hi, I've got two tables, A and B, the first one containing columns A_ID | info where A_ID is primary, so that this table stores various information about an object, and the second containing columns A_ID | property where property is an integer referring to a particular property that an object may possess. I've seperated these into two tables so that an object may have several (ie an unknown number of) properties. Now, I want to find all objects that have at most properties 1,2,3, say (so something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this -- can anyone help? Also, what if I want to find all the objects possessing properties 4,5,6, as well as possibly other things? I've done it with nested SELECTs (constructed programmatically), but I think this is quite slow and not a very pleasing solution. Obviously, both these things will need to be done for an arbitrary list of integers. Thanks, DL Without knowing more about your data or schema, couldn't you do something like SELECT A_ID, property FROM "B" WHERE property IN ( 1, 2, 3 ); To accommodate 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 Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] EXECUTE with SELECT INTO variable, or alternatives
Per the docs: "The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. There are two other ways to do it, however: one is to use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as described in Section 35.8.2." http://www.postgresql.org/docs/8.0/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN So you've already 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 Splivalo wrote: I can assign a value to a variable in several ways: myVar := (SELECT col FROM table WHERE somecondition...) myVar := col FROM table WHERE somecondtition... SELECT col INTO myVar FROM table WHERE somecondition How do I do any of the above using EXECUTE? I need to be able to assign the value to a variable, a value returned by a querry on a temporary table. So far I have found workaround like this: myRec record; FOR rec IN EXECUTE ''SELECT col FROM table WHERE somecondition'' LOOP myVar := rec END LOOP Obviously, the above SELECT query returns only one row. Still, if is realy inconvinient to have FOR...LOOP...END LOOP construct for assigning the value to a variable 'read' from the temporary table. Mario ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Strange join...maybe some improvements???
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-5150615-469-5151 (fax) On Oct 12, 2005, at 8:52 AM, Andy wrote: I have 3 tables: CREATE TABLE orders( id int4 SERIAL, id_ag int4, id_modell int4 ->> this is linked to the modell.id ) CREATE TABLE modell( id int4 SERIAL, id_hersteller int4) CREATE TABLE contactpartner( id int4 SERIAL, id_ag int4, ->> this is linked to order.id_ag or modell.id_hersteller id_user int4 ). I get a list of id_ag from the contactpartner which belongs to a user(AG_LIST). Then I have to selectselect/count all the data's from the order table that have the order.id_ag in the AG LIST or which have the modell.id_hersteller in the AG_LIST. I have this query: SELECT count(o.id) FROM orders oINNER JOIN modell m ON m.id=o.id_modellWHERE o.id_ag IN (SELECT id_ag FROM contactpartner cp WHERE id_user=15) OR m.id_hersteller IN (SELECT id_ag FROM contactpartner cp WHERE id_user=15) Aggregate (cost=7828.60..7828.60 rows=1 width=4) (actual time=1145.150..1145.151 rows=1 loops=1) -> Hash Join (cost=1689.64..7706.32 rows=48913 width=4) (actual time=153.059..1136.457 rows=9395 loops=1) Hash Cond: ("outer".id_modell = "inner".id) Join Filter: ((hashed subplan) OR (hashed subplan)) -> Seq Scan on orders o (cost=0.00..3129.17 rows=65217 width=12) (actual time=0.031..94.444 rows=65217 loops=1) -> Hash (cost=1218.07..1218.07 rows=66607 width=8) (actual time=151.211..151.211 rows=0 loops=1) -> Seq Scan on modell m (cost=0.00..1218.07 rows=66607 width=8) (actual time=0.044..87.154 rows=66607 loops=1) SubPlan -> Index Scan using contactpartner_id_user_idx on contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual time=0.010..0.018 rows=4 loops=1) Index Cond: (id_user = 15) -> Index Scan using contactpartner_id_user_idx on contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual time=0.092..0.116 rows=4 loops=1) Index Cond: (id_user = 15)Total runtime: 1145.689 ms I tried also this one: SELECT count(o.id) FROM orders oINNER JOIN modell m ON m.id=o.id_modellINNER JOIN contactpartner cp ON cp.id_user=15 AND (o.id_ag=cp.id_ag OR cp.id_ag=m.id_hersteller) Aggregate (cost=11658.63..11658.63 rows=1 width=4) (actual time=1691.570..1691.570 rows=1 loops=1) -> Nested Loop (cost=7752.40..11657.27 rows=542 width=4) (actual time=213.945..1683.515 rows=9416 loops=1) Join Filter: (("inner".id_ag = "outer".id_ag) OR ("outer".id_ag = "inner".id_hersteller)) -> Index Scan using contactpartner_id_user_idx on contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual time=0.108..0.188 rows=4 loops=1) Index Cond: (id_user = 15) -> Materialize (cost=7752.40..8723.57 rows=65217 width=12) (actual time=37.586..352.620 rows=65217 loops=4) -> Hash Join (cost=1677.59..7368.18 rows=65217 width=12) (actual time=150.220..1153.872 rows=65217 loops=1) Hash Cond: ("outer".id_modell = "inner".id) -> Seq Scan on orders o (cost=0.00..3129.17 rows=65217 width=12) (actual time=0.034..95.133 rows=65217 loops=1) -> Hash (cost=1218.07..1218.07 rows=66607 width=8) (actual time=149.961..149.961 rows=0 loops=1) -> Seq Scan on modell m (cost=0.00..1218.07 rows=66607 width=8) (actual time=0.032..86.378 rows=66607 loops=1)Total runtime: 1696.253 ms >>>>>>>> but this brings me some double information(the same o.id) in the situation in which the o.id_ag and m.id_hersteller are different, but still both in the AG_LIST. Is there any way to speed up this query??? Regards, Andy.
Re: [SQL] SQL Functions
On Oct 21, 2005, at 9:19 AM, [EMAIL PROTECTED] wrote: I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; In PostgreSQL 8.1, you'll have output parameters 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-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] FOR EACH STATEMENT trigger ?
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_DISPO_PDD (PRD_ID INT NOT NULL, PDD_BEGIN DATENOT NULL, PDD_ENDDATE, PDD_QUANTITY FLOAT NOT NULL); We want to never have more thant one PDD_END = NULL for the same PRD_ID. The assertion we can do is : ALTER TABLE T_PRODUIT_DISPO_PDD ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL CHECK (NOT EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PDD_FIN IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1)) Which is not supported by PG So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a FOR EACH ROW. Here is the code I try : CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS $code$ DECLARE n_rows integer; BEGIN SELECT COUNT(*) INTO n_rows WHERE EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PRD_ID IN(SELECT NEW.PRD_ID FROM NEW) AS T AND PDD_END IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1); IF ( n_rows IS NOT NULL ) THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple de colonne PRD_ID + PDD_FIN'; ROLLBACK TRANSACTION; END IF; RETURN NULL; END $code$ LANGUAGE 'plpgsql' VOLATILE Which produce an error ! Of course I can do that with a FOR EACH STATEMENT like this one : CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS $code$ DECLARE n_rows integer; BEGIN SELECT 1 INTO n_rows WHERE EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PRD_ID = NEW.PRD_ID AND PDD_FIN IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1); IF ( n_rows IS NOT NULL ) THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple de colonne PRD_ID + PDD_FIN'; ROLLBACK TRANSACTION; END IF; RETURN NULL; END $code$ LANGUAGE 'plpgsql' VOLATILE CREATE TRIGGER E_IU_PRD AFTER INSERT OR UPDATE ON T_PRODUIT_DISPO_PDD FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); But it is absolutly not that I Want Thanks -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Merge overlapping time-periods
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! Although I try for some time, I am not able to write an SQL-Query that can do the following: I have a very big table (let’s call it “mytable”) with information like this: ID BEG END 1 2000-01-01 2000-03-31 1 2000-04-01 2000-05-31 1 2000-04-15 2000-07-31 1 2000-09-01 2000-10-31 2 2000-02-01 2000-03-15 2 2000-01-15 2000-03-31 2 2000-04-01 2000-04-15 3 2000-06-01 2000-06-15 3 2000-07-01 2000-07-15 There’s an ID and time periods defined by a start value (BEG) and an end value (END) I want to merge all periods belonging to the same ID, iff their time periods are overlapping or in a direct sequence. Therefore the result should somehow look like this: ID BEG END 1 2000-01-01 2000-07-31 1 2000-09-01 2000-10-31 2 2000-01-15 2000-03-31 2 2000-04-01 2000-04-15 3 2000-06-01 2000-06-15 3 2000-07-01 2000-07-15 I tried using “WITH RECURSIVE” but I didn’t succeed. My server is PostgreSQL 8.4. Unfortunately I can’t do anything like update or install some fancy module… Thank you for your help! Best regards, Marcel Jira -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] [GENERAL] Creating rule for sliding data
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 NULL ); 2 - having 2 child table : CREATE TABLE T_MESURE_BEFORE2000_MSR ( CHECK ( MSR_DATE < DATE '2000-01-01') ) INHERITS (T_MESURE_MSR) CREATE TABLE T_MESURE_AFTER1999_MSR ( CHECK ( MSR_DATE >= DATE '2000-01-01') ) INHERITS (T_MESURE_MSR) THE QUESTION... How to make the proper rule for T_MESURE_MSR il a rox goes from 1998 to 2003 ? This one does not work : CREATE RULE R_U_MSR_BEFORE2000 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_DATE = NEW.MSR_DATE, MSR_MESURE = NEW.MSR_MESURE WHERE ( OLD.MSR_DATE < DATE '2000-01-01' ); -- rows does change partition (first INSERT NEWs then DELETE OLDs) INSERT INTO T_MESURE_MSR VALUES ( NEW.MSR_ID, NEW.MSR_DATE, NEW.MSR_MESURE ) WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); DELETE FROM T_MESURE_MSR WHERE MSR_ID = OLD.MSR_ID AND MSR_DATE = OLD.MSR_DATE AND MSR_MESURE = OLD.MSR_MESURE WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); And no more for this one : CREATE RULE R_U_MSR_BEFORE2000 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_DATE = NEW.MSR_DATE, MSR_MESURE = NEW.MSR_MESURE WHERE ( OLD.MSR_DATE < DATE '2000-01-01' ); -- rows does change partition (first INSERT the NEWs then DELETE the OLDs) INSERT INTO T_MESURE_MSR SELECT MSR_ID, MSR_DATE, MSR_MESURE FROM NEW WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); DELETE FROM T_MESURE_MSR WHERE (MSR_ID, MSR_DATE, MSR_MESURE) IN (SELECT MSR_ID, MSR_DATE, MSR_MESURE FROM OLD WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' )); Any idea ? Thanks -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Creating rule for sliding data
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_DATE = NEW.MSR_DATE, MSR_MESURE = NEW.MSR_MESURE WHERE ( OLD.MSR_DATE < DATE '2000-01-01' ); -- rows does change partition (first INSERT NEWs then DELETE OLDs) INSERT INTO T_MESURE_MSR SELECT MSR_ID, MSR_DATE, MSR_MESURE FROM NEW WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ; DELETE FROM T_MESURE_MSR WHERE MSR_ID = OLD.MSR_ID AND MSR_DATE = OLD.MSR_DATE AND MSR_MESURE = OLD.MSR_MESURE AND NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ); The problem is nowhere in the doc there is a mention where much more than one commande must be place into brackets ! A + -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MS-SQL Store Procedure to Postgresql Function
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, especially I really dont know how to set type to readonly in PostgreSQL. thanks ALTERPROCEDURE [dbo].[sp_DeleteUserData] @ACDetailsID dbo.ACdetailsID_type READONLY AS DECLARE@ID int begintry begintransaction DECLARE c_ACDetailsID CURSOR FOR SELECT id FROM @ACDetailsID OPEN c_ACDetailsID FETCH NEXT FROM c_ACDetailsID INTO @ID WHILE (@@FETCH_STATUS = 0) BEGIN delete from UserAccountDetails where UserDataAcountId=@ID delete from UserAC where UserDataAcountId=@ID FETCH NEXT FROM c_ACDetailsID INTO @ID End--end of while loop committransaction CLOSEc_ACDetailsID DEALLOCATEc_ACDetailsID endtry begincatch rollback transaction; print error_message( -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Natural sort order
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 from queries? I found a function at: http://2kan.tumblr.com/post/361326656/postgres-natural-ordering But it increases the query time from around 0.4msecs to 74msecs... Might be not much if occasional queries are made..but I use it for building up a hierarchical tree menu in a web application where every msecs counts (o; cheers richard -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Understanding Binary Data Type
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 with storing text and numerical characters into tables but my friend told me that databases can hold much more than just ASCI text. In so I've read up on some pages that describe the bytea data type: http://en.wikipedia.org/wiki/Binary_large_object http://www.postgresql.org/docs/9.1/static/datatype-binary.html So my question is can and in fact does PostgreSQL and most other RDBMS have the ability to store large binary files like photos, music, etc etc into an actual table? I'm guessing the data is dumped into the table but rather linked or parsed through the file system store path into the database itself, right? I would just like to know in a basic round about way how databases store and handle large files like .jpg or .png files& regardless how relative this term is, how common is it to use these files or 'bytea' data in tables? Actually SQL standard offer the ability to store large datafile directly on the filesystem, but under the control of the RDBMS (the OS cannot read, write or remove the file directly). This concept is based on the DATALINK SQL type. The main advantage is that the file stay exactly as a file and can be transactionned and backuped like all other dataobjects of the database. Some RDBMS like IBM DB2 or MS SQL Server does it (For SQL Server it is called FILESTREAM due to some main differences, but the concept is the same). Actually PG does not offer this feature. A + Thanks for any info! -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] date is not equals to dia/month/year: what's wrong?
Hi friends,
I was making a web app using Apache+PHP(4.0.1) and PostgreSQL
7.0.2, all running on OS/2 Warp FP12 (brazilian portuguese) and
discovered the following:
contas=> \d hipercard
Table "hipercard"
Attribute | Type |
Modifier
--+--+--
id_hipercard | integer | not null default
nextval('hipercard_id_hipercard_
seq'::text)
valor| money| not null
datacompra | date | not null
parcelas | integer | not null
descricao| varchar(240) |
Index: hipercard_pkey
contas=> select * from hipercard where id_hipercard=1;
id_hipercard | valor | datacompra | parcelas | descricao
--+-++--+---
1 | $100.00 | 2000-04-30 |1 |
(1 row)
contas=>select id_hipercard, valor, date_part('day', datacompra) as
"diaCompra", date_part('month', datacompra) as "mesCompra",
date_part('year', datacompra) as "anoCompra", parcelas, descricao from
hipercard where id_hipercard=1;
id_hipercard | valor | diaCompra | mesCompra | anoCompra | parcelas
|
descric
ao
--+-+---+---+---+--+
---
1 | $100.00 |29 | 4 | 2000 |1
|
(1 row)
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
[SQL] Date problem
Hi all, Hi all, Before all, a little (off-topic) comment: >It's been my experience that Win32 ODBC does not drop >connections until the database client is closed (e.g. MS >Access is shut down). "There is some things that only Micro$oft can do for you..." Ok. Let's work. I posted a mail before explaining a strange cituation if my Postgresql: when I use date_part() function to split day, month and year of a date type column, it returns one day before. In other words, '2000-01-01' returns day: 31, month:12, year: 1999. I'm using OS/2 with Fix Pack 12, Postgresql 7.0.2. Can any one explain this? []'s Edipo Elder F. de Melo
Re: [SQL] Date problem
On Fri, 29 Sep 2000 09:58:27 -0400, Tom Lane wrote: Returning from previous thread, i found a solution for my problem: "when everything fails, read the manual..." Reading the OS/2 command reference, it says that TZ has three parts: XXXCYYY. XXX - is the timezone name, C is the increment from UTC time, and YYY is the name of summertime. Ommiting the name of summertime (TZ=AST4), pgsql returned the correct select. >>> Not sure how this would apply to 2000-01-01, though. What timezone >>> are you in, anyway? >> OS/2 sets TZ=AST4ADT (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
[SQL] I can be a BUG?
Hi all, My wife is working on a university graduate project with PHP and PostgreSQL. She used ERWin to model the tables and exported the model into ASCII file with a set of SQL commands to create the tables (with a short corrections of types). Whe she tried to import into PostgreSQL, using pgsql and "\i" command, it create about 95% of the tables. The others wasn't created because she forgot change the type of some fields. To delete the tables, I sugest to drop the database and create it again. She droped the database and, when she created the new one, all tables was there. We repeat this process some times and it can't create a empty database. Even on a new, uncreated, with another name, database, all the tables of the problematic database was there. Can anyone explain if we are making somethink wrong or this is a BUG? 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
Re: [SQL] I can be a BUG?
On Fri, 01 Dec 2000 18:00:42 -0500, 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 ;-) That's it! I didn't see she created on template1 and on project database too. Thank's for help! []'s Edipo Elder F. de Melo
[SQL] audit table containing Select statements submitted
No response from the pgsql-admin list so I though I would try cross posting here: [email protected] [email protected] 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 that holds? Current_user Timestamp "The Select Statement Submitted by the User" I would like to easily determine who viewed what and when they viewed it. I have considered the fact that the result from SELECT yesterday may be different than the result set returned by the SAME SELECT statement today, but when used in conjunction with the INSERT, UPDATE, DELETE audit logging I have already created, the answers to who viewed, what and when would be readily available. I have been searching all morning and... The only thing I find on logging of Select statements is that the information can be held in the Log Files...if Logging is enabled. As I am only interested in the statements presented against certain tables... Turning on logging gives me more than I need or care to look through. I could write a script to parses the Log Files into a Database Table but would prefer to avoid enabling the file logging of statements if possible. Thanks for any reference or help you may be able to provide. Jim ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Returning String as Integer
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; return $x; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jorge Godoy Sent: Friday, May 05, 2006 4:52 PM To: [email protected] Subject: Re: [SQL] Returning String as Integer Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu: > Hi all, >I have a table with a column of type 'text'. It mainly contains > numbers. Is there any way to select a value from this column and return > it as an integer? testing=# select '123'::integer; int4 -- 123 (1 registro) testing=# -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Bizarre sort order
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 migrated from 8.0 to 8.3. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] random rows
Hi! > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. use the LIMIT clause example SELECT * FROM test_table LIMIT 100; you can also use the OFFSET clause to skip to n row and the 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 running 2.4.2 Linux Kernel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
