Re: [GENERAL] Geographic data sources, queries and questions

John D. Burger wrote: For instance, does the US have 50 states - what about the US Virgin Islands, etc? Off-topic, but the US Virgin Islands are an "unincorporated United States insular area" ("territory"--note the lowercase t). The Dept. Of the Interrior addresses this in their FAQ: http

Re: [GENERAL] querying the age of a row

but it doesn't seem like it would be a problem. brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Surrogate VS natural keys

all. I doubt i ever would. Generally, you'd be using this to relate rows from a more generalised table using either the club ID or the user ID. I can't see how having a seperate serial ID column would be useful for any kind of select. brian ---(end of broadcas

[GENERAL] ORDER BY with exception

a trigger that bumped the 'other' id above that of the new entry, then re-relate everything else in the DB that's connected to the 'other' discipline. But that strikes me as kind of a hack. The third option is to re-order the resultset in the PHP script th

Re: [GENERAL] ORDER BY with exception

R BY tip! I had no idea i could do that. I'll see if i can work that in. brian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] ORDER BY with exception

Michael Glaesemann wrote: On Jun 21, 2007, at 17:35 , brian wrote: I have a lookup table with a bunch of disciplines: To answer your ordering question first: SELECT id, name FROM discipline ORDER BY name = 'other' , name; id |name +- 8 | com

[GENERAL] monthly tally of new memberships

. I'll bet it's obvious, isn't it? :-\ brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] monthly tally of new memberships

Michael Glaesemann wrote: On Jul 18, 2007, at 13:29 , brian wrote: This returns the new memberships for each day, ignoring days without any. What i'd like to do though, is to select only the 1st of each month, summing the new memberships or that month, eg: month | applications

Re: [GENERAL] monthly tally of new memberships

Alvaro Herrera wrote: brian wrote: Michael Glaesemann wrote: On Jul 18, 2007, at 13:29 , brian wrote: This returns the new memberships for each day, ignoring days without any. What i'd like to do though, is to select only the 1st of each month, summing the new memberships or that mont

Re: [GENERAL] date ranges

INTO foo (dt) VALUES ('2024-03-02'); ERROR: new row for relation "foo" violates check constraint "foo_dt_check" There's probably a more elegant way to do this. brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] how to detect the backup database every day

change it to ">/dev/null" (without the quotes). If the emails have been sent but this is an account on a remote server you might want to investigate having the emails for that remote account sent to your regular email address. brian ---(end of broadcast)

Re: [GENERAL] track row / field usage

counter? just trying to see which rows, fields or values are most sort after. cheers, jzs Set the log_statement line in postgresql.conf to "all". brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PgAdmin .sql default handler

r" and select the app you want to handle these from your Applications directory. brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] SQL Diff ?

Kevin Kempter wrote: Hi List; I have a very large table (52million rows) - I'm creating a copy of it to rid it of 35G worth of dead space, then I'll do a sync, drop the original table and rename table2. Once I have the table2 as a copy of table1 what's the best way to select all rows that h

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the "lost" table is the same one - the bussiest one (mostly read)... and everytime

Re: [GENERAL] Tables dissapearing

u are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 'drop' in your application. Perhaps you should do the same on the CMS. [1] Not meant as a dig at non-free software. brian ---

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: brian wrote: IMHO, it's not at all improbable, given that this software is connecting to the same databases you are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 

[GENERAL] log_statement and PREPARE

t am not seeing any of my prepared statements in the log. INSERT, UPDATE, and friends i do see. FWIW, the app is PHP using MDB2. But checking its source doesn't give me any reason to believe the issue lies there. Shouldn't i see these PREPAREs? brian ---(end of broa

Re: [GENERAL] log_statement and PREPARE

Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: That was understood. What i meant is that the only time i see anything *related to* the prepared statement i think should be there is when the EXECUTE fails for some reason because the context of the error is logged. That particular E

Re: [GENERAL] log_statement and PREPARE

Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: The only hint of a prepared statement being logged is when there's an error. eg: <2007-09-05 17:35:22 EDT>ERROR: duplicate key violates unique constraint "auth_member_id_key" <2007-09-05 17

Re: [GENERAL] log_statement and PREPARE

Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: But that should mean that my prepared statement that contains an INSERT should be logged, yes? (8.1 issues notwithstanding) I ask because i've set log_statement to 'mod' but am not seeing any of my prepared statemen

Re: [GENERAL] log_statement and PREPARE

Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: The docs (8.1) say the following about log_statement: -- snip -- ... mod logs all ddl statements, plus INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE and EXPLAIN ANALYZE statements are also logged if their contained command is

Re: [GENERAL] SQL for Deleting all duplicate entries

[t.n] FROM foo AS t; Put those into a tmp table, truncate the original, then put the saved rows back in. brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] Column ordering

, drop the original, recreate it with the new column in the correct position, and move the data back into it. brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] query help

If that's not working for you, it's perhaps because you have rows for columns and columns for rows. http://www.nabble.com/an-other-provokative-question---tf4394285.html sincerely siva What the heck does this have to do with anything? Please don't top-post. brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] Please change default characterset for database cluster

e you require then go somewhere else. It's silly to suggest that the Postgres developers should alter the default behaviour simply so that you can continue paying money for service that is clearly inadequate to your needs. brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] can I define own variables?

return $_SHARED{$_[0]}; $$ LANGUAGE plperl; SELECT set_id('my_user_id', 42); SELECT CAST(get_id('my_user_id') AS INT); [1] http://www.varlena.com/GeneralBits/ brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the

Re: [GENERAL] Calculation of per Capita on-the-fly - problems with SQL syntax

rse your year column by expanding any entries with a dash into a range. But i'm not sure that you could then compare a range against a list (WHERE ... IN ... ) Probably best done in a function. brian ---(end of broadcast)--- TIP 4: Have

Re: [GENERAL] problem with a column of type timestamp

R TABLE your_table ALTER COLUMN birthtime TIMESTAMP WITH TIMEZONE; http://www.postgresql.org/docs/8.0/static/sql-altertable.html brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an inde

Re: [GENERAL] Am I overseen ?

Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? ---(end of broadcast)--- TIP 5: don't forget to increa

Re: [GENERAL] running postgres

Ken Johansson wrote: and i though PostgreSQL would be easier to get up and running than SQL server Question? Complaint? Thinking out loud? Lyrics to a song? We're all standing by, Ken! ---(end of broadcast)--- TIP 3: Have you check

Re: [GENERAL] ordering rows

mplishing this task other than fetch? Perhaps you should post your SELECT statement and a sample of the result you're getting (and a sample of what you desire to get wouldn't hurt). brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] running postgres

Ken Johansson wrote: Hello, Sorry everyone for the stupid rant. Wont happen again. And thanks for offer assistance. I seem to have it running and once i have a valid question ill post it. Sorry again.. Ken We'll put it down as "thinking out loud" then. It happ

Re: [GENERAL] Am I overseen ?

clear. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). I don't feel at all qualified to a

Re: [GENERAL] Group By question

the columns, this is impossible. SELECT Dept, SUM((Col1 + Col2) * col3) AS total FROM foo GROUP BY Dept ORDER BY Dept; dept | total --+--- 1 |29 2 |18 3 | 9 (your example had an arithmetic error) brian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Problem of installation on Mac

Michael Glaesemann wrote: On Oct 19, 2007, at 13:50 , brian wrote: Michael Glaesemann wrote: On Oct 20, 2000, at 13:05 , Martin Gainty wrote: Martin, it continues to amaze me how you're able to predict and contribute to the discussion 7 years in advance! Michael Glaesemann

Re: [GENERAL] Problem of installation on Mac

o see his posts now (for forward-incrementing instances of "now"). (or something like that) brian ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Order-independent multi-field uniqueness constraint?

e best solution, but shouldn't that be: "... foo_uniq_x_y UNIQUE(SELECT canonicalize(x,y))"? brian ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Automating Backup & Restore

e to do the dump/restore even if it is sent straight from one to another. See this thread: http://archives.postgresql.org/pgsql-general/2007-10/msg01178.php brian ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [GENERAL] subversion support?

Roberts, Jon wrote: Are there plans to support a plug-in to a version control system like subversion for DDL? We really need to version our functions we will be writing but currently, we have to go through some hoops to get the function code into subversion. This is hoops? svn ci your_functio

Re: [GENERAL] subversion support?

s into source control. About as ironic as any other random software package/project that also uses version control in development not having those hooks. brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your

Re: [GENERAL] Selecting tree data

, 'post 5', 4, 1, '6pm') (3, 'post 3', 1, 1, '6pm') (6, 'post 6', NULL, 1, '5pm') (2, 'post 2', NULL, 2, '8pm') And reverse sorted would be: (2, 'post 2', NULL, 2, '8pm') (6, 'post 6', NU

Re: [GENERAL] Selecting tree data

Pat Maddox wrote: On 10/26/07, brian <[EMAIL PROTECTED]> wrote: SELECT * FROM posts ORDER BY root_id, id; brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] postgresql in PHP

, are you using a prepared statement? brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] select random order by random

SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo ASC; I should think that you would get a better result if you dropped the ORDER BY clause. brian ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send

Re: [GENERAL] Number to Words Conversion

yogesh wrote: Hello Frnds, Is there any way to Convert the Number to its equivalent String ( Words) e.g. 10 to TEN. Urgent require the Answer.. (column sizes just an example) CREATE TABLE integer_string ( number SMALLINT NOT NULL, word VARCHAR(24) NOT NULL ); brian

Re: [GENERAL] Selecting all but a few fields in a query

RETURN NEXT the_type; END LOOP; RETURN; END; $_$ LANGUAGE plpgsql brian ---(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] Optimal time series sampling.

ou happen to be using an operating system that refuses to let go of formatting instructions when copying plaintext, there's always Notepad. brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PLpgsql debugger question

hat you're referring to when you say it was "advertised as a feature" but it's not a part of the PG release. You can get it here: http://pgfoundry.org/projects/edb-debugger/ brian ---(end of broadcast)--- TIP 3: Have

Re: [GENERAL] PLpgsql debugger question

Tony Caduto wrote: brian wrote: I don't know what you're referring to when you say it was "advertised as a feature" but it's not a part of the PG release. You can get it here: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375

Re: [GENERAL] getting the number of rows affected by a query

e_row_count') AS INT) AS the_row_count; Where 'x' represents your row count, however you get that. If you're using pg >= 8.2 there's a RETURNING clause for DELETE. I'm not sure if that's what you want. brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Foreign keys and inheritance

B & T (and maybe A). brian ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] POLL: Women-sized t-shirts for PostgreSQL

n PG group. Unfortunately, sourcewear's server seems to be configured with Indexes off but no DirectoryIndex. And google is showing an awful lot of comments from very unsatisfied customers. So, um, where can we see the shirts? Are they still sold through sourcewear? Are they still in bu

Re: [GENERAL] initdb - encoding question

"latin 1". Try using en_CA.utf-8 instead. brian ---(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] initdb - encoding question

Josh Harrison wrote: initdb -E en_CA.utf-8 -D /export/home/sjothirajah/postgres8.3/pgsql/data gives this error initdb: "en_CA.utf-8" is not a valid server encoding name My bad. Use UTF-8. brian ---(end of broadcast)--- TIP 5: don&#

Re: [GENERAL] user name and password woes

the "does not exist" error. Try doing: pg_dump -S administrator ... (using the postgres-specific administrator user). brian ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] thank you

ilable anytime after 9:00am PST Sincerely, Joshua D. Drake Now THAT is an upstanding gesture. Well done, sir! (AND he arrives to work early!) all the best, brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

Re: [GENERAL] Is there PHP mysql_real_escape_string for postgresql?

needed to use them. Actually, now i think of it, it is possible to include PEAR packages locally (ie. from within a particular website as opposed to using an "installed" setup) but it could be a bit of a dependency pain for certain packages. brian ---(end of

Re: [GENERAL] Howto backup all functions?

Ow Mun Heng wrote: Hi, is there a simple way for me to backup all the functions which I've written for a server? pg_dump -s will output the schema only (which includes all of your defined functions). brian ---(end of broadcast)---

Re: [GENERAL] self ordering list

trigger. Why not update everything into a temp table first, then update the original with the new values from that? Or maybe a view is better suited to this. brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

Re: [GENERAL] pgsql cannot read utf8 files moved from windows correctly!

rvice/viewContent.do?externalId=tn_19059&sliceId=1 Perhaps yours is a related problem? brian ---(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] Postgres from PHP in Leopard

les (mind the line-breaks): http://fatalerror.wordpress.com/2007/12/23/\ installing-apache-php-mysql-postgresql-on-mac-osx-leopard-from-source/ http://fatalerror.wordpress.com/2007/12/14/\ running-apache-2-php5-entropy-with-postgresql-on-leopard/ brian --

Re: [GENERAL] many to one of many modeling question

s (id) [ON DELETE ...], CONSTRAINT FOREIGN KEY object_3_id REFERENCES object_3 (id) [ON DELETE ...] ); Out of curiosity, is this for a CakePHP app? brian ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, p

Re: [GENERAL] PHP and Postgres arrays

Hannes Dorbath wrote: Yannick Warnier wrote: I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays any

Re: [GENERAL] Stored procedures when and how: was: Sun acquires MySQL

Pavel Stehule wrote: > ... bottleneck is in repeated assign s := s || .. I will try trick: create or replace function list(int) returns varchar as $$ begin return array_to_string(array(select '' || i || '' from generate_series(1, $1) g(i)), ''); end$$ language plpgsql immutable; test

Re: [GENERAL] Count

Bob Pawley wrote: I have a table with four columns that will either be null or hold the value 'true'. Any reason why the NULL values aren't instead FALSE? NULL != FALSE. I want to obtain the count of these columns, within a particular row, that have 'true' as a value (0 to 4). SELECT (C

Re: [GENERAL] Best practices for protect applications agains Sql injection.

? For instance, with PHP, you might want to look into the PEAR MDB2 package (specifically, the prepared statements). brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[GENERAL] match accented chars with ASCII-normalised version

route, I'd add an insert/update trigger to call a function (PL/Perl, I'm looking at you) that handles the conversion to link_name. brian ---(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] [pgsql-advocacy] PostgreSQL Certification

Josh Berkus wrote: Josh, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). Who else is in this? Have you talked to t

[GENERAL] Some indexing advice for a Postgres newbie, please?

quite. :( Thanks, Brian. -- 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 indexing advice for a Postgres newbie, please?

On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: >On 02/19/2015 09:10 AM, brian wrote: >> Hi folks, >> >> I have a single-user application which is growing beyond the >> fixed-format data files in which it currently holds its data, I need a >> proper database

Re: [GENERAL] temp table question

Kevin Kempter wrote: Hi list; If I create a temp table (i.e. create temp table xyz as select from ...) is the scope of this table limited to a session. Meaning, can several sessions all run the above create temp table statement all referencing the same temp table name at the same time? Yes

Re: [GENERAL] Function returning subset of columns from table (return type)

Myk wrote: Hi I'm pretty new to PostgreSQL, and have encountered a bit of trouble with functions, namely the return type. Version is 8.0.15. I have the following table: note ( id int, added date, updated date, text varchar(1000) ) and want to define a function that just returns the dates and

Re: [GENERAL] Function returning subset of columns from table (return type)

Gurjeet Singh wrote: All's okay, except you should not have declared it IMMUTABLE, because the results depend on a database query. From the docs: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it

Re: [GENERAL] Does has_table_privilege() have a case bug

Jeff Davis wrote: On Tue, 2008-02-05 at 16:10 -0800, johnf wrote: I create a table named "Account_Text_Table" the owner is 'johnf'. select has_table_privilege('johnf', 'public.Account_Text_Table', 'SELECT') I get the following error: ERROR: relation "public.account_text_table" does not exist

Re: [GENERAL] Fossology Install & Config Issues...

Kurt Schroeder wrote: I am attempting to install the Fossology OSS license analysis software on a server running Ubuntu Linux and PostgreSQL. I have gotten to the point where I am at the final install command, and I get an error on a "dbcheck" program that says: # Checkin

Re: [GENERAL] Fossology Install & Config Issues...

\g or terminate with semicolon to execute query \q to quit fossology=> \q Connect to fossology as a regular user and do: CREATE ROLE fossy WITH OPTION CREATEDB LOGIN password fossy if you haven't already. brian ---(end of b

Re: [GENERAL] ERROR: COPY quote must be a single ASCII character

es using awk, sed etc. but don't want to change the structure of > the file. OK, how about replacing '||' with '|' on a *copy* of your file, then? It's not making that big a change to the structure, in any case. Otherwise, you won't be able to do what

Re: [GENERAL] Mechanics of Select

Willem Buitendyk wrote: I have the following function that returns the first day of the next month from whatever date is inserted. If I use this as part of a select statement then it takes almost twice as long to perform. Is this because for each scanned record this function is being called?

Re: [GENERAL] Using sequences in SQL text files

HHB wrote: Hi. I have sequence for each table in my database. In order to populate same data in the database, I created some SQL text files. --- insert into categories values (id value from sequence, '..', '...'); insert into books values (id value from sequence, '..', '...', '..', fk to category

Re: [GENERAL] Suggestions for schema design?

cluster wrote: I really need some input: In a system that handles money transfers I have a table to store each money transfer. A number of different events can result in a money transfer but the events are so different that each event type is stored in its own table. So we have a schema of

Re: [GENERAL] Error in PlPython procedure

mars_osrp wrote: Hi All, I am using PlPython procedure to insert data in a table: plan = plpy.execute("insert into test(id,name) values(1 , 'test_py')") I am getting error: ERROR: invalid input syntax for integer: "" In fact preparing a plan and passing a list of values for columns is also g

Re: [GENERAL] New inst. 8.3.0 has no client

John Tiedeman wrote: When I upgraded to Kubuntu 7.10 I opted to install 8.3 rather than stick with 8.2. I knew to dump my 8.2 files first. After I finished installing, I got (and still get) a message that I should have an 8.3.0-client. I don't see any separate downloads listed so assume it's in t

Re: [GENERAL] Function problem

Enrico wrote: Hi all, I have this piece of code DECLARE minv my_inv; r record; totale numeric(20,5); valore numeric(20,5); BEGIN [.] totale := 0; for r in select * from tminv loop [.] valore := r.prezzo*r.giacenza; totale := totale+valore;

Re: [GENERAL] request help forming query

danmcb wrote: Hi !'ve been wondering how to formulate a query to get a set of objects out of a database, and am a bit stuck. I hope that someone here might be able to help. This is what the db looks like: Table TYPES id int primary key, description text Table GROUPS id int primary key descrip

Re: [GENERAL] copy with escape

blackwater dev wrote: I have data that I'm running through pg_escape_sting in php and then adding to stdin for a copy command. The problem is "O'reilly" is being changed to "O''Reilly" in the string and then in the db. I saw with the copy command I can specify the escape but it isn't working

Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year

Bruno Baguette wrote: Hello ! I have a week number (ISO 8601) and a year, based on theses values, I would like to get the first and the last dates of that week. How I can do that ? The only solution is doing a big generate_series to build a subset that contains the week of all the dates bet

Re: [GENERAL] issue with an assembled date field

Chris Bowlby wrote: Hi All, I am currently running into an issue with a query and would like to get some assistance if possible. The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux Enterprise Server 9 SP3 I am converting an encoded field (lot_id) into a date field, the 5

Re: [GENERAL] issue with an assembled date field

Chris Bowlby wrote: Hi Colin, Thanks for your response, if I remove the where clause from my example, I also am able to execute the query with out issue, as follows: test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text || "substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FR

Re: [GENERAL] issue with an assembled date field

or my main concatenation: '01/01/0'::text || ... You're going to have another problem in about 22 months. > Brian is right > > change substring(ilch.lot_id::text, 5, 1) and > change '01/01/0'::text || > > to > '01/01/'::text || substring(

Re: [GENERAL] pgsql structure export to XML

abracadabuda wrote: Hi, i need to export pgsql structure of tables, vies, PK, FK etc. etc. into simple XML structure. My 3 hour searching ended with one result: postgresql-autodoc, but i don't have enough rights on server to install it. Is there any solution for my problem - is there any progr

Re: [GENERAL] Connect to postgres from a dynamic IP

Jorge Godoy wrote: Em Monday 03 March 2008 13:17:03 você escreveu: My understanding is no password is sent in the clear with md5 per: http://www.postgresql.org/docs/8.3/interactive/auth-methods.html#AUTH-PASSW ORD But the MD5 hash is. This page states that the password can't be directly sni

[GENERAL] data import - duplicates

I have ~350K rows of sample data that has come to me in 64 text files (CSV) that I'd like to import into a new database. Each file can be considered its own category and is so named. That is, each file name will be inserted into a separate categories table. I'd like to relate each row to its ca

Re: [GENERAL] data import - duplicates

Webb Sprague wrote: I haven't tested but this is what I would do (uses arrays, which are handy when you need them), with the names changed to protect the innocent: begin; -- create a table with some duplicates in one of the columns (y is ck); wsprague=# select x, x%4 as y into temp fbar from g

Re: [GENERAL] Dump format for long term archiving.

Ron Mayer wrote: If one wanted to dump some postgres databases for long term archival storage (maybe decades), what's the recommended dump format? Is the tar or plain text preferred, or is there some other approach (xml? csv?) I should be looking at instead? Or should we just leave these in so

Re: [GENERAL] how to make this database / query faster

Richard Broersma wrote: On Sat, Mar 15, 2008 at 4:41 PM, mark <[EMAIL PROTECTED]> wrote: On Sat, Mar 15, 2008 at 4:37 PM, Richard Broersma < [EMAIL PROTECTED]> wrote: On Sat, Mar 15, 2008 at 4:21 PM, mark <[EMAIL PROTECTED]> wrote: select * from users where session_key is not Null order b

Re: [GENERAL] how to make this database / query faster

mark wrote: On Sat, Mar 15, 2008 at 5:04 PM, brian <[EMAIL PROTECTED]> wrote: As there's an index on id would it be faster to transpose the WHERE conditions? WHERE id > your_last_id AND session_key IS NOT NULL I can't remember if the order of WHERE is significant. brian,

Re: [GENERAL] Foreign keys to inherited tables

Leon Mergen wrote: Hello, I was wondering, I'm reading that there is no support for foreign keys to inherited (child) tables -- are there any plans on supporting these in the (near) future, and/or are there any practical workarounds for this ? This has worked well for me: CREATE TABLE child_

[GENERAL] table of US states' neighbours

I'd like to add a table, state_neighbours, which joins each state with all of its neighbour states. Does anyone know where I can find such a list? I'm not interested in "nearest neighbour", just any connected state. eg. IA would be associated with {MN,WS,IL,MO,KS,NB,SD} and HI & AK would not b

Re: [GENERAL] table of US states' neighbours

Colin Wetherbee wrote: brian wrote: I'd like to add a table, state_neighbours, which joins each state with all of its neighbour states. Does anyone know where I can find such a list? I'm not interested in "nearest neighbour", just any connected state. That sounds like

  1   2   3   4   5   6   >