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

[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

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,

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

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

Re: [GENERAL] Controlling complexity in queries

Jay Levitt jay.lev...@gmail.com 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

Re: [GENERAL] Oddball data distribution giving me planner headaches

Jeff Amiel becauseimj...@yahoo.com 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

Re: [GENERAL] How to get normalized data from tekst column

Andrus kobrule...@hot.ee 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

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 t...@sss.pgh.pa.us 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

Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

In article cabrt9rdxhkcxrq8wbohnikpf-cggktejwdw3q2_kxfedp4p...@mail.gmail.com, Marti Raudsepp ma...@juffo.org 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

Re: [GENERAL] New feature: accumulative functions.

In article CAFj8pRDx6JLmneV30kWNrcwzGLOSqyK-qN7T4_N37L9UPd2M=q...@mail.gmail.com, Pavel Stehule pavel.steh...@gmail.com writes: 2011/9/25 pasman pasmański pasma...@gmail.com: I found second use case. Look at expression: where left(str,n)='value' function left(str,n) increase

Re: [GENERAL] Column Privileges: NULL instead of permission denied

In article df0c87d105b235419e2d9e5066cccf510b7...@gcmxbe02.dac.int, Matthew Hawn matth...@donaanacounty.org 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

Re: [GENERAL] PostgreSQL 9.1.0 bug?

In article 21641.1316159...@sss.pgh.pa.us, Tom Lane t...@sss.pgh.pa.us writes: Harald Fuchs hari.fu...@gmail.com 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

Re: [GENERAL] Bit datatype performance?

In article CAPHN3JX1YNxnGsu3q5A0wGqMMwjXMcmu8LnZ72jepE2A=t2...@mail.gmail.com, Antonio Vieiro anto...@antonioshome.net 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

[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

Re: [GENERAL] join between a table and function.

In article cakwofmjwz3znxcj9radn4ov+krsa-133968yvag3l8m3w3z...@mail.gmail.com, Lauri Kajan lauri.ka...@gmail.com 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

Re: [GENERAL] hstore installed in a separate schema

In article 1312401318.5199.yahoomailclas...@web120108.mail.ne1.yahoo.com, Ioana Danes ioanasoftw...@yahoo.ca 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

Re: [GENERAL] Difference between inet and cidr

In article cabrt9rar2bfrxdx93h_aeqskmuchmwursfenp8itspexsws...@mail.gmail.com, Marti Raudsepp ma...@juffo.org writes: Hi, On Tue, Jul 5, 2011 at 09:50, Yan Cheng CHEOK ycch...@yahoo.com wrote: The essential difference between inet and cidr data types is that inet accepts values with nonzero

Re: [GENERAL] Select from Java Strings

In article 4e116e11.1030...@gmail.com, Daron Ryan daron.r...@gmail.com 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

Re: [GENERAL] Adding a default value to a column after it exists

In article 20110413163120.gu24...@shinkuro.com, Andrew Sullivan a...@crankycanuck.ca 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'.

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

In article 20101022161331.gd9...@frohike.homeunix.org, Peter Bex peter@xs4all.nl writes: As far as I can see, this would imply either creating views on the whatever for every user (or company?), or manually crafting queries to do the same. Not necessarily. Consider this: CREATE TABLE

Re: [GENERAL] Constraint: string length must be 32 chars

In article 4cba2bc4.9030...@darrenduncan.net, Darren Duncan dar...@darrenduncan.net 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

Re: [GENERAL] Prefix LIKE search and indexes issue.

In article aanlktims+x5bpfaxf+9_cohiaga7=b_npn=hw99kg...@mail.gmail.com, Marcelo de Moraes Serpa celose...@gmail.com 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

Re: [GENERAL] index scan and functions

In article 20100719162547.ga17...@localhost, arno a...@renevier.net 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)

Re: [GENERAL] Some insight on the proper SQL would be appreciated

In article 4c0f4ba8.3040...@gmail.com, Ognjen Blagojevic ognjen.d.blagoje...@gmail.com 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 =

Re: [GENERAL] Ltree - how to sort nodes on parent node

In article 59670b22-30cb-4e6e-83c8-c1d1036c9...@solfertje.student.utwente.nl, Alban Hertroys dal...@solfertje.student.utwente.nl writes: 2). Drop the ltree column and go with a truly recursive approach, something like this: CREATE TABLE node ( categorytextNOT NULL PRIMARY

Re: [GENERAL] Ltree - how to sort nodes on parent node

In article 1f96e061-713c-4929-a7d9-278e5b608...@solfertje.student.utwente.nl, Alban Hertroys dal...@solfertje.student.utwente.nl 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

[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 -

Re: [GENERAL] plPgSQL + CDIR/INET types ...

In article alpine.bsf.2.00.1003302306490.97...@hub.org, Marc G. Fournier scra...@hub.org 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

Re: [GENERAL] How to perform text merge

In article 609bf3ce079445569fc0d047a5c81...@andrusnotebook, Andrus kobrule...@hot.ee 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

[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=»%«',

Re: [GENERAL] DROP column: documentation unclear

In article 20100308213549.gb...@svana.org, Martijn van Oosterhout klep...@svana.org 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

Re: [GENERAL] Large Objects: Sizeof and Deleting Unlinked LOs

In article 4b72aeb3.4000...@selestial.com, Howard Cole howardn...@selestial.com 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

Re: [GENERAL] Statement level triggers

In article 4b5702b9.50...@postnewspapers.com.au, Craig Ringer cr...@postnewspapers.com.au 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.

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

In article 13289.1260290...@sss.pgh.pa.us, Tom Lane t...@sss.pgh.pa.us writes: Julian Mehnle jul...@mehnle.net 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=#

Re: [GENERAL] pgsql 'prefix' error

In article 87tywid19x@hi-media-techno.com, Dimitri Fontaine dfonta...@hi-media.com 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()

Re: [GENERAL] pgsql 'prefix' error

In article 4b0bbc8e.6010...@indoakses-online.com, Bino Oetomo b...@indoakses-online.com 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

Re: [GENERAL] How is the right query for this condition ?

In article 5a9699850911222009j272071fbi1dd0c40dfdf62...@mail.gmail.com, Brian Modra epai...@googlemail.com writes: 2009/11/23 Bino Oetomo b...@indoakses-online.com: Dear All Suppose I created a database with single table like this : --start-- CREATE DATABASE bino; CREATE TABLE

Re: [GENERAL] cidr data type question

In article 1257149236.3426.9.ca...@localhost, Vasiliy G Tolstov v.tols...@selfip.ru 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

Re: [GENERAL] How to match sets?

In article c07f9bfd-5fc1-4b8b-ba87-c8bdc47d0...@solfertje.student.utwente.nl, Alban Hertroys dal...@solfertje.student.utwente.nl 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

Re: [GENERAL] unique index for periods

In article 20090820065819.ga2...@gheift.kawo1.rwth-aachen.de, Gerhard Heift ml-postgresql-20081012-3...@gheift.de 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

Re: [GENERAL] Generating random unique alphanumeric IDs

In article 20090816122526.gw5...@samason.me.uk, Sam Mason s...@samason.me.uk 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

Re: [GENERAL] Refer to another database

In article 4a77c4af.2060...@gmx.de, Andreas Kalsch andreaskal...@gmx.de 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

Re: [GENERAL] Return LEFT JOINed tables when one has no matching column

In article 4a425379.90...@alteeve.com, Madison Kelly li...@alteeve.com 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

Re: [GENERAL] WITH RECURSIVE clause -- all full and partial paths

In article aaf543e90906120856r5219cf9cv7f13ba0d37494...@mail.gmail.com, aryoo howar...@gmail.com 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

Re: [GENERAL] Help with join syntax sought

In article 43639.216.185.71.24.1242834374.squir...@webmail.harte-lyne.ca, James B. Byrne byrn...@harte-lyne.ca 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

Re: [GENERAL] [GENEAL] dynamically changing table

In article 437faa9f-df2d-429e-9856-eb2026b55...@solfertje.student.utwente.nl, Alban Hertroys dal...@solfertje.student.utwente.nl 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.

Re: [GENERAL] Convert Arbitrary Table to Array?

In article 17050.1234200...@sss.pgh.pa.us, Tom Lane t...@sss.pgh.pa.us writes: Lee Hughes l...@hughesys.com 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

Re: [GENERAL] Array, bytea and large objects

In article 4989e659.3000...@computer.org, David Wall d.w...@computer.org 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

Re: [GENERAL] Call volume query

In article 1233269836.13476.10.ca...@ubuntu, Mike Diehl mdi...@diehlnet.com 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

Re: [GENERAL] selecting recs based on a tmp tbl vals that are wildcarded ?

In article 482e80323a35a54498b8b70ff2b8798003e5ac7...@azsmsx504.amr.corp.intel.com, Gauthier, Dave dave.gauth...@intel.com 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

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

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

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

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

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

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

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

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

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

[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

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

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

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

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?

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

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

[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)

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

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

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

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

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.

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

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

Re: [GENERAL] Importing *huge* mysql database into pgsql

In article [EMAIL PROTECTED], .ep [EMAIL PROTECTED] writes: Hello, I would like to convert a mysql database with 5 million records and growing, to a pgsql database. All the stuff I have come across on the net has things like mysqldump and psql -f, which sounds like I will be sitting forever

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

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

[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 kleptog@svana.org 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

Re: [GENERAL] PostgreSQL 8.2.0 and ip4r?

In article [EMAIL PROTECTED], Martijn van Oosterhout kleptog@svana.org 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

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

Re: [GENERAL] Dynamic partial index

In article [EMAIL PROTECTED], gustavo halperin [EMAIL PROTECTED] writes: Hello I'm interesting in a partial index for a rows that aren't older than 6 mounts, something like the sentence below: /CREATE INDEX name_for_the_index ON table (the_column_of_type_date) WHERE (

Re: [GENERAL] Best approach for a gap-less sequence

In article [EMAIL PROTECTED], Jorge Godoy [EMAIL PROTECTED] writes: AgentM [EMAIL PROTECTED] writes: Since the gapless numbers are purely for the benefit of the tax people, you could build your db with regular sequences as primary keys and then regularly (or just before tax-time) insert

Re: [GENERAL] Best approach for a gap-less sequence

In article [EMAIL PROTECTED], Richard Broersma Jr [EMAIL PROTECTED] writes: I am curious, can you calculate something like this using only sql? Or you you need to employee a procedural language like plpsgql? You could use something like SELECT (SELECT count(*) FROM tbl t2 WHERE t2.id

Re: [GENERAL] Best approach for a gap-less sequence

In article [EMAIL PROTECTED], Jorge Godoy [EMAIL PROTECTED] writes: Harald Fuchs [EMAIL PROTECTED] writes: Why putting gapless numbers into the database at all? Just calculate them at query time. And how would you retrieve the record that corresponds to invoice number #16355, for example

Re: [GENERAL] Best approach for a gap-less sequence

In article [EMAIL PROTECTED], Scott Ribe [EMAIL PROTECTED] writes: Why putting gapless numbers into the database at all? Just calculate them at query time. There is ABSOLUTELY NO WAY that would be acceptable for accounting or legal purposes. It would be the same as fabricating the numbers

Re: [GENERAL] LISTEN considered dangerous

In article [EMAIL PROTECTED], Flemming Frandsen [EMAIL PROTECTED] writes: I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Naturally, the listen should not do anything at all when followed by a rollback.

Re: [GENERAL] join on next row

In article [EMAIL PROTECTED], Sim Zacks [EMAIL PROTECTED] writes: I want my query resultset to be Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2) Where Event(2) is the first event of the employee that took place after the other event. Example EventIDEmployee

Re: [GENERAL] SQL ASCII encoding

In article [EMAIL PROTECTED], Martijn van Oosterhout kleptog@svana.org writes: As a british user, latin9 will cover most of your needs, unless ofcourse someone wants to enter their name in chinese :) Since british users don't use French OE ligatures or Euro currency signs, even latin1 would

Re: [GENERAL] partial resultset in java

In article [EMAIL PROTECTED], Luckys [EMAIL PROTECTED] writes: I believe you should restrict number of rows that needs to be returned, or giving a choice to the user, although showing the total count. Even if you display all 20K records, no one is going to see them all,  you can even add one

Re: [GENERAL] referential integrity without trigger

In article [EMAIL PROTECTED], Alexander Presber [EMAIL PROTECTED] writes: Hello everybody, Assuming I want to empty and refill table A (with roughly the same content, preferrably in one transaction) and don't want to completely empty a dependent table B but still keep referential integrity

Re: [GENERAL] Question on the use of bracket expressions in Postgres

In article [EMAIL PROTECTED], Michael Glaesemann [EMAIL PROTECTED] writes: On Dec 15, 2005, at 0:29 , Jimmy Rowe wrote: select * from catalog where file_name like 'abc%def%.200[2-5]%'; The following select keeps returning (0 rows). LIKE doesn't consider [2-5] to be a range, but rather the

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

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

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

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

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 -

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

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

  1   2   >