[GENERAL] Better way to process boolean query result in shell-like situations?
Hi, I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A typical example would be a Puppet Exec that creates a user only if it does not exist yet. But unfortunately psql always returns with the exit code 0 if the query was run without errors. In a shell script I can use a query that returns an empty string for failure and something else for success and then test that à la: | if [ -n "$(psql -Atc "[…]") ]; then echo Success.; fi but for example in Puppet this requires putting around '/bin/bash -c "[…]"' with yet another level of quoting. The best idea I had so far was to cause a runtime error (here with the logic reversed: If the user exists, psql re- turns failure, otherwise success): | [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'tim';"; echo $? | FEHLER: ungültige Eingabesyntax für ganze Zahl: »tim« | 1 | [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'does-not-exist';"; echo $? | usename | - | (0 rows) | 0 | [tim@passepartout ~]$ But this (in theory) could fail if usename could be con- verted to a number, and for example 'a'::INT will fail al- ways. Are there better ways? The environment I am most interested in is 9.3 on Ubuntu Trusty. Tim P. S.: I /can/ write providers or inline templates for Pup- pet in Ruby to deal with these questions; but here I am only looking for a solution that is more "univer- sal" and relies solely on psql or another utility that is already installed. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quick Date/Time Index Question
David Johnston pol...@yahoo.com wrote:
I have a database field that stores a timestamp to second+ precision;
however, I want to search against it only to day precision. If I leave the
field in second precision and try to WHERE field BETWEEN date0 AND date0 I
get no results (OK, fine) but then I cast the field to date WHERE
field::date BETWEEN date0 AND date0 and get the expected results. So now I
want to index field::date by I cannot create a functional index on
field::date OR CAST(field AS date) OR date_trunc('day',field) due to
either syntax (::) or non-IMMUTABLE function errors (cast; date_trunc).
Is there some other way to create an index on only the date portion of the
field? Is it even necessary since any index ordered on timestamp is also,
by definition, order on date as well?
Not necessarily a direct answer, but have you tried querying
WHERE field = date0 AND field (date0 + 1)?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quick Date/Time Index Question
Tom Lane t...@sss.pgh.pa.us wrote:
I have a database field that stores a timestamp to second+ precision;
however, I want to search against it only to day precision. If I leave the
field in second precision and try to WHERE field BETWEEN date0 AND date0 I
get no results (OK, fine) but then I cast the field to date WHERE
field::date BETWEEN date0 AND date0 and get the expected results.
Try WHERE field BETWEEN date0 AND date0+1. When comparing a date to a
timestamp, the date is considered to represent midnight of its day, so
you're testing for a zero-width range there.
Dare I to say it? :-) Not quite true:
| tim=# SELECT t
| tim-#FROM (VALUES ('2011-01-01 00:00:00'::TIMESTAMP),
| tim(# ('2011-01-02 00:00:00'::TIMESTAMP)) AS d (t)
| tim-#WHERE t BETWEEN '2011-01-01'::DATE AND ('2011-01-01'::DATE + 1);
| t
| -
| 2011-01-01 00:00:00
| 2011-01-02 00:00:00
| (2 Zeilen)
| tim=#
So you would have to assert that no timestamp will ever fall
on midnight.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to distribute quantity if same product is in multiple rows
(anonymous) wrote: It can be done in SQL: SUM(kogus) OVER (PARTITION BY toode ORDER BY ID) - kogus (*1) will give you the running sum of the product up to that row. You can then subtract that value from the delivered quantity to calculate the delivered quan- tity for the current row. But doing so automatically is probably bad. For example, if a user has a purchase order with one position of two pieces and one position of four, it is very likely that when a shipment of four pieces arrives, the latter position shall be marked as delivered. So I would leave the decision to the user. If four pieces arrived, first position of 2 pieces should marked as delivered. Second position of 4 pieces shoudl be marked as partialli delivered by setting undelivered quantity of this row to 2 How to use your suggestion for this ? Que? You take the query above, join it in the UPDATE and set the delivered quantity to the minimum of the ordered quantity and taitmkogus - sumkogus. How to implement this is PostgreSql 8.1,8.2, 8.3 ? [...] An example for calculating running sums without window functions can be found at URI:http://archives.postgresql.org/pgsql-sql/2001-07/msg00152.php. I would rather use a PL/pgSQL function in this case, though. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to distribute quantity if same product is in multiple rows
(anonymous) wrote: Order contains same product in multiple rows. I tried to calculate undelivered quantity using script below but it produces wrong result: delivered quantity is substracted from both rows, not distributed. How to distibute undelivered quantity according to row quantity in every row ? Can it be done using SQL or should SCAN loop in plpgsql used? [...] It can be done in SQL: SUM(kogus) OVER (PARTITION BY toode ORDER BY ID) - kogus (*1) will give you the running sum of the product up to that row. You can then subtract that value from the delivered quantity to calculate the delivered quan- tity for the current row. But doing so automatically is probably bad. For example, if a user has a purchase order with one position of two pieces and one position of four, it is very likely that when a shipment of four pieces arrives, the latter position shall be marked as delivered. So I would leave the decision to the user. Tim (*1) In PostgreSQL 9.0, you might be able to use ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING instead (untested). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] coalesce seems to give strange results
Richard Yen rich...@iparadigms.com wrote: Ah, I see what you mean. If there's no rows to return, then there's no coalesce-ing to do... That's right, /but/ if you use a sub-select, you can achieve something similar: | tim=# SELECT COALESCE((SELECT ROUND(EXTRACT(epoch FROM now() - query_start)) | tim(#FROM pg_stat_activity | tim(#WHERE current_query = 'IDLE in transaction'), | tim(# 0); | coalesce | -- | 0 | (1 Zeile) | tim=# sorry for the spam. [...] That wasn't spam :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help doing a CSV import
Craig Ringer cr...@postnewspapers.com.au wrote: I am in the process of moving a FoxPro based system to PostgreSQL. We have several tables that have memo fields which contain carriage returns and line feeds that I need to preserve. I thought if I converted these into the appropriate \r and \n codes that they would be imported as carriage returns and line feeds, but instead they are stored in the database as \r and \n. PostgreSQL doesn't process escapes in CSV import mode. You can reformat the data into the non-csv COPY format, which WILL process escapes. Or you can post-process it after import to expand them. Unfortunately PostgreSQL doesn't offer an option to process escapes when CSV mode COPY is requested. I posted a little Python script that reads CSV data and spits out COPY-friendly output a few days ago. It should be trivially adaptable to your needs, you'd just need to change the input dialect options. See the archives for the script. Another option is a small Perl script or something similar that connects to both the FoxPro and the PostgreSQL database and transfers the data with parameterized INSERT. The ad- vantage of this is that you have tight control of charsets, date formats, EOL conventions Co. and do not have to won- der whether this and that file is in this and that stage of the conversion process, the disadvantage is obviously that you lose any speed benefit of bulk COPY. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.4, XPath and name() function
Craig Ringer cr...@postnewspapers.com.au wrote:
[...]
I would like to get unit, but I just get an empty array ({}).
How can I get unit ?
AFAIK, this is not related to PostgreSQL, but inherent to
XPath in that it returns elements from the document that
fulfill the XPath expression *unchanged*.
My (poor) understanding is that XPath can be used as an
expression language and as a selector specifier language.
You can observe this in XSLT, where
xsl:template match=some-xpath/
uses XPath as a selector of elements, and:
xsl:value-of select=some-xpath/
uses XPath as an expression language, returning the output
of a given XPath expression or function not just the
matched/not matched status.
I found this very confusing myself when learning XSLT, and
it's possible I'm still misunderstanding it somewhat, but
it's clear that XPath can be used in more than one way.
[...]
Okay, that's maybe due to my XML socialization: An XPath ex-
pression to me has always been something you use in
xsl:template and xmllint --shell, as in xsl:value-of
Co. you have also access to other functions. It's even in
the specification :-):
| [...] The primary purpose
| of XPath is to address parts of an XML [XML] document. [...]
(from: URI:http://www.w3.org/TR/xpath/)
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help doing a CSV import
David Fetter da...@fetter.org wrote: [...] Another option is a small Perl script or something similar that connects to both the FoxPro and the PostgreSQL database and transfers the data with parameterized INSERT. The ad- vantage of this is that you have tight control of charsets, date formats, EOL conventions Co. and do not have to won- der whether this and that file is in this and that stage of the conversion process, the disadvantage is obviously that you lose any speed benefit of bulk COPY. You can do your transformations and hand the stream off to the COPY interface. See the pg_putcopydata() section of the DBD::Pg manual for examples. :) Eh, yes, but then you have to do all the escaping yourself and the simplicity of get values A, B, C from this connec- tion and pass it onto that goes away :-). Now if there'd be a pg_putcopydata(array of arrayrefs) ... :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.4, XPath and name() function
ced45 cedric.dup...@ifn.fr wrote:
I have trouble using XPath name() function in a XML field.
For example, when I execute the following query :
SELECT XPATH('name(/*)', XMLPARSE(DOCUMENT 'unitvalue/unit'))
I would like to get unit, but I just get an empty array ({}).
How can I get unit ?
AFAIK, this is not related to PostgreSQL, but inherent to
XPath in that it returns elements from the document that
fulfill the XPath expression *unchanged*.
So you will probably have to tackle your problem from an-
other angle.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz pagong...@gmail.com wrote: [...] * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. You mean archaeological sites that are not also natural sites? | SELECT * FROM site_arqeuology | WHERE id_site NOT IN | (SELECT id_site FROM site_natural); There are numerous other ways to do this, i. e., with LEFT JOIN, EXCEPT, etc. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz pagong...@gmail.com wrote: I was looking for an opinion on the actual table structure. :-) How should i build the data set? Is my second example ok? The first is the long version but i wanted to put together all the common fields to both types of sites and then (maybe) build tables to accomodate the specific fields so there are no empty columns on the table if i put everything in the same table. [...] That's way too fuzzy for good advice. Few people build data sets on archaeological sites, and even those probably don't use all the same structure. Tim (not telepathic) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz pagong...@gmail.com wrote:
Thank you for the opinion, Alban. The names are the least of my worries, i
typed them without thinking. And its portuguese. :-)
If, using that design, i had a different table with something like arq_types
{ id_arq_type, descr } that i could somehow connect to the generic table
(the one with the common fields), how could i go about querying those tables
for all the results of a specific type, for example? Or maybe i could add a
table_name field on that arq_type table?
Tim:
Dont consider this to be strictly for archeology, i mean in a generic sense
that if we have several data sets with common fields, if we could divide
them into several tables, one with common fields, and the others with fields
related to each type. My doubt was regarding how to have a separate table
with types that could be used to help query the common fields table and
fetch the corresponding table of that specific type. I understand its a bit
ungrateful for you guys to understand what i mean, considering that i am
probably making things even more confusing. :-)
[...]
I think the main problem is that you haven't stated your ex-
perience with SQL (or databases in general). Your questions
above (somehow connect to the generic table, go about
querying those tables) indicate that you seem to be lacking
basic knowledge. In this case, it won't help you, us or your
database to ask how to structure your data; you should read
a tutorial, and then choose a structure that you understand
and that works for you.
But at the moment, you're basically saying: I'd like to
build a vehicle; I haven't decided yet whether it should
take me to the next pub or the moon. Which screws should I
use?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] to_date function
Carlos Henrique Reimer carlos.rei...@opendb.com.br wrote: I've a Linux box running postgresql 8.2.17 and facing some strange results from the to_date function. As you can see in the following tests the problem occurs when the template used includes upper and lower case characters for the minute (Mi or mI). Am I using the incorrect syntax or is it a bug? [...] In general, the template patterns are case-sensitive (cf. month vs. Month vs. MONTH). So mI will probably be interpreted as a literal m and I meaning last digit of ISO year which isn't what you want. So use MI and be happy. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to remove a for-loop from programming language and put it into the query?
Pedro Zorzenon Neto pedro2...@mandic.com.br wrote:
[...]
So, I need to get a report of all diagnostics of all hardware on
december 25th.
(external programming language)
for ($i = 1; $i 500; $i++) {
// return me the most recent diag_value from a hardware_id $i
// at the desired timestamp
runquery(select diag_value from diagnose_logs where ts = '2009-12-25
23:59:59' and hardware_id = $i order by ts desc limit 1);
}
Currently I have an index on diagnose_logs(ts,hardware_id)
I have 3 milion registers of 500 different hardware_id.
The time to run 500 times this query is long... about 1 minute. When I
need a montly day-by-day report of 500 hardwares, it takes about half an
hour.
can I turn this for-loop into a single query to run in postgres?
Another month, another case for DISTINCT ON:
| SELECT DISTINCT ON (hardware_id)
| hardware_id, diag_value
| FROM diagnose_logs
| WHERE ts = '2009-12-25 23:59:59'
| ORDER BY hardware_id, ts DESC;
BTW, I'd prefer WHERE ts '2009-12-26' as otherwise you
don't catch a timestamp '2009-12-25 23:59:59.5' (not to
speak of leap seconds).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to balance rows across multiple tables
Mike Christensen m...@kitchenpc.com wrote: [...] Here's the catch: I want to DELETE any row (in either table) that has zero quantity, since I no longer need this data (plus I have a CHECK constraint on this value and require it to be positive anyway).. Is there actually a way to do this in a SQL function? Thanks! Probably. But I wouldn't take that path: If your function has a bug, you'll have /no/ record of what went wrong, but only some numbers that may or may not be correct. So I'd rather use a more elaborate table structure where you can track when you bought/planned to use/used what quan- tity of items, and then use SUM() Co. to report what you own and what you need to buy. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres table contents versioning
John Gage jsmg...@numericable.fr wrote: Is there an equivalent of svn/git etc. for the data in a database's tables? Can I set something up so that I can see what was in the table two days/months etc. ago? I realize that in the case of rapidly changing hundred million row tables this presents an impossible problem. The best kludge I can think of is copying the tables to a directory and git-ing the directory. If you're looking at this from a disaster recovery point of view, you should read up on PostgreSQL's PITR capabilities. If you need the information in your application, you should google for temporal databases on how to amend your table structures. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Filtering by tags
Anders Steinlein and...@steinlein.no wrote: No one with any response on this? [...] Insert a LEFT JOIN in the first subquery? Tim (too lazy to test :-)) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] loading many queries from a file
Szymon Guz mabew...@gmail.com wrote: I've got a file with many SQL queries, also some function definitions and so on. I'd like to load it to database, but using some library like JDBC/ODBC/DBI, not using the obvious psql. Do you know how I could load those many queries? Usually there could be loaded only one query, I saw that psql parses the file looking for the semicolon. Is there any other way than parsing the file and loading queries one by one? Is your file friendly or arbitrary? With DBI, you can exe- cute several commands in one do() call, but you cannot split one command across several calls. The PostgreSQL frontend/ backend protocol seems to allow that by a cursory look, but that's *very* far from JDBC/ODBC/DBI. If there are no guarantees on the format of your file, I would try to adapt psql's psqlscan.l Co. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Equivalent to use database in postgre
javijava welove.e.mu...@gmail.com wrote: i'm newby in postgre sql world. i need to know how to do a simple script that create a database,the y select it (in other languajes using USE) and after create tables with this database. How can I say use name_database on postgre sql? You must specify the database to use on connect; if you want to use psql for your script, you can use \c name_database à la: | CREATE DATABASE testdatabase; | \c testdatabase | CREATE TABLE testtable (testcolumn int); Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
Andrus kobrule...@hot.ee wrote:
Autogenerated select statement contains 0 .. n left joins:
SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';
This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.
Problem:
if there is no searched primary key row in ko database, select should also
return empty row.
To get this result I added right join:
SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';
but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.
How to force this statement to return one row always ?
It's a bit difficult to decipher what you're looking for
(what do you mean by empty row?), but you may want to try
something along the lines of:
| SELECT v.primarykey, ko.somecolumns
| FROM (VALUES ('someprimarykeyvalue')) AS v (primarykey)
| LEFT JOIN ko ON v.primarykey = ko.primarykey
| LEFT JOIN t1 ON t1.primarykey = ko.t1foreignkey
| [...]
| LEFT JOIN tn ON tn.primarykey = ko.tnforeignkey;
Whether that suits your needs depends very much on the data
structure and the tools you use.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
Brett Mc Bride brett.mcbr...@deakin.edu.au wrote: How about: SELECT * from ( SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue' UNION ALL SELECT default_value ) LIMIT 1; [...] ... with a proper ORDER BY clause. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
Brett Mc Bride brett.mcbr...@deakin.edu.au wrote: My understanding of UNION ALL is that it won't sort the rows...? [...] It doesn't, but that's not promised for every data set, ev- ery PostgreSQL version, every phase of the moon. To quote URI:http://www.postgresql.org/docs/8.4/interactive/queries-union.html: | UNION effectively appends the result of query2 to the result | of query1 (although there is no guarantee that this is the ^^^ | order in which the rows are actually returned). Furthermore, ^ | it eliminates duplicate rows from its result, in the same | way as DISTINCT, unless UNION ALL is used. SQL deals with (unordered) sets, and therefore any use of LIMIT without ORDER BY indicates a bug waiting to bite you when you least expect it. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to alias a table
Ivan Sergio Borgonovo m...@webthatworks.it wrote: I'm refactoring some code and I'll find helpful to be able to alias tables. What I'd like to do would be to refer to the same table with an alias in the code and later substitute the alias with an actual VIEW. Of course I could define a view as select * from original_table right from the start but I'm worried this may incur in some overhead I currently can't afford. Have you tried it? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
Bruce Momjian br...@momjian.us wrote: [...] + # single-page text + postgres.txt: postgres.html + $(LYNX) -force_html -dump -nolist -stdin $ $@ ^^ + [...] Isn't that unnecessary/wrong as the filename is supplied on the command line? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variables in SQL scripts
Machiel Richards machi...@rdc.co.za wrote: [...] I did some googling as well and found something I tested using a simple method: - I created an sql script to set the variable Name DB o \set DB dbname o \c :DB - I connected to postgresql using the postgres database and ran the sql script which seemed to work fine as it then connected me to the database. However will this method work with the above situation as well or are there other ways of doing this? [...] Why don't you try it? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some insight on the proper SQL would be appreciated
Andy Colson a...@squeakycode.net wrote: thanks very much Andy. Very elegant. I do need to presere the users that have5 entries though, so I think I can modify your function to do that as well. Oh, duh! because nothing is less than 1900-01-01... my date math sucks. It should probably return '2100-01-01' or something. If you're using stored functions, you could (and should be- cause the whole table is probably read anyhow) also code a function that reads all entries, resets a counter at the start and whenever the user changes, increments it on every row and returns all rows where the counter is less than five. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Andreas Kretschmer akretsch...@spamfence.net wrote:
I have a simple query like:
SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
The problem is that I need to retrieve the rows in the same order as the set
of
ids provided in the select statement. Can it be done?
No. The only way is:
select * from ... where id in (...) order by case when id=23 then 1,
case when id=56 then 2 end, case when id=2 then 3 end, ...
Or, quick 'n' dirty:
| SELECT * FROM customer
| WHERE id IN (23, 56, 2, 12, 10)
| ORDER BY POSITION(':' || id || ':' IN ':23:56:2:12:10:');
When using CASE, make sure you read the documentation to the
end: I stumbled upon CASE id WHEN 23 THEN 1 WHEN 56 THEN 2
WHEN [...] END only just recently by pure chance :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Sam Mason s...@samason.me.uk wrote: SELECT c.* FROM customer c, ( SELECT *, row_number() OVER () FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) WHERE c.id = x.val ORDER BY x.ord; Wow, that's really cool and a nice case for row_number(). Just thinking about it now; do SQL's semantics say it'll always do the right thing? PG does in a couple of quick tests (i.e. one where customer is a small table and PG prefers a seqscan and where it's larger and prefers an index scan) but I'm not sure if this could change. PostgreSQL's documentation on VALUES has at least no guaran- tee of the order of data. I'd prefer David's solution :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Stephen Frost sfr...@snowman.net wrote: Just thinking about it now; do SQL's semantics say it'll always do the right thing? PG does in a couple of quick tests (i.e. one where customer is a small table and PG prefers a seqscan and where it's larger and prefers an index scan) but I'm not sure if this could change. PostgreSQL's documentation on VALUES has at least no guaran- tee of the order of data. I'd prefer David's solution :-). Uhm, that's why there's an explicit ORDER BY.. I seriously doubt that would ever be violated. If there was an approach suggested which didn't include an ORDER BY *somewhere*, I'd be suspect of it. The query read: | SELECT c.* | FROM customer c, ( | SELECT *, row_number() OVER () | FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) | WHERE c.id = x.val | ORDER BY x.ord; and the question is whether VALUES (1), (2), (3) will al- ways return (1), then (2), then (3) and whether ROW_NUMBER() OVER () will always keep that order intact. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me write a query
Nikolas Everett nik9...@gmail.com wrote: Sorry. Here is the setup: CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT NOT NULL, timestamp TIMESTAMP); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '12 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval '11 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '10 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval '9 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '8 hours'); I want to write a query that spits out: state1 | timestamp + 1 | now() - interval '12 hours' 2 | now() - interval '9 hours' 1 | now() - interval '8 hours' Standard grouping destroys the third row so that's out. No grouping at all gives repeats of state1. Is this what partitioning is for? Partitioning usually means splitting data across several tables for faster access which is probably not what you want here. A simple solution would be to use LAG() and discard rows where the current value is equal to the preceding value: | SELECT state1, timestamp | FROM | (SELECT id, | state1, | state2, | LAG(state1) OVER (ORDER BY timestamp) AS prevstate1, | timestamp FROM test) AS SubQuery | WHERE state1 IS DISTINCT FROM prevstate1 | ORDER BY timestamp; Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
Hector Beyers hqbey...@gmail.com wrote: thank you for your replies yesterday on this topic. I have one more question though: Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do some security through obscurity. It is for research purposes. Maybe to save populate a table with 1000 rows, but the meta-data only knows of about 500 of them? Only on an export of a dump can you find the data again. [...] Before delving deeper into this, you should get your termi- nology straight: What do you mean by meta-data? What do you mean by export of a dump? What do you mean by without the meta data noticing? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse gor...@gmail.com wrote:
[...]
I would like to do;
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 18:00:00' order by timestamp desc limit 1;
name | timestamp | count
---+---+---
CNT-3 | 2010-05-23 17:53:18.58674 |43
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 19:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 18:53:19.151988 |50
(1 rad)
freescada= select * from counter_log_view where name='CNT-3' and timestamp
'2010-05-23 20:00:00' order by timestamp desc limit 1;
name | timestamp | count
---++---
CNT-3 | 2010-05-23 19:53:19.683514 |51
(1 rad)
In one query. Is this possible in *any* way?
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
Tim
P. S.: Naming columns timestamp and count will lead to
trouble :-).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse gor...@gmail.com wrote:
Yes timestamp and count - is not good names for columns...
I tried something like;
select name,date_trunc('hour',timestamp),timestamp,count from
counter_log_view where name='CNT-3' and timestamp '2010-05-23 20:00:00'
order by timestamp limit 10;
name | date_trunc | timestamp | count
---+-++---
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:43:17.411386 |23
CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:53:17.45934 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:03:17.489321 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:13:17.586089 |24
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:23:17.69116 |25
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:33:17.795955 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:43:17.89265 |28
CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:53:17.989268 |30
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:03:18.1447 |33
CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:13:18.199568 |35
(10 rader)
Seems promising... But then I would like to select only the last from
date_trunc...
Hm...
Why did you not use the query I posted:
[...]
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
[...]
Is copy paste too much effort?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to return an Int4 when subtracting dates/timestamps
Andre Lopes lopes80an...@gmail.com wrote: I need to return an Int4 when I do this king of select [code] select CURRENT_DATE - '2009-12-31' from tbl_sometable [/code] This select returns an Interval. How can I return an Integer? Like '138' That expression returning an interval would be contradictory to the documentation and a simple test: | tim=# SELECT CURRENT_DATE - '2009-12-31'; | ?column? | -- | 138 | (1 Zeile) | tim=# SELECT CURRENT_DATE::TIMESTAMP - '2009-12-31'; | ?column? | -- | 138 days | (1 Zeile) | tim=# So how far away from this kind of select is your actual query? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] last and/or first in a by group
Dino Vliet dino_vl...@yahoo.com wrote: I want to know if postgresql has facilities for getting the first and or the last in a by group. Suppose I have the following table: resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival station, the class of the reservation and the daysbeforedeparture and records like: xxx,NYC,BRA,C,80 xxx,NYC,BRA,M,75 xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,Z,40 zzz,NYC,LIS,J,39 I want to select only the most recent records being: xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,J,39 How would you accomplish this? I googled and found this: http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggregate-Fun-Whos-on-First-and-Whos-on-Last.html I hope there are alternatives because I don't have to program this myself. The other option is that I load this data in SAS and do it there but I only have SAS at work and want to do this at home. You can either use window functions in PostgreSQL 8.4 (cf. FIRST_VALUE() OVER (...)/LAST_VALUE() OVER (...)) or use the DISTINCT ON syntax: | tim=# SELECT DISTINCT ON (resnr) resnr, dep, arr, cls, dbd FROM TestTable ORDER BY resnr, dbd; | resnr | dep | arr | cls | dbd | ---+-+-+-+- | xxx | NYC | BRA | Q | 50 | yyy | WAS | LIS | T | 55 | zzz | NYC | LIS | J | 39 | (3 Zeilen) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about unique indexes
Alban Hertroys dal...@solfertje.student.utwente.nl wrote: [...] None of these solutions are pretty. It should be quite a common problem though, how do people normally solve this? Partial indexes? Doesn't look pretty either though: | tim=# \d DE_Postcodes | Tabelle »public.de_postcodes« | Spalte | Typ | Attribute | --+-+--- | postcode | integer | not null | city | text| not null | suffix | text| | street | text| not null | first| integer | | last | integer | | Indexe: | de_postcodes_key1 UNIQUE, btree (postcode, city, suffix, street, first, last) WHERE suffix IS NOT NULL AND first IS NOT NULL AND last IS NOT NULL | de_postcodes_key2 UNIQUE, btree (postcode, city, suffix, street, first) WHERE suffix IS NOT NULL AND first IS NOT NULL AND last IS NULL | de_postcodes_key3 UNIQUE, btree (postcode, city, suffix, street, last) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NOT NULL | de_postcodes_key4 UNIQUE, btree (postcode, city, suffix, street) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NULL | de_postcodes_key5 UNIQUE, btree (postcode, city, street, first, last) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NOT NULL | de_postcodes_key6 UNIQUE, btree (postcode, city, street, first) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NULL | de_postcodes_key7 UNIQUE, btree (postcode, city, street, last) WHERE suffix IS NULL AND first IS NULL AND last IS NOT NULL | de_postcodes_key8 UNIQUE, btree (postcode, city, street) WHERE suffix IS NULL AND first IS NULL AND last IS NULL | de_postcodes_postcodecity btree (postcode, city) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function to Table reference
(anonymous) wrote: Is there a way to find which functions are being used by table. Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in fnc_a and fnc_c, How can we find that ? can you please help? Basically, you can't. Functions are more or less black boxes to PostgreSQL. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
Peter Hunsberger peter.hunsber...@gmail.com wrote: I still don't get it. I do want a zero for the subversion_flags to be stored in the table. But it returned an error because it didn't like subversion_flags='' in the UPDATE SQL statement. subversion_flags | integer | not null default 0 Right. '' is not 0. the old version of pgsql converted '' to 0 for you, incorrectly. Now if you want 0 you need to say 0. Or, since you have the default, set it to null (Which may be what you thought you where doing?) Setting it to NULL does not set it to the default value. You have to use the keyword DEFAULT for that. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Domain for regular expressions?
Hi,
is there a proper domain for regular expressions? At the
moment I'm using:
| CREATE DOMAIN RegularExpression AS TEXT CHECK('' ~ VALUE OR '' !~ VALUE);
which works.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using readline for frequently used queries
I wrote: depending on the database, I use some dashboard queries rather frequently. To ease executing them, I've put: | $include /etc/inputrc | $if psql | \e[24~: \fSELECT * FROM DashboardQuery;\n | $endif in my ~/.inputrc (\e[24~ is [F12]). Obviously, this only works if a) the current line and b) the query buffer are empty. Before I try and err: Has anyone put some thought in how to reliably do that? My first impulse would be C-a, C-k, \r, C-j, but this doesn't solve the problem if psql is in quote mode (e. g. the previous line contained an opening ' or ''). I found that C-c (SIGINT) makes a clean sweep but it doesn't work as a readline macro as it gets handled by psql. So I opted for: | \e[24~: \C-a\C-k\\r\n\fSELECT * FROM DashboardQuery;\n which works well enough except for quote mode. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using readline for frequently used queries
Hi, depending on the database, I use some dashboard queries rather frequently. To ease executing them, I've put: | $include /etc/inputrc | $if psql | \e[24~: \fSELECT * FROM DashboardQuery;\n | $endif in my ~/.inputrc (\e[24~ is [F12]). Obviously, this only works if a) the current line and b) the query buffer are empty. Before I try and err: Has anyone put some thought in how to reliably do that? My first impulse would be C-a, C-k, \r, C-j, but this doesn't solve the problem if psql is in quote mode (e. g. the previous line contained an opening ' or ''). TIA, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Literals in foreign key definitions
Alban Hertroys dal...@solfertje.student.utwente.nl wrote: [...] Now the intent here is to restrict foreign keys referencing the base class to unitclass records that describe a baseclass and to restrict foreign keys referencing a derived class to unitclass records that do NOT describe a baseclass. Basically I'm trying to disallow derived classes to be derived of other derived classes. I can of course add a few triggers to force that constraint, but I think it would be nice if the above syntax could be made to work. Or is this already in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? If you want to avoid triggers, another, simpler approach is to have a otherwise superfluous column is_baseclass with a default TRUE and constraints NOT NULL and CHECK(is_baseclass) and then use a normal foreign key constraint. I usually find that easier to read as it's more familiar. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify a record has a column in a plpgsql trigger
Mike Ginsburg mginsb...@collaborativefusion.com wrote: [...] Thanks for the help! I'll look into the exceptions to see how expensive they are. On a related note, I was just told by our sysadmins that pg 8.4 might not be installed by the time this needs to be rolled out, leaving me in a bind since I have been using EXECUTE ... USING queries. A sample of my trigger is below: FOR colRow IN SELECT attname FROM pg_catalog.pg_attribute WHERE attnum 0 AND attrelid = TG_RELID LOOP EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO n USING NEW; EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO o USING OLD; IF n o THEN q := 'INSERT INTO change_log (...) VALUES (...); EXECUTE q; END IF; END LOOP; Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING? Wouldn't it be *much* easier to just have /two/ trigger functions? Your editor columns probably don't pop up and disappear randomly. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JOIN Record returning Function
Daniel Schuchardt d.schucha...@prodat-sql.de wrote: i have a function that returns a record. myfunc(IN id INTEGER) RETURNS RECORD. in that function the record is build from some subquery's in dependence of data. Now i need to join that function to its correponding main table that holds the id. SELECT myfunc.* FROM maintable JOIN myfunc(maintable.pk) ON true WHERE maintable.field=statisticdata; ERROR: invalid reference to FROM-clause entry for table maintable TIP: There is an entry for table maintable, but it cannot be referenced from this part of the query. so far so good, thats clear. But does anyone know a tricky solution for that problem? SELECT myfunc(pk) FROM maintable WHERE field = statisticdata;? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Books, the lulu.com scam
Thom Brown thombr...@gmail.com wrote: So I bought this book thinking it was a PDF file which I am fine with. Its not. They download an .acsm file that will only work with their proprietary Windoze software. I am really angry with lulu.com about this. I'm not sure I understand the purpose of them providing an electronic book copy of the PostgreSQL documentation since it's already available as both a PDF (A4 and US) and Windows help file (CHM) for free: http://www.postgresql.org/docs/manuals/ Apparently, you can make money out of it. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
Andreas 'ads' Scherbaum a...@pgug.de wrote: I think we should spend some money and buy some inexpensive cameras and tripods along with a bunch of memory cards for next year, and record everything. I know other PUGs/conferences series have done that with good results. On FOSDEM this attempt failed two years in a row. Last year the reason was: the camera had to be on the top level line with seats. Else it would only record parts of the room, eeither only the speaker and not the projector wall or vise versa. But this camera position resulted in a very bad light situation and in addition the sun protection darkened the room even more. You not only need a camera, you also need someone operating this camera all the time. At another conference, the camera's focus was on the speaker the whole time and the slides were edited in later, thus making them more legible. Of course, this requires either a camera operator who switches the video input when appro- priate or two recordings that are edited in post-production. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason s...@samason.me.uk wrote: [...] I would assume that you just have to convert A, B and C to seconds (since epoch) and then use a normal integer division. The problem is that the Gregorian calender is far too complicated. For example, think what would happen with an interval of months. It doesn't help converting to seconds because the length of a month in seconds changes depending on which year the month is in and which month you're actually dealing with. This makes any definition of division I've ever been able to think of ill defined and hence the above calculation won't work. Yep, you would probably need some safety margin and add a WHERE clause. I should have thought about that earlier as I recently stumbled (again) over why INTERVAL / INTERVAL was not defined. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason s...@samason.me.uk wrote:
any definition of division I've ever been able to think of [is]
ill defined
Yep, you would probably need some safety margin and add a
WHERE clause. I should have thought about that earlier as
I recently stumbled (again) over why INTERVAL / INTERVAL
was not defined.
Not sure what you mean by a safety margin, but I don't think it would
help much. Hours are defined using seconds (they're *always* 3600
seconds long, but, say, a day isn't *always* 24 hours long) so I don't
see what a safety margin would do.
A month can last 28 to 31 days and a year 365 to 366 days,
but for example:
| tim=# SELECT EXTRACT('epoch' FROM '1 month'::INTERVAL) / 60.0 / 60 / 24;
| ?column?
| --
|30
| (1 Zeile)
| tim=#
You would have to adjust the result of (EXTRACT('epoch'
FROM B) - EXTRACT('epoch' FROM A)) / EXTRACT('epoch' FROM
C) by a factor of 31/30 (30/28? 28/30?) and then chop off
timestamps after B with a WHERE clause.
JFTR: Hours can of course also be 3601 (or theoretically
3599) seconds long, but not in PostgreSQL :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to list user-specific configuration parameters?
Hi, how can I list the user-specific configuration parameters, i. e. those set by ALTER ROLE name SET ...? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to list user-specific configuration parameters?
Alvaro Herrera alvhe...@commandprompt.com wrote: how can I list the user-specific configuration parameters, i. e. those set by ALTER ROLE name SET ...? Get them from the pg_authid catalog. 8.5 alpha2 has a new \drds command in psql for that purpose. Thanks! Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
Karl Koster klkos...@optonline.net wrote:
It looks like I have to abandon xml2 functions in PostgreSQL
8.4. The problem is I can't seem to find an incantation of
xpath that will perform the same thing. I have tried the
following snippet:
select xpath('/trade/trade-info/id/text()', cast(xml as
xml))[1] as id from risk.trade_table
which, from the documentation should give me the first (and
only in this case) xml node text value for the XPath
expression. Instead I get the following error message from
the SQL parser:
ERROR: syntax error at or near [
LINE 1: ...h('/trade/trade-info/id/text()', cast(xml as xml))[1] as id ...
^
** Error **
ERROR: syntax error at or near [
SQL state: 42601
Character: 62
When I run the select statement without an array index, it
correctly returns a single column of arrays of length one
(expected) for the XPath node text value. Can anyone shine a
light on what I am doing wrong?
The build of the 8.4 database I am using is 8.4.1.9250.
You have to put brackets around the function call:
| select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from
risk.trade_table;
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
I wrote:
[...]
You have to put brackets around the function call:
| select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id
from risk.trade_table;
... or, after a look in the dictionary, whatever you call
( and ) :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
Peter Hunsberger peter.hunsber...@gmail.com wrote: [...] or a recursive query (which I always find very hard to com- prehend): | WITH RECURSIVE RecCols (LeftBoundary, Value) AS | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t) | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE c.col = p.Value + 1) | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols | GROUP BY LeftBoundary | ORDER BY LeftBoundary; Could you run both against your data set and find out which one is faster for your six million rows? Turns out the server is v 8.3, looks like I need to get them to upgrade it so I get recursive and windowing :-(. If this happens any time soon I'll let you know the results. Many thanks. After some tests with a data set of 7983 rows (and 1638 ran- ges): Don't! :-) The recursive solution seems to be more than double as slow as the iterative. I'll take it to -per- formance. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
Peter Hunsberger peter.hunsber...@gmail.com wrote: [...] I have one solution that joins the table against itself and does (among other things) a subselect looking not exists col +1 and not exists col -1 on the two instances of the table to find the start and end. This is, as you might guess, is not very efficient (my actual data is some 6 million+ rows) and I'm guessing there has to be something more efficient with windowing or possibly grouping on min and max (though I can't see how to make sure they are part of a contiguous set). Anyone have any ideas? You can either use a PL/pgSQL function (SETOF TEXT just for the convenience of the example): | CREATE FUNCTION SummarizeRanges () RETURNS SETOF TEXT AS $$ | DECLARE | CurrentFirst INT; | CurrentLast INT; | CurrentRecord RECORD; | BEGIN | FOR CurrentRecord IN SELECT col FROM t ORDER BY col LOOP | IF CurrentFirst IS NULL THEN | CurrentFirst := CurrentRecord.col; | CurrentLast := CurrentRecord.col; | ELSIF CurrentRecord.col = CurrentLast + 1 THEN | CurrentLast := CurrentRecord.col; | ELSE | RETURN NEXT CurrentFirst || ', ' || CurrentLast; | CurrentFirst := CurrentRecord.col; | CurrentLast := CurrentRecord.col; | END IF; | END LOOP; | IF CurrentFirst IS NOT NULL THEN | RETURN NEXT CurrentFirst || ', ' || CurrentLast; | END IF; | RETURN; | END; | $$ LANGUAGE plpgsql; or a recursive query (which I always find very hard to com- prehend): | WITH RECURSIVE RecCols (LeftBoundary, Value) AS | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t) |UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE c.col = p.Value + 1) | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols | GROUP BY LeftBoundary | ORDER BY LeftBoundary; Could you run both against your data set and find out which one is faster for your six million rows? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason s...@samason.me.uk wrote: generate_series(A, B, C) can also be written as A + generate_series(0, (C - B) / C) * C If you can figure out the limit then it seems easy, though I'm not sure how you'd do that. What limit? Sorry, I was calling the second parameter to generate_series the limit. Calculating (C - B) / C isn't easy for timestamps, whereas it's easy for dates. I believe this is why there's a specific version for the former but not the latter. (I obviously meant (B - A) / C :-).) Is it? I would assume that you just have to convert A, B and C to seconds (since epoch) and then use a normal integer division. [...] generate_series(DATE, DATE) would just be syntactic sugar, and I like sweets. We all do, but in software it's got to be balanced against the overhead of maintaining support for these functions. My knowledge of PostgreSQL's codebase is nonexistent, so I do not know how unstable it is. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
Grzegorz Jaśkiewicz gryz...@gmail.com wrote: why would you store data thats wrapped in two copies of its fieldname along with other punctuation? wouldn't it make more sense to decompose your XML source into proper tables so proper indexes and relational sql queries can be made? otherwise, every query turns into a massive sequential scan and parsing operation. you can always have index on xpath() ... and than use same expression in WHERE, and postgresql will use index. Interesting. I had thought that it was not possible to index on XML columns because no comparison operators were defined: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY); | FEHLER: Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree« | HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren. yet an array of XML works: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY); | HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml« | CREATE TABLE | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '?xml version=1.0?booktitleManual/titlechapter.../chapter/book'))); | INSERT 0 1 though only once: | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '?xml version=1.0?booktitleManual 2/titlechapter.../chapter/book'))); | FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln So an index on xpath() should not be possible. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What's wrong with this regexp?
Nick nboutel...@gmail.com wrote: SELECT TRUE WHERE '/steps/?step=10' ~ '^\/steps\/\?step=10$' Im guessing its an escape issue, but where am I going wrong? You need to double-escape the question mark: Once for the string literal, once for the regular expression (and you do not need to escape the slashes). This gives: | tim=# SELECT TRUE WHERE '/steps/?step=10' ~ E'^/steps/\\?step=10$'; | bool | -- | t | (1 Zeile) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason s...@samason.me.uk wrote: 8.4 has a generate_series(timestamp,timestamp,interval) which would seem to be a bit more flexible than you want. Yes, I know :-). But as generate_series(A, B, C) can also be written as A + generate_series(0, (C - B) / C) * C (or something flexible like that :-)), a For things as complicated as timestamps I'm not sure if this is such a trivial transform. If you can figure out the limit then it seems easy, though I'm not sure how you'd do that. What limit? generate_series(DATE, DATE) would inter alia get rid off the need to cast the result from TIMESTAMP to DATE and to explicitly specify '1 day'. Just a small, trivial enhance- ment for a popular use case :-). Interesting, I tend to aim for maximum expressiveness not ease of expressiveness. It would be somewhat easy to add the above if you want though: CREATE FUNCTION generate_series(date,date) RETURNS SETOF date IMMUTABLE LANGUAGE sql AS $$ SELECT generate_series($1::timestamp,$2::timestamp,interval '1 day')::date; $$; or I suppose you could use the integer series generation: SELECT $1 + generate_series(0,$2 - $1); If I didn't know that, I would not have characterized the feature request as trivial. Hum, now I'll have to see which is better. That second version seems to be slightly quicker (20 to 30%, for ranges from a year up to a century respectively) so you may prefer it, but the difference is going to be in the noise for any query I've ever used generate_series for. Which of my mails made you think that I was not satisfied with PostgreSQL's current performance? generate_series(DATE, DATE) would just be syntactic sugar, and I like sweets. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Alvaro Herrera alvhe...@commandprompt.com wrote: [...] Suppose the feature request was not a trivial one, but maybe a DEPENDS ON object clause for CREATE FUNCTION to allow PostgreSQL to deny requests to drop a table/view/ function that is needed by a function - where would I pro- pose that? On -hackers, just like any other feature request, trivial or not. Thanks. Any particular form? A quick glance at the archives did not reveal any feature requests that were not accompa- nied by a patch :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason s...@samason.me.uk wrote: suppose I thought that PostgreSQL would benefit greatly from a generate_series(DATE, DATE[, INT]) RETURNS DATE function 8.4 has a generate_series(timestamp,timestamp,interval) which would seem to be a bit more flexible than you want. Yes, I know :-). But as generate_series(A, B, C) can also be written as A + generate_series(0, (C - B) / C) * C (or something flexible like that :-)), a generate_series(DATE, DATE) would inter alia get rid off the need to cast the result from TIMESTAMP to DATE and to explicitly specify '1 day'. Just a small, trivial enhance- ment for a popular use case :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Procedure for feature requests?
Hi, suppose I thought that PostgreSQL would benefit greatly from a generate_series(DATE, DATE[, INT]) RETURNS DATE function - where do I suggest such a thing? Here on -general? On -hackers? Directly edit URI:http://wiki.postgresql.org/wiki/Todo? Suppose the feature request was not a trivial one, but maybe a DEPENDS ON object clause for CREATE FUNCTION to allow PostgreSQL to deny requests to drop a table/view/ function that is needed by a function - where would I pro- pose that? TIA, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE statement with syntax error doesn't raise a warning?
Mirko Pace mirko...@gmail.com wrote: I've ran an update statement like this (obviously wrong, I know!): update my_table set boolean_field = true AND my_notes = 'something' where id in (select id from my_table order by random() limit 4000); in my psql client and I had a UPDATE 4000 result but, correctly, anything was changed in my_table. So... why pg didn't raise a warning about syntax issue? Because there is no syntax error? TRUE AND my_notes = 'something' is a valid expression (and equivalent to my_notes = 'something'). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where clauses and selects
Scott Frankel lekn...@pacbell.net wrote: Is it possible to perform a select in the where clause of a statement? I have a situation where I've got one arm tied behind my back: I can only have a single table in the select and from clauses, but the where clause appears to be freed from that restriction. Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. [...] Something along the lines of: | SELECT foo.foo_id, foo.name FROM foo WHERE foo.bar_id = (SELECT bar.bar_id FROM bar WHERE bar.name = 'martini'); should do the trick. I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Dump it. Seriously. There are so many fine things you can do with a full-fledged database - you certainly do not want to be restricted in this regard. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comment on constraint
Andreas Kretschmer akretsch...@spamfence.net wrote: There is a question in the german pg-forum: It is possible to add a comment on a constraint, but \dd doesn't display that comment. There is also a old question in this mailing-list without an answer: http://archives.postgresql.org/pgsql-general/2003-07/msg01448.php I think, this is a bug, isn't it? Why? The man page for psql clearly says: |\dd[S] [ pattern ] | Shows the descriptions of objects matching the | pattern, or of all visible objects if no argu- | ment is given. But in either case, only objects | that have a description are listed. By default, | only user-created objects are shown; supply a | pattern or the S modifier to include system ob- | jects. ‘‘Object’’ covers aggregates, functions, | operators, types, relations (tables, views, in- ^^^ | dexes, sequences), large objects, rules, and | triggers. [...] ^ So no comments are shown for constraints or table columns or ... Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] maximum count of contiguous years
gorsa gorsa.1...@gmail.com wrote: [...] is there a select statement containing 'AND award_year BETWEEN 1994 AND 2002' that could generate the following? scholar_idconsistent_yrs 1 4 2 5 3 2 You could either do some wild fancy query where you parti- tion the data by scholar_id, then by award_year, then filter on the condition that the sum of award_year and RANK() (?) less one equals the current award_year, find the maximum of those, ... ... or you could just write a short function in your ap- plication (or a set-returning PL/pgSQL function if your ap- plication is dumb). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query and the number of row result
bilal ghayyad bilmar...@yahoo.com wrote: I am talking in case I am writing a script for a function, and I need to know the number of the returned rows of the query, then I will do IF statement based on that number, how? [...] Presuming that you are talking about a function written in PL/pgSQL, you will have to count them yourself or issue a second query SELECT COUNT(*) FROM [...]. For the special case that you want to find out whether no row at all was found, you can look at IF (NOT) FOUND. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea corruption?
Nathan Jahnke njah...@gmail.com wrote:
[...]
my $encodeddata = $data;
$encodeddata =~ s!(\\|[^ -~])!sprintf(\\%03o,ord($1))!ge; #prepare
data for bytea column storage
[...]
my $insert_sth = $connection-prepare('insert into testtable (data)
values (?) returning id');
$insert_sth-execute($encodeddata);
my $ref = $insert_sth-fetchrow_hashref;
my $id = $ref-{id};
my $getall_sth = $connection-prepare('select * from testtable where id=?');
$getall_sth-execute($id);
my $newref = $getall_sth-fetchrow_hashref;
my $newdata = $newref-{data};
$newdata =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : \\!ge; #decode
bytea column storage format
[...]
hash of data changes ... if you uncomment the $data = '123abc' line
you can see that it works with those six bytes fine, and it also works
with most other binary data, just not this binary data. any insight
would be appreciated. thanks.
Why do you encode/decode the data in your own application a
second time? It is already encoded by DBD::Pg.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looping through string constants
David Kerr d...@mr-paradox.net wrote:
I'd like to loop through a group of constant string values using plpgsql
The best analog i can think of would be in a shell script
#!/usr/bin/ksh
for a in a b c d e; do
echo $a
done
./a.ksh
a
b
c
d
e
Is there some tricky way I can make that happen in postgres?
(I don't want to put the values in a table =) that would be too easy!)
If you do not want to use arrays, you can always use:
| FOR r IN SELECT a FROM (VALUES ('a'), ('b'), ('c'), ('d'), ('e')) AS t(a)
ORDER BY a LOOP
| RAISE NOTICE '%', r.a;
| END LOOP;
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Field name problem
Alan Chandler a...@chandlerfamily.org.uk wrote: [...] So I tried to load the same database on my home machine using pg_restore. This runs version 8.4.0, and it failed loading the restore with and error at this field over because, I presume, its a reserved word in SQL. Is there anyway, I can access any of my other backups and load the data in a current database? a) Install an old PostgreSQL server, restore to it, change the attribute name, dump from it, restore to a new ser- ver. b) Restore the backup to an SQL file, load it into a suit- able editor, change (very carefully) over to the new attribute name and restore that file to a new server. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 8.4 packages for Fedora 11?
Hi, I'm in the process of upgrading a Fedora 10 box to 11. So far, Fedora's repository carries only 8.3.7. I am a bit he- sitant to use the yum.pgsqlrpms.org repository's packages as I like to keep the number of repositories as small as possi- ble. So: - Is there any ETA for official Fedora 11 packages? - If I'd use yum.pgsqlrpms.org's packages, can I later re- place them with Fedora packages without any surprises? Thanks in advance, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 8.4 packages for Fedora 11?
Tom Lane t...@sss.pgh.pa.us wrote: I'm in the process of upgrading a Fedora 10 box to 11. So far, Fedora's repository carries only 8.3.7. I am a bit he- sitant to use the yum.pgsqlrpms.org repository's packages as I like to keep the number of repositories as small as possi- ble. So: - Is there any ETA for official Fedora 11 packages? Yes: never. The Fedora package series for F-11 will be 8.3.x and nothing but. F-12 will have 8.4.x. That's a pity. - If I'd use yum.pgsqlrpms.org's packages, can I later re- place them with Fedora packages without any surprises? Well, that would be going back a major PG version, which is just as problematic as going forward a major version, in fact more so. If you don't mind a dump/initdb/reload cycle then you can try it, but there's no guarantee an 8.4 pg_dump file will load into 8.3 without some manual editing. No, I meant replacing yum.pgsqlrpms.org's packages with sometime-in-the-future-released Fedora 11 PostgreSQL 8.*4* packages. With the situation at hand, I will install the yum.pgsqlrpms.org repository and, come Fedora 12, decide then how to proceed. Thanks, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
