Re: [SQL] [NOVICE] Understanding Encoding
Beena Emerson memissemer...@gmail.com writes:
It still gives same result:
$ LANG=ko_KR LC_ALL=ko_KR
$ psql -d korean
korean=# SHOW client_encoding;
client_encoding
-
EUC_KR
(1 row)
korean=# INSERT INTO tbl VALUES ('ê·¸ë ì¤');
ERROR: invalid byte sequence for encoding EUC_KR: 0xa0 0x88
What you need to figure out is what encoding the text you are typing
is in. You're telling psql it's EUC_KR but it evidently isn't.
If you're typing these characters manually then it's probably determined
by a setting of the terminal-emulator program you're using. But if
you're copying-and-pasting then things get more complicated.
Also, what you did above is not what Amit suggested: he wanted you to put
the variable assignments on the same command line as the psql invocation,
so that they'd affect the environment passed to psql. I'm suspicious of
his solution because I'd have thought the terminal program would set up
the right environment ... but you might as well try it.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update column based on postgis query on anther table
Stefan Sylla stefansy...@gmx.de writes: Now I want to use a trigger function to automatically update the column 'id_test1_poly' in tabel 'test1_point': /**/ create or replace function test1_point_get_id_test1_poly() returns trigger as $$ begin new.id_test1_poly=test1_point_get_id_test1_poly(new.id); return new; end; $$ language plpgsql volatile; -- create trigger for function: create trigger test1_point_get_id_test1_poly after insert or update on test1_point for each row execute procedure test1_point_get_id_test1_poly(); I think you need that to be a BEFORE insert or update trigger. In an AFTER trigger, it's too late to affect the stored row. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
Dev Kumkar devdas.kum...@gmail.com writes: Any plans to fix this in next release or having a patch to fix this? No. This has been discussed (many times) before. There isn't any feasible way to change this behavior without breaking an incredible amount of code, much of which isn't even under our control. The marginal increase in standards compliance is not worth the pain --- especially when the aspect of the standard in question isn't even one that most of us like. (All-upper-case is hard to read.) If this is a deal-breaker for you, then I'm sorry, but you need to find another database. Postgres settled on this behavior fifteen years ago, and we're not changing it now. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
Alvaro Herrera alvhe...@2ndquadrant.com writes: Dev Kumkar escribió: But what I am asking here is if an alias name is provided be it upper case, lower case, or a mix then shouldn't it be preserved as as it is given. All this talk is when alias names are unquoted, when quoted then its standard behavior as seen in other databases. Aliases are treated just like any other identifier. The downcasing happens in the lexer (src/backend/parser/scan.l), which is totally unaware of the context in which this is happening; so there's no way to tweak the downcasing behavior for only aliases and not other identifiers. Quite aside from implementation difficulty, restricting the change to just column aliases doesn't make it more palatable. You'd entirely lose the argument that the change increases spec compliance, because the spec is perfectly clear that a column alias is an identifier just like any other. And you'd still be paying a large part of the application breakage costs, because the identifiers coming back in query descriptors are one of the main ways applications would notice such a change. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Index Usage and Running Times by FullTextSearch with prefix matching
rawi only4...@web.de writes:
And querying: FTS with prefix matching:
SELECT count(a)
FROM t1
WHERE a_tsvector @@ to_tsquery('aaa:* b:* c:* d:*')
(RESULT: count: 619)
Total query runtime: 21266 ms.
FWIW, I get fairly decent performance for cases like this in HEAD
(at least with a GIN index; GIST seems much less able to do well with
short prefixes). What PG version are you testing?
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bug in 9.2.2 ? subquery accepts wrong column name : upd
Achilleas Mantzios ach...@matrix.gatewaynet.com writes: dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM (select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY sst.setid ORDER BY character_length(vsl.name))) as arragg FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY sst.setid) as qry WHERE array_length(qry.arragg,1)1 AND qry.setid IN (SELECT setid from sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1); [ works ] however, there is not column setid in sis_oper_cons, If not, that's a perfectly legal outer reference to qry.setid. Probably not one of SQL's better design features, since it confuses people regularly; but it's required by spec to work like that. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE query with variable number of OR conditions in WHERE
Ben Morrow b...@morrow.me.uk writes: Quoth jorgemal1...@gmail.com (JORGE MALDONADO): I am building an UPDATE query at run-time and one of the fields I want to include in the WHERE condition may repeat several times, I do not know how many. UPDATE table1 SET field1 = some value WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n) I build such a query using a programming language and, after that, I execute it. Is this a good approach to build such a query? You can use IN for this: UPDATE table1 SET field1 = some value WHERE field2 IN (value_1, value_2, ...); IN is definitely better style than a long chain of ORs. Another possibility is to use = ANY(ARRAY): UPDATE table1 SET field1 = some value WHERE field2 = ANY (ARRAY[value_1, value_2, ...]); This is not better than IN as-is (in particular, IN is SQL-standard and this is not), but it opens the door to treating the array of values as a single parameter: UPDATE table1 SET field1 = some value WHERE field2 = ANY ($1::int[]); (or text[], etc). Now you can build the array client-side and not need a new statement for each different number of comparison values. If you're not into prepared statements, this may not excite you, but some people find it to be a big deal. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
Mark Stosberg m...@summersault.com writes: # Explicitly grant access to the view. db= grant select on entities_not_deleted to myuser; GRANT # Try again to use the view. Still fails db= SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; ERROR: permission denied for relation entities What's failing is that the *owner of the view* needs, and hasn't got, select access on the entities table. This is a separate check from whether the current user has permission to select from the view. Without such a check, views would be a security hole. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?
Ashwin Jayaprakash ashwin.jayaprak...@gmail.com writes: Hi, here's what I'm trying to do: - I have a table that has an HSTORE column - I would like to delete some key-vals from it - If after deleting key-vals, the HSTORE column is empty, I'd like to delete the entire row with update_qry as( update up_del as r set data = delete(data, 'c=678') where name = 'cc' returning r.* ) delete from up_del where name in (select name from update_qry) and array_length(akeys(data), 1) is null; *Q1: *That DELETE statement does not work Nope, it won't, because a single query can only update any particular table row once, and the DELETE plus its WITH clauses is still only a single query. If you want no empty hstore values to be an invariant of your data structure, then expecting every update query to implement that correctly seems like a pretty bad idea anyway. Consider using a trigger to do that, ie something like BEFORE UPDATE FOR EACH ROW DO if new hstore value is null then delete the row and return null. A problem with that approach is that the returned count of updated rows won't be very meaningful, and RETURNING values likewise. If that's a problem for you, you could use an AFTER trigger instead, which will be a little slower but it hides the deletes behind the scenes. (Note: a DELETE issued in a trigger is a separate query, which is why it doesn't fall foul of the limitation your WITH query did.) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Writeable CTE Not Working?
Kong Man kong_mansatian...@hotmail.com writes: Hi Victor, I see 2 problems with this query: 1) CTE is just a named subquery, in your query I see no reference to the upd_code CTE. Therefore it is never gets called; So, in conclusion, my misconception about CTE in general was that all CTE get called without being referenced. I think this explanation is wrong --- if you run the query with EXPLAIN ANALYZE, you can see from the rowcounts that the writable CTE *does* get run to completion, as indeed is stated to be the behavior in the fine manual. However, for a case like this where the main query isn't reading from the CTE, the CTE will get cycled to completion after the main query is done. I think what is happening is that the main query is updating all the rows in the table, and then when the CTE comes along it thinks the rows are already updated in the current command, so it doesn't replace 'em a second time. This is a consequence of the fact that the same command-counter ID is used throughout the query. My recollection is that that choice was intentional and that doing it differently would break use-cases that are less outlandish than this one. I don't recall specific examples though. Why are you trying to update the same table in two different parts of this query, anyway? The best you can really hope for with that is unspecified behavior --- we will surely not promise that one of them completes before the other starts, so in general there's no way to be sure which one would process a particular row first. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to access multicolumn function results?
Andreas maps...@gmx.net writes: SELECT some_fct( some_id ) FROM some_other_table; How can I split this up to look like a normal table or view with the column names that are defined in the RETURNS TABLE ( ... ) expression of the function. The easy way is SELECT (some_fct(some_id)).* FROM some_other_table; If you're not too concerned about efficiency, you're done. However this isn't very efficient, because the way the parser deals with expanding the * is to make N copies of the function call, as you can see with EXPLAIN VERBOSE --- you'll see something similar to Output: (some_fct(some_id)).fld1, (some_fct(some_id)).fld2, ... If the function is expensive enough that that's a problem, the basic way to fix it is SELECT (ss.x).* FROM (SELECT some_fct(some_id) AS x FROM some_other_table) ss; With a RETURNS TABLE function, this should be good enough. With simpler functions you might have to insert OFFSET 0 into the sub-select to keep the planner from flattening it into the upper query and producing the same multiple-evaluation situation. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Curious problem of using BETWEEN with start and end being the same versus EQUALS '='
Venky Kandaswamy ve...@adchemy.com writes: On 9.1, I am running into a curious issue. It's not very curious at all, or at least people on pgsql-performance (the right list for this sort of question) would have figured it out quickly. You're getting a crummy plan because of a crummy row estimate. When you do this: WHERE a.date_id = 20120228 you get this: - Index Scan using alps_agg_date_id on bi2003.alps_agg a (cost=0.00..17870.00 rows=26292 width=1350) (actual time=0.047..142.383 rows=36132 loops=1) Output: a.date_id, a.page_group, a.page, a.int_alloc_type, a.componentset, a.adc_visit, upper((a.adc_visit)::text) Index Cond: (a.date_id = 20120228) Filter: ((a.page)::text = 'ddi_671'::text) 26K estimated rows versus 36K actual isn't the greatest estimate in the world, but it's plenty good enough. But when you do this: WHERE a.date_id BETWEEN 20120228 AND 20120228 you get this: - Index Scan using alps_agg_date_id on bi2003.alps_agg a (cost=0.00..10.12 rows=1 width=1350) Output: a.date_id, a.adc_visit, a.page_group, a.page, a.int_alloc_type, a.componentset, a.variation_tagset, a.page_instance Index Cond: ((a.date_id = 20120228) AND (a.date_id = 20120228)) Filter: ((a.page)::text = 'ddi_671'::text) so the bogus estimate of only one row causes the planner to pick an entirely different plan, which would probably be a great choice if there were indeed only one such row, but with 36000 of them it's horrid. The reason the row estimate is so crummy is that a zero-width interval is an edge case for range estimates. We've seen this before, although usually it's not quite this bad. There's been some talk of making the estimate for x = a AND x = b always be at least as much as the estimate for x = a, but this would increase the cost of making the estimate by quite a bit, and make things actually worse in some cases (in particular, if a b then a nil estimate is indeed the right thing). You might look into whether queries formed like date_id = 20120228 AND date_id 20120229 give you more robust estimates at the edge cases. BTW, I notice in your EXPLAIN results that the same range restriction has been propagated to b.date_id: - Index Scan using event_agg_date_id on bi2003.event_agg b (cost=0.00..10.27 rows=1 width=1694) Output: b.date_id, b.vcset, b.eventcountset, b.eventvalueset Index Cond: ((b.date_id = 20120228) AND (b.date_id = 20120228)) I'd expect that to happen automatically for a simple equality constraint, but not for a range constraint. Did you do that manually and not tell us about it? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date Index
Adam Tauno Williams awill...@whitemice.org writes:
OGo= create index job_date_only on job(extract(date from start_date at
time zone 'utc'));
ERROR: timestamp units date not recognized
There's no field called date in a timestamp. I think what you're
trying to achieve is date_trunc('day', start_date at time zone 'utc')
http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Database object names and libpq in UTF-8 locale on Windows
Sebastien FLAESCH s...@4js.com writes: - I can use UTF-8 string constants in my queries. - I can pass UTF-8 data to the database with parameterized queries. - I can fetch UTF-8 data from the database. - I can create db object names with UTF-8 characters. But the db object names must be specified with double quotes: When I do not use quoted db object names, I get a strange problem. The table is created, I can use it in my program, I can even use it in the pgAdmin query tool, but in the pgAdmin db browser, there is no table name displayed in the treeview... That sounds like a pgAdmin bug. You should report it in the pgAdmin mailing lists. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
Sergio Calero. angusyou...@yahoo.es writes: I'd like to execute a query using the underscore as a pattern. select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a'; [ but this fails to match 'Garafía' ] I suspect what you have here is an encoding problem. That is, probably the í is represented as a multi-byte character (most likely UTF8) but the server thinks it's working with a single-byte encoding so that any one character should be only one byte. You didn't say what your encoding setup is, so it's hard to do more than speculate. PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit You do realize this is about 3 years out of date? The 8.4 series is up to release 8.4.13, and a lot of those updates contained fixes for serious bugs. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
Sergio C. angusyou...@yahoo.es writes: We started the cluster up with this command: ./initdb -D /usr/local/postgre/data -E UTF8 -U sir That doesn't prove anything about the specific database where you're having the problem ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] locks and select for update
Wayne Cuddy lists-pg...@useunix.net writes: The documentation about concurrency states that all the different types of locks are table locks even if the word row appears in the lock name. Section 13.3.1, Table-level Locks, states that all the lock types discussed therein are table-level locks, even though some of them have names containing the word ROW for historical reasons. I don't see how you'd read it to imply that there are no finer-grained locks anywhere in Postgres. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Tablesample Function on Postgres 9.1
Mubarik, Aiysha amuba...@microstrategy.com writes: I am trying to run a simple query to test out the tablesample function, but in postgres 9.1 it does not recognize the function. As from the wiki it seems like tablesample is supported (https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation) Sorry, that wiki page is just blue-sky speculation. If the feature were supported, you would find it in the main documentation. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Error: Template Id should be teh identifier of a template - help
Alex alex.thegr...@ambix.net writes: When I execute this: UPDATE HTMLPAGE SET PAGE_URL = REPLACE(PAGE_URL, '.dot', '.html') WHERE PAGE_URL LIKE '%.dot'; I get the following error from psql. Could you tell me what is wrong or how to fix it? Failed to execute SQL : SQL UPDATE HTMLPAGE SET PAGE_URL = REPLACE(PAGE_URL, '.dot', '.html') WHERE PAGE_URL LIKE '%.dot'; failed : ERROR: Template Id should be the identifier of a template That is not any built-in Postgres error message. Perhaps it is coming from a trigger function or some such? In any case, we can't help you. You need to identify what layer of software it's coming from, and complain to the appropriate people. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] fsync debug messages in pgsql logs
Wayne Cuddy lists-pg...@useunix.net writes: I'm seeing this in my PGSQL logs, is this something to be concerned about? I know the file system it sits on is reliable and the DB appears to run with fine, additionally the log indicates it's a debug message. I did some google searches and didn't find much. When I examine the file system file by these names to not exist. If it only complains once per file name, this is expected behavior when somebody drops a table just before the checkpoint mechanism tries to fsync it. (If the failure were to repeat, then it might be something more interesting.) It does seem a bit odd that only fsm files are being complained of, though. What PG version is that exactly? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] fsync debug messages in pgsql logs
Wayne Cuddy lists-pg...@useunix.net writes: On Wed, Aug 08, 2012 at 12:23:22PM -0400, Tom Lane wrote: If it only complains once per file name, this is expected behavior when somebody drops a table just before the checkpoint mechanism tries to fsync it. (If the failure were to repeat, then it might be something more interesting.) It does seem a bit odd that only fsm files are being complained of, though. What PG version is that exactly? It's 9.0.4. I'm frequently truncating tables but not dropping them. Under the hood, TRUNCATE creates a new empty table and then drops the old one after commit. So that sounds consistent. It's still a bit odd that all the messages are about FSM files, but maybe that has something to do with sequence-of-operations in the DROP. Anyway, nothing to see here AFAICT. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] can this be done with a check expression?
Wayne Cuddy lists-pg...@useunix.net writes: I have a table with 3 columns: name text start_id integer end_id integer start_id and end_id are ranges which must not overlap but can have gaps between them. Is it possible to formulate a table check constraint that can verify that either id does not fall within an existing range at insert time? IE prevent overlaps during insert? You can't do it reliably with a check constraint, at least not short of taking table-wide locks to serialize all modifications of the table. (If you were willing to do that, a check constraint calling a function that does an EXISTS probe would work; although personally I'd use a trigger instead. Either way, performance is likely to suck.) A less bogus way of doing things is to use an EXCLUDE constraint, although that will restrict you to be running PG 9.0 or newer. You also need some way of representing the ranges as indexable objects. In 9.0 or 9.1, probably the best way is to use contrib/seg/ to represent the ranges as line segments. 9.2 will have a cleaner solution, ie range types. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: view derived from view doesn't use indexes
Russell Keane russell.ke...@inps.co.uk writes: Using PG 9.0 and given the following definitions: CREATE OR REPLACE FUNCTION status_to_flag(status character) RETURNS integer AS $BODY$ ... $BODY$ LANGUAGE plpgsql CREATE OR REPLACE VIEW test_view1 AS SELECT status_to_flag(test_table.status) AS flag, test_table.code_id FROM test_table; CREATE OR REPLACE VIEW test_view2 AS SELECT * FROM test_view1 WHERE test_view1.flag = 1; I think the reason why the planner is afraid to flatten this is that the function is (by default) marked VOLATILE. Volatile functions in the select list are an optimization fence. That particular function looks like it should be IMMUTABLE instead, since it depends on no database state. If it does look at database state, you can probably use STABLE. http://www.postgresql.org/docs/9.0/static/xfunc-volatility.html regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
Sergey Konoplev gray...@gmail.com writes: On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. DELETE FROM some_table USING some_table AS s WHERE some_table.col1 = s.col1 AND some_table.col2 = s.col2 AND some_table.id s.id; No, that's a self-join, which isn't what the OP wanted. You can make it work if you self-join on the primary key and then left join to the other table, but that's pretty klugy and inefficient. What was being discussed is allowing people to write directly DELETE FROM some_table USING some_table LEFT JOIN other_table ... where the respecification of the table in USING would be understood to mean the target table. Right now this is an error case because of duplicate table aliases. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
Thomas Kellerer spam_ea...@gmx.net writes: Lately I had some queries of the form: select t.* from some_table t where t.id not in (select some_id from some_other_table); I could improve the performance of them drastically by changing the NOT NULL into an outer join: select t.* from some_table t left join some_other_table ot on ot.id = t.id where ot.id is null; If you're using a reasonably recent version of PG, replacing the NOT IN by a NOT EXISTS test should also help. Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How does Numeric division determine precision?
Will Pugh willp...@gmail.com writes: It seems that is 9.1, numerics that don't have a specified precision and scale are arbitrary scale/precision. For many operations this is straightforward. However, when doing a division operation that does not terminate, I'm curious about how the number of digits is determined. According to select_div_scale() in src/backend/utils/adt/numeric.c, /* * The result scale of a division isn't specified in any SQL standard. For * PostgreSQL we select a result scale that will give at least * NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a * result no less accurate than float8; but use a scale not less than * either input's display scale. */ I wouldn't necessarily claim that that couldn't be improved on, but that's what it does now. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
chester c young chestercyo...@yahoo.com writes: do not know if right mailing list in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 col wide, then moving to a larger terminal, eg, 132 col wide, the readline(?) editor in psql still treats like 80 cols, making it impossible to edit longer text. You'd need to tell the readline people about that one. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SELECT 1st field
Jan Bakuwel jan.baku...@greenpeace.org writes: What I need is the ability to name the column in the view, ie. create view v as select 1 as id from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
Wes James compte...@gmail.com writes: Why is there a different order on the different platforms. This is not exactly unusual. You should first check to see if lc_collate is set differently in the two installations --- but even if it's the same, there are often platform-specific interpretations of the sorting rules. (Not to mention that OS X is flat out broken when it comes to sorting UTF8 data ...) If you want consistent cross-platform results, C locale will get that for you, but it's pretty stupid about non-ASCII characters. For more info read http://www.postgresql.org/docs/9.1/static/charset.html regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
Carlos Mennens carlos.menn...@gmail.com writes: I'm not understanding why I'm not able to change this column type from char to integer? forza=# ALTER TABLE customers ALTER COLUMN cust_id TYPE integer; ERROR: column cust_id cannot be cast to type integer Try ALTER ... cust_id TYPE integer USING cust_id::integer. If you don't specify a USING expression, the command requires an implicit coercion from one type to the other, and there is none from char(n) to int. You can force it with an explicit coercion, though. It strikes me that cannot be cast is a poor choice of words here, since the types *can* be cast if you try. Would it be better if the message said cannot be cast implicitly to type foo? We could also consider a HINT mentioning use of USING. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Uniform UPDATE queries
Dennis dennis.verb...@victorem.com writes: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique queries, a keyword similar to DEFAULT, let's say CURRENT, is required to indicate that the current value must not change. No it isn't. Just write the name of the column, eg update mytable set x = x, y = new value, z = z where ... There's no reason to invent nonstandard syntax for this. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sintax error
kevalshah keval.sha...@gmail.com writes: At present whenever there is any error in syntax of fired query i get error message in terms of charcter at char 53 I also would like to have line no: from psql terminal.. Umm ... any reasonably recent version of psql will show syntax errors like this: regression=# select 1/ from foo; ERROR: syntax error at or near from LINE 1: select 1/ from foo; ^ If you're using something so old that it doesn't do that, the answer is to update. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY without quoting
Lee Hachadoorian lee.hachadoor...@gmail.com writes: COPY ... TO ... WITH CSV defaults to quoting string fields with embedded delimiters, quotes, and newlines. In pgAdmin I can execute to file and specify no quoting for the output, in which case I get (what I want) a file with no quotes, even though there are embedded commas in the strings. Uh ... why exactly would you want that? It seems impossible to parse such a file. If what you want is an unparsable file, you could just strip out the quotes with sed after the fact. But COPY is not in the business of producing non-machine-readable files, so the fact that it doesn't have an option for this doesn't bother me. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Type Ahead Issue
Carlos Mennens carlos.menn...@gmail.com writes: Am I missing something here? When the command is on one line, auto complete works fine but when I break it up as show above in the 2nd example, it acts like 'COLUMN' isn't even a valid option but if I manually type the word 'COLUMN' and finish the command, it works. Yeah, the autocompletion logic can only see the current line of input, so in your second example it has no idea that this is an ALTER TABLE command. My recollection is that there's no very nice way around that given the limitations of the readline callback interface, though maybe if somebody got ambitious they could improve it. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Natural sort order
Richard Klingler rich...@klingler.net writes: Took some time until I could try out this... But as soon I want to create the fcuntion based index it tells me: Error : ERROR: functions in index expression must be marked IMMUTABLE FWIW, this example works fine for me. Maybe you have some weird user-defined version of substr() or ~ that isn't immutable? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array_agg order by
jdmorgan jdmor...@unca.edu writes: I am using a array_agg to get a subset of data to use in a query.However, I can't figure out how to get the data returned in the array_agg function to sort with an order by function.Any help would be appreciated.Here is my query as it is now.I am using postgresql 8: Well, if you were using 9.0 or later, you could do this right: select array_agg(foo order by bar), otherstuff from ..tables..; You can kind of get there in older versions with a sub-select: select array_agg(foo), otherstuff from (select foo, otherstuff from ..tables.. order by whatever) ss; However that's a bit shaky because you can't do very much in the outer query, like say grouping, without risking messing up the sort ordering. You have to keep a close eye on the EXPLAIN output for your query to make sure nothing re-sorts the data before it gets to the Aggregate step. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function definitions - batch update
Marcin Krawczyk jankes...@gmail.com writes: I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all definitions ? I've got like 500 functions so doing it one by one will be time consuming. If you're feeling like a DBA cowboy, become superuser and issue a direct UPDATE against the prosrc column of pg_proc, being careful not to update rows that aren't the functions you want to hit. Slightly saner would be to read pg_proc and construct CREATE OR REPLACE FUNCTION commands that you then EXECUTE. The latter, if not done as superuser, would at least ensure you didn't accidentally break any functions you don't own. In either case, I'd practice against a test copy of the database before doing this live ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
chester c young chestercyo...@yahoo.com writes:
have database with many partitions. each partition table has its own primary
key sequence.
Column || Modifiers
---++--
uno_id|| not null default nextval('cmp0004.cmt_uno_id_seq'::regclass)
when dumped and then resorted, the pk sequence is changed to an inherited-
from table:
Column|| Modifiers
--++--
uno_id || not null default nextval('uno_uno_id_seq'::regclass)
(another error is that the pk sequence does not spec the schema!)
I see no reason to think there is a bug here; it's more likely that you
do not understand the display behavior of regclass constants. They only
print a schema name if your current search_path is such that the
relation wouldn't be found by writing just the unqualified name.
So the most likely explanation for the discrepancy above is that you
executed the two \d commands under different search_path settings.
It's possible that you have actually found a pg_dump bug, but if so
you'll need to submit a complete test-case exhibiting the bug.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
chester c young chestercyo...@yahoo.com writes:
here is a test case:
[ slightly less messed-up test case for anyone who wants to duplicate this: ]
create schema s1;
create schema s2;
create table s1.t1(
c1 bigserial primary key,
c2 text );
create table s2.t1(
c1 bigserial primary key
) inherits( s1.t1 );
In the original database, s1.t1.c1 has a default referring to sequence
s1.t1_c1_seq, while s2.t1.c1 has a different default referring to
sequence s2.t1_c1_seq. However, pg_dump examines the database roughly
like this:
1. Set search_path to s1.
2. Examine s1.t1's default expression; it looks like
nextval('t1_c1_seq'::regclass)
3. Set search_path to s2.
4. Examine s2.t1's default expression; it looks like
nextval('t1_c1_seq'::regclass)
5. Textually compare these defaults, find that they appear textually
identical, conclude that s2.t1's default is inherited from s1.t1.
Which it is not --- but after a dump and restore, it will be,
because pg_dump set it up that way.
The only near-term fix I can see for that is for pg_dump to stop
trafficking in inherited defaults at all. That is, always install
defaults with ALTER TABLE ONLY tab ALTER COLUMN col SET DEFAULT, and
do that over again explicitly for each child table. Since (AFAICT)
the system catalogs don't explicitly record inherited-ness of defaults,
this should produce the correct state even when the default really was
inherited, and it dodges this problem of search-path-sensitive
printouts, or indeed the whole idea of trying to compare text
representations of default expressions at all (which is surely a kluge
from the get-go). The code in pg_dump is jumping through hoops to
use inherited creation of defaults whenever possible, but I'm not sure
I see much point in that.
In the longer term it might be nicer if the system catalogs did record
inherited-ness of defaults (and then pg_dump could rely on that info
instead of guessing); but that would be a far more invasive change.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DECIMAL or NUMERIC Data Types
Carlos Mennens carlos.menn...@gmail.com writes: Does it matter when writing SQL code in PostgreSQL if I use DECIMAL or NUMERIC date types for a column named 'price' assuming it's to store the associated items actual dollar amount? No, because they're the same type in Postgres, as you were already told last week: http://archives.postgresql.org/pgsql-general/2011-12/msg00899.php regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block
Jan Bakuwel jan.baku...@greenpeace.org writes: Why-o-why have the PostgreSQL developers decided to do it this way...? Because starting and cleaning up a subtransaction is an expensive thing. If we had auto-rollback at the statement level, you would be paying that overhead for every statement in every transaction, whether you need it or not (since obviously there's no way to forecast in advance whether a statement will fail). Making it depend on explicit savepoints allows the user/application to control whether that overhead is expended or not. If you want to pay that price all the time, there are client-side frameworks that will do it for you, or you can roll your own easily enough. So we do not see it as a big deal that the database server itself doesn't act that way. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does anyone know of any issues around ARRAY UNNEST
Belinda Cussen belinda.cus...@servian.com.au writes: The code works ok on my database too when I call the procedure only once sequentially. I hit the segmentation fault consistently when I try to call the proc concurrently. What do you mean by concurrently? There is no multithreading within Postgres backends, and it's really hard to believe that unnest would be affected by what's happening in other server processes. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Does anyone know of any issues around ARRAY UNNEST
Belinda Cussen belinda.cus...@servian.com.au writes: I've managed to produce this fault consistently now. Below is the simplified code: CREATE TABLE foo_1 (id int primary key,media_uri TEXT); INSERT INTO foo_1(id) SELECT i FROM generate_series(1,100) g(i); CREATE OR REPLACE FUNCTION bb_crash_db_5 () RETURNS TEXT AS $$ DECLARE v_activity_id_list INTEGER ARRAY; BEGIN SELECT ARRAY(SELECT id FROM foo_1 ORDER BY id LIMIT 10) INTO v_activity_id_list; UPDATE foo_1 SET media_uri = 'a' WHERE id IN (SELECT activity_id FROM UNNEST (v_activity_id_list) activity_id) ; return 'success'; END; $$ LANGUAGE plpgsql; I then open 2 command lines and run: select bb_crash_db_5(); Thanks, I was able to reproduce it with this test case. It turns out not to have anything directly to do with UNNEST, but with the code that deals with concurrent row updates. I've committed a fix, which will appear in next week's updates. Thanks for the report and test case! regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strange error message
Uwe Bartels uwe.bart...@gmail.com writes: I've got a strange error message in our java application. ERROR JDBCTransaction:124 - JDBC commit failed *org.postgresql.util.PSQLException: ERROR: bogus varno: 65001* This is a symptom of some kind of planner error. If you are not running the latest minor release of your Postgres branch, update and see if it goes away. If not, please file a bug report with sufficient information to reproduce the problem by hand (ie, the problem query plus schema+data sufficient to run it against). regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] the use of $$string$$
John Fabiani jo...@jfcomputer.com writes: I just discovered that I can use $$string$$ to account for the problem of single quotes in the string (or other strange char's). However, I noticed that the table field contained E'string'. I actually tried to find info on this but I did not find anything. Could someone explain what it means or better provide a web link for me to discover the info. http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html See 4.1.2.4. Dollar-quoted String Constants, about halfway down the page. You might care to read the rest of 4.1.2 while at it. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] optimize self-join query
Ty Busby tybu...@gmail.com writes: I have a table that stores a very large starting number called epc_start_numeric and a quantity. I've apparently built the most inefficient query possible for doing the job I need: find out if any records overlap. Imagine the epc_start_numeric + quantity representing a block of numbers. I need to find out if any of these blocks overlap. Yeah, overlap is a hard problem. Basically, Postgres doesn't have any way to do your query short of comparing each row to each other row, so the cost goes up as O(N^2). If you know more than you've let on about the properties of the intervals, you might be able to improve things. For instance if the intervals fall into nonoverlapping buckets then you could add a constraint that the buckets of the two sides are equal. Postgres is a lot better with equality join constraints than it is with range constraints, so it would be able to match up rows and only do the O(N^2) work within each bucket. In the long run we might have better answers --- Jeff Davis has been working on range types for years now, and one of the long-range goals of that is to have smarter support for this type of problem. But for now, it's going to be painful. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql function executed multiple times for each return value
Steve Northamer stevenortha...@gmail.com writes: So my questions are: 1) How do we cause the paymentcalc function to be executed only once? In recent versions, I think marking it volatile would be sufficient. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ambiguous local variable name in 9.0 proc
David Johnston pol...@yahoo.com writes: On Behalf Of Samuel Gendler I'm happy to modify the proc definition, except that I am unsure how to do so other than to rename the variable, which is my least favourite way to do that. I'd far rather qualify the name somehow, so that it knows that I am refering to a local variable, if at all possible. Suggestions? Not tested but I think all local variables are implicitly scoped to the function name so you should be able to do the following: WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema More accurately: you can qualify a local variable's name with the label attached to the block in which it's declared; or with the function's name if the variable is a function parameter. See the fine manual: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html I believe there is some way to define the function so that it does not throw that particular error; it would be up to you make sure that the ambiguity is being resolved correctly (which it should in this particular case). Yes, see variable_conflict in http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html I wouldn't really recommend turning off the conflict detection, though. We put it in because of the number of hours people had wasted on unrecognized conflicts. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Use select and update together
Guillaume Lelarge guilla...@lelarge.info writes: On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote: select * from ( update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning * ) as x wouldn't work even in PG 9.1. So what data structure is coming out of an update ... returning * statement? It obviously doesn't work like a subquery. The only way to make something like this work in 9.1 would be: WITH x AS (update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *) SELECT * FROM x; The reason for that restriction is that WITH guarantees that the contained query is evaluated once and only once, whereas an ordinary subquery guarantees no such thing. So the effects of the UPDATE would be quite unpredictable if we allowed the former syntax. (In the specific example given it would likely work all right anyway, since there is no reason for a plain SELECT FROM to do anything except scan the subquery once. But if you did a join, say, watch out!) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
Emi Lu em...@encs.concordia.ca writes:
Does psql provide something like the following query command?
select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')
If you remember the operator name equivalent to ILIKE (~~*)
you can do
select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
Emi Lu em...@encs.concordia.ca writes:
On 08/30/2011 11:24 AM, Tom Lane wrote:
select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));
If next version could have not ilike ('', '') added into window
functions, that's will be great!
Why? And what's this got to do with window functions?
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cursor names in a self-nested function
Pavel Stehule pavel.steh...@gmail.com writes: you can use a refcursor type http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html It would suffice to explicitly set mycursor to null before doing the OPEN, thus instructing the system to assign a unique cursor name. CREATE FUNCTION test(id integer) RETURNS TEXT AS $BODY$ DECLARE mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 id; newid INTEGER; out TEXT; BEGIN out := id::text || ' '; mycursor := null; OPEN mycursor; raise notice 'mycursor = %', mycursor; -- debug LOOP FETCH mycursor INTO newid; EXIT WHEN newid IS NULL; out := out || test (newid); END LOOP; RETURN out; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] which is better: using OR clauses or UNION?
adam_pgsql adam_pg...@witneyweb.org writes:
I have a query hitting a table of 25 million rows. The table has a
text field ('identifier') which i need to query for matching rows. The
question is if i have multiple strings to match against this field I
can use multiple OR sub-statements or multiple statements in a
UNION. The UNION seems to run quicker is this to be expected?
Your test cases don't seem exactly comparable; in particular I think the
second one is benefiting from the first one having already read and
cached the relevant disk blocks. Notice how you've got, eg,
- Bitmap Index Scan on in_dba_data_base_identifier
(cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347 rows=318
loops=1)
Index Cond: (lower(identifier) ~=~ 'sptigr4-2210
(6f24)'::character varying)
versus
- Bitmap Index Scan on
in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual
time=0.178..0.178 rows=318 loops=1)
Index Cond: (lower(identifier) ~=~
'sptigr4-2210 (6f24)'::character varying)
Those are the exact same subplan, so any honest comparison should be
finding them to take the same amount of time. When the actual readings
are different by a factor of several hundred, there's something wrong
with your measurement process.
In the end this comes down to whether duplicates will be eliminated more
efficiently by a BitmapOr step or by sort/uniq on the resulting rows.
I'd have to bet on the BitmapOr myself, but it's likely that this is
down in the noise compared to the actual disk accesses in any
not-fully-cached scenario. Also, if you don't expect the sub-statements
to yield any duplicates, or don't care about seeing the same row twice
in the output, you should consider UNION ALL instead of UNION.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Mysterious column name
Nikolay whee...@gmail.com writes: select testtable.name from testtable; // returns strange result. See http://archives.postgresql.org/pgsql-bugs/2010-10/msg00269.php This will change in 9.1: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=543d22fc7 regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a strange order by behavior
Samuel Gendler sgend...@ideasculptor.com writes: Interesting. The original thread to which I was referring has a subject of Sorting Issue and the original request showed a list of vehicle model names which were sorting as though there were no spaces. The user had collation set to en_US.UTF-8. However, my database (on OS X) sorts both his example and the example that started this thread correctly, despite my lc_collate being set to the same value. OS X's support for comparisons in UTF-8 locales is widely understood to be broken. In general, as you've found out, there's little compatibility in sort order across platforms; but OS X is just completely unlike other modern Unixoid platforms. I would not take it as the definition of correct. AFAIK, if you're looking for strict character-by-character sorting rather than those weird dictionary rules, C locale (a/k/a POSIX locale) is your only choice, and you have to put up with the odd sort order you will get for all non-ASCII characters. In principle a locale could be defined that does character-by-character but does something reasonable with non-ASCII; but none of the major platforms seem to offer one. Also, just to be perfectly clear: this is not Postgres' fault, it's just sorting the way strcoll() says to. You'll get the same sort order from the command-line sort(1) program, if you feed it the same data in the same locale environment. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] query expression body ::= joined table grammar rule not accepted by Postgres
Clem Dickey dicke...@us.ibm.com writes: This expression is (I think) a well-formed query, but is rejected by Postgresql 8.4 (t is a table name). t t1 NATURAL JOIN t t2; Hmm ... I think you are looking at SQL92 or SQL99. The later versions of the spec don't seem to permit joined table to be used that way. In particular, in SQL:2003 and SQL:2007 I don't see any derivation path from query expression to joined table. So while we possibly could support this, it's legacy syntax, and I doubt there's enough interest to bother. [ digs a bit more... ] Ah, here we are: Annex E of SQL:2003 calls out various incompatibilities from SQL:99, notably 6) In ISO/IEC 9075-2:1999, a query expression body, query term, or query primary could consist of a joined table. None of those three elements can consist of a joined table in this edition of ISO/IEC 9075. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
Guillaume Lelarge guilla...@lelarge.info writes: On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: Can anybody tell me why this doesn't work? pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( Because it's not supported. The START clause expects a value, not a subquery. More generally, there are no utility statements in PG that accept non-constant expressions in their parameters. (A utility statement is anything other than SELECT, INSERT, UPDATE, DELETE.) There's been occasional speculation about changing that, but it would take a significant amount of work I think. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and with PG 9.0.4
Robert Haas robertmh...@gmail.com writes: On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic jasmin.dizdare...@gmail.com wrote: enable_material = off Is there any chance you can reproduce this with a simpler test case that doesn't involve quite so many joins? I didn't stop to count, but are there enough that join_collapse_limit or from_collapse_limit could be in play? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sorting Issue
Ozer, Pam po...@automotive.com writes: Isn't this the English standard for collation? Or is this a non-c locale as mentioned below? Is there anyway around this? LC_COLLATE = 'en_US.utf8' en_US is probably using somebody's idea of dictionary order, which I believe includes ignoring spaces in the first pass. You might be happier using C collation. Unfortunately that requires re-initdb'ing your database (as of existing PG releases). regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sorting Issue
Samuel Gendler sgend...@ideasculptor.com writes: It's not at all clear why they are not coming out of the db in alphabetically sorted order when the query includes order by VehicleTrimAbbreviated asc Usually the thing to ask at this point is what's the database's LC_COLLATE setting? Non-C locales often have truly bizarre sorting rules. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Specifying column level collations
Thomas Kellerer spam_ea...@gmx.net writes: My database is initialized as follows: postgres=# select version(); version PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit (1 row) I gather this is on Windows. Windows has its own notion of locale names, which look like this: lc_collate | German_Germany.1252 rather than the de_DE type of convention that's used by every other platform on the planet. There is not yet support in initdb for pre-populating pg_collation with Windows-style entries, so you will have to create your own entries. Presumably this would work for you, for instance: CREATE COLLATION german (locale='German_Germany.1252'); I don't know how to find out exactly what locale names are recognized by Windows, so can't help you much further than that. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FATAL: invalid cache id: 19
manuel antonio ochoa manuel8aalf...@gmail.com writes: How can I solve this problem : FATAL: invalid cache id: 19 There was a bug with that symptom in 9.0.0 and 9.0.1 ... if you're running one of those versions, update. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] getting PSQLException Can't infer the SQL type to use with Native Query call
Steven Dahlin pgdb.sldah...@gmail.com writes: In trying to call a postgresql 8.4 stored function I am passing a class as well as a List (I have to save a master record as well as the detail records). This is being done with a native query using EclipseLink 2.1. However, I am getting back a message for the first item: PSQLException: Can't infer the SQL type to use for an instance of com.hwcs.veri.shared.dto.MyClass. Use setObject() with an explicit Types value to specify the type to use. That looks like a complaint from JDBC, so possibly you'd have better luck asking on pgsql-jdbc. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple recursive part possible?
Svenne Krap svenne.li...@krap.dk writes: The two recursive parts seems to do the right thing each on its own, but together i get an error... with recursive downpath as ( [ something ] ), with recursive uppath as ( [ something ] ) select ... Leave out the second with recursive. WITH introduces a list of name-AS-subselect clauses, not just one. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
Humair Mohammed huma...@hotmail.com writes:
I am running into a behavior with a postgresql function with a SETOF
refcursor's returning multiple columns. Not sure if there is a different way
to retrieve a SETOF refcursor's with variable columns? Alternatively can I
return a primitive value and a refcursor from the same function. I tried
specifying this as OUT parameters without any luck. In Oracle you can pass
this in functions:
Platform:postgresql-x64-9.0 (PostgreSQL 9.0.1, compiled by Visual C++ build
1500, 64-bit)Java1.6JDBC4 Postgresql Driver, Version 9.0-801
Function:CREATE OR REPLACE FUNCTION test() RETURNS SETOF refcursor
AS$BODY$DECLAREref1 refcursor;ref2 refcursor;BEGIN OPEN ref1 FOR
SELECT 1; RETURN NEXT ref1; OPEN ref2 FOR SELECT 2, 3;
RETURN NEXT ref2; RETURN;END;$BODY$ LANGUAGE plpgsql
Java Code:CallableStatement cs = conn.prepareCall({ call test()
});ResultSet rs = cs.executeQuery();
while (rs.next()) { System.out.println(rs.getString(1));ResultSet rs2 =
(ResultSet)rs.getObject(1); while (rs2.next()) {
ResultSetMetaData rsmd = rs2.getMetaData(); int numberOfColumns =
rsmd.getColumnCount();System.out.println(numberOfColumns: +
numberOfColumns); System.out.println(rs2.getString(1));
System.out.println(rs2.getString(2)); }}
Output:unnamed portal 1numberOfColumns:
11org.postgresql.util.PSQLException: The column index is out of range: 2,
number of columns: 1.at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkColumnIndex(AbstractJdbc2ResultSet.java:2680)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2697)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1872)
at PgBlob.test(PgBlob.java:64)at PgBlob.main(PgBlob.java:37)
It appears the second result-set takes in the number of columns from the
first irrespective of the number of columns from the second. If the change
the function to return 2 refcursor's with same number of columns then it
works as expected.
Function:CREATE OR REPLACE FUNCTION test() RETURNS SETOF refcursor
AS$BODY$DECLAREref1 refcursor;ref2 refcursor;BEGIN OPEN ref1 FOR
SELECT 1, null; RETURN NEXT ref1; OPEN ref2 FOR SELECT 2, 3;
RETURN NEXT ref2; RETURN;END;$BODY$ LANGUAGE plpgsql
Output:unnamed portal 1numberOfColumns: 214unnamed portal
2numberOfColumns: 223
The example function works okay for me in psql. I think this is
actually a question about how to deal with such cases through the JDBC
driver, so I'd suggest asking on the pgsql-jdbc list. (Perhaps in a
less messy format this time, and could we ask for a useful Subject:
line too?)
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unnesting of array of different size explodes memory
Andreas Gaab a.g...@scanlab.de writes: As I now understand, the following query leads to 12 results, not just 4 (or 3...): SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY[4,5,6,7]); Why could postgres use as much memory till the kernel complained when unnesting 1200 and 1300 elements resulting in 1.6e6 rows. Are there settings to prevent this such as work_mem? Multiple SRFs in a targetlist are a good thing to avoid. The behavior is ... um ... peculiar, and the fact that we can't reclaim memory partway through is really the least of the problems with it. Try doing it like this instead: SELECT * from unnest(ARRAY[1,2,3]) a, unnest(ARRAY[4,5,6,7]) b; This has saner behavior and is less likely to leak memory. Not to mention less likely to be deprecated or de-implemented altogether in the far future. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pass in array to function for use by where clause? how optimize?
Anish Kejariwal anish...@gmail.com writes: (select store_id, avg(sales) sales from store where group_id in(select $1[i] from generate_subscripts($1, 1) g(i)) Seems like a pretty brute-force way to deal with the array. Try where group_id = any($1) Not sure if it'll be a great deal faster, but it's at least easier to write. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pass in array to function for use by where clause? how optimize?
Rob Sargent robjsarg...@gmail.com writes: On 04/13/2011 09:09 AM, Tom Lane wrote: Anish Kejariwalanish...@gmail.com writes: (select store_id, avg(sales) sales from store where group_id in(select $1[i] from generate_subscripts($1, 1) g(i)) Seems like a pretty brute-force way to deal with the array. Try where group_id = any($1) Not sure if it'll be a great deal faster, but it's at least easier to write. Would adding a subservient function which actually uses the value of the iteration (group_id) as a single parameter be of any use? Well, it'd be unlikely to solve the OP's actual complaint, which was performance. Hiding the semantics from the planner via a function (which generally looks like a black box to the planner) is even worse than hiding the semantics in a sub-SELECT, which I think is probably the root cause of the performance issue here. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_attributte, data types
=?UTF-8?Q?Viktor_Bojovi=C4=87?= viktor.bojo...@gmail.com writes: while listing data types i have noticed that some types have underscore prefix but i don't know why is that. so im asking if somene can tell me. These are type examples: _char _float4 _int2 _oid _regtype _text _varchar Those are array types. The normal convention is that foo[] is named _foo under the surface. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] replace_matches does not return {null}
Andreas Gaab a.g...@scanlab.de writes:
I tried to order a text-column only by parts of the entries. Therefore I used
regexp_matches(), but unfortunately I am loosing rows.
SELECT regexp_matches('abc','[0-9]+'), regexp_matches('123','[0-9]+');
Does not return {null}, {123} but no result at all.
Yes, because regexp_matches returns a rowset of zero or more results.
The fine manual suggests putting it in a sub-select if what you want
is a null or a single result:
SELECT ... , (SELECT regexp_matches(...)) FROM ...
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Oracle Equivalent queries in Postgres
Thomas Kellerer spam_ea...@gmx.net writes: Pavel Stehule, 16.02.2011 12:20: Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA sorry, I expected so all mature databases support it. Yes, this is really hard to understand. I would assume creating the INFORMATION_SCHEMA views based on the existing Oracle views is just a matter of maybe 3-4 days of days work. So it is really not understandable that Oracle does not support this. But then they probably don't care - after all it's Oracle. No, from their point of view it would be actively damaging: providing standardized views would reduce customer lock-in, by making applications more portable to other DBMSes. The pain the OP is feeling is a marketing advantage, so far as Oracle is concerned. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
Tony Capobianco tcapobia...@prospectiv.com writes: I'm altering datatypes in several tables from numeric to integer. In doing so, I get the following error: dw=# \d uniq_hits Table support.uniq_hits Column | Type | Modifiers +-+--- sourceid | numeric | hitdate| date| total | numeric | hitdate_id | integer | Indexes: uniq_hits_hitdateid_idx btree (hitdate_id), tablespace support_idx Tablespace: support esave_dw=# alter table uniq_hits alter sourceid type int; ERROR: integer out of range Sourceid should not be more than 5 digits long. I'm able to perform this query on Oracle and would like something similar on postgres 8.4: delete from uniq_hits where sourceid in (select sourceid from uniq_hits where length(sourceid) 5); That seems like a pretty bizarre operation to apply to a number. Why not where sourceid 9? Or maybe where abs(sourceid) 9 would be better. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE FROM takes forever
Josh slus...@gmail.com writes: I'm trying to do a DELETE FROM on my large table (about 800 million rows) based on the contents of another, moderately large table (about 110 million rows). The command I'm using is: DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records); This process ran for about two weeks before I decided to stop it -- it was dragging down the DB server. I can understand long-running processes, but two weeks seems a bit much even for a big table. Is this the best way to approach the problem? Is there a better way? Some background: The server is version 8.3, running nothing but Pg. The 'records' table has 'id' as its primary key, and one other index on another column. The table is referenced by just about every other table in my DB (about 15 other tables) via foreign key constraints, Hmm ... do all of those referencing tables have indexes on the referencing columns? It seems plausible that the time is going into seqscan searches for referencing rows. You might try doing EXPLAIN ANALYZE of this same delete for a limited number of rows (maybe 1000 or so) so that you could see what plan you're getting and where the time really goes. I think 8.3 had the ability to break out time spent in triggers, so if the problem is the FK propagation, EXPLAIN ANALYZE would show it. Also, the NOT IN is probably going to suck performance-wise no matter what, for such large numbers of rows. Converting to NOT EXISTS might help some, though I don't remember right now how smart 8.3 is about either. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Transaction-specific global variable
Florian Weimer fwei...@bfk.de writes: hstore greatly simplifies creating triggers for logging table changes, which is great. However, when creating a log record, I would like to include information about the party who made this change. We generally do not allow direct database access for application code, so the PostgreSQL user does not provide sufficient information on its own. Instead, I'd like to create a transaction-specific variable which stores context information to be included in the log table. I suppose I could create a stored procedures in C which provides this functionality, but I wonder if there is already something similar I could reuse. For instance, I could reuse the application_name configuration variable, but this seems a bit gross. You could abuse the custom-GUC-variable mechanism: just set custom_variable_classes and then define some variable that doesn't actually have any underlying loadable module. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] aggregation of setof
Andreas Gaab a.g...@scanlab.de writes: Could not the function regexp_matches(text, text) be defined to only return a text[] not a setof text[]? It'd be pretty hard to change at this point, because of backwards-compatibility considerations, and because the two functions share a single implementation. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Control reached end of trigger procedure without RETURN
MoNiLlO moni...@adinf.es writes: When I launch one insert returns error: I put the function and the returned error. CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp() RETURNS trigger AS ... ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function ventas_imp_a_ventas_cab The function that's lacking a RETURN is not the one you're showing us. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] why does seq scan instead of index scan
=?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= nakag...@ivp.co.jp writes: I'm trying to use like 'xx%' search on Text[] column. I thought it uses index scan. But actually it uses seq scan. Why? Those ANY expressions are not indexable. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find NOT NULLs in a group of 20 columns
gvim gvi...@gmail.com writes: I'm struggling to find anything other than a complex stored proc to do this: Out of a group of 20 columns return the rows in which: 1. Only 1 of the 20 columns IS NOT NULL 2. More than 1 of the 20 columns IS NOT NULL Any ideas? Cast to int and add up? SELECT ... WHERE ((col1 is not null)::int + (col2 is not null)::int + ... (col20 is not null)::int) = 1 -- or 1 regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY with FORMAT in Postgresql 9.x
Humair Mohammed huma...@hotmail.com writes: Postgres 9.x provies the COPY command with new syntax with a new option called FORMAT http://www.postgresql.org/docs/current/static/sql-copy.html However when I run the following command: COPY dQstn FROM 'c:/bcp/postgres/dQstn.csv' WITH FORMAT csv; I get the following error message: ERROR: syntax error at or near FORMAT If I use the 8.x syntax without the FORMAT option it works fine. This is expected: you have to use the new syntax (with parens) for any of the new options. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] subselect and left join not working?
Jasen Betts ja...@xnet.co.nz writes: On 2010-11-29, Jorge Arenas jorge.are...@kamarble.com wrote: select zona_id from zonas where zona_id not in (select zona_id from usuarios ### ###### where per_id =2) select 'FRED' from from usuarios where per_id =2 what'shappening is your not in subquery is being 'corrupted' by the surrounding query, the expression zona_id is being replaced with the value from the main query. so the inner query return multiple copies of the value from the outer query and the not-in fails. That explanation is nonsense, and so is the proposed fix. What I suspect is really going on is that the subselect yields one or more NULL values. If there's a NULL then NOT IN can never return TRUE, only FALSE (if the tested value is definitely present) or NULL (meaning it might match one of the NULLs, because NULL means unknown in this context). Newbies get caught by that all the time :-( ... it's not one of SQL's better features. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] atomic multi-threaded upsert
Mikhail V. Puzanov misha.puza...@gmail.com writes: -- increment and get the counter if exists UPDATE sequences SET counter = counter + 1 WHERE section = 'testSection' AND name = 'testKey' RETURNING counter; Seems that what you have here is a bad manual implementation of a sequence. Why don't you use a real sequence object and nextval()? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] obtaining difference between minimum value and next in size
John Lister john.lister...@kickstone.com writes: Is it possible to obtain the difference between just the minimum price and the next one up per product, If you're using = 8.4, try a window function. LEAD or LAG ought to do it. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
Tarlika Elisabeth Schmitz postgres...@numerixtechnology.de writes: I changed event.sql (3 lines): \COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' Now I am getting error messages: psql:event.sql:1: \copy:parse error at end of line I don't believe you can split backslash commands across lines. When I remove the linefeeds I don't get errors but it does not import anything. You wanted pstdin, not stdin. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
Tarlika Elisabeth Schmitz postgres...@numerixtechnology.de writes: The following command works fine when pasing it to psql via the -c option: cat event.csv | \ psql -c COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' When executed from a file via -f, it does nothing (no error messages either): event.sql: COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' cat event.csv | psql -f event.sql I believe that psql's interpretation of stdin when reading from a file is that the COPY data is to come from that same file (look at the script produced by pg_dump for an example). So it reads to the end of the file, which is right away --- otherwise you'd get some incorrect-data errors. The data sourced from the cat command is never noticed at all. I think you can get the effect you're after using \copy ... from pstdin. See the psql man page. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unexpected ORDER BY
Andreas maps...@gmx.net writes: I've got an PG 8.4 on a opensuse box running. The DB is unicode german. There is a text column I'd like to order by but as far as I see PG ignores special chars and uses only characters and numbers. This is driven by the locale you're using, specifically the LC_COLLATE setting. If you want plain ASCII sort order, you need to switch to C locale. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] I'm stuck - I just can't get this small FUNCT to run!
Ralph Smith rsm...@10kinfo.com writes: I'm also stuck on 7.4 for at least a few more months, but that's not part of the problem. ... well, actually, it is; because newer versions give considerably more helpful syntax error messages. I tried plugging this function into a current version, and got syntax error at or near daRec LINE 34: FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_... ^ which was at least close enough to the problem (the missing semi on the previous line) to be of some use, unlike the error pointing at line 92. Similarly, after fixing the first missing semi I got ERROR: syntax error at or near select LINE 38: select into vBusOwnerID businessownerid from business ... ^ which is just after the other one. If you aren't in a position to move your production DB yet, you might at least consider using a newer version for development. That has its own hazards of course, like accidentally using features that don't exist in 7.4, but it could save you a lot of time in cases like this. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] A more efficient way?
James Cloos cl...@jhcloos.com writes: I've a third-party app which is slower than it ought to be. It does one operation in particular by doing a query to get a list of rows, and then iterates though them to confirm whether it actually wants that row. As you can imagine that is very slow. This query gets the correct data set in one go, but is also slow: select p from m where s = 7 and p not in (select p from m where s != 7); See if you can recast it as a NOT EXISTS. NOT IN is hard to optimize because of its weird behavior with nulls. If you're working with a less-than-current version of PG, you may instead have to resort to a left-join-with-is-null locution, ie select m.p from m left join (select p from m where s != 7) m2 on (m.p=m2.p) where m2.p is null and m.s = 7; but it's hard to wrap your head around that sort of thing, so I'd advise against using it if you can get decent performance with EXISTS. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SIMILAR TO
Rommel the iCeMAn icec...@gmail.com writes: I use the following code to detect alphanumeric strings: IF _my_variable SIMILAR TO '^[a-zA-Z0-9]+$' THEN // do stuff here END IF; In pg8.4 this worked perfectly. I upgraded to pg9.0 and it no longer works. From the release notes it appears that the behavior of SIMILAR TO has changed in pg9.0. My question is, how do I modify my code so that it works in 9.0? Drop the ^ and $; they are incorrect for SIMILAR TO. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Random sort with distinct
Ozer, Pam po...@automotive.com writes: Select Distinct VehicleMake, VehicleModel From VehicleYearMakeModelTrim Order by random() Limit 10; I don't want to bring back the random number I just want the sort order to be random. How can I sort randomly? This query breaks because random() is not in the select. Well, yeah: the implication of the ORDER BY is that a new random value is to be computed for each row of VehicleYearMakeModelTrim. After you combine rows with DISTINCT it's not clear which of those values should be used to sort a grouped row. You need to put the DISTINCT and the ORDER BY in separate query levels, like this: select * from (Select Distinct VehicleMake, VehicleModel From VehicleYearMakeModelTrim) ss Order by random() Limit 10; regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_config -less
Rob Sargent rsarg...@xmission.com writes: A local installation of 9.0 does not seem to include pg_config. (not with pg_dump pg_ctl etc, no man page) This is a Suse box (openSUSE 11.2 (x86_64)). Most likely, Suse's packager decided to put it in the postgresql-devel subpackage (or maybe they spell it postgresql-dev or something else). It should certainly be available somewhere from them --- if not, file a packaging bug report. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Slow response in select
Gary Stainburn gary.stainb...@ringways.co.uk writes: I've posted th explain analyze at http://www1.ringways.co.uk/explain_analyse.txt I've marked a line with a sort in that appears to be the bit that's taking the time. Am I right? Well, it's not really that big a part of the whole cost: only 150ms out of the total. You could improve the speed of the sort by increasing work_mem enough to let it be done in-memory; but I'm not sure it's worth bothering with. If you knocked 100ms off the runtime that way you'd be doing well. One thing to realize about this kind of query is that the planner gets stupid when there are more than join_collapse_limit relations being JOINed. I'm not sure that it matters much in this example: it looks like it's a star schema and pretty much any join order is as good as any other. But you might want to try raising join_collapse_limit just to see whether the plan changes and whether it gets materially better. There's a definite planning-time penalty to raising that value, though, so I'd not recommend changing it in production unless you see big wins on a lot of queries. Better to reorder the JOINs manually if it turns out that join order does matter. Basically, if you're gonna join that many relations, it's gonna cost ya :-(. Star schemas are overrated IMO. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table returning functions
=?UTF-8?B?SmFubiBSw7ZkZXI=?= roede...@ethz.ch writes: Ok I now know that it really seems to do what I expected. But I still wonder what it does if I use two functions f() and g() that return a different number of rows. You get the least common multiple of their periods. It's ugly, and the lack of any very sane way to define the behavior is the main argument for deprecating SRFs in the targetlist. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 9.0rc1 - query of view produces unexpected results
Nathan Grange nath...@actarg.com writes: Or if this is a bug with 9.0, what actions do I take to make the PostgreSQL team awares? I think you already did ;-) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Steve stev...@gmx.net writes: I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? I can imagine that just querying a bunch of bigint would not make a big difference but what about several thousand of values? Would sorting them and sending the SQL query with ordered data influence the speed of the query? It's unlikely to make enough difference to be worth the trouble. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Steve stev...@gmx.net writes: Von: Tom Lane t...@sss.pgh.pa.us It's unlikely to make enough difference to be worth the trouble. Making a quick sort is ultra easy in C. Anyway... is there a difference in the speed of the query with pre-sorted values or not? If there is one then I will go and sort the values. I didn't opine on whether it was easy or not. I said it was unlikely to be worth the trouble. You could very well spend more time sorting the values than you buy in whatever you might save on the server side. Each value in the IN list is going to require a separate index probe. The sorting might buy something in locality of reference for successive probes, but most likely not enough to notice. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Controlling join order with parenthesis
Marc Mamin m.ma...@intershop.de writes: According to the documentation, I thought it was possible to force given join orders with parenthesis. You can do that if you dial down join_collapse_limit. Bear in mind that leaves you *solely* responsible for picking an intelligent join order. I wouldn't recommend it as a production setting. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sequential scan evaluating function for each row, seemingly needlessly
Bryce Nesbitt bry...@obviously.com writes: On psql 8.3.9, I ran a limited query limited to 5 results. There was a moderately expensive function call which I expected to be called 5 times, but was apparently called for each row of the sequential scan. Why? Given the plan: Limit (cost=19654.53..19654.54 rows=5 width=12) (actual time=10001.976..10001.990 rows=5 loops=1) - Sort (cost=19654.53..19826.16 rows=68651 width=12) (actual time=10001.972..10001.976 rows=5 loops=1) Sort Key: add_date Sort Method: top-N heapsort Memory: 25kB - Seq Scan on extractq (cost=0.00..18514.26 rows=68651 width=12) (actual time=19.145..9770.689 rows=73550 loops=1) Total runtime: 10002.150 ms (6 rows) any interesting work is going to be done at the seqscan level. Sort just sorts, and Limit just limits; neither do any user-defined calculations. So yeah, your functions got run for every row of the table. (This isn't totally a PG aberration, btw: if you read the SQL spec closely you'll discover that ORDER BY is defined to happen after any calculations specified in the SELECT list.) You could try something like select my_expensive_function(...), etc, etc from (select * from some-tables order by foo limit n) ss; where the inner select list just pulls the columns you'll need in the outer calculations. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] naming arguments in aggregate function
=?UTF-8?Q?Viktor_Bojovi=C4=87?= viktor.bojo...@gmail.com writes: I am trying to name arguments in aggregate function, but i don't know how, You can't --- it's not implemented. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polygon overlay
Gary Fu g...@sigmaspace.com writes: I don't see any overlap between the polygon and the box in the following sql. Can anyone explain why it returns 't'? select polygon(path '((0,0),(10,10),(20,10),(10,0))') polygon(box '((16,0),(17,1))'); ?column? -- t (1 row) Up to 8.4 the poly_overlap function did this: * Determine if polygon A overlaps polygon B by determining if * their bounding boxes overlap. * * XXX ought to do a more correct check! I see it's been improved for 9.0 ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a general ? on select performance
John Fabiani jo...@jfcomputer.com writes: I have program that loops though several thousands of records. The loop contains a select statement that confirms that I don't have a dup and then inserts a record into a different table. IOW's the statement is checking against a static table. The size of the table I'm checking does not change during the loop. I'm sure many have done something similar. Are you really, truly making *no* changes to the table you're reading? What I'm suspecting is that you are making such changes, in fact lots of them. The number of live rows may not change, but the table is bloating with lots of dead row versions, which can't be cleaned up yet since the transaction that deleted them is still open. This leads to O(N^2) or worse slowdown. There are usually ways around this type of problem, but we'd need more details about what you're really doing. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
