[SQL] pg_dump and oid

ation does not exist). Thanks -- Richard NAGY Presenceweb ---(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 ma

[SQL] pg_dump and BLOB

ation does not exist). Thanks -- Richard NAGY Presenceweb  

Re: [SQL] confused by select.

table will be solved for much quicker. Cheers, Richard

[SQL] Can I get this all in one query?

I'm designing a database/website that will allow students to "grade" the professors/classes the students attend. There are eight different "factors" that the students assign grades on. Until tonight I had one table that kept the scores assigned by students. This table name REVIEW had a field n

[SQL] Re: Auto increment

In article , "Mads Jensen" <[EMAIL PROTECTED]> wrote: > Hi > > I'm a newbiw with pgsql: > > 1: Haven't been able to find the officiel manual to pgsql. What's the > complete URL? Hmm, I don't know, I use the book which you can find with all the rest of the docs at:

[SQL] Help: Using a regular expression match as a value

.. $1 of firstname, /^R.*d/ as name_starts_with_r_and_ends_with_d ... TIA Richard DeVenezia

[SQL] Is there a string to inet function that can be used in order by ?

  Richard DeVenezia

Re: [SQL] Query optimisation

now of one way to make the index work. Use groupes.nom>='beatl' AND groupes.nom<='beatlz' And that should work - although 'z' is a poor character to use - pick the highest valid character in your character set. You might also want to look in the archives for the thread on pgsql-general with a subject of 'Simple Question: Case sensitivity' - Richard Huxton

Re: [SQL] Weird problem with script...

t. > > Odd. The three statements work just fine for me when executed by hand > in 7.0.3. Anyone else able to reproduce a problem? > > regards, tom lane Works fine on 7.0.0 putting them in a text file and doing psql < filename Jamu - how are you executing this? - Richard

[SQL] Non-procedural field merging?

ondering if any of the smarter people on the list have an SQL way of doing it (something with sub-queries?) PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but I don't actually care in this case. TIA - Richard Huxton

Re: [SQL] Non-procedural field merging?

ggregates without resorting to C. Shame it's not a standard SQL feature. Thanks Tom - don't know how you find the time to give so much help in the lists. - Richard Huxton

Re: [SQL] Querying date interval

ustment on 30th Sep or 1st Oct - that caused issues in some previous versions of postgres IIRC (have a rummage in the archives) - Richard Huxton

Re: [SQL] primary key and indexing

ase Don't know the script you're talking about. Check the settings in the connect command. Try "perldoc Pg" for information on how the Pg module works. - Richard Huxton

Re: [SQL] select returns no line

| varchar() not null | 15 | > | user_exp | timestamp| 4 | > +--+--+- --+ > Indices: users_pkey > >users_user_login_key Have you tried dropping the index? Could be mangled or a locale problem... - Richard Huxton

Re: [SQL] monster query, how to make it smaller

oll in ('286.35', '286.30', '286.25') instead of all the UNIONs? This is the same as ... AND (Z_durch_sol1='286.35' OR Z_durch_sol1='286.30' ...) HTH - Richard Huxton

Re: [SQL] script for unidirectional database update

;s and > file transfer ist the most simple thing to do. Does anyone have a script > to automate this? > Look into pg_dumpall - Richard Huxton

Re: [SQL] Archival of Live database to Historical database

e query. You could use rules/triggers to set a "dirty" flag for each record that needs copying - but it sounds like you're already doing that. If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY - Richard Huxton

Re: [SQL] fetching the id of a new row

; value, which must be set to nextval()) will be used; to obtain the id, > if indeed you need it, you can than select currval(), which is > guaranteed to work on a per-backend basis. Yep - it's either get nextval and insert or insert and check currval. - Richard Huxton

Re: [SQL]how to select * from database1 table,database2 table

pplication and do the join "by hand" there. - Richard Huxton

Re: [SQL] constraint/restrict

al unique, footxt text); create table bar (barid serial, barfoo int4 references foo (fooid), bartxt text); Then after a few inserts... delete from foo where fooid=1; ERROR: referential integrity violation - key in foo still referenced from bar - Richard Huxton

Re: [SQL] problem with dates

omewhere that explains this just point > me to it. Look at the page "datetime-types.htm" in the docs (might only be in the docs for 7.1). - Richard Huxton

Re: [SQL] Estimation of SQL statements

to know what you are trying to acheive. The best way to measure speed is to build a system and populate it with dummy data. Then, analyse your results. - Richard Huxton

Re: [SQL] Function to return recordset

e: select foo_function('x'); At present it is only useful for use by other functions (I believe). I think Tom said this is on the developer todo list though, so sometime after 7.1 it should be possible. - Richard Huxton

Re: [SQL] underscore problem

ables by quoting them, as you've discovered. This usually crops up converting from a different DB to Postgresql. - Richard Huxton

Re: [SQL] SELECT DISTINCT problems

access refers to as a "totals query". Try something like: SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY LecturerName Basically, anything that is not min() or max()ed should be mentioned in the GROUP BY. You might need to quote "LecturerName" (like that) etc since they are mixed-case. On the other hand the ODBC might deal with all that for you. - Richard Huxton

Re: [SQL] Insert into VIEW ???

ge on this in the programmer's guide. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Temp Tables & Connection Pooling

time and run a separate reaper process to kill anything not used for 15 minutes (or whatever). You should be able to automate this to a degree with triggers etc. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Re: Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT

Gerald Gutierrez wrote: > > I'd like to generalize my function. As per Richard Huxton's suggestion to > create tables named after session ID (thanks Richard)s, I'd like to pass in > some table names so that the algorithm can read from and write into tables > that

Re: [SQL] Problems with RULE

From: "Jens Hartwig" <[EMAIL PROTECTED]> > Hello Richard, > > this was a very precise analysis - thanks for the effort you made! Precisely wrong in this case. My mistakes have some of the finest tolerances in the world. 8-) > Nevertheless the Tom's explan

Re: [SQL] List Concatination

rip the final trailing comma. Note that this is probably not a good idea - the ordering of the contacts will not be well-defined. When I asked about this Tom Lane was quite surprised that it worked, so no guarantees about long-term suitability. - Richard Huxton --

Re: [SQL] Poor document!!

rib/ directory for details on full-text indexing (fti). - Richard Huxton ---(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: [SQL] my pgsql error?

ext; y text; nd text; begin d := rtrim(to_char(t::timestamp, \'Day\')); m := rtrim(to_char(t::timestamp, \'DD Month\')); y := rtrim(to_char(t::timestamp, \'\' )); nd := d || \' \' || m || \' \' || y; return nd; end;' lang

Re: [SQL] copy a record from one table to another (archive)

chive > with a simple command like move ? > begin; insert into archive_foo (select * from foo where foo_id=1); delete from foo where foo_id=1; commit; Is probably the closest you could get. Alternatively, you could wrap the above up in a function and just go: select do_archiv

Re: [SQL] Re: Use of the LIMIT clause ?

it is different? I don't know that it's worth it... it seems to inconvenience some people either way. I may soon be moving a moderately complex system from MySQL to Postgres and it wouldn't be the end of my world if I had to reverse all the LIMITs. Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] my pgsql error?

Christopher Sawtell <[EMAIL PROTECTED]> said: > On Wed, 14 Mar 2001 01:38, Richard Huxton wrote: > > From: "Christopher Sawtell" <[EMAIL PROTECTED]> > > > > > Please could a kind soul help me with this. > > [ ... ] > > > Note

Re: [SQL]

ee: cannot find block containing chunk It's complaining that memory it tries to free hasn't been allocated. Something has got mangled here. I'd take a backup of the table drop the index and recreate it, see if that helps. Also - what version of Postgres is this? One of the developers

Re: [SQL] PL/pgSQL "compilation error"

fact_counts_agg SET open_count=open_count-1 \ >WHERE group_artifact_id=new.group_artifact_id; Failing that, email me the definition of artifacts_count_agg and artifact and I'll play with it here. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/pgSQL "compilation error"

pport the "ELSE > IF" structure. Thus, the compiler is looking for more "END IF"s that it > doesn't find, and errors out when it gets to the end of the procedure > without seeing them. Missed that completely - reading my expectations, not the code. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] need to join successive log entries into one

6:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 Try: select run,min(start),max(done) from mytable group by run; - Richard

Re: [SQL] pl/Perl

r-manual (developer's version - 7.1 - anyway) on the website. Actually - there seems to be a break in the "Next" links in my local copy of the docs. Is that just me? Try a google search for tcl/tk - should turn up plenty of general stuff on the language. I

Re: [SQL] need to join successive log entries into one

ming the entries are put in one at a time and in order. That way you just need to look at the last entry to determine if the new one is in the same batch. Any use? - Richard Huxton > -- > George Young, Rm. L-204[EMAIL PROTECTED] > MIT Lincoln Laboratory > 244 Wood S

Re: [SQL] RE: Help with UPDATE syntax

^^^ > ug2.role = map.role); ^^^ I take it these are actually "user_group_map"? - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an a

Re: [SQL] List Concatination

; > Hmmm ... this feature is very, very, useful now that I know how to use > > it. I'd love to see it hang around for future versions of PgSQL. Tom? > As I said before, user-defined aggregates are certainly not going away. > I don't recall the conversation Richard was

Re: [SQL] Oracle to PostgreSQL help: What is (+) in Oracle select?

;s an outer join. In Postgres it'd be SELECT o.* from one left outer join two using ( key ) but it's new in 7.1 . Richard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail comm

Re: [SQL] MultiByte strings

ming you enabled this at the ./configure stage you need to use the -E encoding flag when you run createdb. See the Administrator's Guide / Localisation section for details. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all

Re: [SQL] serial type question

do a \d or pg_dump it to see how it works. > I would like to migrate to a job number created when the insert is > done. Once you see how it works, pg_dump the database, edit the file and re-import the data. Nice clean solution and easy to cope with if something

Re: [SQL] creating "job numbers"

e will be gaps in your numbering. Also check out the SERIAL data-type which can provide automatic numbering for the fields. I'm sure there are examples in Bruce's book (there's a link on www.postgresql.org) - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] how to build this string ?

rchives for someone else who did this recently (in the last month or so, Tom Lane was involved in the discussion too). I forget the fella's name, but he should have almost exactly what you want. You can then do something like: select typ, commify(diam) from zylinder group by typ; - Richard Huxt

Re: [SQL] SOME PL/PGSQL PROBLEMS

example plpgsql functions or check http://techdocs.postgresql.org or Bruce's book at http://www.postgresql.org/docs/awbook.html If you have a specific example, please post it with the version of PG you are running. HTH - Richard Huxton ---(end of broadcast)---

Re: [SQL] rows equal

l have to check the contents of the columns. If they are never supposed to be the same, define a unique index. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command t

Re: [SQL] Serials.

u'll need to combine the sequence with another value - see my postgresql notes at techdocs.postgresql.org and perhaps look into the plpgsql cookbook (www.brasileiro.net from memory) - Richard Huxton ---(end of broadcast)--- TIP 2: you ca

Re: [SQL] Help

t can then update your database. This can be as simple as placing a script into /etc/cron.monthly/ on some systems (e.g. Linux Redhat) but in any case is not too complicated. PS - it is usually easier to do this early on the first day of each month (every month has a day 1, not all have a day 31).

Re: [SQL] Still don't know how to build this string ?

ROUP BY typ; typ | joinall -+-------- 1 | 800,840,870,1120 2 | 760,800,900,1200,1234,1352 (2 rows) Note the explicit cast of diam into text. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Functions and Triggers

the postgres notes from techdocs.postgresql.org - I've got an example there of exactly this (it's in the "automating" chapter - sorry, forget the precise URL) Also have a look at the Cookbook (linked to from same place) which might well have more examples. - Richard Huxto

Re: [SQL] pl/pgsql and returning rows

I believe this is on the todo list for a later 7.x release. Just from the top of my head, you might try a view with a select rule, although I'm not completely clear what your objectives are. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] RE: pl/pgsql and returning rows

;s supposed to be shorthand for a join. - Richard > > As a workaround, you can insert your row into an existing table, then > > retrieve it from there later. I think you need to enumerate all of the > > fields, as in 'INSERT INTO table VALUES (ret.field1, > > ret.field2,

Re: [SQL] Escaping \

e the SELECT-statement out you get '''\\\'' and that is not > unterminated. Had something similar myself the other day. The reason is that you are already one deep in Postgres' string-parser, so you need something like: SELECT '''' AS RESULT

Re: [SQL] primary key scans in sequence

ly it isn't "bigint"). Postgres doesn't realise that it can use an index on a bigint to do comparisons to an integer. If you explicitly cast the constant to a bigint, it should be willing to do an index scan, like so: select * from mitglied where mitglie

Re: [SQL] date_part bug?

was fixed before 7.0.2 You might want to try the to_char function and see if that displays the same problem. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Function x returns opaque in error typeidTypeRelid

ything relevent I tend to return either 1 or 'succeeded' or similar. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Trigger Function and Html Output

this sounds like a strange way to do things, but i think this is the > best way to go. Definitely look at some of the general-purpose templating modules. They'll all handle tables. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] Trigger Function and Html Output

From: "Stef Telford" <[EMAIL PROTECTED]> > Richard Huxton wrote: > > Each select works on a view, rather than hardcode the view into the > perl CGI, i would rather have the table header/column titles returned > as the first item as text/html (i know abou

Re: [SQL] passing parameters between forms

3.php.If there is another way out pls tell. > I tried using functions to get the work done.but couldn't get my way out. This is more of a cgi question really - I'd suggest a quick visit to www.faqs.org and have a look at the CGI faq. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Error:TypeCreate: type links already defined

ook in the pg_types table: select oid,* from pg_types where typname like 'lin%'; Don't go deleting it without knowing what it is though. If your table is called "links" I'd guess it's fine to delete it, but I'd take a backup first anyway. - Richard Huxton

Re: [SQL] Appropriate indices to create for these queries

just on one of them. I tend to apply indexes to fields that take part in a join then add them one at a time to other fields as it becomes clear which takes part in important queries. Don't forget that it takes a certain amount of effort to maintain an index. You've already found the EXPLAI

Re: [SQL] passing null parameter to plpgsq functions

feature ? how can I work around this ? It's a feature in versions before 7.1 (or possibly 7.0.x) you can still get that behaviour in 7.1 by asking for strict null handling on a specific function. The only remedy AFAIK is to upgrade. - Richard Huxton ---(end of br

Re: [SQL] passing null parameter to plpgsq functions

From: "Picard, Cyril" <[EMAIL PROTECTED]> > Thank you ! I planned to use the 7.1 to get the outer join capability. > > Is the 7.1 stable ? Pretty much - it's reached release candidate stage and I've been using it for development

Re: [SQL] Memory and performance

ss and it is VERY SLOW. If you need to import large quantities of data, look at the copy command, that tends to be faster. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] Index on View ?

is basically just a select rule that rewrites queries based on it. Indexes on underlying tables should be used though. Difficult to suggest what indices you might need without knowing the view/tables/queries involved. - Richard Huxton ---(end of broadcast)--

Re: [SQL] Index on View ?

From: "Keith Gray" <[EMAIL PROTECTED]> > Richard Huxton wrote: > > > > Indexes on underlying tables should be used though. Difficult to suggest > > what indices you might need without knowing the view/tables/queries > > involved. > > As an e

Re: [SQL] Subqueries in select clause

gt; clause. In 7.1 at least you can do it if you alias the sub-query: select max(d) from (select count(b) as d from c group by a) as calias; Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade anyway - I seem to remember some problems with 7.0.2) HTH

Re: [SQL] groups in postgres

posible manipulating groups? Or do I have to > play with triggers. Sounds like you want different groups for different users, and a view for each group. You can then define rules for each view defining the access that is available. Is that what you're after? -

Re: [SQL] Re: Cursors in plpgsql

y I have lost the link but I remeber that I have You can get to it from http://techdocs.postgresql.org/ - one of Roberto Mello's contributions IIRC - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please sen

Re: [SQL] INT8 sequences

ello's plpgsql cookbook and the int8 page in my postgresql notes - I summarised anything useful I saw about it. The first example I give is probably fine for just generating unique id's. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you ch

Re: [SQL] Is function atomic?

From: "Wei Weng" <[EMAIL PROTECTED]> > If it is not, is it possible to acquire a lock on a row ? how about a > lock on a table? All functions take place within a transaction, but since PG doesn't support nested transactions yet you can't roll back the effects

Re: [SQL] view and performance

uch difference either way unless the query was very fast to execute. - Richard Huxton ---(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

Re: [SQL] Is function atomic?

From: "John Hasler" <[EMAIL PROTECTED]> > Richard Huxton writes: > > All functions take place within a transaction, but since PG doesn't > > support nested transactions yet you can't roll back the effects of a > > nested function. > > Do you

Re: [SQL] Returning multiple Rows from PL/pgSQL-Function

so: CREATE VIEW get_emotions_view AS SELECT emotion_id, emotion1, ... ORDER BY date_epoch + full_rating*3600*12 LIMIT 300; and then issue a query like: SELECT * FROM get_emotions view WHERE emotion_id BETWEEN last_em_id()-3000 AND last_em_id(); If you set the "is_cachable" flag on the las

Re: [SQL] Returning multiple Rows from PL/pgSQL-Function

You can create a functional index as easily as a normal one: CREATE FUNCTION calculated_score(integer, integer) RETURNS integer AS ' BEGIN RETURN $1 + ($2 + 3600 + 12) END; ' LANGUAGE 'plpgsql'; CREATE INDEX emot_calc_idx ON emotions ( calculated_score(dat

Re: [SQL] Problems with PG_DUMP and restore

when I attemped to > restore the data (via \i filename), it failed selectively; some tables > were restored but many were not. No errors were logged. Try having a look at the order the tables get inserted esp. with regard to any foreign keys etc. - I'm not sure pgdump is

Re: [SQL] Problems with PG_DUMP and restore

From: "Josh Berkus" <[EMAIL PROTECTED]> > Richard, > > Try having a look at the order the tables get inserted esp. with > > regard to > > any foreign keys etc. - I'm not sure pgdump is that clever about such > > things. > > Thanks. I did try

Re: [SQL] SQL - histogram

rom heights; cm - 150 160 (2 rows) richardh=> select cm,count(id) from people, heights where height>=cm group by cm; cm | count -+--- 150 | 4 160 | 2 (2 rows) HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Group by date_part

otherwise #1. I'd be very interested in any clever way of doing this without a temporary table (or equivalent - if functions could return sets of values you could use that, but it's basically the same thing). - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] "Display of specified number of records."

estdate limit 40 offset 10; This calculates the results and then throws away the first nine and anything after the 50th. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Date Validation?

date selector. Out of curiosity Josh, why aren't you validating in PHP? - only takes a couple of lines there. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] ANNOUNCE: Updated PostgreSQL Notes

& DBI) - typos corrected etc. If you do download it, please take a minute to send some feedback, coz I can't tell which pages are useful without it. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, pleas

Re: [SQL] Hey! ORDER BY in VIEWS?

sense so I stuck the example in my PostgreSQL notes. - Richard Huxton ---(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: [SQL] ERROR: UNIQUE constraint matching given keys for referenced table "sequences" not found

get this message: > > ERROR: UNIQUE constraint matching given keys for referenced table > "sequences" not found > > The problem is that the referenced field and table exist. > Any hint? Do you have a unique index on sequences.seq_code? - Richard Huxton

Re: [SQL] pl/pgsql - code review + question

aren't enforcing uniqueness on (tnumber,mnumber) now might be a good time to do so. If the teamnum isn't always a fixed length search for the '-' with strpos() richardh=> select strpos('abcdefg','e'); strpos 5 HTH - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] pl/pgsql - code review + question

> raise exception ''Member '' || unitno || '' not found''; RAISE EXCEPTION ''Member % not found'', unitno; Don't know why the parser for RAISE doesn't like string concat. Possibly because it maps to the elog() error

Re: [SQL] pl/pgsql - code review + question

From: "Tom Lane" <[EMAIL PROTECTED]> > "Richard Huxton" <[EMAIL PROTECTED]> writes: > > Don't know why the parser for RAISE doesn't like string concat. > > Laziness ;-). Someone should fix plpgsql so that RAISE does take > expre

Re: [SQL] pl/pgsql - code review + question

From: "Richard Huxton" <[EMAIL PROTECTED]> > False Laziness perhaps (spot my Perl background) > > OK - stick me down for having a look at it. Had a quick peek and I think > it's within my abilities. Give me a couple of weeks, because I haven't > looke

Re: [SQL] where'd the spaces come from

the current version, might be worth posting a bug report. You can work around it with something like: ... substr(to_char(t.tnumber,'000'),2,3) ... HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Re: Records exactly the same.

;id" or "seq" field of type SERIAL and get on with your project. The reason people on the list are shouting is because we got burnt at some time with exactly this thing and we're trying to stop that happening to you. Oh - check the glossary at techdocs.postgresql.org and look at

Re: [SQL] How to get the server version??

From: "Roberto João Lopes Garcia" <[EMAIL PROTECTED]> > Is there any way, possible an SQL or pgsql command, to get the server version? select version(); - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading thr

Re: [SQL] Get name of columns in a table

María Elena Hernández wrote: > To get a list of columns, run "psql -E" and do a standard "\df foo" where foo is your table name. This will show you exactly how PG does it. I've found this very useful with all the backslash commands. HTH - Richard Huxton --

Re: [SQL] Converting epoch to timestamp?

only way I've ever known to do it. Note the one-hour offset because I'm currently in BST rather than GMT timezone (ignore the few seconds discrepancy - that's me querying then cutting and pasting). Be interested to find out if there's a neater way. Can't believe there's

[SQL] Outer Join Syntax

I'm doing a feasability study on porting our flagship product to Postgres (from MS_SQL). I have run across a few snags, the largest of which is the outer join syntax. MS has some nice syntactical sugar with the *=/=* operators that Postgres dosen't seem to support. I am confused on how to repli

Re: [SQL] where'd the spaces come from

9.9') => ' 0012.0' to_char(12,'FM9990999.9') => '0012' I think the issue is you look at to_char() and make assumptions if you're not familiar with it. I *seem* to remember someone saying Oracle worked this way. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] 2 tables, joins and same name...

ir where dest.airport_dep_id_id=air.airport_id and > dest.airport_arr_id=air.airport_id; You have to join against the airport table twice: SELECT dest.dest_name, air1.name as airport1, air2.name as airport2 FROM desination dest, airport air1, airport 2 WHERE dest.airport_dep_id = air1.airport_id

  1   2   3   4   5   6   7   8   9   10   >