Re: [GENERAL] Question about COPY command
On fim, 2009-01-08 at 08:39 -0500, Josh Harrison wrote:
Hi,
A basic question about the COPY command syntax
This is the syntax in the postgres manual.
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
..
.
What is the difference between copying from 'filename' and copying from
'stdin' ???
The most important distinction is that 'filename' refers to a file
residing on the *server*, but STDIN is clientside.
For security, the file variant requires superuser privileges.
gnari
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query questions
On Sat, 2005-09-03 at 00:59 -0800, Poul Jensen wrote: I'm building a database containing key parameters for ~500,000 data files. The design I found logical is Two tables for each file: 1) Larger table with detailed key parameters (10-15 columns, ~1000 rows), call it large_table 2) Small table with file summary (~30 columns, 1 row), call it small_table you want to create 1 million tables, all with one of 2 schemas? why not just 2 tables, each with the additional file column ? ... SELECT large_table columns FROM regular expression WHERE condition on large_table IF condition on corresponding small_table; this would then be something like: SELECT large_table columns FROM large_table WHERE file ~ regular expression AND condition on large_table AND subquery involving small_table gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: ： Re: [GENERAL] A strange problem
On Sun, 2005-08-28 at 16:22 +0800, Tang Tim Hei wrote: The following commands are little different from the previous one. (1) select A.* from test.currency A, test.price_list B where A.curr_cd=B.curr_cd and A.curr_cd='USD' (2) select A.* from test.currency A, test.price_list B, test.country C where A.curr_cd=B.curr_cd and A.curr_cd='USD' For command (1), it is ok. The result is what I expect. However, for command (2), it has problem. I added the test.country C to it, here I actually just write a table name to it and no more other purpose. I do not understand what you mean by that. the added table name means an additional cartesian join However, the result maybe totally different. that is because of the added cartesian join If the table country is not empty, the result is just the same as in command (1) it will only be the same if the table contains EXACTLY 1 row but if country is empty, there are no result row. a cartesian join to 0 rows results in 0 rows if you are talking about something else, please show us a concrete simple example. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Index not being used unless enable_seqscan=false
On Wed, 2005-08-10 at 12:01 -0700, Shane wrote: Hello all, I am working with a simple table and query abut cannot seem to get it to use the index I have created. However, if I set enable_seqscan=false, the index is used and the query is much faster. I have tried a vacuum analyze but to no avail. [snip] explain analyze select msgid from seen where msgtime cast(now() - interval '6 months' as timestamp(0) without time zone); QUERY PLAN - Seq Scan on seen (cost=0.00..107879.45 rows=1081044 width=46) (actual time=7597.387..27000.777 rows=28907 loops=1) Filter: (msgtime ((now() - '6 mons'::interval))::timestamp(0) without time zone) Total runtime: 27096.337 ms (3 rows) Same query with enable_seqscan=false [snip faster plan] Any ideas on how I can fix this. I get this problem now and again with other databases but a vacuum usually fixes it. The planner is not very good at estimating selectivity of single unequalities. If you can specify a range in the where clause, you might possibly have better luck. ...WHERE msgtime cast(now() - interval '6 months' as timestamp(0) without time zone AND msgtime = '2000-01-01' Also, you might want to try to increase the STATISTICS target of msgtime. Sometimes an ORDER BY clause can help the planner on choosing indexscan, although in this case the difference in estimated cost is so high that I doubt it. gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Suppressing Error messages.
On Fri, 2005-08-05 at 07:42 -0700, Basith Salman wrote:
Hi All,
I was wondering if there is way to suppress the error messages on the stdout
from a perl dbi execute command, basically if I do a
sth-execute() on a command and say the row cannot be updated then
I get a err msg to stdout if there is foreign key violation, I want this
error message
to be directed to a log file.
From perldoc DBI:
PrintError (boolean, inherited)
The PrintError attribute can be used to force errors to
generate warnings (using warn) in addition to returning
error codes in
the normal way. When set on, any method which results in
an error occuring will cause the DBI to effectively do a
warn($class $method failed: $DBI::errstr) where $class
is the driver class and $method is the name of the method
which failed. E.g.,
...
By default, DBI-connect sets PrintError on.
If desired, the warnings can be caught and processed using a
$SIG{__WARN__} handler or modules like CGI::Carp and CGI::ErrorWrap.
is this what you want ?
it seems that the error messages go to STDERR, not STDOUT.
maybe a 2 error.log ?
gnari
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] How to join function with a table?
On Fri, 2005-08-05 at 10:53 -0500, Yudie Pg wrote:
Hi everyone,
I have a function returning set of date called datelist(date,date)
example:
select * from datelist('8/1/2005, 8/5/2005');
8/1/2005
8/2/3005
8/3/2004
8/4/2005
8/5/2005
I would like to join this function with a table
create table payment(
id int4 not null,
date_start date,
date_end date
)
id | date_start | date_end
1 | 8/1/2005 | 8/2/2005
2 | 8/4/2005 | 8/6/2005
I wish I could do join that returns something like this with the
function
id | datelist
--
1 | 8/1/2005
1 | 8/2/2005
2 | 8/4/2005
2 | 8/5/2005
2 | 8/6/2005
what about something like
select id,datelist
from payment as p,
(select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;
gnari
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cost problem
On Tue, 2005-08-02 at 17:32 +0300, Victor wrote: Hello. I use psql (PostgreSQL) 7.4.5. I have a cost problem. A simple select lasts too long. I have a table with aprox 900 rows. All rows are deleted and reinserted once a minute. EXPLAIN ANALYZE SELECT * FROM logati; QUERY PLAN --- Seq Scan on logati (cost=0.00..100741.22 rows=1198722 width=340) (actual time=35927.945..35944.272 rows=842 loops=1) Total runtime: 35945.840 ms The rows are not actually removed when DELETE happens. the next VACUUM vill put them into the Free Space Map if they are older than the oldest running transaction, so that their space can be reused. this means that you must VACUUM this table frequently to keep it from bloating. If the data distribution keeps changing , you should also ANALYZE frequently (but not when the table in empty), but that is another matter. to fix an already severely bloated table, you might need to do a VACUUM FULL on it once, but after that frequent-enough regular (non-FULL) VACUUMS should do. depending on the nature of your processes, you might want to use TRUNCATE to empty your table. gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql.conf value need advice
On Wed, 2005-08-03 at 13:30 -0300, marcelo Cortez wrote: folks what is preferible value for stats_reset_on_server_start ? depends on whether you want stats to be accumulated for longer periods than between restarts. I imagine that 'on' is what most people need. in any case, you can reset stats with the function pg_stat_reset() what is default value? the default is 'on' see http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-STATISTICS gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] indexes are fucked
On Tue, 2005-08-02 at 10:04 -0700, Dr NoName wrote: I got another problem with postgres. This time it refuses to use the indexes. Check this out: [snip] siam_production= explain SELECT render.* FROM render WHERE person_id = 432; QUERY PLAN - Seq Scan on render (cost=0.00..39014.72 rows=27833 width=1493) Filter: (person_id = 432) An explain analyze would be more informative, with and without seqscan enabled. What proportion of rows have this particular value of person_id? Maybe you need to increase statistics target of the column. What is the output of these: set enable_seqscan = off; explain SELECT render.* FROM render WHERE person_id = 432; set enable_seqscan = on; explain SELECT render.* FROM render WHERE person_id = 432; select count(*) from render; select count(*) from render WHERE person_id = 432; gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] indexes are fucked
On Tue, 2005-08-02 at 10:50 -0700, Dr NoName wrote: What is the output of these: set enable_seqscan = off; explain SELECT render.* FROM render WHERE person_id = 432; QUERY PLAN --- Index Scan using render_person_id_idx on render (cost=0.00..108735.88 rows=27833 width=1493) (actual time=0.11..77.62 rows=5261 loops=1) Index Cond: (person_id = 432) Total runtime: 80.99 msec (3 rows) so it will use the index if I disable seq scan? wtf? Setting enable_seqscan to off artificially adds a high fake cost factor to seqscans, so the planner will not use them, unless there is no alternative. This usually should not be done in production, but can be useful for debugging. Here we see that the planner estimated 27833 rows, but actually only 5261 rows were retrieved. Based on the high number of rows, a cost of 108735 was estimated. set enable_seqscan = on; explain SELECT render.* FROM render WHERE person_id = 432; QUERY PLAN -- Seq Scan on render (cost=0.00..39014.72 rows=27833 width=1493) (actual time=7.11..743.55 rows=5261 loops=1) Filter: (person_id = 432) Total runtime: 747.42 msec (3 rows) the seqscan is cheaper when a large enough proportion (maybe 5%) of rows are retrieved, and indeed the cost is estimated at 39014 try to increase statistics for this column: ALTER TABLE render ALTER COLUMN person_id SET STATISTICS 1000; ANALYZE render; 1000 is the maximum value, and probably overkill, but you might start with that. If this helps, you can try to lower values until you find the lowest one that still suits your data. Usually, 200 is enough. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] hpw to Count without group by
On Wed, 2005-06-01 at 16:16 -0500, Yudie Pg wrote: Hello, I have a table, structure like this: [...] Expected query result: sku, category, display_name, category_count 10001, 5, postgresql, 3 10006, 7, photoshop, 2 10008, 9, Windows XP, 2 The idea is getting getting highest ranking each product category and COUNT how many products in the category with SINGLE query. the first 3 columns can be done with select distinct on (category) ... order by category, rank desc but it still missing the category_count. I wish no subquery needed for having simplest query plan. how about a simple join ? select sku,category,display_name,count from (select distinct on (category) category, sku,display_name from product order by category,rank ) as foo natural join (select category,count(*) as count from product group by category ) as bar; gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Update on tables when the row doesn't change
On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote: Dawid Kuroczko wrote: Be wary of the NULL values though. :) Either don't use them, add something like 'AND (text1 NEW.text1 OR text1 IS NULL OR NEW.text1 IS NULL)' or something more complicated. :) Thanks for the notice, but I have a special operator for this: CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS and then there is the 'IS DISTINCT FROM' construct http://www.postgresql.org/docs/8.0/static/functions-comparison.html#AEN6094 gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] is in postgres solution
On Mon, 2005-05-16 at 11:43 +0300, Margus Roo wrote:
Hello.
I have 2 variables type timestamp. Example date1 = 2005-01-01 23:00 and
date2 = 2005-05-04 12:00. I want get something like age(date2,date1) but
ouput format must by hours::minutes.
get the difference in minutes with:
extract('epoch' from date2-date1) / 60
and do the formatting with / and %
or
select extract('epoch' from date2-date1)/3600
|| '::'
|| extract('minutes' from date2-date1);
gnari
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] ORDER BY options (how to order data as AAA, aaa,
On Tue, 2005-05-10 at 11:41 +0200, Julian Legeny wrote: ... But I would like to sort all data as following: NAME --- AAA aaa BBB bbb CCC ccc How can I write sql command (or set up ORDER BY options) for selecting that? how about ORDER BY lower(NAME),NAME ? gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] backup compress...blobs/insert commands/verbose
[note: it is better to create a new thread than to hijack an unrelated one] On Mon, 2005-05-09 at 18:45 +0200, Zlatko Matic wrote: Can someone explain me the following options while using pgAdimn III for backup: my guess is... blobs include blobs in backup. blobs are not included by default (I think) insert commands text file backups create a file containing a series of SQL commands. data is imported with COPY, which is effective, but you might prefer a series of INSERT commands, if you want to keep it more portable between database systems, or need to import it into old postgresql versions. gnari [snip quote of an entire unrelated email] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [INTERFACES] calculated identity field in views,
On Wed, 2005-05-04 at 09:01 -0700, Jeff Eckermann wrote: If I understand right, oids are globally unique within your database. I am affraid not gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] does database shut down cleanly when WAL device
On Tue, 2005-05-03 at 10:30 -0400, Brandon Craig Rhodes wrote: theories: a) Putting the WAL on a separate device from the database tables not only increases efficiency, but reliability as well - because as long as one keeps a database backup and a WAL history that goes back to the last backup (as described in 22.3 of the 8.0.1 manual), then one is insulated from losing data from a single disk failure: - If the drive holding the tables fails, then take the most recent backup and bring it up to date using the WAL. I assume the WAL history you mention are the WAL segments archived for PITR. they are usually shipped to a safe place, as soon as they are archived, but not kept on the same device as the WAL itself, if at all possible.(often near the database backup) Then, if either of the devices holding the database or WAL fails, the database can be rebuilt using backup+archived WALs upto the point of the last WAL archive. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Date addition/subtraction
On Tue, 2005-05-03 at 19:32 +0200, Craig Bryden wrote: How in postgres can I do date/time subtraction or addition. e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour? easier than you think select current_timestamp - interval '1 hour'; select current_date -interval '30 days'; -- timestamp select current_date + interval '1 week'; -- timestamp select date (current_date + interval '1 week'); -- date see: http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sorting by constant values
On Tue, 2005-05-03 at 13:29 -0400, Robert Fitzpatrick wrote: I have a column that I want to sort by certain values. The values are Unit, Exterior and Common. I want all the records with Unit first, Common second and Exterior last in the sort order. These are the only 3 possible values, is there a way to sort manually like that with the alphanumeric values? ... ORDER BY length(col); :-) gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Unique constraint violation on serial column
On Mon, 2005-04-11 at 11:03 -0700, Bill Chandler wrote: ERROR: duplicate key violates unique constraint event_tbl_evt_id_key EVENT_TBL evt_id bigserial, unique d1 numeric(13) obj_id numeric(6) d2 numeric(13) val varchar(22) correction numeric(1) deltanumeric(13) and a bit later , in response to a question, On Mon, 2005-04-11 at 14:24 -0700, Bill Chandler wrote: Tom, This is not the EXACT command (don't have that since this a client site and they did not have logging turned on) but the insert command would have looked something like: INSERT INTO EVENT_TBL VALUES(1039850293991, 'X.Y.Z', 1039110343000, '10.25', 1, 739950991) firstly, the types do not seem to match the table definition. secondly, you seem to be inserting a literal value into your serial column. did you mean to say that the insert was INSERT INTO EVENT_TBL (d1,...) VALUES (...) ? what is the current value of the sequence ? are there any rows there evt_id is higher than that ? gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] very slow after a while...
On Wed, 2005-04-06 at 18:18 +0300, Costin Manda wrote: The script does the following thing: 1. read the count of rows in two tables from the mssql database 2. read the count of rows of the 'mirror' tables in postgres these are tables that get updated rarely and have a maximum of 10 records together 3. if the counts differ, delete from the mirror table everything and reinsert everything. 4. THEN do the inserts that get updated on error if you empty the table in step 3, why do you have to test for the duplicate id error? are there duplicates in the mssql table? gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Time
On Mon, 2005-04-04 at 13:49 +0100, Rob Kirkbride wrote: Hi, I'm trying to fetch out the epoch value of a time, the data type is 'timestamp with time zone'. When I do select extract(epoch from time) it returns a fractional part as well. Am I doing this the correct way? Is the fractional part microseconds? no, it is fractions of a second. test=# select extract(epoch from '2005-04-04 14:00:00.00 +00'::timestamptz); date_part 1112623200 (1 row) test=# select extract(epoch from '2005-04-04 14:00:00.123400 +00'::timestamptz); date_part - 1112623200.1234 (1 row) the accuracy probably depends on your platform gnari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] not able to connect to Database
On Thu, 2005-03-31 at 16:10 +0530, Nageshwar Rao wrote: When I do psql test (database name) it says database test does not exists. But with pgAdminIII utility I get to see the database test and able to create tables ,insert the data etc. Why is this? maybe the existing data base is named Test ? what does psql -l say ? gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Views!
On Tue, 2005-03-29 at 11:48 -0500, Hrishikesh Deshmukh wrote: [rearranged] On Tue, 29 Mar 2005 09:01:24 -0500, Sean Davis [EMAIL PROTECTED] wrote: On Mar 29, 2005, at 8:27 AM, Hrishikesh Deshmukh wrote: I have 254 tables, i want to subset it in 237 and 17 tables?! Is creating views the answer?/ Is there a better way to subset them? Have a look at schemas: I have go through the docs; what you are suggesting is that take 237 tables and add them into a schema! maybe less work to move the other 17 tables. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Perl and AutoCommit
On Sun, 2005-03-27 at 00:31 -0500, Madison Kelly wrote:
What I thought would work was:
$DB-begin_work() || die...
# a lot of transactions
$DB-commit() || die...
maybe a more complete testcase would be in order.
[EMAIL PROTECTED]:~/test $ cat trans.pl
use DBI;
our $dbh = DBI-connect('dbi:Pg:dbname=test') or die 'no database';
$dbh-do('create table transtest(a text)');
$dbh-begin_work() or die 'error in begin';
$dbh-do(insert into transtest values ('foo'));
$dbh-do(insert into transtest values ('bar'));
$dbh-commit() or die 'error in commit' ;
system(psql -d test -c 'select * from transtest;')
[EMAIL PROTECTED]:~/test $ perl trans.pl
a
-
foo
bar
(2 rows)
[EMAIL PROTECTED]:~/test $
[EMAIL PROTECTED]:~/test $ perl -v
This is perl, v5.8.4 built for x86_64-linux-thread-multi
[EMAIL PROTECTED]:~/test $ perl -MDBI -le 'print $DBI::VERSION'
1.42
[EMAIL PROTECTED]:~/test $
perhaps a database operation is failing between your begin_work()
and your commit() ?
gnari
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] sort array optimisation in pl/perl
On Fri, 2005-03-25 at 15:29 +0100, GIROIRE Nicolas (COFRAMI) wrote: [re-arranged] [mailto:[EMAIL PROTECTED] la part de Ragnar Hafstað On Thu, 2005-03-24 at 15:49 +0100, GIROIRE Nicolas (COFRAMI) wrote: I create an array which is result of query on postgresql database and then I want to sort rows in a particular way (impossible by query on database). [snip suggestions] thanks for this help. The method functions great but the profit of time is good just if I have a lot of elements to deplace. in that case, it most likely need to look at your algorythm. There is little we can help you with, asy ou have not given us any info on your sort requirements. gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] sort array optimisation in pl/perl
On Thu, 2005-03-24 at 15:49 +0100, GIROIRE Nicolas (COFRAMI) wrote: I create an array which is result of query on postgresql database and then I want to sort rows in a particular way (impossible by query on database). can you give us more details on this particular sort order? My solution consists to put a rows (indice m+1) in a temporary other and then move all element before indice n to m in rows with indice n+1 to m+1 and last i put my temporary variable to indice n. I want to know if somebody know a better solution. I think of 2 solutions but i don't success to apply : - the first is to use list in which I could deplace references as a chained list - the second will be to deplace tab[n..m] to tab[n+1..m+1] in one instruction as ada language it all depends on the expected sizes of your arrays, but perl has some nice array operations, such as slices and splice() these come to mind: $x=$arr[$m+1];@[EMAIL PROTECTED];$arr[$n]=$x; or: @arr[$n..$m+1]=($arr[$m+1],@arr[$n..$m]); gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question insert data
On Sat, 2005-03-19 at 18:36 +0100, [EMAIL PROTECTED] wrote: 2. I've create a table based on from the master. I copied a fraction from the master into the new table using a where clause (insert into ... select * from ... where a = b . The number of records copied is about 2553. Issueing the same select statement on the master table gives me a list of 5106 which is twice the number of copied records. sounds like you inserted into the master table instead of the new table. can you show us the exact SQL you used? or even better, can you make this happen in a simple test case? gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython function problem workaround
On Tue, 2005-03-15 at 07:33 -0600, David wrote: [about the line-termination problem in plpython] I'd like to insert one note here. While I'm not particularly familiar with either perl or python, when I encountered the referred-to thread regarding Windows/Unix newline incompatibilities in Python, I ran some tests on my Linux system. I wrote some very simple scripts in Perl and Python and tested them. They ran OK when I saved them in native Linux newlines, they ran, but if I saved them in DOS mode (vim), neither the Python nor the Perl scripts would run. actually, perl scripts with \r\n line endings will run just fine in unix/linux. what you might have been experiencing, is the fact that the unix shell is expecting \n lineendings, and the #! line could have been failing to run the perl executable. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg/plsql question
On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote: While I have accomplished what I needed with the pgedit script given by John, I am still curious as to why mine is not working... Here is the latest version: /* */ create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_t varchar; begin v_user := ''user''; v_schema := ''public''; FOR v_t in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', t; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; Please note that all ticks above are single ticks. Here is what I do to execute it: excilan=# \i grant.sql CREATE FUNCTION excilan=# select fred_on_all(); ERROR: missing .. at end of SQL expression CONTEXT: compile of PL/pgSQL function fred_on_all near line 8 taken from http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING quote Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR loops (integer or query result) by checking whether the target variable mentioned just after FOR has been declared as a record or row variable. If not, it's presumed to be an integer FOR loop. This can cause rather nonintuitive error messages when the true problem is, say, that one has misspelled the variable name after the FOR. Typically the complaint will be something like missing .. at end of SQL expression. /quote try (untested): create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_rec RECORD; begin v_user := ''user''; v_schema := ''public''; FOR v_rec in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', v_REC.tablename; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Massive performance differences
On Tue, 2005-03-15 at 18:10 +0100, Andreas Hartmann wrote: explain analyze select * from veranstaltung_original order by semester; Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual time=2568.10..2573.02 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467 width=223) (actual time=1936.68..2506.83 rows=5467 loops=1) ^^^ isn't this value (1936.68) suspiscious for a seq scan ? can a lot of dead tuples cause this? maybe VACUUM FULL ANALYSE time ? gnari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] sql question
On Sun, 2005-03-13 at 23:13 -0600, George Essig wrote: On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven [EMAIL PROTECTED] wrote: [snip problem] select id, fref as ref from my_table union select id, mref as ref from my_table; union ALL (see other replies) gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Convert Cursor to array
On Mon, 2005-03-14 at 10:44 +0100, FERREIRA William (COFRAMI) wrote: so we choice to use a different solution which consist on using the index of a chapter and its evolution. if we have this data : chapter_id | evolution | index 1 | 0 | 1 2 | 0 | 2 3 | 0 | 3 4 | 1 | 2 by using our sort function we obtain this : chapter_id | evolution | index 1 | 0 | 1 4 | 1 | 2 2 | 0 | 2 3 | 0 | 3 in what way is this different than ... ORDER BY index ASC, evolution DESC; ? gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Problem with special character
On Mon, 2005-03-14 at 16:32 -0500, David Gagnon wrote: Hi, I really have a problem with a production environment (RH 9, Postgresql 7.4). When I deploy on the production env the same stored procedure with the same data (different OS and postgresql instance) the stored procedure crash. I get this error: java.io.IOException: Unable to get row: ERROR: cannot EXECUTE a null querystring when executing the I looked with pgadminIII and found that a charater used as a string separator (i.e.: ) is shown as on the production database. It look just oki in my dev env. (I included part of both stored procedure below). were the 2 clusters initialized with the same locale settings ? gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problem with special character
On Mon, 2005-03-14 at 19:13 -0500, David Gagnon wrote: I did tried to update the stored-procedure via PgadminIII and it worked. The problem seems to be JDBC driver .. But all works well on my TOMCA/POSTGRESL/WINDOWS platform. I think it's something in the database setting .. is there other setting that can cause this behavior ? maybe some difference in the environments that the two tomcats run in? are their locales the same ? gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] General query optimization howto
On Sun, 2005-03-13 at 02:34 +0100, Miroslav ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. EXPLAIN ANALYZE is even more useful. start with looking for inconsistencies between row estimates and actual row counts. these could mean that you need to ANALYZE, or increase statistics for some columns. also look for expensive sequential scans where you would expect an index scan. this may be due to missing indexes, imcompatible column types, lack of ANALYZE, or insufficient statistics. browse through the archives of the pgsql-performance list, to get a feel of typical problems, and to read illuminating responses from regulars. if you still are having problems, make the simplest test case you can, and post an EXPLAIN ANALYZE to pgsql-performance, along with relevant data, such as table definitions, typical data distributions and postgres version. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [JDBC] [GENERAL] MS Access to PostgreSQL
On Fri, 2005-03-11 at 17:52 +, Brian Gunning wrote:
[rearranged quoting]
From: [EMAIL PROTECTED]
--- William Shatner [EMAIL PROTECTED] wrote:
I have recently migrated from MS Access to
PostgreSQL.Previously I had
a SQL command
ResultSet aGroupResultSet =
aGroupPathStmt.executeQuery(
SELECT \groupID\,\fullpath\ FROM
\groups\ WHERE
\fullpath\ Like ' +
aPath + ');
where aPath was equal to 'folder\another folder\%'.
I don't think the problem isn't with the backslashes. They are escaped and
seem to be working fine e.g. to insert a backslash in Access I had to use
one escape character ('\\') whereas in PostgreSQL four backslashes ('')
are required. The line that inserts the % is as follows...
String aPath = group.getPath() + aOldGroupName + \\%;
are the backslashes in group.getPath() + aOldGroupName escaped ?
does aGroupPathStmt.executeQuery() escape backslashes ?
did you print the SQL statement to screen or logfile to make sure
what is actually sent to executeQuery() ?
does your language support placeholders ? this kind of problems are
usually easier to deal with with them.
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Checking for schedule conflicts
On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote: Given the tables defined below, what's the easiest way to check for schedule conflicts? So far, the only way I've come up with is to create a huge, multi-dimensional array in PHP, with a data element for every minute of all time taken up by all events, and then check for any of these minutes to be set as I go through all the records. (ugh!) But, how could I do this in the database? But I'd like to see something like select count(*) FROM events, sched WHERE sched.date=$date AND events.id=sched.events_id ... GROUP BY date, startfinish and finishstart HAVING count(*) 1 And here's where I get stumped. You can't group by start or end because we need to check if they OVERLAP any other records on the same date. Ideas? use the OVERLAPS operator ? http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Pgsql dynamic statements and null values
On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote: We use a dynamic statement in a pgsql stored function to insert rows into a table determined at run time. After much debugging, I've discovered that a null incoming argument will cause the dynamic statement to evaluate to null. The error message emitted is unable to execute null statement. can't you use COALESCE() ? gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] IDENT Authentication
On Fri, 2005-03-04 at 19:59 -0500, David A. Cobb wrote: I've newly transitioned from Windoze to Debian Linux. And, of course, I'm installing things left and right. I had pgsql up and running, then I had to do a lot of tearing out and reinstalling other stuff. Now, when I try to connect to psql I get: psql: FATAL: IDENT authentication failed for user superbiskit edit /etc/postgresql/pg_hba.conf and do a sudo /etc/init.d/postgresql reload gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] sql join question
On Tue, 2005-03-01 at 13:42 -0800, Scott Frankel wrote: [snip problem] Task: find all color names in each of palette1's tones. Can this be done in a single SQL statement? [snip table examples] looks like a job for NATURAL JOIN test=# select color_name from palettes natural join tones natural join colors where palette_name='plt1'; color_name rose madder crimson red ochre phthalocyanine leaf green (5 rows) gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] sql join question
On Tue, 2005-03-01 at 16:51 -0800, Scott Frankel wrote: Sweet! And not so sweet. The natural join worked beautifully with my test schema; but it failed to yield any rows with my real-world schema. I think I've tracked down why: duplicate column names. i.e.: ... CREATE TABLE palettes (palette_pkey SERIAL PRIMARY KEY, palette_name text UNIQUE DEFAULT NULL, qwe text); CREATE TABLE tones(tone_pkey SERIAL PRIMARY KEY, tone_name text UNIQUE DEFAULT NULL, palette_pkey integer REFERENCES palettes, qwe text); Are the 'qwe' columns in both tables clobbering each other and preventing the join from succeeding? the docs really explain this better than I can, but a table1 NATURAL JOIN table2 is shorthand fo a table1 JOIN table2 USING (list_of_common_keys) so: select color_name from palettes join tones USING (palette_pkey) join colors USING (tone_pkey) where palette_name='plt1'; see: http://www.postgresql.org/docs/8.0/interactive/sql-select.html gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Referencing created tables fails with message that
On Sun, 2005-02-27 at 18:50 -0500, Tommy Svensson wrote: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. you do not give actual examples, nor do you say how you created the tables, but one possibility is that you ran into the case-folding feature. names are folded to lowercase unless quoted in doublequotes. if you (or the client you use) created your table with quoted upper-case or mixed case names, you must do the same with the selects. CREATE TABLE Foo (a text); SELECT a from Foo; -- works SELECT a from Foo; -- fails the same applies to other names, such as columns. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems with pgcrypto and special characters
On Mon, 2005-02-28 at 18:32 +0100, Markus Wollny wrote:
To get straight to the point, here's my problem:
mypgdb=# select bytea2text(decrypt(encrypt('Tübingen'::bytea,
'mypassphrase'::bytea,'bf'::text),'mypassphrase'::bytea,'bf'::text)) as foo;
foo
-
T\303\274bingen
(1 row)
I have compiled and installed pg_crypto and I'am using the following function
as workaround for a bytea-to-text-cast:
are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('Tübingen'::bytea) as foo;
?
have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('Tübingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;
(sorry for the obsessive indentation)
gnari
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] DBI and placeholders question
On Mon, 2005-02-14 at 18:41 +, mike wrote:
Is it possible to have a placeholder on the left side of a select
criteria?
yes
eg:
SELECT CASE WHEN date_part('dow',?::date)=5
this bit is fine
THEN CASE WHEN ? = 2 OR ? =3 OR ?=6 OR ?=7 OR ?=8 OR ?=12 THEN '7:00'
here the ? is being read as NULL ie, output from LOG
SELECT CASE WHEN date_part('dow', '2005-02-28'::date)=5 THEN CASE WHEN
NULL = 2 OR NULL =3 OR NULL=6 OR NULL=7 OR NULL=8 OR NULL=12 THEN
'7:00' WHEN NULL = 1 OR NULL =5 OR NULL=13 THEN '3:30' ELSE NULL
END
please show us a minimal case that behaves as you say.
are you saying this only happens in nested 'CASE' ?
does this only happen if you use such a long a long series
of ?=val1 OR ?=val2 .
what actual values did you use for the question marks?
this seems to work for me:
#!/usr/bin/perl
my DBI;
my $dbh = DBI-connect('dbi:Pg:dbname=test')
or die 'no database connection';
my $sql=q{select CASE WHEN ? = 1 THEN CASE
WHEN ? = '2' OR ? = 3 THEN 'YEP' ELSE 'NOPE' END END};
my ($v)=$dbh-selectrow_array($sql, {}, 1,2,3);
print $v\n;
gnari
I am getting the ? translated as nulls
apologies for off-topic, but any help appreciated
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Confused by to_char
On Tue, 2005-02-08 at 12:28 +, mike wrote:
I am am trying to get a day string from a date using to_char ie:
SELECT date1,ti1 ,to1,ti2,to2,adj,ei,eo,to_char('2005-02-07','Day') FROM
vw_times_list1
however I get
function to_char(unknown, unknown) is not unique
test=# select to_char('2005-02-07'::date,'Day');
to_char
---
Monday
(1 row)
gnari
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] postgres session termination
On Sun, 2005-01-30 at 21:24 -0500, Rick Schumeyer wrote: I think this is a common task, but Im not sure how to do it. I want to run a query that can return many records, display them 10 at a time, and be able to go forward/backward in the list. Im not concerned about the list changing after the initial query. Im accessing this via a php web page. Im thinking that maybe the best way to do this, other than re-running the query each time, is to put the results into a temporary table. I think this will work if I never call disconnect from the php script. My question is, when does my Postgres session end? Is there a timeout? in normal php context, the generation of each web page should be considered a separate database session. there is no garantee that the 'next page' request from the user gets the same database connection, even if you use connection pooling. you can use LIMIT and OFFSET to do what you want. if your rows are ordered by a unique key, and you only have to browse forward, you can do: SELECT * from table where key ? where the '?' is last value retrieved. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgres session termination
On Mon, 2005-01-31 at 15:38 -0600, Scott Marlowe wrote: On Mon, 2005-01-31 at 09:28, Alban Hertroys wrote: John DeSoi wrote: I think there are much better ways to do this. If the result set is large, the user could be waiting a very long time. Two possibilities are (1) use a cursor or (2) use limit and offset in your select statement grab only the rows you need to display. Someone correct me if I'm wrong, but I don't think PHP supports cursors (Maybe PHP 5?). Otherwise, that would have been a neat solution indeed. PHP supports postgresql cursors, and has since php was able to connect to postgresql. well, my impression was that the OP wanted to divide result sets between web pages, so cursors would not help anyways,as they do not survive their session. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Prompt User From a pgplsql Function
On Sat, 2005-01-29 at 13:21 -0500, Terry Lee Tucker wrote: [on prompting user in the middle of a transaction] Thanks for the reply, Tom. I just had an idea about writting the answer from the client to a table designed for that purpose. It could have a unique key as the pid and a column for storing the answer. When the notice receiver returns, I could have the function check for the answer in the table. I have successfully created my own notice receiver and it works. won't there be visibility problems with this ? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange results of ORDER BY clause when item begins
On Wed, 2005-01-19 at 11:24 -0800, [EMAIL PROTECTED] wrote: [non-word character being ignored by ORDER BY] ... testdb1= show LC_COLLATE; lc_collate - en_US.UTF-8 (1 row) this is a 'feature' of your en_US locale: bash$ export LC_COLLATE=en_US bash$ (echo usra;echo usrq;echo /usr/lib)| sort usra /usr/lib usrq bash$ export LC_COLLATE=C bash$ (echo usra;echo usrq;echo /usr/lib)| sort /usr/lib usra usrq maybe you should have run initdb with LC_COLLATE=C gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 16:25 +0100, Bo Lorentsen wrote: [about a volatile function in a where clause not generating index scan] Will the only possible way to fix this be to make a volatile function with a return type (I know this is not possible now, but in theory) ? this has nothing to do with the return type. a volatile function is a function that is not garanteed to return the same value given same input parameters, (such as currval()). when a volatile function is used thus: SELECT * FROM mytable WHERE col=myvolatilefunc(); the planner must call the function once per table row, and assume possibly different return values each time, so an indexscan will not improve timings. on the other hand, if the function is labeled STABLE, the planner can assume that the same value will alway be returned, so only one call to it can be made, and an indexscan might be found the most effective. hope this helps gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote: Ragnar Hafstað wrote: when a volatile function is used thus: SELECT * FROM mytable WHERE col=myvolatilefunc(); the planner must call the function once per table row, and assume possibly different return values each time, so an indexscan will not improve timings. Why not use the index scan for every row, is this a limit in the planner ? I think there is something in the planner I don't understand :-) the planner will just use the plan it estimates will be fastest. because of how indexscans work in postgresql, in this case it would be slower than a tablescan (assuming the function really is volatile) gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 14:11 -0500, Tom Lane wrote: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= [EMAIL PROTECTED] writes: On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote: Why not use the index scan for every row, is this a limit in the planner ? I think there is something in the planner I don't understand :-) the planner will just use the plan it estimates will be fastest. because of how indexscans work in postgresql, in this case it would be slower than a tablescan (assuming the function really is volatile) It has nothing to do with speed, it has to do with giving the correct answer. We define correct answer as being the result you would get from a naive interpretation of the SQL semantics --- that is, for every row in the FROM table, actually execute the WHERE clause, and return the rows where it produces TRUE. I should not have used the word 'indexscan'. I just meant that it would be less effective to use an index to look up each result of the volatile function than using a tablescan. It was clear that the function would have to be called for each row, but the OP was asking (I think) why the index was not used. gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl
On Tue, 2005-01-11 at 14:59 +0300, ON.KG wrote: I'm trying in 'plperl' forking the processes by 'fork' function, but receiving this message Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork' trapped by operation mask at (eval 2) line 11. Does it mean, that in 'plperl' I can't use 'fork' function??? there are 2 variants of the language: plperl and plperlu plperlu is 'untrusted', that is, it is allowed to do dangerous stuff, but plperl is more limited and can be used by a user without privileges. OTOH, i doubt that fork is allowed even in plperlu, as i imagine it could have weird effects on the backend. gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] need help Connect failure in an applet
On Fri, 2005-01-07 at 13:03 -0500, John Doggett wrote: When I did that, I now get a different error, that the postmaster is refusingthe connection. The solution to this problem is supposed to be adding tcpip_socket = true to the postgresql.conf file and restarting the postgresql service. When I did that, I got an error when restarting the service. If you are using 8.0, you should instead look at the listen_addresses configuration. (I understand that tcpip_socket is not used any more) Note that I have not tried 8.0 yet myself, so this could just be hallucinations on my part. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Query, view join question.
On Thu, 2005-01-06 at 17:57 +0100, Joost Kraaijeveld wrote: Hi Tom, I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never make any tpo's again to upset you this way. no-one was upset. the point is just that you are more likely to get useful answers when those who would help you do not first have to guess what you did. a simplified case, as you tried to show us, is excellent, but you should test it first, and post a cut-and-paste copy of your commands and output to minimize typos. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SELECT WHERE NOT, is not working
On Wed, 2005-01-05 at 13:51 -0800, [EMAIL PROTECTED] wrote: I have a small table in which I have a Character(1) field called reengine. The field either has an X or is empty. This field does not have NULL values. There are 27 records in the table, 25 are marked with an 'X' in reengine. I am querying the table from pgadmin v1.1.0 for windows. When I write select count(*) from resource where reengine = 'X'; the result is 25 when I write select count(*) from resource where NOT (reengine = 'X'); the result is zero even though there are two records without the 'X'. it really looks like you have NULLs where you say that the field is empty. did you try: select count(*) from resource where reengine is NULL 'X'; [...] The records are being written with insert statements from a Windows2000 computer using ODBC. maybe ODBC (or your client) maps empty strings to NULLs ? gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Index on a view??
On Wed, 2005-01-05 at 13:03 -0800, Jeff Davis wrote: On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote: On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: [snip] PostgreSQL doesn't have materialized views per se but it does have functionality that can implement them. Can you tell me what you mean by that? triggers, maybe ? gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT WHERE NOT, is not working
On Wed, 2005-01-05 at 22:32 +, Ragnar Hafstað wrote: select count(*) from resource where reengine is NULL 'X'; typo. I meant of course: select count(*) from resource where reengine is NULL; gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Shared Sequences?
On Sun, 2005-01-02 at 16:19 -0500, C. Duncan Hudson wrote: [about databases sharing a sequence] I have 3 instances of the application (each for a different business unit) and I don't want them generating the same numbers for different things. I want the numbers, across all business units, to be truly chronological - so I'd like them to share the same sequence. do you realize that sequences are not garanteed to be chronological? gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Multi row sequence?
On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote: On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Dec 17, 2004 at 11:10:12 -, Filip Wuytack [EMAIL PROTECTED] wrote: Is it possible to have a sequence (as a multirow prim key), where sequence (id) only increase per group of data (grp). Why do you want to do this? It would be a lot simpler to generate unique values over the table and that will work just fine if all you need is uniqueness. Here's a case where what he said would come in handy: arranging a particular display order within the individual groups. You have the unique key for the entire table, but you need something like a serial restricted to just a group of rows. would a normal sequence not do if that was the only purpose? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Multi row sequence?
On Sun, 2004-12-19 at 22:43 +0200, Ciprian Popovici wrote: On Sun, 19 Dec 2004 13:51:39 + Ragnar Hafstað [EMAIL PROTECTED] wrote: On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote: On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Dec 17, 2004 at 11:10:12 -, Filip Wuytack [EMAIL PROTECTED] wrote: [question about mysql's special AUTO_INCREMENT on a secondary column in a multiple-column index] Here's a case where what he said would come in handy: arranging a particular display order within the individual groups. You have the unique key for the entire table, but you need something like a serial restricted to just a group of rows. would a normal sequence not do if that was the only purpose? Not if you need the main key values to stay put. I am afraid I do not follow you. gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgplsql SELECT INTO ... FOR UPDATE
On Wed, 2004-12-15 at 01:38 -0800, Eric Brown wrote: __ I'm trying to write a stored procedure in plpgsql that selects a row and possibly increments one of its fields. I thought I would do SELECT INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql doesn't like the FOR UPDATE in a stored procedure. the docs seem to imply that the FOR UPDATE clause should follow the WHERE clause. and indeed, it would seem that your should follow the '*' try SELECT * INTO my_record WHERE ... FOR UPDATE gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: relation table does not exist - HELP
On Sat, 2004-12-11 at 01:54 -0200, itamar wrote: when I run select * from table I get this error. ERROR: relation table does not exist The table name is is folded to lowercase, unless it is in quotes. so if the name of the table is TABLE, you need select * from TABLE Is this your problem ? gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
