Re: [GENERAL] Recursive Arrays 101
Thomas Kellerer writes: > I always wonder whether it's more efficient to aggregate this path > using an array rather than a varchar. Mainly because representing the > numbers as varchars will require more memory than as integer, but then > I don't know the overhead of an array structure and whether appending > to an array doesn't actually copy it. If you go that direction, you're not far away from the ltree extension (which might also be a solution for the OP's problem). -- 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] “Loop” in plpgsql Function - PostgreSQL 9.2
"drum.lu...@gmail.com" writes: > So, the new plan is: > > 1 - Select 50.000 rows and gives it a batch number. > 2 - Select *MORE* 50,000 rows and gives it a *NEW* batch number. > 3 - Select *MORE* 50,000 rows and gives it a *NEW* batch number. Why so complicated? Here's a simplified example: CREATE TABLE mytable ( id serial NOT NULL, payload int NOT NULL, batch_number int NULL, PRIMARY KEY (id) ); INSERT INTO mytable (payload) SELECT x FROM generate_series(1, 2000) AS g(x); \set batchSize 600 UPDATE mytable SET batch_number = (id % (SELECT count(*) FROM mytable) / :batchSize) + 1; SELECT batch_number, count(*) AS cnt FROM mytable GROUP BY batch_number ORDER BY batch_number; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is PRIMARY KEY the same as UNIQUE NOT NULL?
Ben Leslie writes: > "Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL" > > I wanted to clarify if that was, technically, true. Yes, but see below. > "identifying a set of columns as primary key also provides metadata > about the design of the schema, as a primary key implies that other > tables can rely on this set of columns as a unique identifier for > rows." This means that e.g. you can use ALTER TABLE othertbl FOREIGN KEY (refid) REFERENCES mytbl without specifying the column(s) of mytbl. This implies that there can be only one primary key (possibly covering more than one column), and that's the second difference to UNIQUE NOT NULL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] log_min_duration question
>From the 9.5 docs: log_min_duration_statement (integer) Causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds. Setting this to zero prints all statement durations. Minus-one (the default) disables logging statement durations. For example, if you set it to 250ms then all SQL statements that run 250ms or longer will be logged. While the first sentence explicitly states a fixed unit (namely msec), the last sentence seems to imply a (maybe optional) unit suffix (ms in this case). Which one is true? How would e.g. "SET log_min_duration_statement=1min" be interpreted? I'd prefer a docs clarification. -- 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] execute same query only one time?
Johannes writes: >> What the reason to execute all statements which return different >> columns at once? >> >>> Saving roundtrips, >> >> In most cases they are not so big. Getting a bunch of duplicated data >> is wasting you network bandwidth and don't increase speed. > > In my and your example no duplicated data (result sets) is send over the > network. The server do not need to wait until the client snips out the > id and sends it id in the next query again. So the server can compute > the result set without external dependencies as fast as possible. Sounds like what you're really after is a stored procedure, isn't it? -- 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] Duplicate records returned
In article <[EMAIL PROTECTED]>,
Richard Huxton <[EMAIL PROTECTED]> writes:
>> $the_sql = " SELECT projectname, username, sum(hours)";
>> $the_sql .= " FROM timerecs";
>> $the_sql .= " WHERE projectname = projects.projectname ";
>> $the_sql .= " AND projectname = restrictions.projectname";
>> $the_sql .= " AND projects.parent = 'Projects'";
>> $the_sql .= " AND projects.pct < 100";
>> $the_sql .= " AND restrictions.hidden = 5";
>> $the_sql .= " AND projectname = 'Testing'"; # just for tsting
>> $the_sql .= " AND username = 'long'";# just for testing
>> $the_sql .= " AND projectname = projects.projectname ";
>> $the_sql .= " GROUP BY projectname, username";
>> $the_sql .= " ORDER BY projectname, username";
>> $the_sql .= " ;";
> You might want to read up on "HERE documents" for multi-line blocks of text.
In case the above code is Perl, I think
my $sql = q{
SELECT ...
FROM ...
WHERE ...
GROUP ...
};
looks nicer than a here-document.
> 1. You've also not put all your tables into the FROM clause:
>FROM timerecs, projects, restrictions
>This *should* be generating a warning of some kind
> 2. You're not qualifying which column comes from which table, which
> makes it harder to see what's happening. Try:
>FROM timerecs t, projects p, restrictions r
>WHERE t.projectname = p.projectname
>AND ...
>That's called table aliasing, where you give a short name to tables.
This still mixes JOIN conditions with other result restrictions.
SELECT ...
FROM timerecs t
JOIN projects p ON p.projectname = t.projectname
...
makes it more explicit.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] "Concatenate" two queries - how?
In article <[EMAIL PROTECTED]>, "A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: >> Now, I want to enable queries which display national as well as >> regional values. I could probably work with independent queries, but >> I think it would be "cleaner" and more efficient to get everything >> into a single query. >> >> Can someone give me a hint how this would work? >> >> Thanks a lot! > select ... UNION select ... Apparently Stefan doesn't know about UNION, and thus he probably doesn't know that UNION ALL is almost always preferrable. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] IP addresses
In article <[EMAIL PROTECTED]>, "Tom Allison" <[EMAIL PROTECTED]> writes: > I am planning on doing a LOT of work with ip addresses and thought that the > inet data type would be a great place to start. Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be happy. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Restart a sequence regularly
In article <[EMAIL PROTECTED]>, Richard Huxton <[EMAIL PROTECTED]> writes: > Kathy Lo wrote: >> On 11/21/07, Richard Huxton <[EMAIL PROTECTED]> wrote: >>> You probably shouldn't attach any meaning to the numbers from a sequence >>> - they're just guaranteed to be unique, nothing else. > What you say here contradicts the following. >> Actually, the sequence is formed by 4-digit of year and 6-digit of >> sequence. > So you *are* attaching significance to the number (by adding the > current year to the front of it). > Don't block users - have multiple sequences. If you define > my_seq_2007, my_seq_2008, my_seq_2009 etc and then wrap access to them > in a function you can EXTRACT() the year from the CURRENT_DATE and use > that to form your per-year unique value. Since sequences don't guarantee consecutivity anyway, why not just use one sequence and prepend the year, e.g. by a view? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] count(*) and bad design was: Experiences with extensibility
In article <[EMAIL PROTECTED]>, Chris Browne <[EMAIL PROTECTED]> writes: > There may be a further optimization to be had by doing a > per-statement trigger that counts the number of INSERTs/DELETEs done, > so that inserting 30 tuples (in the table being tracked) leads to > adding a single tuple with count of 30 in the summary table. This would be nice, but at least the 8.2.4 docs say Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement. Is this restriction removed in a later version? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
In article <[EMAIL PROTECTED]>, Phil Rhoades <[EMAIL PROTECTED]> writes: > People, >> select count(*) as cnt, name from tst group by name having count(*) = 1 > This worked for my basic example but not for my actual problem - I get > "column comment must appear in the GROUP BY clause or be used in an > aggregate function" errors so I have a related question: > With table: > name comment > 1first comment > 2second comment > 3third comment > 3fourth comment > 4fifth comment > 5sixth comment > - how can I use something like the previous select statement but where > the comment field does not appear in the "group by" clause and gives the > following result: > 1first comment > 2second comment > 4fifth comment > 5sixth comment If you want to select both columns, but have uniqueness over the first only, you can use a derived table: SELECT tbl.name, tbl.comment FROM tbl JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t ON t.name = tbl.name ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is news.postgresql.org down?
In article <[EMAIL PROTECTED]>, Rainer Bauer <[EMAIL PROTECTED]> writes: > Hopefully it won't be down for too long as I use a newsreader to read > the lists. I use www.gmane.org for that. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Constraint: string length must be 32 chars
In article <4cba2bc4.9030...@darrenduncan.net>, Darren Duncan writes: > I would further recommend turning the above into a separate data type, > especially if you'd otherwise be using that constraint in several > places, like this ... FWIW, the shatypes contrib package includes a binary md5 datatype. -- 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] Advice needed on application/database authentication/authorization/auditing model
In article <20101022161331.gd9...@frohike.homeunix.org>, Peter Bex writes: > As far as I can see, this would imply either creating views on the > for every user (or company?), or manually crafting queries > to do the same. Not necessarily. Consider this: CREATE TABLE t1 ( id serial NOT NULL, val int NOT NULL, usr text NOT NULL, PRIMARY KEY (id) ); COPY t1 (val, usr) FROM stdin; 1 u1 2 u1 3 u2 4 u3 \. CREATE VIEW t1v (id, val) AS SELECT id, val FROM t1 WHERE usr = current_user; Now user "u1" can only see rows 1 and 2. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.2/8.3 incompatibility
I've found an incompatibility between PostgreSQL 8.2.4 and 8.3.0 which
is not clearly documented. Here's a short example:
CREATE TABLE t1 (
id CHAR(5) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (id) VALUES ('t1id1');
INSERT INTO t1 (id) VALUES ('t1id2');
INSERT INTO t1 (id) VALUES ('t1id3');
CREATE TABLE t2 (
id SERIAL NOT NULL,
t1id VARCHAR(5) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t2 (t1id) VALUES ('t1id1');
INSERT INTO t2 (t1id) VALUES ('t1id2');
INSERT INTO t2 (t1id) VALUES ('t1id3');
ALTER TABLE t2
ADD CONSTRAINT t2_t1id_fk
FOREIGN KEY (t1id) REFERENCES t1 (id);
(Note the different column types.)
This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
following (somewhat misleading) error message:
ERROR: insert or update on table "t2" violates foreign key constraint
"t2_t1id_fk"
DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
Should this be documented explicitly?
Should the error message look different?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] 8.2/8.3 incompatibility
In article <[EMAIL PROTECTED]>,
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Thu, 7 Feb 2008, Harald Fuchs wrote:
>> This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
>> following (somewhat misleading) error message:
>>
>> ERROR: insert or update on table "t2" violates foreign key constraint
>> "t2_t1id_fk"
>> DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
> If the types were considered not comparable, you should have gotten a
> message to that effect rather than a not present message.
Yes, this was really confusing.
> More to the point that comparison should have succeeded I think.
Well, it did succeed in 8.2.x, and I'm actually grateful that 8.3.0
noticed the sloppiness on my side.
> What do the following give?
> select * from t1 where id=CAST('t1id1' as VARCHAR(5));
This returns t1id1, as expected.
> select * from ONLY t2 fk LEFT OUTER JOIN ONLY t1 pk ON
> (pk.id = fk.t1id) WHERE pk.id IS NULL;
This returns an empty result set.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Are indexes blown?
In article <[EMAIL PROTECTED]>, "Shoaib Mir" <[EMAIL PROTECTED]> writes: > On Fri, Feb 15, 2008 at 6:13 PM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Thanks. But I had installed from rpm. Can I just download that .so > file and put in the lib folder for pgsql and then start using it? > Well I would say download the source for the same version you have, copy it to > your desktop machine, build it and then build the .so file for contrib module > using 'make' and 'make install' once that is done copy the .so from lib > folder of PG to your production PG box's lib folder. But you have to ensure that you build PostgreSQL on your desktop machine in exactly the same way as the RPM got built (integer_datetimes etc). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
In article <[EMAIL PROTECTED]>, Alban Hertroys <[EMAIL PROTECTED]> writes: >> I'm thinking of something like the trick of surrounding C code with >> pairs of #if 0 and #endif, which effectively comments out code, >> even when it contains /* C-style comments */. >> >> Is there some similar trick for SQL code? > I have been playing with the idea of using cpp to pre-process such > files, and maybe even put them in a Makefile. I don't think there's > any reason that wouldn't be possible with SQL files. Added bonus, you > can use macros in your SQL, for things like environment paths (for > including other SQL files for example) etc. If it's OK to preprocess SQL, you could also use the M4 macro processor which comes with every decent operating system (i.e. Unix). -- 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] Survey: renaming/removing script binaries (createdb, createuser...)
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> writes: >> I note that we can continue to have the current executables stashed in >> PREFIX/share/libexec and let the "pg" executable exec them. > Not share/ surely, since these are executables, but yeah. > This brings me to the idea that "pg" is a very small stupid program > that just tries to match its first argument against a filename in > PREFIX/libexec/postgresql. If it finds a match it execs that program > with the remaining args, else it fails. Add an optional command-line argument for specifying an alternative PREFIX, and the problem of multiple PostgreSQL versions on one host is solved as well. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with planner choosing nested loop
In article <[EMAIL PROTECTED]>, "Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> writes: > On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote: >> ... I have no idea how it could be fixed. > - CREATE INDEX xifoo ON foo(bar_id); > - ANALYZE; > - Retry. A compound index CREATE INDEX xifoo2 ON foo (foo_a, bar_id) might be more worthwhile. -- 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] Numbering rows by date
In article <[EMAIL PROTECTED]>, "Andrus" <[EMAIL PROTECTED]> writes: > I have table > create Document ( docdate date, docorder integer ) > I need update docorder column with numbers 1,2 in docdate date order > Something like > i = 1; > UPDATE Document SET docorder = i++ > ORDER BY docdate; > How to do this is PostgreSQL 8.2 ? I don't think you can avoid a temp table: CREATE TEMP TABLE tmp ( docdate date, docorder serial ); INSERT INTO tmp (docdate) SELECT docdate FROM documents ORDER BY docdate; UPDATE documents d SET docorder = t.docorder FROM tmp t WHERE d.docdate = t.docdate; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] generate_series woes
I think there's something sub-optimal with generate_series. In the following, "documents" is a table with more than 12 rows, vacuumed and analyzed before the queries. EXPLAIN ANALYZE SELECT count (d.id), floor (s.val / 5000) FROM generate_series (1::INT, 5009) AS s (val) LEFT JOIN documents d ON d.id = s.val GROUP BY 2 ORDER BY 2; This returns: Sort (cost=4231.52..4232.02 rows=200 width=8) (actual time=41.886..41.887 rows=2 loops=1) Sort Key: (floor(((s.val / 5000))::double precision)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=4219.88..4223.88 rows=200 width=8) (actual time=41.843..41.846 rows=2 loops=1) -> Nested Loop Left Join (cost=0.00..4214.88 rows=1000 width=8) (actual time=1.274..38.193 rows=5009 loops=1) -> Function Scan on generate_series s (cost=0.00..12.50 rows=1000 width=4) (actual time=1.209..3.102 rows=5009 loops=1) -> Index Scan using documents_pkey on documents d (cost=0.00..4.18 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=5009) Index Cond: (d.id = s.val) Total runtime: 42.218 ms Now let's wrap generate_series into an SQL function: CREATE FUNCTION genser (int, int) RETURNS SETOF int AS $$ SELECT * FROM generate_series ($1, $2) AS g(x); $$ LANGUAGE sql; EXPLAIN ANALYZE SELECT count (d.id), floor (s.val / 5000) FROM genser (1::INT, 5009) AS s (val) LEFT JOIN documents d ON d.id = s.val GROUP BY 2 ORDER BY 2; Not surprisingly, this returns the same plan: Sort (cost=4479.02..4479.52 rows=200 width=8) (actual time=43.606..43.607 rows=2 loops=1) Sort Key: (floor(((s.val / 5000))::double precision)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=4467.38..4471.38 rows=200 width=8) (actual time=43.559..43.561 rows=2 loops=1) -> Nested Loop Left Join (cost=0.00..4462.38 rows=1000 width=8) (actual time=3.564..39.740 rows=5009 loops=1) -> Function Scan on genser s (cost=0.00..260.00 rows=1000 width=4) (actual time=3.503..5.435 rows=5009 loops=1) -> Index Scan using documents_pkey on documents d (cost=0.00..4.18 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=5009) Index Cond: (d.id = s.val) Total runtime: 44.047 ms (9 rows) But look what happens if we tell PostgreSQL how many rows "genser" will return: CREATE FUNCTION genser (int, int) RETURNS SETOF int AS $$ SELECT * FROM generate_series ($1, $2) AS g(x); $$ LANGUAGE sql ROWS 5009; EXPLAIN ANALYZE SELECT count (d.id), floor (s.val / 5000) FROM genser (1::INT, 5009) AS s (val) LEFT JOIN documents d ON d.id = s.val GROUP BY 2 ORDER BY 2; Now we get a better plan: Sort (cost=15545.54..15546.04 rows=200 width=8) (actual time=27.857..27.859 rows=2 loops=1) Sort Key: (floor(((s.val / 5000))::double precision)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=15533.89..15537.89 rows=200 width=8) (actual time=27.817..27.819 rows=2 loops=1) -> Merge Right Join (cost=1610.15..15508.85 rows=5009 width=8) (actual time=7.714..24.133 rows=5009 loops=1) Merge Cond: (d.id = s.val) -> Index Scan using documents_pkey on documents d (cost=0.00..13472.20 rows=125518 width=4) (actual time=0.045..6.112 rows=5010 loops=1) -> Sort (cost=1610.15..1622.67 rows=5009 width=4) (actual time=7.651..9.501 rows=5009 loops=1) Sort Key: s.val Sort Method: quicksort Memory: 427kB -> Function Scan on genser s (cost=0.00..1302.34 rows=5009 width=4) (actual time=3.559..5.262 rows=5009 loops=1) Total runtime: 28.445 ms (12 rows) Since generate_series is a builtin function, can't it tell how many rows it will return? -- 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] generate_series woes
In article <[EMAIL PROTECTED]>, "Merlin Moncure" <[EMAIL PROTECTED]> writes: > On Mon, Apr 14, 2008 at 5:21 AM, Harald Fuchs <[EMAIL PROTECTED]> wrote: >> I think there's something sub-optimal with generate_series. >> In the following, "documents" is a table with more than 12 rows, >> vacuumed and analyzed before the queries. > everything is working exactly as intended. while it's obvious to you > that the generate series function returns a particular number of rows > based on your supplied inputs, it's not (yet) obvious to the planner. Which was exactly my point. Since generate_series is a builtin function, the planner could theoretically know the number of rows returned, thus choosing a better plan. OTOH, the difference between theory and reality is in theory smaller than in reality. > your genser function supplies the hint the planner needs and it > adjusts the plan. most set returning functions (particularly > non-immutable ones) are not so easy to determine the # of rows from > the input parameters anyways. Yes, of course. I used "genser" just to show that there is a better plan. -- 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] WITH RECURSIVE clause -- all full and partial paths
In article , aryoo writes: > Dear list, > In reference to the message below posted on the 'pgsql-hackers' list regarding > 'iterative' queries, > could anyone help me write the queries that return all full and all partial > paths from the root? Probably you want to use the following query: WITH RECURSIVE subdepartment AS ( SELECT id, parent_department, name AS path FROM department WHERE name = 'A' UNION ALL SELECT d.id, d.parent_department, sd.path || '.' || d.name FROM department d JOIN subdepartment sd ON sd.id = d.parent_department ) SELECT id, path FROM subdepartment; This returns: id | path +- 1 | A 2 | A.B 3 | A.B.C 4 | A.B.D 6 | A.B.C.F (5 rows) -- 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] Return LEFT JOINed tables when one has no matching column
In article <4a425379.90...@alteeve.com>, Madison Kelly writes: > SELECT > a.tbl1_name, > b.tbl2_date, > c.tbl3_value AS some_value > FROM > table_1 a > LEFT JOIN > table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id) > LEFT JOIN > table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id) > WHERE > c.tbl3_variable='some_variable' > AND > a.tbl1_id=123; > I want the data from table_1 and table_2 to return and table_3 to > return NULL when there is no matching > c.tbl3_variable='some_variable'. Is this possible? Move "c.tbl3_variable='some_variable'" from WHERE to c's ON clause. -- 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] Refer to another database
In article <4a77c4af.2060...@gmx.de>, Andreas Kalsch writes: > To be completely > in context of a schema - so that I can use all tables without the > prefix - I have to reset the search_path very often. Why? Just say "ALTER DATABASE foo SET search_path = public, bar, baz" once and you're done. -- 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] Generating random unique alphanumeric IDs
In article <20090816122526.gw5...@samason.me.uk>, Sam Mason writes: > I've just had a look and PG does actually seem to be returning values as > I'd expect, i.e. 0 <= n < 1. That's what everyone would expect. If it's really implemented like that the documentation is wrong, isn't it? -- 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] unique index for periods
In article <20090820065819.ga2...@gheift.kawo1.rwth-aachen.de>, Gerhard Heift writes: > Hello, > I try to create an unique index for a (time)period, and my goal is to > prevent two overlapping periods in a row. > ... > Is there another solution to solve my problem? Have a look at http://pgfoundry.org/projects/temporal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to match sets?
In article , Alban Hertroys writes: > An example of the two sets I need to "join" are, at the left hand side: > unit | token | exponent > ---+---+-- > m.s^-1 | m | 1 > m.s^-1 | s | -1 > m.s^-2 | m | 1 > m.s^-2 | s | -2 > And at the right hand side: > token | exponent > ---+-- > m | 1 > s | -2 > The goal of the query is to find which unit at the left hand side > matches all the tokens and exponents at the right hand side, which > would be 'm.s^-2' in the above example. The order in which the tokens > are returned can be random, there isn't really a defined order as it > doesn't change the meaning of a unit. > I do have a possible solution using array_accum [1][2] on an ordered > version (on unit,token,exponent) of these sets. It's not a pretty > solution though, I'm not happy with it - it's a transformation (from a > set to an array) where I feel none should be necessary. Isn't there a > better solution? Hm, how about a "double negation", i.e. return all units except those with a non-match? In SQL: SELECT t1.unit FROM t1 EXCEPT SELECT t1.unit FROM t1 LEFT JOIN t2 ON t2.token = t1.token AND t2.exponent = t1.exponent WHERE t2.token IS NULL -- 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] cidr data type question
In article <1257149236.3426.9.ca...@localhost>, Vasiliy G Tolstov writes: > Hello. > I have table with cidr data type column (table is white/black list of > networks). > Does it possible to query - is the some address contains in any cidr > network in this table? (for all networks in the table do "contains or > equals" > http://www.postgresql.org/docs/8.4/interactive/functions-net.html > ) If you mess with IP addresses, you should have a look at the ip4r contrib module. Since I discovered it, I've never looked back at cidr/inet. -- 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] Adding a default value to a column after it exists
In article <20110413163120.gu24...@shinkuro.com>, Andrew Sullivan writes: > On Wed, Apr 13, 2011 at 09:21:20AM -0700, Gauthier, Dave wrote: >> Is there a way to add a default value definition to an existing column? >> Something like an "alter table... alter column... default 'foo'". > ALTER TABLE table ALTER [ COLUMN ] column SET DEFAULT expression > (see http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html) > Note that this doesn't actually update the fields that are NULL in the > column already. For that, once you had the default in place, you > could do > UPDATE table SET column = DEFAULT WHERE column IS NULL And you probably want to do ALTER TABLE table ALTER [ COLUMN ] column SET NOT NULL after that. -- 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] Select from Java Strings
In article <4e116e11.1030...@gmail.com>,
Daron Ryan writes:
> Hello David,
> This is a simplified version of my own attempt:
> SELECT *
> FROM ("oxford", "webster")
> WHERE NOT ( columnName = ANY (SELECT name FROM dictionaries))
> The idea is that "oxford" and "webster" come from the Java program end up
> in the column called columnName.
If the list is not very long, you could use a VALUES expression:
SELECT g.x
FROM (VALUES ('oxford'), ('webster')) AS g(x)
WHERE g.x NOT IN (SELECT name FROM dictionaries)
--
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] Difference between inet and cidr
In article , Marti Raudsepp writes: > Hi, > On Tue, Jul 5, 2011 at 09:50, Yan Cheng CHEOK wrote: >> The essential difference between inet and cidr data types is that inet >> accepts values with nonzero bits to the right of the netmask, whereas cidr >> does not. > Say, if you have a /8 netmask, the 'cidr' type requires that all the > 24 rightmost bits are zero. inet does not have this requirement. > E.g: > db=# select '255.0.0.0/8'::cidr; > 255.0.0.0/8 > db=# select '255.1.0.0/8'::cidr; > ERROR: invalid cidr value: "255.1.0.0/8" > DETAIL: Value has bits set to right of mask. > And inet allows this: > db=# select '255.1.0.0/8'::inet; > 255.1.0.0/8 > Hope that helps. Do the inet/cidr types have any advantage over the ip4r contrib module? -- 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] hstore installed in a separate schema
In article <1312401318.5199.yahoomailclas...@web120108.mail.ne1.yahoo.com>, Ioana Danes writes: > Hi, > I am planning to use the contrib module hstore > but I would like to install it on a separate schema, not public, > and include the schema in the search_path. > Do you know if there are any issues with this scenario. > In the hstore.sql script I see it forces it into public: > -- Adjust this setting to control where the objects get created. > SET search_path = public; > I did some tests for my use cases and it all works fine... That's the way I use any contrib module because it doesn't mix my own stuff with the module's stuff. With an ALTER DATABASE mydb SET search_path = public, hstore all that works transparently. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join between a table and function.
In article , Lauri Kajan writes: > I have also tried: > select > *, getAttributes(a.id) > from > myTable a > That works almost. I'll get all the fields from myTable, but only a > one field from my function type of attributes. > myTable.id | myTable.name | getAttributes > integer | character| attributes > 123 | "record name" | (10,20) > What is the right way of doing this? If you want the attributes parts in extra columns, use SELECT *, (getAttributes(a.id)).* FROM myTable a -- 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] Bit datatype performance?
In article , Antonio Vieiro writes: > Hi all, > One of my entities 'E' may be 'tagged' with an arbitrary set of 256 tags 'T'. > A first approach could be to add a M:N relationship between 'E' and 'T'. > A second way to do this could be to add a BIT(256) datatype to 'E', > setting bits to '1' if the entity is tagged with each one of the 256 > tags (i.e. using a 'bitmask' on the set of tags). > Since querying entities 'E' with a certain set of tags 'T' must be > very fast I was wondering if the second approach would be faster. What > do you think? I think the best way is to put the tags into a hstore column. With a GiST index on that column access is very fast. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.1.0 bug?
I think I've found a bug either in PostgreSQL 9.1.0 or in the ip4r package (version 1.05). Since the problematic behavior occurs on different tables and on different servers, it should be relatively easy to reproduce: CREATE TABLE foo ( id serial NOT NULL, range ip4r NOT NULL, PRIMARY KEY (id) ); Now populate this table with >2M rows where the range values don't overlap. Ensure this by doing ALTER TABLE foo ADD CONSTRAINT foo_range_ix EXCLUDE USING gist (range WITH &&); After a while PostgreSQL will begin to eat all your main memory and then crash. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.1.0 bug?
In article <21641.1316159...@sss.pgh.pa.us>, Tom Lane writes: > Harald Fuchs writes: >> I think I've found a bug either in PostgreSQL 9.1.0 or in the ip4r >> package (version 1.05). > Good catch --- gistendscan is forgetting to free so->giststate. Confirmed - adding a "pfree(so->giststate)" solves my problem. Thanks for the quick fix! -- 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] Column Privileges: NULL instead of permission denied
In article , Matthew Hawn writes: > I have a table with privileged data that is restricted using column level > permissions. I would like to have single query that returns data from > the table. If the user has permission, it should return the data but > return NULL if the user does not have permission. I do not want to > create separate queries in my application for different users. > Ex: > Table people: Name, SSN > If I try: > Select name, ssn from people; > I get if the user does not have permission: > **ERROR: permission denied for relation people ** > I would like to get: > No Permission: > Dave, NULL > Bill, NULL > Steve, NULL > Permission: > Dave, 456 > Bill, 789 > Steve, 123 The only thing I can think of is an ugly kludge: -- Define the following function as a user with SELECT privilege CREATE FUNCTION doselect(IN currusr text, OUT name text, OUT ssn int) RETURNS SETOF RECORD AS $$ SELECT name, CASE has_column_privilege($1, 'people', 'ssn', 'SELECT') WHEN true THEN ssn ELSE NULL END AS ssn FROM people $$ LANGUAGE sql SECURITY DEFINER; CREATE VIEW people_view AS SELECT * FROM doselect(current_user); -- The following query will do what you want SELECT * FROM people_view; -- 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] New feature: accumulative functions.
In article
,
Pavel Stehule writes:
> 2011/9/25 pasman pasmański :
>> I found second use case. Look at expression:
>>
>> where left(str,n)='value'
>>
>> function left(str,n) increase monotonically for str and n. With this
>> feature it can use index on str.
>>
>> Classic index needs recreating.
>>
> these use cases are just theory - for example - this case should be
> solved with immutable functions
> I can use a functional index left(str, const) and use a query
> where left(str, const) = left('value', const) and left(str, n) = 'value'
> There are a theoretical cases, but these cases should be solved via
> special data type and GiST index
If I don't misunderstand you, this data type is called 'prefix_range',
available at PgFoundry.
--
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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
In article , Marti Raudsepp writes: > Ah, the reverse() function is not included with PostgreSQL 9.0 yet. > This is what I use: > CREATE FUNCTION reverse(input text) RETURNS text > LANGUAGE plpgsql IMMUTABLE STRICT AS $$ > DECLARE > result text = ''; > i int; > BEGIN > FOR i IN 1..length(input) BY 2 LOOP > result = substr(input,i+1,1) || substr(input,i,1) || result; > END LOOP; > RETURN result; > END$$; Pavel Stehule has found a better solution for that: CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$ SELECT string_agg(substring($1 FROM i FOR 1), '') FROM generate_series(length($1), 1, -1) g(i) $$ language sql; But the best, of course, is upgrading to 9.1. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What about improving the rules system we have, was Re: Rules going away
In article <4116.1317226...@sss.pgh.pa.us>, Tom Lane writes: > Not sure this specific proposal makes any sense at all. IMO the only > real advantage that rules have over triggers is that they work on a > set-operation basis not a tuple-by-tuple basis. Isn't that what statement-level triggers are for, at least in other DB systems? How about telling PostgreSQL's statement-level triggers something about the set of rows they affect? -- 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] Ltree - how to sort nodes on parent node
In article <59670b22-30cb-4e6e-83c8-c1d1036c9...@solfertje.student.utwente.nl>, Alban Hertroys writes: > 2). Drop the ltree column and go with a truly recursive approach, something > like this: > CREATE TABLE node ( > categorytextNOT NULL PRIMARY KEY, > sort_order int NOT NULL, > parent textREFERENCES tree (category) > ON UPDATE CASCADE > ON DELETE CASCADE > ); > WITH RECURSIVE tree AS ( > SELECT * > FROM node >WHERE parent IS NULL > UNION ALL > SELECT node.* > FROM tree, node >WHERE node.parent = tree.category >ORDER BY sort_order > ) > SELECT * FROM tree; Here's a working version: WITH RECURSIVE tree (path, category, sort_order, parent) AS ( SELECT category, category, sort_order::text, parent FROM node WHERE parent IS NULL UNION ALL SELECT t.path || '.' || n.category, n.category, t.sort_order || '.' || n.sort_order, n.parent FROM tree t JOIN node n ON n.parent = t.category ) SELECT path FROM tree ORDER BY sort_order -- 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] Ltree - how to sort nodes on parent node
In article <1f96e061-713c-4929-a7d9-278e5b608...@solfertje.student.utwente.nl>, Alban Hertroys writes: > On 20 Apr 2010, at 18:05, Harald Fuchs wrote: >> Here's a working version: >> >> WITH RECURSIVE tree (path, category, sort_order, parent) AS ( >> SELECT category, category, sort_order::text, parent >> FROM node >> WHERE parent IS NULL >> UNION ALL >> SELECT t.path || '.' || n.category, >> n.category, >> t.sort_order || '.' || n.sort_order, >> n.parent >> FROM tree t >> JOIN node n ON n.parent = t.category >> ) >> SELECT path >> FROM tree >> ORDER BY sort_order > May be, but then you're just re-inventing ltree again. Not quite - with proper normalization you're storing the path elements only once and create the ltree-style paths on the fly. > I'm pretty sure this must be possible without adding convoluted > things like casting sort orders to text (which can for example cause > issues like '10' ending up between '1' and '2'). Ah, you're right. I think _some_ convolution is still needed because we must remember the sort order for each path element. > Since this is 8.4 anyway (CTE's after all), can't the sorting be > done using a windowing function or something? We have recursion now, > there's got to be a proper solution, I just can't get my mind around > it right now. I don't think windowing functions will help here. Anyway, here's a complete example which also deals with the 1/10/2 issue you mentioned above: CREATE TABLE node ( id serial NOT NULL, category text NOT NULL, sort_order int NOT NULL, parent int NULL REFERENCES node (id), PRIMARY KEY (id) ); CREATE UNIQUE INDEX node_pc_uq ON node (parent, category); -- Enforce unambiguous sorting CREATE UNIQUE INDEX node_ps_uq ON node (parent, sort_order); COPY node (id, category, sort_order, parent) FROM stdin; 1 Top 1 \N 2 Science 1 1 3 Physics 1 2 4 Chemistry 2 2 5 Biology 3 2 6 History 4 2 7 Archeology 5 2 8 Hobby 2 1 9 Fishing 1 8 10 Football2 8 11 Swimming3 8 12 Climbing4 8 13 Colors 3 1 14 Black 1 13 15 Red 2 13 16 Blue3 13 17 Gray4 13 18 Purple 5 13 19 Brown 6 13 \. WITH RECURSIVE tree (path, id, sort_order, parent) AS ( SELECT category, id, ARRAY[sort_order], parent FROM node WHERE parent IS NULL UNION ALL SELECT t.path || '.' || n.category, n.id, t.sort_order || n.sort_order, n.parent FROM tree t JOIN node n ON n.parent = t.id ) SELECT path, id, sort_order, parent FROM tree ORDER BY sort_order; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some insight on the proper SQL would be appreciated
In article <4c0f4ba8.3040...@gmail.com>, Ognjen Blagojevic writes: > Plenty of solutions here: > http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ This doesn't mention the incredibly powerful windowing functions of PostgreSQL >= 8.4.0: SELECT username, firstname, lastname, signedup FROM ( SELECT username, firstname, lastname, signedup, row_number() OVER (PARTITION BY username ORDER BY signedup) FROM mytbl ) dummy WHERE row_number <= 5 -- 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] index scan and functions
In article <20100719162547.ga17...@localhost>, arno writes: > Thanks, that's exactly what I was looking for. No, I'd say you're looking for the ip4r package which provides an indexable IP address range type. -- 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] Prefix LIKE search and indexes issue.
In article , Marcelo de Moraes Serpa writes: > Some good souls hinted me at the prefix extension, but > how would I use it? Like this: CREATE TABLE users ( id serial NOT NULL, name text NOT NULL, reversed_domain prefix_range NULL, PRIMARY KEY (id) ); CREATE INDEX users_dom_ix ON users USING gist (reversed_domain); SELECT id, name, reversed_domain FROM users WHERE reversed_domain @> 'com.app.mycompany'; -- 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] A challenge for the SQL gurus out there...
In article <[EMAIL PROTECTED]>, "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > or maybe not and I'm just not getting it. > So here's the scenario: > I have 3 tables > forum: with primary key "id" > forum_thread: again primary key "id" and a foreign key "forum_id" referencing > th primary key of the forum table > forum_post: again primary key "id" with a forign key "thread_id" referencing > the primary key of the forum_thread table > The forum_post table also has a field "date_posted" (timestamp) with an index > on it. > What I need is an efficient way to create overviews (just think about a forum) > I.e. the forum table has 3 records, one for each forum category > I want to get a list looking like > forum idthread_id post_id > 1 6 443 > 2 9 123 > 3 3 557 > The trick is, that I need the latest post (by the date posted column) for > each > category (speak forum_id). Due to the keys the forum_thread table has to be > involved. > I've been thinking about this for hours now, but I just can't come up with a > query that will give me 3 records, one for each category showing the latest > post. Try something like this: SELECT t1.forum_id, p1.thread_id, p1.id AS post_id, p1.date_posted FROM forum f1 JOIN forum_thread t1 ON t1.forum_id = f1.id JOIN forum_post p1 ON p1.thread_id = t1.id LEFT JOIN ( SELECT t2.forum_id, p2.thread_id, p2.date_posted FROM forum_thread t2 JOIN forum_post p2 ON p2.thread_id = t2.id ) AS f2 ON f2.forum_id = f1.id AND f2.date_posted > p1.date_posted WHERE f2.forum_id IS NULL ORDER BY t1.forum_id -- 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] psql scripting tutorials
In article <[EMAIL PROTECTED]>, Greg Smith <[EMAIL PROTECTED]> writes: > On Tue, 9 Sep 2008, Artacus wrote: >> Can psql access environmental variables or command line params? > $ cat test.sql > select :TEST as "input"; > $ psql -v TEST=16 -f test.sql > input > --- > 16 > (1 row) Nice trick, but when I try the following variant: psql -v TEST=16 -c 'select :TEST as "input"' I get ERROR: syntax error at or near ":" LINE 1: select :TEST as "input" This seems to be contrary to the psql manual page: These assignments are done during a very early stage of start-up... -- 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] Converting string to IN query
In article <[EMAIL PROTECTED]>,
"Andrus" <[EMAIL PROTECTED]> writes:
> I found that following query works:
> create temp table test ( test int ) on commit drop;
> insert into test values(1);
> select * from test where test = ANY ( '{1,2}' );
> Is this best solution ?
> Will it work without causing stack overflow with 8.2 server default
> settings
> if string contains some thousands numbers ?
If you get thousands of numbers, it is safer and maybe also faster to
put them into a temporary table, analyze it, and then join it to the
table in question.
--
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] OR or IN ?
In article <[EMAIL PROTECTED]>,
"A. Kretschmer" <[EMAIL PROTECTED]> writes:
> am Tue, dem 14.10.2008, um 8:33:21 +0200 mailte Luca Ferrari folgendes:
>> Hi all,
>> I've got a query with a long (>50) list of ORs, like the following:
>>
>> SELECT colB, colC FROM table WHERE colA=X OR colA=Y OR colA=Z OR
>>
>> Is there any difference in how postgresql manages the above query and the
>> following one?
>>
>> SELECT colB, colC FROM table WHERE colA IN (X,Y,Z,...)
> Depends on the version: (same table foo)
> 8.1:
> test=*# explain select * from foo where a in (1,2,3);
> QUERY PLAN
> -
> Seq Scan on foo (cost=0.00..47.45 rows=32 width=4)
>Filter: ((a = 1) OR (a = 2) OR (a = 3))
> 8.4devel:
> test=# explain select * from foo where a in (1,2,3);
> QUERY PLAN
> -
> Seq Scan on foo (cost=0.00..43.00 rows=36 width=4)
>Filter: (a = ANY ('{1,2,3}'::integer[]))
> As you can see, 8.1 rewrite the query to many ORs.
I think that "OR or IN" is the wrong question. Where do those >50
values come from? If they come from a DB operation, just include this
operation in your query. Otherwise, you should ask yourself how many
values you might get: >50, >500, >5000? There's a point where it's
probably more efficient to COPY all those values into an indexed
temporary table, ANALYZE it, and JOIN it into your query.
--
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 m:n-Combination
In article <[EMAIL PROTECTED]>,
Ludwig Kniprath <[EMAIL PROTECTED]> writes:
> Dear list,
> I have to solve a simple Problem, explained below with some sample-Data.
> A typical M:N-constellation, rivers in one table, communities in the
> other table, m:n-join-informations (which river is running in which
> community) in a third table.
> Table rivers:
> R_ID R_Name
> 1 river_1
> 2 river_2
> 3 river_3
> 4 river_4
> 5 river_5
> Table communities :
> C_ID C_Name
> 1 community_1
> 2 community_2
> 3 community_3
> 4 community_4
> 5 community_5
> Join-table
> mn_2_r_id mn_2_c_id
> 1 1
> 1 2
> 1 3
> 1 4
> 2 1
> 3 2
> 3 5
> 4 3
> ...
> (in real database this relation is an gis-relation with thousands of
> rivers and countries, related by spatial join, but the problem is the
> same...)
> I want to know, which river is running through communities 1,2,3 *and* 4?
> You can see the solution by just looking at the data above (only
> "river_1" is running through all these countries), but how to query
> this by sql?
Probably the fastest way is to do an OR join and counting the matches:
SELECT r.r_name
FROM rivers r
JOIN join_table j ON j.mn2_r_id = r.r_id
JOIN communities c ON c.c_id = j.mn2_c_id
WHERE c.c_name IN ('community_1', 'community_2',
'community_3', 'community_4')
GROUP BY r.r_name
HAVING count(*) = 4
--
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] Redefining an existing Table Schema for Foreign Key Constraint - Question
In article <[EMAIL PROTECTED]>, Brian714 <[EMAIL PROTECTED]> writes: > Currently, the database contains thousands of records in the Customers and > Creditcards tables. I would like to re-define the Customers table to follow > the following schema: > Customers Table > id:integer -- primary key > first_name:varchar(50) > last_name:varchar(50) > cc_id:integer references Creditcards.id > address:varchar(200) > email:varchar(50) > password:varchar(20) > As you can see, I would like to convert the column that used to be > "credit_card_number" from the Customers table and turn it into a "cc_id" > which is an integer that references the column "id" from the table > Creditcards. This is the usual 1:n relationship, but I think you got it backwards. There are two questions to ask: 1. Are there customers with more than one credit card? 2. Are there credit cards owned by more than one customer? -- 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] Redefining an existing Table Schema for Foreign Key Constraint - Question
In article <[EMAIL PROTECTED]>, Michelle Konzack <[EMAIL PROTECTED]> writes: > Hallo Harald, > Am 2008-11-03 13:41:52, schrieb Harald Fuchs: >> In article <[EMAIL PROTECTED]>, >> Brian714 <[EMAIL PROTECTED]> writes: >> > Customers Table >> > id:integer -- primary key >> > first_name:varchar(50) >> > last_name:varchar(50) >> > cc_id:integer references Creditcards.id >> > address:varchar(200) >> > email:varchar(50) >> > password:varchar(20) >> This is the usual 1:n relationship, but I think you got it backwards. >> There are two questions to ask: >> 1. Are there customers with more than one credit card? > This could be a problem for the above table... >> 2. Are there credit cards owned by more than one customer? > CreditCards are personaly and sometimes (in France) I need an ID card to > prove, that I am the holder... > So how can one credit card can have more then one owner? That's exactly why I told you "I think you got it backwards". You need a cust_id column in your CreditCards table, not a cc_id column in your Customers table. -- 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] cumulative count
In article <[EMAIL PROTECTED]>,
Carson Farmer <[EMAIL PROTECTED]> writes:
> date | user
> --+-
> 20050201 | Bill
> 20050210 | Steve
> 20050224 | Sally
> 20050311 | Martha
> 20050316 | Ryan
> 20050322 | Phil
> 20050330 | William
> 20050415 | Mary
> 20050428 | Susan
> 20050503 | Jim
> and I want to run a query that returns a *count* of the number of
> users *each month*, ordered by year and *month*, with an additional
> column that is a *running total of the count*, as in:
> year|month |count| run_count
> ---+++-
> 2005 | 02 | 3 | 3
> 2005 | 03 | 4 | 7
> 2005 | 04 | 2 | 9
> 2005 | 05 | 1 |10
> I can get almost everything I want with:
> SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total
> FROM (SELECT EXTRACT(year from added_date) AS year,
> EXTRACT(month FROM added_date) AS month,
> TO_CHAR(added_date, 'Month') AS month_name,
> COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a,
> (SELECT EXTRACT(year FROM added_date) AS year,
> EXTRACT(month FROM added_date) AS month,
> TO_CHAR(added_date, 'Month') AS month_name,
> COUNT(*) AS count
> FROM users_table GROUP BY 1, 2, 3) AS b
> WHERE a.year >= b.year AND a.month >= b.month
> GROUP BY 1, 2, 3, 4
> ORDER BY a.year, a.month asc;
> but I can't quite figure out the running total of the count. The above
> example works right up to the end of the first year, then the values
> no longer make sense. My guess is it's something to do with my WHERE
> clause, but I can't think of a better way to do things.
Yes, your WHERE condition is the problem. It should be
WHERE a.year > b.year OR a.year = b.year AND a.month > b.month.
You could simplify the date logic by doing the year/month split later, e.g.
CREATE TEMP TABLE tmp AS
SELECT date_trunc('month', date) AS dt, count(*) AS count
FROM users_table
GROUP BY dt;
SELECT extract(YEAR FROM t1.dt) AS year,
extract(MONTH FROM t1.dt) AS month,
t1.count,
sum(t2.count) AS run_count
FROM tmp t1
LEFT JOIN tmp t2 ON t2.dt <= t1.dt
GROUP BY year, month, t1.count
ORDER BY year, month;
(AFAIK the WITH clause in PostgreSQL 8.4 would let you get rid of
the temp table.)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How is the right query for this condition ?
In article <5a9699850911222009j272071fbi1dd0c40dfdf62...@mail.gmail.com>, Brian Modra writes: > 2009/11/23 Bino Oetomo : >> Dear All >> >> Suppose I created a database with single table like this : >> --start-- >> CREATE DATABASE bino; >> CREATE TABLE myrecords(record text); >> --end >> >> and I fill myrecords with this : >> --start-- >> COPY myrecords (record) FROM stdin; >> 1 >> 12 >> 123 >> 1234 >> \. >> --end >> >> In my bash script, I have variable called 'vseek', that will be use for >> query parameter. >> How to query the table , for (i.e): >> >> a. If vseek = '127' , I want the result is ==> '12' >> b. if vseek = '123987' , I want the result is ==> '123' >> c. if vseek = '14789' , I want the result is ==> '1' >> >> Kindly please give me any enlightment > You can use a plpgsql to do that e.g. > create or replace function getMatchingRecord(vseek text) > ... For larger tables where an index search would be useful, check out pgfoundry.org/projects/prefix: CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); COPY myrecords (record) FROM stdin; 1 12 123 1234 \. SELECT id, record FROM myrecords WHERE record @> '127' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @> '123987' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @> '14789' ORDER BY length(record::text) DESC LIMIT 1; -- 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] pgsql 'prefix' error
In article <4b0bbc8e.6010...@indoakses-online.com>, Bino Oetomo writes: > I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb > I install it using dpkg , and run the prefix.sql > Create database .. named 'prefbino', and > CREATE TABLE myrecords ( > record prefix_range NOT NULL, > PRIMARY KEY (record) > ); > Looks good, next > I try to create some records, But I got this error : > ---START-- > prefbino=# COPY myrecords (record) FROM stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> 1 >>> 12 >>> 123 >>> 1234 >>> \. > ERROR: duplicate key value violates unique constraint "myrecords_pkey" > CONTEXT: COPY myrecords, line 2: "12" > ---STOP-- > Kindly please give me further enlightment At least in prefix 1.0.0 unique indexes seem to be broken. Just drop the primary key and add a separate index: CREATE INDEX myrecords_record_ix ON myrecords USING gist (record); -- 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] pgsql 'prefix' error
In article <87tywid19x@hi-media-techno.com>,
Dimitri Fontaine writes:
> The BTree opclass is not made to resist to overlapping data. Maybe in
> this case though we could say that 12 contains less elements than 1 so
> it's less than 1. Here's a test to redefine the pr_cmp() operator in
> term of that, as a patch against current CVS (which is 1.0.0).
> Can you test with this version and maybe better data set?
Looks good.
> Note that as said earlier the indexing you need to speed up queries is
> the GiST one, but it could be you want the PK constraint noneless.
Indeed - I think it's a good thing to be able to prevent something
like
INSERT INTO myrecords (record) VALUES ('12'), ('12');
Oh, here's another gripe: how about renaming README.txt to README.prefix
and removing TESTS.* from "make install"?
--
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] regexp_matches() quantified-capturing-parentheses oddity
In article <13289.1260290...@sss.pgh.pa.us>,
Tom Lane writes:
> Julian Mehnle writes:
>> So far, so good. However, can someone please explain the following to me?
>> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)+',
>> 'g');
>> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip',
>> '([...@.]|[...@.]+){1,2}', 'g');
>> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip',
>> '([...@.]|[...@.]+){1,3}', 'g');
> These might be a bug, but the behavior doesn't seem to me that it'd be
> terribly well defined in any case. The function should be pulling the
> match to the parenthesized subexpression, but here that subexpression
> has got multiple matches --- which one would you expect to get?
Perl seems to return always the last one, but the last one is never just
'p' - so I also think that Julian has spotted a bug.
--
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] Statement level triggers
In article <4b5702b9.50...@postnewspapers.com.au>, Craig Ringer writes: >> What'd be the behavior of a (plpgsql) trigger function when called as >> a statement level trigger? >> Let's say that a statement will involve more than one row. >> The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says: >> >> "Statement-level triggers do not currently have any way to examine the >> individual row(s) modified by the statement." > It means you don't have NEW or OLD record-variables. Other databases have NEW and/or OLD pseudo-tables for that. My suggestion about implementing that got turned down because, without a primary key, you can't say which NEW and OLD rows belong to each other. Since tables often have a primary key I still think that this would be an addition making statement-level triggers much more useful than they are now. -- 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] Large Objects: Sizeof and Deleting Unlinked LOs
In article <4b72aeb3.4000...@selestial.com>, Howard Cole writes: > Is there an SQL function to determine the size of a large object? I'm using a pgsql helper function for that: CREATE FUNCTION lo_size(oid oid) RETURNS integer LANGUAGE plpgsql AS $$ DECLARE fd int; res int; i int; BEGIN fd = lo_open(oid, 262144); -- INV_READ IF fd < 0 THEN RAISE EXCEPTION 'lo_open returns %', fd; END IF; res = lo_lseek(fd, 0, 2); -- SEEK_END IF res < 0 THEN RAISE EXCEPTION 'lo_lseek returns %', res; END IF; i = lo_close(fd); IF i < 0 THEN RAISE EXCEPTION 'lo_close returns %', i; END IF; RETURN res; END; $$; > Also, can I safely delete all the large objects in > pg_catalog.pg_largeobject? For example: > select lo_unlink(loid) from (select distinct loid from > pg_catalog.pg_largeobject) as loids where loid not in (select my_oid > from my_only_table_that_uses_large_objects) Did you have a look at contrib/lo? -- 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] DROP column: documentation unclear
In article <20100308213549.gb...@svana.org>, Martijn van Oosterhout writes: >> "subsequent ... will store a null value" would imply that deleted columns >> will still take some place, while "the space will be reclaimed ..." would >> suggest that new rows (insert or updates in mvcc) don't have the deleted >> column anymore - I'm not quite sure how to interpret this. What is pg >> doing? > What you're missing is that in postgres NULLs are stored as a bit in > the header and there is no data. So in a sense NULLs take no space > (well, one bit) which means both statements are true. But if you already have eight nullable columns, the (maybe originally non-null) column which has been dropped would cause the header to be one byte larger, wouldn't it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace puzzle
I've got a problem with regexp_replace which I could reduce to the following:
CREATE FUNCTION digest(text, text) RETURNS bytea
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/pgcrypto', 'pg_digest';
CREATE FUNCTION sha224enc(text) RETURNS text AS $$
BEGIN
RAISE WARNING 'arg=»%«', $1;
RETURN encode(digest($1, 'sha224'), 'hex');
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE TABLE t1 (
id serial NOT NULL,
val text NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (val) VALUES ('d11');
INSERT INTO t1 (val) VALUES ('xd22');
INSERT INTO t1 (val) VALUES ('x d33');
SELECT val,
regexp_replace(val,
'^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$',
'\1' || '»\2«='|| sha224enc('\2') || '\3', 'i')
FROM t1
WHERE val ~*
'^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$';
(I want to replace patterns within a string by their SHA-224 hash.)
However, when I run this example I get:
WARNING: arg=»\2«
val|regexp_replace
---+--
d11 |
»d11«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
x d33 | x
»d33«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
(2 rows)
i.e. the first '\2' gets properly expanded by the second paren match,
but the second '\2' doesn't get expanded.
What am I overlooking?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to perform text merge
In article <609bf3ce079445569fc0d047a5c81...@andrusnotebook>,
"Andrus" writes:
> Database column contains merge data in text column.
> Expressions are between << and >> separators.
> How to replace them with database values ?
> For example, code below should return:
> Hello Tom Lane!
> How to implement textmerge procedure or other idea ?
> Andrus.
> create temp table person ( firstname text, lastname text ) on commit drop;
> insert into person values ('Tom', 'Lane');
> create temp table mergedata ( template text ) on commit drop;
> insert into mergedata values ('Hello <>!');
> select textmerge(template,'select * from person') from mergedata;
Here's a quick shot:
CREATE FUNCTION textmerge(tpl text, query text) RETURNS text AS $$
DECLARE
pref text = substring(tpl FROM '(.*)<<');
expr text = substring(tpl FROM '<<(.+)>>');
post text = substring(tpl FROM '>>(.*)');
tmp1 text = regexp_replace(query, E'\\*', expr);
tmp2 text;
BEGIN
EXECUTE tmp1 INTO tmp2;
RETURN pref || tmp2 || post;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
--
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] plPgSQL + CDIR/INET types ...
In article , "Marc G. Fournier" writes: > Has anyone either played with, or gotten to work, a plPgSQL function > that would take: 192.168.1.1/24 and determine the start and end IP > from that? Or even start IP + # of IPs in the subnet? Just install ip4r from pgfoundry, and you'll never look back at the old cidr/inet types. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Large Object leakage
I have a DB (mydb) where one table (mytbl) contains a large object column. The contents are managed by contrib/lo. This breaks when I want to copy the DB to another host where the schema is already present with some old contents: when I do pg_dump -c mydb | psql -q -h otherhost mydb -f - pg_dump emits a "DROP TABLE mytbl" which leaves the old lo contents on otherhost orphaned and, even worse, raises an exception if pg_dump wants to create a large object with an id already present. I thought about a TRUNCATE TRIGGER which could make the appropriate lo_unlink calls, but this trigger won't be called by DROP TABLE. Any ideas? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get normalized data from tekst column
"Andrus" writes: > David, >>Regular Expressions are your friend here. If you do not know them you > should learn them; though if you ask nicely someone may just provide you > the solution you need. >>Split-to-array and unnest may work as well. > > Thank you very much. I dona**t know regexps. > Can you provide example, please for 8.1. Or maybe CASE WHEN and substring > testing can also used. The query SELECT id, a[1] AS name, a[2] AS percent FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a FROM project ) AS dummy should work un every halfway recent PostgreSQL version - dunno about 8.1. -- 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] Oddball data distribution giving me planner headaches
Jeff Amiel writes: >> At the moment I think the only way to work around this is >> to denormalize >> your schema a bit. > And I feared as much. > It's biting me in other areas as well...this unusual distribution of > data...certain types of customers have completely different data patterns > than others. > Back to the drawing board...thanks! I find your table structure anyway somewhat strange. For an ordinary parent/child relationship a parent_id column in the customer table would be enough. Do you really have an m:n relationship between parents and children? -- 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] Controlling complexity in queries
Jay Levitt writes: > * You want contextual queries. > > (I guess this is a special case of "you need non relational features".) > > In my case, I want all queries against content to be filtered by their > relevance to the current user. That can't go into a view, because > views don't have parameters; I need a computed column that may be > different every time I run the query, and depends on a piece of > information (the current user ID) that Postgres can't know. How about the following: CREATE TABLE test1 ( id serial NOT NULL, username text NOT NULL, value text NOT NULL, PRIMARY KEY (id) ); COPY test1 (username, value) FROM stdin DELIMITER '|'; user1|user1_1 user1|user1_2 user2|user2_1 user2|user2_2 user2|user2_3 \. CREATE VIEW test1v AS SELECT id, username, value FROM test1 WHERE username = current_user; Here the result of "SELECT * FROM test1v" depends on who issued the query. -- 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] selecting recs based on a tmp tbl vals that are wildcarded ?
In article <482e80323a35a54498b8b70ff2b8798003e5ac7...@azsmsx504.amr.corp.intel.com>, "Gauthier, Dave" writes: > I have a temp table containg wildcarded strings and I want to select values > froma different table using ?like? against all those wildcarded values. > Here?s > the example... > create temporary table match_these (val varchar(32)); > insert into match_these (val) values (?jo%?); > insert into match_these (val) values (?%denn_?); > insert into match_these (val) values (?alt%?); > create table footable (name varchar(32)); > (insert a bunch of records) > Now... > select * from footable where name in (select val from match_these) > ... won?t work because ?in? implies equality. I want something like... > select * from footable where name like (select val from match_these) Why don't you use a simple join? Something like SELECT f.name FROM footable f JOIN match_these m ON f.name ~~ m.val -- 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] Call volume query
In article <1233269836.13476.10.ca...@ubuntu>, Mike Diehl writes: > Hi all. > I've encountered an SQL problem that I think is beyond my skills... > I've got a table full of records relating to events (phone calls, in > this case) and I need to find the largest number of events (calls) > occurring at the same time. > The table had a start timestamp and a duration field which contains the > length of the call in seconds. > I need to find out how many concurrent calls I supported, at peek > volume. > Can this be done in SQL? Or do I need to write a perl script? Try something like the following: CREATE TABLE calls ( id serial NOT NULL, start timestamp(0) NOT NULL, nsec int NOT NULL, PRIMARY KEY (id) ); COPY calls (start, nsec) FROM stdin; 2009-01-30 10:09:00 10 2009-01-30 10:10:00 10 2009-01-30 10:10:02 10 2009-01-30 10:10:04 10 2009-01-30 10:10:06 10 2009-01-30 10:10:08 10 2009-01-30 10:10:10 10 2009-01-30 10:10:12 10 2009-01-30 10:11:00 10 \. SELECT ts, count(c.id) FROM ( SELECT (SELECT min(start) FROM calls) + s.a * interval '1 sec' AS ts FROM generate_series(0, ( SELECT extract(epoch FROM (max(start + nsec * interval '1 sec') - min(start)))::bigint FROM calls )) AS s(a) ) AS t LEFT JOIN calls c ON t.ts BETWEEN c.start AND c.start + c.nsec * interval '1 sec' GROUP BY t.ts ORDER BY t.ts; Here I use generate_series to create timestamp values for every second of the table range and join them to the table itself to see how many calls were active at this time. You could simplify that somewhat by using the "period" datatype available on PgFoundry. -- 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] Array, bytea and large objects
In article <4989e659.3000...@computer.org>, David Wall writes: > If I have an "unlimited" number of name-value pairs that I'd like to > get easy access to for flexible reports, could I store these in two > arrays (one for name, the other for value) in a table so that if I had > 10 name-value pairs or 200 name-value pairs, I could store these into > a single row using arrays so I could retrieve all name-value pairs in > a single SELECT from the db? I would use the hstore contrib module for that. -- 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] Convert Arbitrary Table to Array?
In article <17050.1234200...@sss.pgh.pa.us>,
Tom Lane writes:
> Lee Hughes writes:
>> Hi, I need a function that accepts a table name and returns a 2-dimensional
>> array of the table data.
> Well, in 8.3 and up there are arrays of composite types, so you can
> do something like
> select array(select mytable from mytable);
Interesting. On 8.3.5 I tried
CREATE TABLE mytable (
id serial NOT NULL,
mytable int NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO mytable VALUES
(1, 10),
(2, 20),
(3, 30),
(4, 40),
(5, 50),
(6, 60);
SELECT array(SELECT mytable FROM mytable);
and it returned
{10,20,30,40,50,60}
Only when I renamed the second column from "mytable" to "mytablex" I got
{"(1,10)","(2,20)","(3,30)","(4,40)","(5,50)","(6,60)"}
as you promised. Is there any syntax for treating the first "mytable"
as a composite type name instead of a column name?
--
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] [GENEAL] dynamically changing table
In article <437faa9f-df2d-429e-9856-eb2026b55...@solfertje.student.utwente.nl>, Alban Hertroys writes: > On Mar 30, 2009, at 5:39 PM, A B wrote: >> Hi, >> In the next project I'm going to have a number of colums in my tables, >> but I don't know how many, they change. They all use integers as >> datatype though.. One day, I get 2 new columns, a week later I loose >> one column, and so on in a random pattern. > Ignoring design implications (you got enough replies about that I > think)... > You could add the columns you're sure that you need and put the rest > in an XML field. If you have a problem and want to solve it using XML, you have two problems. Why serializing the rest of the data in an XML field? contrib/hstore seems to accomplish the same, without the XML overhead. -- 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] Help with join syntax sought
In article <43639.216.185.71.24.1242834374.squir...@webmail.harte-lyne.ca>, "James B. Byrne" writes: > What I want to be able to do is to return the most recent rate for > all unique rate-pairs, irrespective of type. I also have the > requirement to return the 5 most recent rates for each rate-pair, > thus the HAVING count(*) = 1, which I thought would allow me to > simply change the test to HAVING count(*) <= 5 in that case. The following queries seem to return what you want. > Given this: > CAD AUD "2009-05-19 16:15:00" "NOON" 1.146300 > CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200 > CAD AUD "2009-05-17 16:15:00" "NOON" 1.151300 > CAD AUD "2009-05-17 20:40:00" "CLSE" 1.141100 > CAD AUD "2009-05-16 16:15:00" "NOON" 1.143700 > CAD AUD "2009-05-16 20:40:00" "CLSE" 1.142100 > CAD USD "2009-05-19 16:15:00" "NOON" 0.864400 > CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100 > What I want to see in the final output is > CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200 > CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100 SELECT t1.currency_code_base AS c1, t1.currency_code_quote AS c2, t1.effective_from AS dt, t1.currency_exchange_type AS type, t1.currency_exchange_rate AS rate FROM currency_exchange_rates t1 WHERE t1.effective_from = ( SELECT max(t2.effective_from) FROM currency_exchange_rates t2 WHERE t2.currency_code_base = t1.currency_code_base AND t2.currency_code_quote = t1.currency_code_quote ) > and if requested for the 5 most recent then I want to see this: > CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200 > CAD AUD "2009-05-19 16:15:00" "NOON" 1.146300 > CAD AUD "2009-05-17 20:40:00" "CLSE" 1.141100 > CAD AUD "2009-05-17 16:15:00" "NOON" 1.151300 > CAD AUD "2009-05-16 20:40:00" "CLSE" 1.142100 > CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100 > CAD USD "2009-05-19 16:15:00" "NOON" 0.864400 SELECT t1.currency_code_base AS c1, t1.currency_code_quote AS c2, t1.effective_from AS dt, t1.currency_exchange_type AS type, t1.currency_exchange_rate AS rate FROM currency_exchange_rates t1 WHERE ( SELECT count(*) FROM currency_exchange_rates t2 WHERE t2.currency_code_base = t1.currency_code_base AND t2.currency_code_quote = t1.currency_code_quote AND t2.effective_from > t1.effective_from ) <= 5 ORDER BY t1.currency_code_base, t1.currency_code_quote, t1.effective_from DESC Both of them must touch all currency_code_base/currency_code_quote pairs; maybe you can avoid that with a composite index. -- 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] plPerl subroutine
In article <[EMAIL PROTECTED]>,
"FERREIRA William (COFRAMI)" <[EMAIL PROTECTED]> writes:
> CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId int4, evo
> int4, fileName text, fileRelativeDir text)
> RETURNS int4 AS
> $BODY$
> my $theClob='';
>
> my $params = 'select
> adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
> $params = $params.')';
>
> $theClob = spi_exec_query($params);
> elog NOTICE, $theClob;
>
> return 4;
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
>
> CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4, evo int4,
> clob text, indx int4)
> RETURNS text AS
> $BODY$
> my $t_clob = $_[3].'totototototototototot';
>
> return $t_clob;
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
> but the CREATE_XML_FILE doesn't display 'totototototototototot' but
> HASH(0xf03fa4)
... and rightly so. As documented in The Fine Manual, spi_exec_query
returns a hash reference. Probably you want something like
my $rv = spi_exec_query($params);
$theClob = $rv-.{rows}[0]->{get_xml_frag};
---(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] prelimiary performance comparison pgsql vs mysql
In article <[EMAIL PROTECTED]>, "Rick Schumeyer" <[EMAIL PROTECTED]> writes: > These results are for a single process populating a table with 934k rows, > and then performing some selects. I also compared the effect of creating > indexes on some of the columns. > I have not yet done any testing of transactions, multiple concurrent > processes, etc. Bad. That's where things begin to get interesting. > I did not make any changes to the default config settings. Bad. On modern hardware MySQL performs quite good with its default settings; PostgreSQL performs horribly without some tuning. > I used pg 8.0.1 and mysql 5.0.2 alpha. Bad. As you noticed, MySQL 5.x is Alpha and not very stable. I'd suggest using MySQL 4.1.10 instead. > I compiled pg from source, but I downloaded an binary for mysql. Good. Since MySQL is multithreaded, it's much harder to compile than PostgreSQL. The MySQL guys actually recommend using their binaries. > select count(*) from data where fid=2 and rid=6; count = 100 > select count(*) from data where x > 5000 and x < 5500; count = 35986 > select count(*) from data where x > 5000 and x < 5020; count = 1525 Bad. These queries are exactly the sore point of PostgreSQL and MySQL/InnoDB, whereas MySQL/MyISAM really shines. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] no IF - am I missing something ?
In article <[EMAIL PROTECTED]>, Michael Fuhr <[EMAIL PROTECTED]> writes: > On Mon, Mar 21, 2005 at 12:35:22AM -0600, Thomas F.O'Connell wrote: >> The number of lines depends merely on where you place your line breaks. >> >> IF(days_complete <= 120, job_price, 0)AS Days_120 >> >> could be written as: >> >> CASE WHEN days_complete <= 120 THEN job_price ELSE 0 END AS Days_120 >> >> There might be somewhat less syntactic sugar, but this is not a five >> line expression and, to me, is more readable than a comma-delimited >> list where position alone indicates function in the expression. > CASE is also standard SQL, whereas IF isn't (unless I've overlooked > it in the SQL:2003 draft). CASE, despite being standard SQL, is even supported by MySQL ;-) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Tracking row updates - race condition
In article <[EMAIL PROTECTED]>, Alex Adriaanse <[EMAIL PROTECTED]> writes: > I think that would greatly decrease the chances of a race condition > occurring, but I don't think it'd solve it. What if 150 other > revisions occur between a row update and its corresponding commit? How about the following: * Use a TIMESTAMP rather than a SERIAL * Set this timestamp to NULL in your INSERT/UPDATE trigger * Use a cron job to set the timestamp to current_timestamp when it's NULL This way the client would lag behind somewhat, depending on the cron job frequency, but it should not miss a change. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Delay INSERT
In article <[EMAIL PROTECTED]>, "ON.KG" <[EMAIL PROTECTED]> writes: > Hi > Does PostgreSQL have something like "INSERT DELAYD" - like it is used in > MySQL? > or any other way to delay inserting? Every INSERT in PostgreSQL is delayed in some sense: firstly, it is not visible to anyone else until you commit, and secondly, it is written first to the (supposedly fast) write-ahead log and only later to the table files. If you have problems with INSERT speed, describe hardware, configuration, and table structure. ---(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] Tracking row updates - race condition
In article <[EMAIL PROTECTED]>, Alex Adriaanse <[EMAIL PROTECTED]> writes: > Thanks for the input everyone. I think Harald's approach will work > well... I'm not so sure anymore :-( Consider something like that: UPDATE tbl SET col1 = 1 WHERE col2 = 1; UPDATE tbl SET col1 = 2 WHERE col2 = 1; with not much time inbetween. By using NULL temporarily, you destroy the ordering. The client won't miss an UPDATE, but it might execute the second one before the first. Neither my nor your idea appear to take care of that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plperl doesn't release memory
In article <[EMAIL PROTECTED]>, Martijn van Oosterhout writes: > Perl uses reference counting, so as long as a string is visibile > anywhere (remember closures), it stays around and disappears as soon as > it's unreferenced. > If you have large strings or arrays you don't need, maybe you need to > explicitly undef them. Using shift and pop on arrays doesn't copy the > element for example. Make sure you're using my on all your variables so > they are cleared on exiting a function. > Also, this doesn't work on circular references, so if you build > structures like that you'll need to explicitly break the chain. ... or use WeakRef. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with a plperl function
In article <[EMAIL PROTECTED]>,
Alex <[EMAIL PROTECTED]> writes:
> Hi,
> i am having a problem with a plperl function. bellow function always
> returns me an error saying "elements of Perl result array must be
> reference to hash"
> Can anyone point me out what i am doing wrong here?
> Basically i try to return data from a select in a specific record format.
> (i know that bellow example does not require a function, just want to
> get a feel for it.)
> Thanks in advance for any tips.
> Alex
> DROP FUNCTION get_item ();
> DROP TYPE my_items;
> CREATE TYPE my_items AS (
> item_id INTEGER, description TEXT,
> order_date DATE);
> CREATE FUNCTION get_item () RETURNS SETOF my_items AS $$
> my $res = [];
> my $rv = spi_exec_query('SELECT * FROM items LIMIT 10');
> my $nrows = $rv->{processed};
> foreach my $rn (0 .. $nrows - 1) {
> my $row = $rv->{rows}[$rn];
> my @record;
> $record[0] = $row->{item_id};
> $record[1] = $row->{item_descr};
> $record[2] = $row->{start_date};
> push @$res, @record;
This is the culprit. You should instead use something like
push @$res, {
item_id => $row->{item_id},
description => $row->{item_descr},
order_date => $row->{start_date},
};
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Plpgsql function with unknown number of args
In article <[EMAIL PROTECTED]>,
"Relyea, Mike" <[EMAIL PROTECTED]> writes:
> I need to create my very first function. I'm using 8.0.2 and I need a
> function that I can call (from my client app) with an unknown number of
> criteria for a select query. The function will then return the results
> of the query. In my mind, it would go something like what I've outlined
> below. I realize that there are syntax mistakes etc, but this is just
> an example:
> CREATE TABLE mytable (
> a INTEGER UNIQUE PRIMARY KEY,
> b VARCHAR(100) NOT NULL,
> );
> CREATE FUNCTION myfunc(criteria) RETURNS AS $$
> BEGIN
> BEGIN
> FOREACH crit IN criteria
> critsql := "b = 'crit' OR "
> NEXT crit
> END;
> PERFORM "SELECT a, b FROM mytable WHERE (" critsql ");"
> END;
> $$ LANGUAGE plpgsql;
> Select * from myfunc(1, 2, 3, 4) would then equate to SELECT a, b FROM
> mytable WHERE (b = '1' OR b = '2' OR b = '3' OR b = '4');
> Select * from myfunc(9, 21) would equate to SELECT a, b FROM mytable
> WHERE (b = '9' OR b = '21');
> My question is how do I do that? I've looked through the docs and can't
> find what I'm looking for. I'm assuming this is possible because it's a
> relatively simple task.
You can't have a variable number of args, but since all args have the
same type you can use an array. The return type is a set of mytable
rows; thus myfunc becomes something like
CREATE FUNCTION myfunc (TEXT[]) RETURNS SETOF mytable AS $$
SELECT *
FROM mytable
WHERE b = ANY ($1)
$$ LANGUAGE sql;
This function can be called like that:
SELECT *
FROM myfunc (ARRAY ['1', '2', '3', '4']);
SELECT *
FROM myfunc (ARRAY ['9', '21']);
---(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
[GENERAL] Finding FOREIGN KEY constraints via information_schema
I'm trying to find out which columns of which tables reference which columns of which tables by querying the information_schema. I found the referencing columns in key_column_usage and the referenced columns in constraint_column_usage - fine so far. Now consider the following: CREATE TABLE t1 ( id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY (id1, id2) ); CREATE TABLE t2 ( id1 INT NOT NULL, id2 INT NOT NULL, CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1 (id1, id2) ); PostgreSQL groks that, and pg_dump correctly generates ALTER TABLE ONLY t2 ADD CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1(id1, id2); My problem is that, while key_column_usage knows the ordinal_position, constraint_column_usage doesn't. How can I find out that it's really REFERENCES t1(id1, id2) and not REFERENCES t1(id2, id1) instead? ---(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] getting the ranks of items
In article <[EMAIL PROTECTED]>,
Lyubomir Petrov <[EMAIL PROTECTED]> writes:
> create sequence seq_tmp;
> select nextval('seq_tmp') as rank, a.id, a.name from (select id, name
> from t order by name desc) a;
> drop sequence seq_tmp;
Using a temporary sequence for that would avoid naming conflicts.
> P.S. I'm sure you can wrap it in plperl stored procedure :)
Yes, prepending the ranking column in the application would be more efficient.
I wonder whether Randall knows Perl? ;-)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] perl and insert
In article <[EMAIL PROTECTED]>,
Rich Doughty <[EMAIL PROTECTED]> writes:
> On 17 May 2005, Hrishikesh Deshmukh wrote:
>> Hi All,
>>
>> Anybody knows how to use perl dbi to read a file line by line and
>> insert into db!
>> The books which i have tell you exclusively on running queries.
> it depends on what you need to achieve, but a good place to start would be
> something like:
>while (my $line = )
>{
> $dbh->do ('INSERT INTO table (line) VALUES (?)', undef, $line);
>}
> Where FILE is your open filehandle, and $dbh is your DBI connection, and
> you've modified the SQL as necessary.
> If performance is an issue, you may want to try this (although the
> performance gains depend on database you're using)
>my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)');
>while (my $line = )
>{
> $st->execute ($line);
>}
If there are many lines in the file, Hrishikesh might be better off
using COPY instead of INSERT. "perldoc DBD::Pg" says how to do that.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Missing numbers
In article <[EMAIL PROTECTED]>, josue <[EMAIL PROTECTED]> writes: > Hello list, > I need to track down the missing check numbers in a serie, table > contains a column for check numbers and series like this: > dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1 > dbalm-# and doc_tipo='CHE' order by doc_numero; > doc_numero | doc_ckseriesfk > + > 19200 | 856938 > 19201 | 856938 > 19215 | 856938 > 19216 | 856938 > 19219 | 856938 > Missing numbers are: > from 19202 to 19214 and 19217,19218 > Does anyone knows a way to get that done in SQL or plpgsql, thanks in > advance You could use something like that: SELECT g.num FROM generate_series ((SELECT min(doc_numero) FROM bdocs), (SELECT max(doc_numero) FROM bdocs)) AS g(num) LEFT JOIN bdocs ON bdocs.doc_numero = g.num WHERE bdocs.doc_numero IS NULL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Missing numbers
In article <[EMAIL PROTECTED]>, Simon Riggs <[EMAIL PROTECTED]> writes: > If the WHERE clause said bdocs.doc_numero > 7 we would hope that this > was applied before the join. Stating this would change the OUTER into an INNER JOIN, and this would imply that the order of the restrictions is irrelevant - for the result set both conditions must be satisfied. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Get postgresql workin in french...
In article <[EMAIL PROTECTED]>, Guy Doune <[EMAIL PROTECTED]> writes: > Hi, > I would know how to set the encoding (unicode, ASCII, > etc.) for getting postgresql accepting my entry with > accent an all the what the french poeple put over > there caracter while they write... French is covered both by Latin9 and Unicode. Don't use Latin1 - it's missing the French OE ligature. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] a stored procedure ..with integer as the parameter
In article <[EMAIL PROTECTED]>, Alex Turner <[EMAIL PROTECTED]> writes: > 1. ( ) text/plain (*) text/html > As sort of a side discussion - I have postulated that quoting all incomming > numbers as string would be an effective defense against SQL Injection style > attacks, as magic quotes would destory any end-quote type syntax: > in_value=1 > select * from table where my_id='$in_value'; > as an example for PHP - Postgres will silenty perform an atoi on the string to > make it a number, but it would prevent: > in_value=1; delete * from user; > select * from table where my_id=$in_value > Am I just smoking crack here, or does this approach have some merit? The former :-) The correct defense against SQL injection is proper escaping, not quoting. How about $in_value = '1''; delete * from user'? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a stored procedure ..with integer as the parameter
In article <[EMAIL PROTECTED]>, Alex Turner <[EMAIL PROTECTED]> writes: > delete * from user; > select * from table where my_id=$in_value > Am > I just smoking crack here, or does this approach have some merit? > The former :-) The correct defense against SQL injection is proper > escaping, not quoting. How about $in_value = '1''; delete from > user'? > This would be escaped by magic_quotes resulting in: > select * from table where my_id='\'1\'\'; delete from user \'', which would > result in an error, and a failed attack would it not, which would be a good > thing? If your "magic_quotes" are magic enough to not blindly surrounding the argument in quotes, but also escape dangerous chars like "'" inside the argument, then you're safe. > I tried to create this scenario, but in a trasactional environment, it > executes, but blew the transation so the data never committed as the select > query generated an error with the insert on the end... ... and that's exactly what it should do. You just need to catch the error and generate a meaningful error message. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Disappearing Records
In article <[EMAIL PROTECTED]>, Douglas McNaught <[EMAIL PROTECTED]> writes: > Rory Browne <[EMAIL PROTECTED]> writes: >> select u.username, g.groupname from users u, groups g where u.group_id=g.id >> (assuming users are in exactly one group) >> >> If the group_id field in the users table was corrupted, and set to a >> value that isn't in the groups table, then that view wouldn't return >> anything. > That's why foreign key constraints are good. :) Well, Rory already confessed that he came from a MySQL background :-) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] newbie design question re impact of VACUUM
In article <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > As a background, I'll be using Postgres in part as a processing queue > for a 40-column stream of information (~ 250 bytes/row) with a > sustained input rate of 20 rows/sec. This queue will be processed > periodically (every few minutes), design constraints are to (1) only > process each row once, and (2) keep the processed rows around for a > period of time (say a month or so). > My first (naive?) idea was to add a boolean "was_processed" column to > the table (defaulted to false) and UPDATE it to true as part of (1). > After reading Chapter 22, though, it seems that even a minor UPDATE > like that copies the row and requires VACUUMing. That's true, but there might be a way to avoid it. If your queue elements have a timestamp, you could run your processing routine not over elements where "was_processed" is false, but over elements within some time interval, e.g. the last minute. This would eliminate the need for an UPDATE. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to use outer join in update
In article <[EMAIL PROTECTED]>, Alban Hertroys <[EMAIL PROTECTED]> writes: > Andrus wrote: >> In my current DBMS I can use >> >> create table t1 ( f1 int, f2 int ); >> create table t2 ( f3 int, f4 int ); >> update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 > That looks like a self-join on t1 without using an alias for the second > instance of t1. > I think you meant: > update t1 set f1=t2.f3 from t2 where f2 = t2.f4 Or this one: UPDATE t1 SET f1 = t2.f3 FROM t1 x LEFT JOIN t2 ON x.f2 = t2.f4 WHERE x.f2 = t1.f2 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL 8.2.0 and ip4r?
I would like to upgrade to PostgreSQL 8.2.0, but there's one thing stopping me: the need for ip4r (http://pgfoundry.org/projects/ip4r). Has anyone managed to fix that? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.2.0 and ip4r?
In article <[EMAIL PROTECTED]>, Martijn van Oosterhout writes: > On Sat, Dec 09, 2006 at 12:10:16PM +0100, Harald Fuchs wrote: >> I would like to upgrade to PostgreSQL 8.2.0, but there's one thing >> stopping me: the need for ip4r (http://pgfoundry.org/projects/ip4r). >> Has anyone managed to fix that? > What exactly is the problem? Does it not compile or something? Yes: $ make USE_PGXS=1 sed 's,MODULE_PATHNAME,$libdir/ip4r,g' ip4r.sql.in >ip4r.sql gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -DIP4R_PGVER=8002000 -I. -I/usr/include/postgresql/server -I/usr/include/postgresql/internal -D_GNU_SOURCE -c -o ip4r.o ip4r.c ip4r.c: In function 'ip4_cast_to_cidr': ip4r.c:627: error: 'struct ' has no member named 'type' ip4r.c: In function 'ip4r_cast_from_cidr': ip4r.c:967: error: 'struct ' has no member named 'type' ip4r.c: In function 'ip4r_cast_to_cidr': ip4r.c:1004: error: 'struct ' has no member named 'type' make: *** [ip4r.o] Error 1 $ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.2.0 and ip4r?
In article <[EMAIL PROTECTED]>, Martijn van Oosterhout writes: > Looking at CVS, line 967 is a blank line, so I have to ask what version > you're compiling. I notice the CVS tree got some patches two months ago > for 8.2 but there has been no release since then. Perhaps you should > try the latest CVS version? This was it! I assumed that the ip4r-1.0.tgz presented on the PgFoundry site was the most recent version. After getting the five files via CVS, everything seems to work now. Thanks for your help! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql substitution variables
In article <[EMAIL PROTECTED]>, "Merlin Moncure" <[EMAIL PROTECTED]> writes: > can anybody think of of a way to sneak these into dollar quoted > strings for substitution into create function? > would i would ideally like to do is (from inside psql shell) > \set foo 500 > create function bar() returns int as > $$ > declare > baz int default :foo; > [...] Can't you put the psql call into a shell script and use shell variable substitution for that? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres SQL Syntax
In article <[EMAIL PROTECTED]>, "Jim C." <[EMAIL PROTECTED]> writes: > Maybe it is and maybe it isn't. I wouldn't know. I'm merely the > unfortunate soul chosen to convert this from MySQL to Postgres. :-/ > I've been working on it for a week now. I've got to say that it pains me > to know that there is apparently no open standard in use for > importing/exporting data from one db to another. Regarding just the data, you could use "mysqldump --tab=DIR" on the MySQL side and "COPY FROM" on the PostgreSQL side. > XML would do the job, wouldn't it? If you have a problem and try to solve it with XML, then you have two problems. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
