[SQL] Can I use subselect as a function parameter?
I want to do this:
CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS BOOLEAN
AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'C';
CREATE TABLE product (
id CHAR(10) PRIMARY KEY,
brandCHAR(12) REFERENCES brandname(id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
eancode CHAR(6) CHECK (eancode IS NULL
OR eancode ~ '[0-9]{6}'),
...,
CONSTRAINT ean CHECK (
CASE WHEN eancode IS NULL OR brand IS NULL
THEN 't'
ELSE ean_checkdigit(
(SELECT ean_prefix
FROM brandname, product
WHERE brandname.id = product.brand
), eancode)
END
)
);
The parser accepts it, but when it is run on a line that matches the ELSE in the
constraint, I get:
copy product from '/usr1/avoca/dumps/dbdump.product'
ERROR: copy: line 2, ExecEvalExpr: unknown expression type 108
Can this be made to work at all (in 7.0.2)?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"I waited patiently for the LORD; and he inclined unto
me, and heard my cry. He brought me up also out of an
horrible pit, out of the miry clay, and set my feet
upon a rock, and established my goings."
Psalms 40:1,2
Re: [SQL] is there a debian package for plperl?
Bruno Boettcher wrote: >Hello, >subject says it all, wanted to give plperl a try, but the lib doesn't >seem installed... now i installed all through debian packaging system, >and the lib surely is somewhere, but i didn't found it yet... > >so i anybody could point on on where to search for it... plperl failed to build in 7.0.2; however, I have just built 7.0.3-1 and plperl.so is now included again. This should be uploaded within the next day or so. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart..."Psalms 24:3,4
Re: [SQL] plpgsql
feblec wrote: >consultas=# CREATE FUNCTION spread(text) ... >consultas-# LANGUAGE 'plpgsql'; >ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the >created procedural languages. create function plpgsql_call_handler() returns opaque as '/usr/local/pgsql/lib/plpgsql.so' <-- or wherever language 'C'; create trusted procedural language 'plpgsql' handler plpgsql_call_handler lancompiler 'PL/pgSQL'; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Be of good courage, and he shall strengthen your heart, all ye that hope in the LORD." Psalms 31:24
Re: [SQL] Invoice number
Mike Castle wrote: >On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote: >> - Sequences are not rollback'able. > >Did you mean SERIAL instead of sequence here? > >If so, why is no rollbackable an issue? All you should need is unique >numbers. Not necessarily exactly sequential numbers. For invoice numbers, it matters. Numbers missing from such a sequence are likely to provoke questions from auditors and taxmen; why borrow trouble? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For a child will be born to us, a son will be given to us; And the government will rest on His shoulders; And His name will be called Wonderful Counsellor, Mighty God, Eternal Father, Prince of Peace." Isaiah 9:6
Re: [SQL] How to trim values?
[EMAIL PROTECTED] wrote: >Hi, > >I'm trying to figure out how to take a value like 3.68009074974387 >(that is calculated from values in my database) and have PostgreSQL >hand me 3.68. Any suggestions would be appreciated. cast it to numeric(x,2) (where x is the total number of digits, and 2 is two decimal places). template1=# select 3.68009074974387::numeric(3,2); ?column? -- 3.68 (1 row) or use round(value,2) template1=# select round(3.68009074974387, 2); round --- 3.68 (1 row) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For God shall bring every work into judgment, with every secret thing, whether it be good, or whether it be evil." Ecclesiastes 12:14
Re: [SQL] References to SERIAL
"Brett W. McCoy" wrote:
>On Sat, 30 Dec 2000, Thomas SMETS wrote:
>
>> If i create a "internal pk" buy defining on a table a field SERIAL.
>> How do I reference this field in the other table to set the field
>> possible value ?
...
>
>You mean as a foreign key? You would do something like
>
>create table books_authors (
> book integer references book(book_pk)
> on delete no action,
...
If you need to know which value was used for the SERIAL field, there are
two ways:
1. Use currval('book_book_pk_seq') to get the last value used in this
session.
bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select currval('junk_id_seq');
currval
-
1
(1 row)
2. Use the OID which is returned by a successful INSERT statement to look
up the newly-created row from the table:
bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select * from junk where oid = 1780993 ;
id | name
+--
1 | Fred
(1 row)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Give to him that asketh thee, and from him that would
borrow of thee turn not away."
Matthew 5:42
Re: [SQL] psql -f option
"Graham Vickrage" wrote: >I am trying to use the psql -f option to load a script into the >DB ( v7.0 ) from the linux command line. > >The documentation says -f enables some nice features such as error messages >with line numbers. It seems to me that this is half true i.e. it shows me >error messages, its doesn't however give me the associated line number in >the script. This would be a very useful feature for me as my scripts can be >very long. > >Is there a configuration option i am missing? Use -e as well, to have the SQL queries echoed; then you can see where the errors are arising. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But because of his great love for us, God, who is rich in mercy, made us alive with Christ even when we were dead in transgressions-it is by grace you have been saved."Ephesians 2:4,5
Re: [SQL] Postgresql database access
"Marcos =?iso-8859-1?Q?Aur=E9lio?= S. da Silva" wrote: >Dear Pg experts, > >I have two databases and i want to refer to one table in a database "X" >when >i'm using database "Y". Something like this: > >select * from X.table > >This causes a parse error. What's the correct sintax? It isn't possible to do this; you can only look at one database at a time. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And thou shalt love the LORD thy God with all thine heart, and with all thy soul, and with all thy might." Deuteronomy 6:5
Re: [SQL] Where can i get Pgaccess
Ramesh H R wrote: >Hello everyone, >Please can anyone tell me, where i can get Pgaccess query tool It is in the PostgreSQL source at src/bin/pgaccess/ See also http://www.flex.ro/pgaccess -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD is my strength and song, and he is become my salvation; he is my God, and I will prepare him an habitation; my father's God, and I will exalt him." Exodus 15:2
Re: [SQL] DATE
john whale wrote: >PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: > ><$NOW;DD;> > >TO GIVE ME A DATE THAT IS X DAYS FORWARD > >ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD >OF THE DATE NOW. I don't recognise the format you are using, but in standard SQL: template1=# select CURRENT_DATE as now, template1-#CAST (CURRENT_DATE + INTERVAL '7 days' AS DATE) as week; now |week + 2001-01-24 | 2001-01-31 (1 row) Use to_char() for date formatting. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If anyone has material possessions and sees his brother in need but has no pity on him, how can the love of God be in him?" I John 3:17
Re: [SQL] "'" in SQL INSERT statement
Markus Wagner wrote:
>Hi,
>
>I have some data that I wish to transfer into a database using perl/DBI.
>Some of the data are strings containing the apostrophe "'" which I use
>as string delimiter.
>
>How can I put these into my database using the INSERT statement?
Escape the apostrophe with another apostrophe or a backslash:
junk=# insert into a (b) values ('John''s text');
INSERT 6815936 1
junk=# select * from a;
a | b
---+-
1 | some text
2 | John's text
(2 rows)
junk=# insert into a (b) values ('Fred\'s text');
INSERT 6815937 1
junk=# select * from a;
a | b
---+-----
1 | some text
2 | John's text
3 | Fred's text
(3 rows)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"My little children, let us not love in word, neither
in tongue; but in deed and in truth."
I John 3:18
Re: [SQL] Is there anything like DESCRIBE?
"Mike D'Agosta" wrote: >Hi, > > I have a number of empty tables and I want to get the column names and >data types with an SQL statement. I want to do this procedurally, not >interactively (so I can't use \d in psql). Postgres doesn't >support DESCRIBE... is there any other way to do this? If you run psql with the -E option, it will show you the query it uses when you type `\d+`. Use that query in your procedural code. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18
Re: [SQL] Don't want blank data
David Olbersen wrote: >Greetings, > Is there a way to have postgresql always return a value for each row > requested? To be more clear, if I were using a Perl SQL hybrid I would wri >te > something like > > SELECT computer_ip or 'unset' FROM computers; > > So that if computers.computer_ip is NULL or '' I will get 'unset' back fro >m > the database. I hope this makes sense and somebody can point me in a good > direction SELECT COALESCE(computer_ip,'unset') AS computer_ip FROM computers; COALESCE() returns the leftmost non-null value from its parameters. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18
[SQL] Re: [HACKERS] How to modify type in table?
"Jaruwan Laongmal" wrote: >Would you like to inform me how to modify type in table? >For example , I define type as varchar(14) , but I want to modify to varcha= >r(120). How to do this. There is no facility to do this directly. (Allowing columns to change their type would possibly involve rewriting the entire table.) You can use pg_dump to dump either a database or a particular table. Then you can edit the CREATE TABLE command in the dump output and then create a new database or table from the dump. If the table in question is not referenced by any other objects, dump it (pg_dump -t table database >dump); edit the dump; delete or rename the old table; and finally restore the dump (psql -d database http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
Re: [SQL] DROP Column
Keith Gray wrote: >Is DROP Column implemented in 7.x? No -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
Re: [SQL] Is this feature a bug?
Christopher Sawtell wrote: >Is the "( 1 row)" string really supposed to be there? >imho it should not be. >How can I turn it off? \pset tuples_only or \t or start psql with the -t option. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
Re: [SQL] createuser problem
"fion yong" wrote: >It gives the following error when i tried to a new user > >createuser demouser1 >Connection to database 'template1' failed. >FATAL 1: SetUserId: user 'fion' is not in 'pg_shadow' > >how should i solve this problem? Become user postgres before you try to run createuser. (Or become any user who has create user privilege.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "God be merciful unto us, and bless us; and cause his face to shine upon us." Psalms 67:1
Re: [SQL] logging a psql script
Ken Kline wrote: >Hello, > I would like my psql script to log everything that it does. psql -e -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26
Re: [SQL] sum(bool)?
Olaf Marc Zanger wrote: >hi there, > >i want to add up the "true" values of a comparison like > >sum(a>b) > >it just doesn't work like this > >any workaround? select count(*) where a > b; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But God commendeth his love toward us, in that, while we were yet sinners, Christ died for us." Romans 5:8
Re: [SQL] int2+float8 problems
"guard" wrote: >select trn_qty,amount2,trn_qty*amount2 from invo > >trn_qty INT2 >amount2 FLOAT8 > >+-+-+--+ >| trn_qty | amount2 | ?column? | >+-+-+--+ >| -1 |7678 | 7678 | > >what to get -7678 This works in 7.1beta4. Perhaps you need to upgrade. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD is my shepherd; I shall not want. He maketh me to lie down in green pastures: he leadeth me beside the still waters, he restoreth my soul...Surely goodness and mercy shall follow me all the days of my life; and I will dwell in the house of the LORD for ever."Psalms 23:1,2,6
[GENERAL] Re: [SQL] Permissons on database
Boulat Khakimov wrote: >Hi, > >How do I grant permissions on everything in the selected databes? > >GRANT doesnt take as on object database name nor does it accept wild >chars However you can give it a list of tables (and other objects). -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD is my light and my salvation; whom shall I fear? the LORD is the strength of my life; of whom shall I be afraid?" Psalms 27:1 ---(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] need to join successive log entries into one
George Young wrote: ... >I need to produce a new table that coalesces immediately successive >operations on a run into one, e.g.: > >run | start | done > 1415| 2001-01-29 12:36: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 > >i.e. where a run has one or more steps with succesive seq values, >or equivalently, with abutting start/end values, then I want >the new table to have only one entry representing the full span of >time. Null 'done' just means it's not done yet. ... >Can anyone think of a way I can do this in postgres? I think you would be better off feeding the log out of the database into a perl script. SQL is a set-oriented language that can't do this sort of thing without some appalling contortions, if at all. (I can't think of a way to do it.) So, store the data in PostgreSQL, but do procedural processing in a more appropriate language. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let your light so shine before men, that they may see your good works, and glorify your Father which is in heaven." Matthew 5:16 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PIVOT of data
Srikanth Rao wrote:
>Hi,
>I have a table like this:
> location| numbrochures | marketing
>---+--+---
> 101 Asheville, NC |4 | NEWS
> 101 Asheville, NC |1 | TV
> 101 Asheville, NC |3 | RADIO
> 101 Asheville, NC |2 | OTHER
> 101 Asheville, NC |3 | null
> 101 Asheville, NC |1 | TV
> 102 'Charlotte', 'NC' |1 | SIGN
> 104 'Colfax', 'NC'|5 | SIGN
> 109 'Moyock', 'NC'|1 | BROCHURE
>(9 rows)
>
>
>I want the headings to be like:
>
>location | NEWS | TV | RADIO | OTHER |
>
>How to get this done using sql for postgresql backend?
SELECT location,
CASE WHEN marketing = 'NEWS'
THEN numbrochures
ELSE NULL
END AS "NEWS",
CASE WHEN marketing = 'TV'
THEN numbrochures
ELSE NULL
END AS "TV",
...
but it's a clumsy hack and won't work if you don't know the
contents of "marketing" in advance.
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Let your light so shine before men, that they may see
your good works, and glorify your Father which is in
heaven." Matthew 5:16
---(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] searching for dates
Birgit Jansen wrote:
>I am trying to select from a table all rows that have a date befor
>1/1/2001 or after some date
>I am not sure how to do it.
>I try
>select date_part('year', start_date) from sometable;
>and that works but how do I get it to only show me the years between
>1990 and 2001 or some
>othere set of dates.
>
>I would realy like to be able to just have a function to tell me if a
>date in my datebase is
>befor or after a date?
SELECT *
FROM table
WHERE start_date BETWEEN '1990-01-01' AND '2001-12-31';
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Thou will keep him in perfect peace, whose mind is
stayed on thee, because he trusts in thee."
Isaiah 26:3
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] a select statement that sometimes joins
Mark Stosberg wrote: > > > >Here's a situation I've run into more than once with SQL: > >I want to select all the rows in a table that match a criteria, where one >of the criteria is possibly having a related entry in a second table. For >my example, lets say I have table named 'messages' and another named >'message_attachments'. The former has a primary key of msg_id, the latter >also contains msg_id, and has an attachment_id as it's primary key. > >This statement shows me all the messages that also have attachments: > >SELECT > messages.msg_id, > message_attachments.attachment_id > FROM messages,message_attachments > WHERE messages.msg_id = message_attachments.msg_id; > >But I want a statement that says: "Show me all the messages, and include >information about an attachment if they have one" SELECT m.msg_id, a.attachment_id FROM messages AS m LEFT OUTER JOIN message-attachments AS a ON m.msg_id = a.msg_id; This requires 7.1 for the LEFT OUTER JOIN. In 7.0.3 you could do it with a UNION. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But seek ye first the kingdom of God, and his righteousness; and all these things shall be added unto you." Matthew 6:33 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] problem with copy command
Here is a method of filtering out the duplicate keys from the input file: Let us assume that the input data is in /tmp/table.in, that that file is tab-delimited and that the primary key is in field 2. psql -d database -c "COPY table TO '/tmp/table.1'" psql -d database -c "COPY table TO '/tmp/table.2'" cat /tmp/table.in /tmp/table.[12] | sort -k 2 -t \[tab] | uniq -u -W 1 -f 1 -t \[tab] >/tmp/table.in.unique [tab] stands for the sequence "ctrl-V tab", which will force an actual tab character into the command line (the backslash will protect it from the shell). We copy the database out twice to ensure that all keys already in it are excluded, otherwise we would be reintroducing all primary keys that were _not_ in the desired input. The end product is a file that excludes all primary keys that are already in the target table. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Concatenate fields
Amanda Riera wrote: >I would like concatenate some fields to have all information in just >one field. I'm doing this below: > >CREATE TABLE bill_2col AS >SELECT bill.bill_id, > (trim(text(bill.bill_number)) || ' | ' || > trim(text(provider.company)) || ' | ' || > trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || > trim(to_char(bill.amount,'999.99')) || ' pts') AS billdesc >FROM bill, provider >WHERE bill.provider_id = provider.provider_id >ORDER BY bill.bill_id; > >When it finds some empty field, it makes all the new field empty, no >matters >if the other are empty or not. In this case, empty means NULL. Any concatenation involving NULL returns NULL; this is according to the standard. Use COALESCE(field,'') to return an empty string if field is NULL, so that no NULLs go into the concatenation. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(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] enumerating rows
Kovacs Zoltan wrote:
>> Use the "serial" column type.
>Unfortunately it's not what I expect. Assume that I have an arbitrary
>"SELECT expr1 as column1, expr2 as column2, ..." which gives
>
>column1 | column2 | ...
>+-+- ...
>..data..
>
>
>I would like to get the same result with the only plus column row_no:
>
>row_no | column1 | column2 | ...
>---+-+-+- ...
> 1 | ..data..
> 2 |
>.
>
>with a new SELECT statement: "SELECT ?, expr1 as column1, expr2 as
>column2, ...". What to write instead of ??
Here is a method which is fairly cumbersome, but will do what you want.
(Whether what you want is useful, is another matter. The row numbers
have no meaning except to delineate which row is printed after which; they
bear no relation to their order in the table.)
Create the C code shown in the attachment.
Compile it (the example shown is for Linux, see the programmer's manual for
how to do it on other systems):
gcc -fpic -c rowno.c
gcc -shared -o rowno.so rowno.o
In the database, create functions as shown (remember to change
the directory from /tmp!):
CREATE FUNCTION reset_row() RETURNS int4
AS '/tmp/rowno.so' LANGUAGE 'C';
CREATE FUNCTION row_no() RETURNS int4
AS '/tmp/rowno.so' LANGUAGE 'C';
Now you can use the function:
bray=# select row_no() as row,id,name from person;
row | id | name
--++---
1 | 11 | Mr Graham Love (Director)
2 | 12 | AILEEN BROWN
...
but you have to do this in between queries:
bray=# select reset_row();
because the numbers don't reset themselves:
bray=# select row_no() as row,id,name from person;
row | id | name
---++---
6015 | 11 | Mr Graham Love (Director)
6016 | 12 | AILEEN BROWN
...
#include "postgres.h"
#include "fmgr.h"
static int32 row = 0;
PG_FUNCTION_INFO_V1(row_no);
Datum
row_no()
{
row += 1;
PG_RETURN_INT32(row);
}
PG_FUNCTION_INFO_V1(reset_row);
Datum
reset_row()
{
row = 0;
PG_RETURN_INT32(row);
}
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Is any one of you in trouble? He should pray. Is
anyone happy? Let him sing songs of praise. Is any one
of you sick? He should call the elders of the church
to pray over him...The prayer of a righteous man is
powerful and effective." James 5:13,14,16
---(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] enumerating rows
Kovacs Zoltan wrote: >> Here is a method which is fairly cumbersome, but will do what you want. >> (Whether what you want is useful, is another matter. The row numbers >> have no meaning except to delineate which row is printed after which; they >> bear no relation to their order in the table.) >Thanks, Oliver! Are you sure there is no other (more >convenient) solution? I don't think this is a part of the SQL standard but >it could be a PostgreSQL extension. Hm? I believe Oracle has an inbuilt feature to do this; I don't know about any other database. Nor do I know if any other databases can accommodate user-defined functions. But what is the actual use of this feature? Why do you need it? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(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] Debian Package problems
Roberto Mello wrote: >On Fri, Apr 13, 2001 at 09:00:45AM -0600, Roberto Mello wrote: > >>If I try to connect through psql, it asks me for the password again. >> It looks like it's using password auth instead of ident auth, which used >> to be the default. >>There's nothing pointed in the packages web pages, a caveat or >> anything. I've looked. > > Okay, I feel dumb now. > It looks like Oliver Elphick (the package maintainer) changed the >default authentication methods. Before local users were "trust" now they >are "password". > Thing is, if it's password, how do you know the password for user >postgres? > This (a note) should be in the package pages, or at least told the user > >during upgrade. Noted. If you do export PGHOST=localhost you will be able to connect without a password, using the default method. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "I sought the LORD, and he heard me, and delivered me from all my fears."Psalms 34:41 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Dropping users with no name
=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= wrote: >I have accidentally created a user with no name. How can I delete this >user? >I have compiled my attempts below: > >Hans > > >persons=# SELECT * FROM pg_user; > usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | >passwd | >valuntil >--+--+-+--+--+---+-- >+-- > > postgres | 26 | t | t| t| t | > | > | 27 | f | f| f| f | > | >(2 rows) > >persons=# DELETE FROM pg_user where usesysid>26; >DELETE 0 pg_user is a view. Try DELETE FROM pg_shadow where usename = ''; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Be strong, and let your heart take courage, all you who hope in the Lord." Psalm 31:24 ---(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] copy into serial field with auto_increment
Andy wrote:
>i've got a textfile of data separated by '|'. One of the fields in my
>table is a serial (auto_increment) field.
>When i import the data with the copy command i get duplicate key error.
>The key is the serial - field.
>I recognized that the copy command doesn't auto_increment the serial
>field. I come from mysql and pushed a 0 into the serial field. But it
>doesn't work. I tested it with an empty field, but it failed to.
>How could i make it work ??
It's not clear to me whether your error is during COPY or afterwards.
If it is during COPY, edit your textfile and assign unique values to
each row.
If it is afterwards, use
SELECT setval('sequence_name', SELECT max(serial_field) FROM table);
to set the sequence value. (You probably have to be running 7.1 to use
a sub-select like that.)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"For I am persuaded, that neither death, nor life, nor
angels, nor principalities, nor powers, nor things
present, nor things to come, nor height, nor depth,
nor any other creature, shall be able to separate us
from the love of God, which is in Christ Jesus our
Lord." Romans 8:38,39
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PSQL 7.1 DEBS
Mateusz Mazur wrote: >Hi. >Is there any place where can I find PSQL 7.1 Debian packages? http://people.debian.org/~elphick/postgresaql (7.1rc4) I'm working on final tweaks to packaging of 7.1 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The sacrifices of God are a broken spirit; a broken and a contrite heart, O God, thou wilt not despise." Psalms 51:17 ---(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] General ISA and Foreign Key
BOUCHPAN-LERUST-JUERY Lionel wrote: >In SQL I have the following tables > >CREATE TABLE film( >VisaExploitation INTEGER NOT NULL, >DureeTournage INTEGER NOT NULL, >Titre VARCHAR( 50 ), > >PRIMARY KEY ( VisaExploitation ) ); >CREATE TABLE filmHistorique( >NbCostume INTEGER >) INHERITS ( film ); > >create table filmDocumentaire( > ) INHERITS ( film ); > >I have a weak entity: > >CREATE TABLE copie( >NumCopie INTEGER NOT NULL, >VisaExploitation INTEGER NOT NULL, >PRIMARY KEY( VisaExploitation, NumCopie ), >FOREIGN KEY( VisaExploitation ) REFERENCES film ON DELETE CASCADE ); > >The problem is I have to be able to have the constraint on >both 2 and 3 and I can't figure how to implement this. You can't do this at present, because there is no support for a foreign key constraint on an inheritance hierarchy (it is a major defect with the current implementation of inheritance). Although inheritance is conceptually correct, the lack of implementation suggests an alternative course: "film" should contain a row for every film and "filmHistorique" and "filmDocumentaire" should have foreign key constraints on "film". You could maintain the contents of "film" by triggers on the other two tables. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21 ---(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] left join syntax
"Haywood J'Bleauxmie" wrote: >I have a database that tracks work orders. Each order tracks two entries >from the employees table; the employee ID of the person assigned to the >work order and the ID of the person who completed the order. Each work >order may have one, both, or neither field filled in. As such, I need to >left join the employee table to the work order table, but I cannot figure >out the syntax for the double-join. As independent selects, I can do the >join: > >SELECT o.ordr_id, a.last_name >FROM ordr o left join employee a on o.assigned_id = a.emp_id; > >SELECT o.ordr_id, c.last_name >FROM ordr o left join employee c on o.completion_id = c.emp_id; > >But I would like to have the whole thing in a single SELECT. Can you help >me out? Just combine them: junk=# select * from ordr; ordr_id | assigned_id | completion_id -+-+--- 1 | | 2 | 1 | 3 | 1 | 2 4 | | 2 (4 rows) junk=# select * from employee; emp_id | last_name +--- 1 | aaa 2 | bbb 3 | ccc (3 rows) junk=# SELECT o.ordr_id, a.last_name AS assigned, c.last_name AS completion junk-# FROM ordr AS o junk-#LEFT JOIN employee AS a ON o.assigned_id = a.emp_id junk-#LEFT JOIN employee AS c ON o.completion_id = c.emp_id junk-# ORDER BY ordr_id; ordr_id | assigned | completion -+--+ 1 | | 2 | aaa | 3 | aaa | bbb 4 | | bbb (4 rows) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Follow peace with all men, and holiness, without which no man shall see the Lord." Hebrews 12:14 ---(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] GRANT ALL ON TO GROUP failure
Jari Aalto wrote: > >Can anyone suggest, what is wrong with the following >sql file? SOmehow the semicolon causes error? ... >Granting ALL to USER >psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near "user >" ... >32:GRANT ALL ON >33: bonus >34: , custid >35: , customer >36: , dept >37: , dual >38: , dummy >39: , emp >40: , item >41: , ordid >42: , ordx >43: , price >44: , prodid >45: , product >46: , sales >47: , salgrade >48: TO GROUP user; user is a reserved word; it should not have been accepted as a group name unless double-quoted, in which case you need to double-quote it every time you mention it. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Preach the word; be instant in season, out of season; reprove, rebuke, exhort with all longsuffering and doctrine." II Timothy 4:2 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need help in composing PostgreSQL query
"Vladimir V. Zolotych" wrote:
>Hi
>
>Please help me compose the query in PostgreSQL.
>Using PostgreSQL 7.1.2.
>
>Suppose relations A and B have columns:
> {X1, X2, ..., Xm, Y1, Y2, ..., Yn}
>and
> {Y1, Y2, ..., Yn}
>Attributes Y1, Y2, ..., Yn are common for both relations
>and have the same type in both.
>
>How can I define in PostgreSQL the query producing
>relation with columns X1,X2,...,Xm containing all those tuples
>satisfying conditon: relation A contains tupple
> {x1,x2,...xm,y1,y2,...,yn}
>for _each_ tupple
> {y1,y2,...,yn}
>in relation B ? Where x1 denotes particular value of
>colum X1 etc.
You seem to be talking about a natural join:
SELECT *
FROM a,b
WHERE a.y1 = b.y1 AND a.y2 = b.y2 AND ... AND a.yn = b.yn;
>For example: consider two tables DEND and DOR.
>
>DEND DOR
>
> s | p p
>+
> s1 | p1 p1
> s1 | p2 p2
> s1 | p3 p3
> s1 | p4 p4
> s1 | p5 p5
> s1 | p6 p5
> s2 | p1 (6 rows)
> s2 | p2
> s3 | p2
> s4 | p2
> s4 | p4
> s4 | p5
>(12 rows)
>
>For such tables our desired query should return:
>
> s
>
> s1
SELECT DOR.s
FROM DEND,DOR
WHERE DOR.p = DEND.p;
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"If any of you lack wisdom, let him ask of God, who
gives to all men generously and without reproach, and
it will be given to him." James 1:5
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] EXECUTE ... INTO?
"Josh Berkus" wrote: >Folks, > >Can anybody tell me the syntax for sending the result of an EXECUTE to a >variable within a PL/pgSQL function again? Jan Wieck posted it to the >list this summer, but the "searchable list archives" are bogging down. FOR variable IN EXECUTE ''SELECT ...'' LOOP END LOOP; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But be ye doers of the word, and not hearers only, deceiving your own selves." James 1:22 ---(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] Another postgres 'file not found' error
On Thu, 2002-06-13 at 19:01, Josh Berkus wrote: > > Debian Woody with 2.4.18 Linux kernel. Postgres install from apt-get > > (7.2.1). > > Hmmm. I might suggest polling both the Debian mailing lists and the > pgsql-hackers mailing list. I remember vaguely hearing about some bug with > Postgres on Debian, but I can't remember where I heard it. I do not think there is any Debian bug report that looks like this. The full bug list is at http://bugs.debian.org/cgi-bin/pkgreport.cgi?pkg=postgresql -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Whether therefore ye eat, or drink, or whatsoever ye do, do all to the glory of God." I Corinthians 10:31 signature.asc Description: This is a digitally signed message part
Re: [SQL] parser: parse error at or near "$1"
On Wed, 2002-08-14 at 13:26, Sugandha Shah wrote: > Hi , > I'm getting this error : > > Error occurred while executing PL/pgSQL function ins_schedule_status > line 42 at SQL statement > parser: parse error at or near "$1" > > Unable to locate the cause. Please any hint or clue will be of great help. > > Below is the function > > > CREATE FUNCTION ins_schedule_status(int4,time without time >zone,varchar(256),int2,int2,int2) RETURNS int4 AS ' > DECLARE > schedule_id ALIAS FOR $1; ... > insert into status_log ( log_id, computer_id,schedule_id,status, schedule_id in the insert is a column name; the alias subsitutes $1 at that point. You had better choose variable names that don't conflict with anything else. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Few Queries
On Wed, 2002-08-14 at 13:29, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar problem and >knows a solution. > > CREATE FUNCTION del_old_history() RETURNS int4 AS ' ^ > declare >var_history_age_limit int4; >set_timedatetime; > > BEGIN > select into var_history_age_limit history_age_limit from database_info; > IF (var_history_age_limit is not null) THEN > > --set_time :=select current_date()+ INTERVAL '1 day'); > select into set_time current_date()+ INTERVAL ''$var_history_age_limit days >''; > delete from history where complete_time <= set_time; > END IF; >return true; ^^^ > END;' > LANGUAGE 'plpgsql'; You don't seem to be returning what you have selected. I would expect "RETURN true;" to give you an error. Shouldn't that be "RETURN var_history_age_limit;"? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] How to execute my trigger when update certain columns
On Wed, 2002-08-14 at 13:09, Raymond Chui wrote: > Let say I have a table has column1 and column2 and I made a trigger for > after INSERT OR UPDATE for each row to execute procedure my_function. > > What I want is the trigger execute my_function only when column1 is > insert or > update, but not going to execute my_function when column2 is insert or > update. The trigger is executed unconditionally, so put the condition inside my_function. If it's an INSERT, column1 must be new, so: IF TG_OP = ''INSERT'' OR (TG_OP = ''UPDATE'' AND (NEW.column1 != OLD.column1 OR (NEW.column1 IS NULL AND OLD.column1 IS NOT NULL) OR (NEW.column1 IS NOT NULL AND OLD.column1 IS NULL) ) ) THEN ... END IF; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For yourselves know perfectly that the day of the Lord so cometh as a thief in the night. For when they shall say, Peace and safety; then sudden destruction cometh upon them, as travail upon a woman with child; and they shall not escape." I Thessalonians 5:2,3 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Separating data sets in a table
On Mon, 2002-08-26 at 07:26, Andreas Tille wrote: > Well for sure this might be an option but as I said I receive the data > in the dump format apropriate to use "COPY FROM ". Would > you really like to suggest me to split those data sets into single lines? > Moreover I'm not sure about how to catch the error messages of failed > COPY statements. How about this approach: Create a temporary table (no constraints) CREATE TEMP TABLE temptable AS (SELECT * FROM tablename LIMIT 1); DELETE FROM temptable; Copy all data into the temporary table COPY temptable FROM 'filepath'; Select from the temporary table all items that satisfy the constraints, insert them into the real table and delete them from the temporary table: BEGIN; INSERT INTO tablename (SELECT * FROM temptable WHERE ...); DELETE FROM temptable WHERE ...; COMMIT; All good data should now be in place. The temporary table should now contain only those items that do not satisfy the constraints for the real table. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Seeing then that all these things shall be dissolved, what manner of persons ought ye to be? You ought to live holy and godly lives as you look forward to the day of God and speed its coming." II Peter 3:11,12 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Separating data sets in a table
On Mon, 2002-08-26 at 10:46, Andreas Tille wrote: > On 26 Aug 2002, Oliver Elphick wrote: > > > Select from the temporary table all items that satisfy the > > constraints, insert them into the real table and delete them from > > the temporary table: > > > > BEGIN; > > INSERT INTO tablename (SELECT * FROM temptable WHERE ...); > > DELETE FROM temptable WHERE ...; > > COMMIT; > > > > All good data should now be in place. The temporary table should > > now contain only those items that do not satisfy the constraints for > > the real table. > This was in my first atempt here. > The problem I have is that I need a JOIN to a further table and > I've got errors from the parser which let me guess that joins are not > allowed in INSERT statements ... at least I do not know how to do it > right if it should be possible. A natural join seems to work fine: bray=# insert into junk (select b.* from batch as b, product as p where b.product = p.id and p.eancode is not null); INSERT 0 7552 I don't have any earlier messages from this thread; what was the syntax you were trying to use? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Seeing then that all these things shall be dissolved, what manner of persons ought ye to be? You ought to live holy and godly lives as you look forward to the day of God and speed its coming." II Peter 3:11,12 ---(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] convert sum (interval) to seconds
On Tue, 2002-09-03 at 11:45, Doppelganger wrote:
> How can I convert sum(interval) to seconds?
...
> If I say,
> select sum(interval) from tablename where login='john';
> it will give me 00:45:28 (That's 45 mins, and 28 secs).
> How can I convert that to seconds? Can I possibly do it
> in just one query? Thank you
Here's one way; I'm not sure if there may not be something more
efficient, though:
junk=# select sum(if) from i;
sum
--
00:45:28
(1 row)
junk=# select extract (minute from sum(if)) * 60 + extract (second
from sum(if)) from i;
?column?
--
2728
(1 row)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"And he said unto his disciples, Therefore I say unto
you, Take no thought for your life, what ye shall eat;
neither for the body, what ye shall put on. For life
is more than meat, and the body is more than clothing.
Consider the ravens, for they neither sow nor reap;
they have neither storehouse nor barn; and yet God
feeds them; how much better you are than the birds!
Consider the lilies, how they grow; they toil
not, they spin not; and yet I say unto you, that
Solomon in all his glory was not arrayed like one of
these. If then God so clothe the grass, which is to
day in the field, and tomorrow is cast into the oven;
how much more will he clothe you, O ye of little
faith? And seek not what ye shall eat, or what ye
shall drink, neither be ye of doubtful mind.
But rather seek ye the kingdom of God; and all these
things shall be added unto you."
Luke 12:22-24; 27-29; 31.
---(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] Update Help
On Tue, 2002-09-03 at 17:38, [EMAIL PROTECTED] wrote: > Let say I have 2 Product table, both of them has columns ProductID and Price > What is the update command if I want to update all Prices of first table to be equal with Price in second table? UPDATE table1 SET price = (SELECT price FROM table2 WHERE table1.product = table2.product); -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you." Luke 12:22-24; 27-29; 31. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to cast this ?
On Thu, 2002-09-12 at 10:22, [EMAIL PROTECTED] wrote: > Hello there > > I have a db table with two char fields : > > log_date char(10) > log_time char(8) > > I added a new field log_timestamp of type timestamptz > > How do I take the data from log_date and log_time and put it into > log_timestamp ? > > update log set log_timestamp = cast (log_date as timestamp) || cast > (log_time as timestamp) > > is not working. I don't know what format you have your dates and times in, but maybe this will work: CAST (log_date || ' ' || log_time) AS TIMESTAMP -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let the wicked forsake his way, and the unrighteous man his thoughts; and let him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon." Isaiah 55:7 ---(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] Help with SQL
On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote: > I've got 2 SQL statements I would like to combine into one. ... > statement 1: SELECT uid, count(uid) FROM triangulated WHERE uid != 'anonymus' AND >uid > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; ... > statement 2: SELECT uid, count(uid) FROM points WHERE uid != 'anonymus' AND uid != > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; ... > what I want to do is have one statement that returns something like this: > uid|count1| count2 > eblevins117923595 > DaClyde39811031 > Drew30104 > zombiechick3159 > > So everything is ordered like statement 1 but includes the count(uid) from the >points DB like statement 2 returns SELECT * FROM (SELECT uid, count(uid) AS count1 FROM triangulated WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c1 LEFT JOIN (SELECT uid, count(uid) AS count2 FROM points WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c2 ORDER BY count1 DESC LIMIT 10; (Apologies for syntax errors, if any - I haven't tried it out,) I used LEFT JOIN because you are ordering by count1, so you probably won't want any rows where count1 is null. If the total of rows from subselect c1 was likely to be less than 10, you might want to do a FULL JOIN and order by count1, count2. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But be ye doers of the word, and not hearers only, deceiving your own selves." James 1:22 ---(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] Problems invoking psql. Help please.
On Sun, 2002-11-17 at 20:24, [EMAIL PROTECTED] wrote: > > You seem to have a very bizarre setup there --- there is no such thing > > as "readpgenv" in the standard Postgres distribution, and > > /usr/lib/postgresql/bin/ isn't the standard place to put the executable > > files either. Perhaps the above is normal for the Debian package of > > Postgres, but I'm afraid you'll have to ask the Debian packager for > > help. Nobody using other platforms is likely to be able to help... > I have Debian and Postgres installed from .deb package. Postgres is > installed in /usr/lib/postgresql by default and it contains readpgenv. > Psql stops working as described, when I remove executable attribute > from readpgenv. readpgenv is a bash script and has only 3 lines: > #!/bin/bash > . /etc/postgresql/postgresql.env > env > > postgresql.env file is an export of PGDATA/PGLIB/PGACCES_HOME variables Yes. The reaon for its existence is that Debian policy prohibits reliance on environmental variables, so I jump through this hoop to read them from a predictable location (if they are not already set). Permissions on /usr/lib/postgresql/bin/readpgenv should be 755. How did they get unset? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "A Song for the sabbath day. It is a good thing to give thanks unto the LORD, and to sing praises unto thy name, O most High." Psalms 92:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 06:30, Hugh Esco wrote: > I did this tonight > > dpkg --purge postgresql > apt-get install postgresql > > and am now still getting the following: > > >biko:/usr/bin# psql -U postgres > >No database specified > >biko:/usr/bin# psql -U postgres template1 > >Could not execv /usr/lib/postgresql/bin/psql There is something wrong with permissions here. You ought to be able, as _any_ user, to run /usr/lib/postgresql/bin/psql Can you? It seems clear that pg_wrapper can't. If not, why not? I assume the file must exist, since you have just reinstalled the package. Is the file itself executable by all users? (Use "ls -l" to check this.) Has someone made an intermediate directory unsearchable? Every directory in its path should have search (i.e. execute) permission for all users. Check /usr, /usr/lib, /usr/lib/postgresql, and so on. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(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] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 13:52, Hugh Esco wrote: > However, when I again attempt to invoke the psql client, I get this: > >biko:/usr/bin$ ./psql -U postgres template1 > >Could not execv /usr/lib/postgresql/bin/psql Pay attention to the exact message and do not flounder around aimlessly. There is no reason to be messing about with pg_hba.conf. You have some kind of system problem here. execv() is a system call to run another executable in place of the current process. If the other executable is not present, or does not have permissions, you will not be able to run it. Find out why. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 14:23, Hugh Esco wrote: > Everything in the path is executable for others. > That is true for: > /usr/lib/postgresql/bin > and for: > /usr/bin > where psql is located. So can you run the executable directly? /usr/lib/postgresql/bin/psql -d template1 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(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] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 15:03, Tom Lane wrote: > Oliver Elphick <[EMAIL PROTECTED]> writes: > > execv() is a system call to run another executable in place of the > > current process. If the other executable is not present, or does not > > have permissions, you will not be able to run it. Find out why. > > Aside from access problems for the executable itself, it could be that > there's a shared-library access problem. Perhaps ldconfig needs to be > told where libpq.so is? It's not the error message you would get for that: olly@linda$ sudo mv /usr/lib/libpq.so.2 /usr/lib/libpq.so.2.bak Password: olly@linda$ psql -d bray /usr/lib/postgresql/bin/psql: error while loading shared libraries: libpq.so.2: cannot open shared object file: No such file or directory -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with a lookup table! Please help.
On Mon, 2002-12-09 at 20:58, Chris Jewell wrote: > ... > However, I now want to have a new table which converts numbers into > words. The problem is this, if I join the main table with the > "translation" lookup table, the column names for each of the four > categories in the main default to the column name in the lookup table > and hence are all the same. What SQL expression should I use to > translate the cryptic numbers into plain english whilst preserving the > column headings in the main table? You just need to name the columns: SELECT t.antibiotic, e1.plain_english AS "Activity against grampos", e2.plain_english AS "Activity against gramneg", e3.plain_english AS "Activity against aerobes", e4.plain_english AS "Activity against anaerobes" FROM tblantibiotics AS t, efficacy AS e1, efficacy AS e2, efficacy AS e3, efficacy AS e4 WHERE t.activity_against_grampos = e1.efficacy_code AND t.activity_against_gramneg = e2.efficacy_code AND t.activity_against_aerobes = e3.efficacy_code AND t.activity_against_anaerobes = e4.efficacy_code; Note that you must use double quotes to quote identifiers, not single quotes as you did in your table creation definitions, which won't work in PostgreSQL. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "I beseech you therefore, brethren, by the mercies of God, that ye present your bodies a living sacrifice, holy, acceptable unto God, which is your reasonable service." Romans 12:1 ---(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] is numeric relational operator problem fixed in v7.3
On Wed, 2002-12-11 at 02:54, jack wrote: > Does the following now works in postgreSQL v7.3? > > Select * > from a_table > where num1 >10; > > *** type of num1 is NUMERIC (12,2) It works -- Oliver Elphick <[EMAIL PROTECTED]> LFIX Limited ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Password user postgres
On Mon, 2002-12-23 at 14:22, Arnaudo Massimo wrote: > Hi everibody, > i have to see the table pg_shadow, but i don't remind the default password > for user postgres in a Debian system. The Debian packages do not set any password. Their default access is "ident sameuser", meaning that you can connect using your Unix login name, provided that user has also been created as a PostgreSQL user. The PostgreSQL superuser is 'postgres'. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And there were in the same country shepherds abiding in the field, keeping watch over their flock by night. And, lo, the angel of the Lord came upon them, and the glory of the Lord shone around them; and they were sore afraid. And the angel said unto them, " Fear not; for behold I bring you good tidings of great joy which shall be to all people. For unto you is born this day in the city of David a Saviour, which is Christ the Lord."Luke 2:8-11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Function for adding Money type
On Sun, 2003-01-26 at 13:53, D'Arcy J.M. Cain wrote: > This year, my team is planning on improving the MONEY type. Of course, we can > always make it a user defined type if PostgreSQL doesn't want it. We will at > least put it into contrib. However, if people think that it is useful and > want to leave it in the main tree that's good too. What we want to do is a) > switch to a 64 bit integer from a 32 bit integer in order to hold amounts of > any reasonabe size and b) allow it to be cast to and from more types. > Perhaps we can also add the ability to specify the number of decimal places > on output but I am not sure if that would affect the primary benefit of using > it, speed. A money type needs to specify what currency is held. The current one changes the currency with the locale, which makes nonsense of existing data. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Wash me thoroughly from mine iniquity, and cleanse me from my sin. For I acknowledge my transgressions; and my sin is ever before me. Against thee, thee only, have I sinned, and done this evil in thy sight..." Psalms 51:2-4 ---(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] COPY use in function with variable file name
On Wed, 2003-01-29 at 08:05, Sondaar Roelof wrote:
> Hello,
>
> I can't figure out how to make this work, or is not possible?
>
> In a function i would like to read a file.
> The file name is determined by a value from a table.
> However the COPY statement does not to accept this?
> I tried various forms of adding (single)-quotes but no luck.
>
> Anyone any ideas?
>
> Function:
> CREATE FUNCTION dnsdhcp_dns_raw()
> /* Fill table dns_raw with dns data */
> RETURNS integer AS '
> DECLARE
> r RECORD;
> ntw TEXT;
> BEGIN
> /* Do for all domain names */
> FOR r IN SELECT domain FROM network
> WHERE position(''n'' IN use) > 0 and ipaddress != ''127.0.0.0/24''
> LOOP
> ntw := ''/tmp/db.'' || r.domain;
> DELETE FROM dns_raw; /* Clear table */
> RAISE NOTICE ''Network: %'', ntw;
> COPY dns_raw FROM ntw DELIMITERS ''~''; /* Get the data */
Since ntw has variable content, you need to do an EXECUTE with the
command in a text string:
EXECUTE ''COPY dns_raw FROM '' || ntw || '' DELIMITERS ''''='''''';
(I hope that is the right number of quotes!)
> END LOOP;
> RETURN 0;
> END;'
> LANGUAGE 'plpgsql';
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Love not the world, neither the things that are in the
world. If any man love the world, the love of the
Father is not in him...And the world passeth away, and
the lust thereof; but he that doeth the will of God
abideth for ever." I John 2:15,17
---(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]
On Sun, 2003-02-02 at 21:47, mail.luckydigital.com wrote: > Can some one please confirm( with a plpgsql function example please ) > a postgres "7.2" function that can return multiple rows to the client. > > I've gone through the docs and can't find anything to support this -it > seems you can only have one return value or null. > > Yes i'm aware this it is possible in 7.3 - can someone please confirm > its not possible in 7.2 or provide me with an example of how to go > about it. Not possible in 7.2 PL/pgSQL -- Oliver Elphick <[EMAIL PROTECTED]> LFIX Limited ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Column Types
On Sun, 2003-09-14 at 07:49, Muhyiddin A.M Hayat wrote:
> If in MySQL i'm using type EMUN what type in Postgres?
Use a CHECK constraint:
CREATE TABLE xxx (
...
colourTEXT CHECK (colour IN ('red', 'green', 'blue')),
...
);
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"But without faith it is impossible to please him; for
he that cometh to God must believe that he is, and
that he is a rewarder of them that diligently seek
him."Hebrews 11:6
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] psql output and password Qs
On Fri, 2003-09-19 at 19:47, ow wrote: > Hi, > > 1) When psql is run from a script, how would one save *all* output generated by > psql (including errors, info messages, etc) in to a file?. I tried redirecting > output with ">" but that did not save error/confirmation messages. Standard output and standard error are different streams. The normal way to do what you want (Bourne shell syntax) is: psql ... >outfile 2>&1 > 2) When psql is run from a script, how would one pass a password to psql? Create a .pgpass file to read it from (see the manual for details). -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Bring ye all the tithes into the storehouse, that there may be meat in mine house, and prove me now herewith, saith the LORD of hosts, if I will not open you the windows of heaven, and pour you out a blessing, that there shall not be room enough to receive it." Malachi 3:10 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] auto_increment
On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote: > How to Create auto_increment field in PostreSQL. > Can I create them using Trigger. Use the SERIAL datatype. See also the functions nextval(), currval() and setval(). -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But my God shall supply all your need according to his riches in glory by Christ Jesus." Philippians 4:19 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sequenties in pgSQL 7.3.x
On Thu, 2003-09-25 at 07:36, Przemysław Słupkowski wrote: > Hi > I am using postgresql 7.3.x. I am upgrating a database PostgreSQL. > The main problem is how to change field last value in sequencde which > exists in database. > I created script to read this field and then I'm drop the table, do > modification and i'm must change this value to another value. > But DBMS returns me message like that > ERROR: You can't change sequence relation seq_id_seq > How to do this Use the setval() function? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Who shall separate us from the love of Christ? shall tribulation, or distress, or persecution, or famine, or nakedness, or peril, or sword?...But in all these things we overwhelmingly conquer through Him who loved us." Romans 8:35,37 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SUM() & GROUP BY
On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
>
> hotel=# SELECT
> hotel-# "public".billing.id,
> hotel-# "public".billing.guest_id,
> hotel-# "public".billing.trx_date,
> hotel-# "public".billing.trx_time,
> hotel-# "public".billing.payment_method,
> hotel-# "public".billing.tax,
> hotel-# "public".billing.dep_id,
> hotel-# "public".department."name",
> hotel-# SUM("public".items.price) AS total,
> hotel-# "public".billing.amount_paid
> hotel-# FROM
> hotel-# "public".billing_items
> hotel-# INNER JOIN "public".billing ON
> ("public".billing_items.billing_id = "public".billing.id)
> hotel-# INNER JOIN "public".department ON ("public".billing.dep_id =
> "public".department.id)
> hotel-# INNER JOIN "public".items ON
> ("public".billing_items.items_id = "public".items.id)
> hotel-# GROUP BY "public".billing.id;
> ERROR: Attribute billing.guest_id must be GROUPed or used in an
> aggregate function
> hotel=#
>
> What Worng ??
Any items in the select list need to be aggregated (e.g.
SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose
there are several billing.guest_id values for each billing.id; which
value should be listed in the output?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Blessed is the man that endureth temptation; for when
he is tried, he shall receive the crown of life, which
the Lord hath promised to them that love him."
James 1:12
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] SYSDATE in PostgreSQL !?
On Mon, 2004-03-01 at 21:18, Louie Kwan wrote: > How can I define a table with columns with sysdate as the default value.. > > If there is no SYSDATE defined in PostgreSQL , what can I do ? > CREATE TABLE channels( ... > updateTimeStamp DATE default (SYSDATE), > createTimeStamp DATE default (SYSDATE) > ); updateTimeStamp DATE DEFAULT CURRENT_DATE or possibly updateTimeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP which will give you time as well as date. Perhaps you should also add NOT NULL. CURRENT_* doesn't change within a transaction. If you need to record real time, use timeofday(). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to avoid (stop) a endless loop in a trigger
On Thu, 2004-03-04 at 14:54, Rodrigo Sakai wrote: > Hi people, i have a problem here. > I'm doing a trigger that when a update occurs i need to do an update on the same > table (target table), but as known, it causes a endless loop whithin infinit > updates. So I need to stop the trigger after it does the first update, is there > any way? > I tried to do a return null, but that was a very bad idea because it stops > completly the function fired by a trigger and all its computation is in vain... > > The test trigger that i did is like : > > CREATE OR REPLACE FUNCTION public.sp_teste_loop() RETURNS trigger AS ' > begin > raise notice \'Trigger Fired\'; > if (TG_OP = \'INSERT\') then > update teste_trigger > set flg_bool = \'S\' > where codigo=NEW.codigo; > > RETURN NEW; > > elsif (TG_OP = \'UPDATE\') then > update teste_trigger > set flg_bool = \'N\' > where codigo=NEW.codigo; > > RETURN NULL; > end if; > end; > ' LANGUAGE 'plpgsql' VOLATILE; Does this update other records, or only the one you are inserting or updating? If the former, add " AND flg_bool IS NULL OR flg_bool != \'S\'" to the update condition (!=\'N\' for the update case); then records that are already OK will not be touched, so the recursion will stop automatically. If the latter, just change NEW.flg_bool and return NEW > CREATE TRIGGER tr_sp_teste_trigger > BEFORE INSERT OR UPDATE > ON public.teste_trigger > FOR EACH ROW > EXECUTE PROCEDURE public.sp_teste_loop(); > > Thank for any help and regards > > > = > Rodrigo Sakai > Database Programmer > [EMAIL PROTECTED] > http://www.2bfree.com.br > Tel: (55) (11) 5083-5577 > Fax: (55) (11) 5549-3598 > = > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inherited tables and new fields
On Tue, 2004-07-20 at 15:36, Jeff Boes wrote: ... > Of course, had we used table inheritance, we'd do something like ... > >select * from draft_template ... > > but it wouldn't do exactly what we are doing now: that is, > fn_all_drafts() returns not only the contents of every row in the tables > draft_X, but also an extra column indicating which table that row > came from. You can do that with an inheritance hierarchy like this: select tableoid::regclass as tablename, * from my_table; >create table all_drafts (editor_id integer) inherits draft_template; > > What frustrates me from time to time is that if "draft_template" is > altered to add a new column, then the function breaks because the new > column appears in "all_drafts" as *following* editor_id. The column > order messes up the code in the function, because it's expecting > all_drafts to look like draft_template, with editor_id added at the end. > > Is this a mis-feature? New columns get added at the end of each table; that is standard. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Greater love hath no man than this, that a man lay down his life for his friends." John 15:13 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] next integer in serial key
On Thu, 2004-07-22 at 03:45, Kenneth Gonsalves wrote: > hi, > how does one get the next number in a serial type row? When inserting a new row, do not mention the serial column in the list of columns, or else give it the value DEFAULT. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Then Peter and the other apostles answered and said, We ought to obey God rather than men." Acts 5:29 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] next integer in serial key
On Thu, 2004-07-22 at 12:48, [EMAIL PROTECTED] wrote: > Actually it does work, call nextval to get your next value, then call > your INSERT statement, > explicitly giving said value for the serial column. Then you can > proceed with using said value in > the INSERT statement of the related inserts with foreign keys to it. > > Alternatively, you can do: > INSERT (accepting the default) > then SELECT currval(the_sequence_object); > then > > NOTE: 2nd method assumes that nobody else called nextval() on the > sequence between when you did the > insert and when you did the select currval(). Note that being inside > a transaction is NOT > sufficient, you need an explicit lock on the sequence. I do not > recommend the 2nd method, too much > can go wrong. This last paragraph is wrong and irrelevant. It is a point which for some reason is continually being misunderstood. currval() *always* returns the last value generated for the sequence in the *current session*. It is specifically designed to do what you are suggesting without any conflict with other sessions. There is *never* any risk of getting a value that nextval() returned to some other user's session. The downside is that it operates outside the transaction and therefore cannot be rolled back. It is also necessary to run nextval() in the session (either explicitly or by letting a serial column take its default) before you can use currval() on the sequence. Oliver Elphick ---(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] Grouping by week
On Fri, 2004-08-06 at 22:29, Caleb Simonyi-Gindele wrote: > I'm using > > SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY > trans_date > > and it is being used to group sales results by week. It works really well. > > What I'm wondering is if I can shift the week from a Mon-Sun > articulation(default with Postgre) to a Sun-Sat sequence. I need it that way > in order to comply with a legacy stats system. How about: SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Be still before the LORD and wait patiently for him; do not fret when men succeed in their ways, when they carry out their wicked schemes." Psalms 37:7 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] surrogate key or not?
On Sat, 2004-08-07 at 05:00, Kenneth Gonsalves wrote: > why shouldnt the primary key change? the only key that should never change is > a key that is used as a foreign key in another table. In a table like this: > > id serial unique > name varchar primary key > > name may change - id will never change. id is used as the foreign key It can change if you use ON UPDATE CASCADE in the foreign key definitions. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Be still before the LORD and wait patiently for him; do not fret when men succeed in their ways, when they carry out their wicked schemes." Psalms 37:7 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sending errors from psql to error file
On Thu, 2004-08-12 at 15:20, Devin Whalen wrote: > Hello, > > I am trying to migrate a client from one database to another. Basically > we designed a web application for them using Postgresql but we have made > many changes to the design of our application since version 1. Now they > want to upgrade. So basically I have to pg_dump their current data and > then import it into our new schema. Now, of course I realize that there > are going to be errors. But they have a lot and I mean a lot of data. > I don't want to have to sit there and watch the import go by, I want to > run a command and then look in a file for any errors after the import is > complete. I tried this command but it didn't work: > gunzip -c cli_postDataInserts.sql.gz | psql cli_post -U system | grep > "ERROR:*" > import_errors > > Any help is appreciated. 1. "didn't work" is not much help 2. Use the --echo-queries and -f options to psql and capture all the output; a bare error line won't tell you much about what happened nor where it happened - you need to see what query was running. The -f will let psql report which line in the input. zcat cli_postDataInserts.sql.gz | psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 Oliver Elphick ---(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] Sending errors from psql to error file
On Thu, 2004-08-12 at 16:01, Devin Whalen wrote: > > zcat cli_postDataInserts.sql.gz | > >psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1 > > Just a few questions about your command. I tried it with one sql > statement that I know doesn't work and the error went into the right > file. However, I would like to know WHY it works...hope you don't mind > shedding some light on it :). Specifically, the - >trace.file 2>&1 > part. I know...well actually think, that the > is redirecting the ouput to the > trace.file. But what the hell is 2>&1 doing?? > Also, the - ...it kinda just looks like a stray dash to mealthough I know it > must be doing something ;). > Will this put all output? Or just the errors. ">" redirects standard output "2>" redirects standard error Your command did not redirect standard error, so the errors didn't get to grep The syntax for -f is "-f filename"; the filename "-" means standard input. (Check the man page for psql!) Oliver ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Sending errors from psql to error file
On Thu, 2004-08-12 at 16:09, Oliver Elphick wrote: > "2>" redirects standard error I forgot to say "&1" means the file open on file descriptor 1, which is always standard output. So "2>&1" means send standard error to standard output, so that a pipe (which just takes standard output) can see the errors as well. All that you can find in the man page for bash or sh or whatever your shell is. (If you used csh or tcsh, I think the syntax would be different.) Oliver ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] multi column foreign key for implicitly unique columns
On Tue, 2004-08-17 at 10:25, Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. > Example: > > CREATE TABLE p ( > name TEXT PRIMARY KEY, > "type" TEXT > ); > > CREATE TABLE f ( > name TEXT, > "type" TEXT, > FOREIGN KEY(name, "type") REFERENCES p(name, "type") > ); > ERROR: there is no unique constraint matching given keys for referenced table "p" What's the point of this? p.name is the primary key and is therefore unique in p, so your foreign key should simply reference p.name. Having f.type as a repetition of p.type violates normalisation principles, since name is completely derivable by a join of f to p on name. > Is this on purpose? I think the foreign key should be allowed. Creating > an extra unique key only has a negative impact on performance, right? If there is no unique key, how does the foreign key trigger find the referenced row except by doing a sequential scan? Bad news! And when one of the duplicate referenced rows changes, what should happen with ON UPDATE or ON DELETE? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "If ye abide in me, and my words abide in you, ye shall ask what ye will, and it shall be done unto you." John 15:7 ---(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] multi column foreign key for implicitly unique columns
On Thu, 2004-08-19 at 17:21, Josh Berkus wrote: > Jan, > > > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > > value and he even wants to ensure this with the constraint. > > And in the absence of that constraint, what ensures that b.y = a.y, exactly? In the absence of b.y, it would be impossible for it to be anything else. Isn't that the point? It seems to me that he was trying to use the database to show errors in his source data, but since his constraint would reject the data, he wouldn't be able to enter it; all he could do would be to see the error. So he might as well turn it round, normalise the data properly and use the database to tell the rest of the system what the data ought to be. Oliver Elphick ---(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] Possible rounding error of large float values?
On Tue, 2004-08-24 at 20:52, Josh Berkus wrote: > Adam, > > > 9223372036854775807 > > > > It gets selected out as: > > > > 9.22337203685478E18 > > This is a property of FLOAT data types. They round. > > > Which appears to be rounded. When we cast it to numeric type we get: > > > > 922337203685478 > > > > Which also is rounded. It is still possible to find the row using the > > original value > > Hmmm ... is 15 digits the limit of NUMERIC? It may be. It must be the limit of float. Numeric can't produce any more than it was given and it was cast from float. bray=# select 653596708775675750507850507570708696432 ::numeric; numeric --------- 653596708775675750507850507570708696432 (1 row) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "I saw in the night visions, and, behold, one like the Son of man came with the clouds of heaven, and came to the Ancient of days, and they brought him near before him. And there was given him dominion, and glory, and a kingdom, that all people, nations, and languages, should serve him; his dominion is an everlasting dominion, which shall not pass away, and his kingdom that which shall not be destroyed." Daniel 7:13,14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Full access to a DB with a second user?
On Sun, 2004-09-05 at 13:53, Erik Wasser wrote:
> Hello [EMAIL PROTECTED],
>
> how can I gave a user full access (SELECT, INSERT,...) to a database
> that he doesn't own? I used google to find a solution and I find a
> Statement[1] that will the do the trick. But it looks very cryptical to
> me. B-) What does this statement do?
>
> > \a
This is a psql directive to urn off output alignment.
> > \t
Don't show column headers or the row count
> > \o /tmp/grant.sql
Redirect output to the named file
> > SELECT 'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
> > ' TO joe;'
> > FROM pg_catalog.pg_class AS c
> > LEFT JOIN pg_catalog.pg_namespace AS n
> > ON n.oid = c.relnamespace
> > WHERE c.relkind IN ('r','v','S') AND
> > n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> > pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY n.nspname, c.relname;
Read the system catalog for a list of relations and construct a GRANT
command for each one to give ALL access to user joe. The output looks
like this:
GRANT ALL ON prod.address TO joe;
GRANT ALL ON prod.address_id_seq TO joe;
GRANT ALL ON prod.address_telephone TO joe;
GRANT ALL ON prod.area TO joe;
...
and is written into the file /tmp/grant.sql as directd earlier.
> > \o
Stop sending output to the file.
> > \i /tmp/grant.sql
Run the output file as a script, thus granting the permissions to joe.
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
"Behold, I stand at the door, and knock; if any man
hear my voice, and open the door, I will come in to
him, and will sup with him, and he with me."
Revelation 3:20
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
On Tue, 2004-09-07 at 06:44, Theo Galanakis wrote:
> How could you determine if a value being inserted into a varchar
> column is numeric?
>
> I was thinking of using a Regular expression to find this, something
> like
>
> .. Where content ~* '^[0-9]{1,10}'
~ '^[0-9]+$'
Your version only checks the beginning of the string.
> There must be an easier way like a isNumeric() function?
Not that I know of.
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
"For whosoever shall call upon the name of the Lord
shall be saved." Romans 10:13
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
On Wed, 2004-09-08 at 17:47, Josh Berkus wrote: > Oliver, Theo: > > > ~ '^[0-9]+$' > > Actually, I usually do: > > ~ '^[0-9]+\.?[0-9]*$' > > ... to include decimals. However, the above assumes that there is at least a > "0" before the decimal; it would be nice to adapt it to matching a leading > decimal (i.e. .057 ) as well. Can't see any easy way, though ... ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$' -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Put on the whole armor of God, that ye may be able to stand against the wiles of the devil." Ephesians 6:11 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Isnumeric function?
On Wed, 2004-09-08 at 18:48, Josh Berkus wrote: > Theo, Oliver, > > > Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ? > > Yes, because it also matches "." , which is not a valid numeric value. > > > ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$' > > Ah, the brute force approach ;-) Nothing like using a nice big hammer! > Actually, the above could be written: > > ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$' But that doesn't allow a trailing decimal point. > > ... though that still seems inelegant to me. Is there a regex expert in the > house? All the elegant approaches I can think of match the empty string. There must be at least one digit and 0 or 1 decimal point with no other characters permitted. If you use this as a constraint, you could make it elegant and combine it with another constraint to exclude '' and '.'. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Put on the whole armor of God, that ye may be able to stand against the wiles of the devil." Ephesians 6:11 ---(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] problems using phpPgAmin
On Mon, 2004-10-18 at 17:28, beyaNet wrote: > Hi, > to those of you that may be using the above named admin tool, any ideas > why I am unable to login with the postgres username even though I have > amended the pg_hb file? How have you amended pg_hba.conf? did you send SIGHUP to the postmaster afterwards to get it to reload the settings? > Are there any other admin tools out there that > i could use on a a unix box? pgadmin3 -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Delight thyself also in the LORD; and he shall give thee the desires of thine heart." Psalms 37:4 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to re-sort a sorted query?
On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: > I have a query that need to be sorted in order of price of store's > product with unique store number. > > Here is a sample data of storeproduct table: > > > ItemSku , StoreNumber , Price > == > 10001 , 7 , 30.00 > 10001 , 7 , 35.00 <-- duplicate store number > 10001 , 5 , 45.00 > 10001 , 2 , 50.00 > > Then I do this query to get unique store number and also the cheapest > price from each store: > > SQL= "Select distinct on (storenumber), itemsku, storenumber,price > from storeproduct where itemsku='10001' > order by storenumber, price" That won't get you the cheapest price, just an arbitrary one determined by the physical storage order. You need to use GROUP BY with an aggregate function: SELECT itemsku, storenumber, MIN(price) FROM storeproduct WHERE itemsku = '10001' GROUP BY itemsku, storenumber ORDER BY price, storenumber; > Result #1: > ItemSku , StoreNumber , Price > 10001 , 2 , 50.00 > 10001 , 5 , 45.00 > 10001 , 7 , 30.00 > > The question is how to make the query that returns as above but sorted > by price? The literal answer to your question is to put price first in the ORDER BY clause, but I'm not convinced you actually want to know something that simple. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to re-sort a sorted query?
On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote: > Oliver Elphick <[EMAIL PROTECTED]> writes: > > On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: > >> Then I do this query to get unique store number and also the cheapest > >> price from each store: > >> > >> SQL= "Select distinct on (storenumber), itemsku, storenumber,price > >> from storeproduct where itemsku='10001' > >> order by storenumber, price" > > > That won't get you the cheapest price, > > Sure it will. It's a perfectly good application of DISTINCT ON. > However, he has to use that particular ORDER BY to get the answers > he wants. Ah - because ORDER BY is applied before DISTINCT ON; I hadn't realised that. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Group by and aggregates
On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote: ... > some_id partno status cmupqty > 1 test1 stock 10.00 15 > 2 test2 incoming12.00 10 > 3 test1 incoming15.00 60 > 4 test1 incoming14.00 11 ... > My result will look something like this: > > partnostatus cmupqty > test1 stock 10.00 15 > test1 incoming15.00 71 > test2 incoming12.00 10 > > Now, I need the first line to say "15.00" in the cmup field. That is, > stock and incoming are obviously not being grouped, but since it's the > same partno I'd like somehow to show the highest cmup. Is there some > black SQL voodoo that'll achieve this ? junk=# select partno, status, (select max(cmup) from my_table as b where b.partno = a.partno) as cmup, sum(qty) from my_table as a group by partno, status, (select max(cmup) from my_table as b where b.partno = a.partno); partno | status | cmup | sum +--+---+- test1 | incoming | 15.00 | 71 test1 | stock | 15.00 | 15 test2 | incoming | 12.00 | 10 (3 rows) Oliver Elphick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query information needed
On Thu, 2005-10-13 at 05:50 -0700, [EMAIL PROTECTED] wrote:
> Dear all,
>
> I have a table created with this specifications:
>
> CREATE TABLE cdr (
> calldate timestamp with time zone NOT NULL default now(),
> clid varchar(80) NOT NULL default '',
> src varchar(80) NOT NULL default '',
> dst varchar(80) NOT NULL default '',
> dcontext varchar(80) NOT NULL default '',
> channel varchar(80) NOT NULL default '',
> dstchannel varchar(80) NOT NULL default '',
> lastapp varchar(80) NOT NULL default '',
> lastdata varchar(80) NOT NULL default '',
> duration bigint NOT NULL default '0',
> billsec bigint NOT NULL default '0',
> disposition varchar(45) NOT NULL default '',
> amaflags bigint NOT NULL default '0',
> accountcode varchar(20) NOT NULL default '',
> uniqueid varchar(32) NOT NULL default '',
> userfield varchar(255) NOT NULL default ''
> );
>
> I want to extract the number of calls placed in 1 hour and the average
> call duration
>
> I'm working with this query:
>
> SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
> ORDER BY calldate;
>
> i tried several other queries but i'm not able to count the number of
> calls in an hour (better in a time interval) and calculate the average
> duration.
For any particular interval:
SELECT COUNT(*), AVG(duration)
FROM cdr
WHERE src='601' AND
calldate BETWEEN CAST ('2005-10-17 10:00:00' AS TIMESTAMP WITH
TIMEZONE) AND
CAST ('2005-10-17 10:59:59' AS TIMESTAMP WITH
TIMEZONE);
--
Oliver Elphick [email protected]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Problem while using start transaction ans commit;
On Mon, 2005-10-17 at 12:53 +0530, Sri wrote: > Hi All, > > I have a small problem in using nested transactions while working on > Postgres 8.0. > > Ex: I have a function A() which in turn calls functions b() and c() , > if i want commit something in b or c. i have to use You cannot start or commit a transaction inside a function. You can use savepoints. -- Oliver Elphick [email protected] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] can not connect to pg on linux FC4
On Fri, 2005-11-04 at 21:12 -0800, Tai Huynh Phuoc wrote: > Hi, > I use pg 8.03 (come with FC4). I can not connect to pg > because of Ident authentication failed for user "root" > error. pg_hba.conf > host all all 127.0.0.1/32 ident sameuser > > Then I try another way by add adding a row to pg_indet > so that I can use root account to connection to db. > mymap root root > and chage "sameuser" to "mymap" in the pg_hba.conf. > But I can not connect too as the above error. > > Thank in advande someone help me. If you are going to use ident authentication on a TCP/IP connection (even localhost) you need an ident server running on the client machine. That is what supplies the authentication. You need to trust the administrator of the client machine, otherwise this can be very insecure. Since you are connecting to the local machine, you could just as easily use a Unix socket connection (do not supply any hostname) in which case ident authentication will use the ownership credentials associated with the socket. In pg_hba.conf, this is a "local" connection and is secure on Linux. > Onother question. Is there a way that I can create a > user using password authetication at the situation I > have no pg account like this sitation?. Assign a password for the user with ALTER USER. Then add a suitable line to pg_hba.conf. Oliver Elphick ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pg_dump
On Fri, 2005-11-11 at 17:04 -0600, Judith Altamirano Figueroa wrote:
> Hi
>
> how could I restore a db if I did next:
>
> pg_dump -Z 9 soi > $DESTINO/soi.bkp
Since you don't specify any dump format, your dump should be plain text
and is compressed with gzip, so the restore command is going to be:
zcat $DESTINO/soi.bkp | psql -d restore_database_name -f -
You have to create the database to restore into first.
--
[Note for documentation:
According to the psql man page:
-Z 0..9
--compress=0..9
Specify the compression level to use in archive formats that
support compression. (Currently only the custom archive format
supports compression.)
However using psql -Z with no format or with "-F p" in fact produces a
gzipped file.]
This might be a suitable amendment to the docs?:
$ diff -u /tmp/pg_dump.sgml.orig /tmp/pg_dump.sgml
--- /tmp/pg_dump.sgml.orig 2005-11-15 11:50:55.0 +
+++ /tmp/pg_dump.sgml 2005-11-15 11:57:38.0 +
@@ -492,8 +492,9 @@
Specify the compression level to use in archive formats that
-support compression. (Currently only the custom archive
-format supports compression.)
+support compression. (Currently the tar archive
+format does not support compression. The plain text format is
+compressed with gzip.)
--
Oliver Elphick [email protected]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL]
On Wed, 2005-11-23 at 23:23 -0500, Ken Winter wrote: > In PL/pgSQL, is there a way to put a *variable* column-name in a dot > notation reference to a RECORD column? > > For example, suppose I want to write a function like the following, which is > to be called by a "BEFORE INSERT" trigger: > > CREATE OR REPLACE FUNCTION foo ( ) RETURNS TRIGGER AS > ' > DECLARE > var VARCHAR; > BEGIN > var := TG_ARGV[0] > NEW. := ''whatever''; > RETURN NEW; > END; > ' > LANGUAGE 'plpgsql' > ; > > The aim of this uninteresting function is to assign the value 'whatever' to > the table column that is passed in by the calling trigger as TG_ARGV[0], > i.e. the first calling argument. > > What I don't know is what to put into the dot notation in place of ". column whose name is the value of var>" so that the column of NEW that is > addressed by the assignment statement is the one passed in as the first > argument. Is there any PL/pgSQL construct that could be substituted in here > to achieve this result? Unfortunately not. > If not, can anybody suggest a way to write a trigger-called function that > would accomplish the same result? You would have to do something like: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ DECLARE var VARCHAR; BEGIN var := TG_ARGV[0]; IF var = 'column_1' THEN NEW.column_1 = 'whatever'; ELSIF var = 'column_2' THEN NEW.column_2 = 'whatever'; ... END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; Oliver Elphick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DEFAULT Constraint based on table type?
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote: > Lets say I have the following tables. > > CREATE TABLE animals(id primary key, name varchar, type varchar); > CREATE TABLE dogs (breed varchar)INHERITS (animals); > CREATE TABLE birds (bool hasFeathers) INHERITS (animals); > > Is there a way I can specify a default on the child table that will populate > the 'type' column? For example, if I am inserting a row in table DOGS, I > would always want the default value for column TYPE to be 'DOG'. If I am > inserting into BIRDS type 'BIRD'. > > I know that I could add individual triggers on each table that set the TYPE > field to a default value on insert but I wanted a more simple solution like > setting a DEFAULT table-constraint. > > Also, In java, this could be done on a parent object by overriding a > constructor or method, using the Class object or instanceof. Is there > anyway for a table to "know" it's "class" in this scenario? SELECT tableoid::regproc, * from animals; -- Oliver Elphick [email protected] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] DEFAULT Constraint based on table type?
On Tue, 2005-11-29 at 10:31 +, Oliver Elphick wrote: > SELECT tableoid::regproc, * from animals; regproc should be regclass; sorry. -- Oliver Elphick [email protected] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(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
[SQL] Re: [NOVICE] Install two different versions of postgres which should run in parallel
On Fri, 2007-08-10 at 14:33 +0300, Loredana Curugiu wrote: > Hi all, > > I need to have two different vesions of postgres running in parallel > on > different ports. Does anyone knows how to install two different > versions > of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux > operating system. If you run Debian or Ubuntu you can do this with the package system. apt-get install postgresql-8.2 postgresql-7.4 The two versions are automatically installed to run on different ports. Look at the docs and manpages for the postgresql-common package to see how it works. If you can't do that, you can install a different version from source in a different directory tree; then have its postmaster run on a different port. Specify the port number when connecting so as to get to the postmaster you want. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Format interval as hours/minutes etc
On Mon, 2007-09-17 at 02:15 +0930, Shane Ambler wrote: > I get the idea you want the interval to be expressed as 2,765 days and > 23 hours or 66,383 hours, which I think would be useful (more so for > shorter intervals). > > I am thinking the exact function you are after isn't there - from what I > can find a larger interval is always given as x years y months z days... > which is why extracting the epoch is the easiest point to start your calcs. > > Maybe this can be a feature request - functions to give an interval in > total number of days/hours/minutes instead of years months days Doesn't the SQL standard allow one to define intervals as YEAR TO MONTH, DAY TO HOUR, HOUR TO SECOND and so on? This sets both the greatest unit to report and the resolution. (YEAR/MONTH cannot be mixed with other types because of the uncertainty of month lengths.) Is there any plan to support that? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Merging two columns into one
Christopher Sawtell wrote: >On Fri, 23 Jun 2000, Gary MacMinn wrote: >> Hi All, >> >> I have two columns in a table (areacode and phone number) that I'd like to > merge >into one (phone number) containing both sets of info. Could anyone suggest a >simple way of achieving this? > >export the data to a file using the copy command, >remove the delimiter with the unix command 'tr -d' >import the file, now minus the delimeter using the copy command. Surely tr will remove ALL the delimiters, so this is not helpful unless these columns are the only ones in the table. You would have to use awk or perl to process the exported file and delete the correct delimiter. For an SQL solution, how about: SELECT col1, col2,..., areacode || phone as phone, colx, coly,... INTO new_table FROM table; Then you can drop the old table and recreate it with the correct columns and import the data into it from new_table. (You could just rename new_table if it doesn't need to be created with constraints.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Train up a child in the way he should go, and when he is old, he will not depart from it."Proverbs 22:6
Re: [SQL] Orderby two different columns
"Mitch Vincent" wrote: >I need to order search results by the two dates. Here is the problem.. > >They want whichever date is the most recent to appear on top.. If I do >'order by resubmitted desc,created desc' I get something like this : > >Applicant Re-submitted Created >A 06/05/2000 12/31/1999 >B 06/05/2000 12/31/1999 >C 05/17/2000 02/09/2000 >D 05/17/2000 01/21/2000 >E 05/11/2000 01/27/2000 >F 05/11/2000 01/21/2000 >G 05/01/2000 12/31/1999 >H 04/28/2000 01/28/2000 >I 04/28/2000 01/12/2000 >J 05/23//2000 > > >Ok, see applicant J? I need him to be above C.. select * from table order by case when resubmitted > created then resubmitted else created end desc; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Train up a child in the way he should go, and when he is old, he will not depart from it."Proverbs 22:6
[SQL] JOIN syntax
Looking at SQL92, it seems that I ought to be able to do this:
SELECT * FROM invoice INNER JOIN (SELECT * FROM invoice_line WHERE lineno > 1)
ON invoice.invno = invoice_line.invno;
ERROR: parser: parse error at or near "("
(I know it can be successfully expressed in a more complex WHERE clause).
Is there anything wrong with the syntax, or is it a missing feature?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Honour thy father and mother; which is the first
commandment with promise; That it may be well with
thee, and thou mayest live long on the earth."
Ephesians 6:2,3
Re: [SQL] CREATE TABLE with foreign key and primary key
"Paulo Roberto Siqueira" wrote: >ufgvirtual=# create table matricula ( >ufgvirtual(# id_aluno char(15) references pessoa, >ufgvirtual(# id_curso int4 references curso_polo, >ufgvirtual(# id_polo int2 references curso_polo, >ufgvirtual(# local_prova varchar(50) not null, >ufgvirtual(# autorizado bool default 'f' not null, >ufgvirtual(# id_plano_pgto int2 references plano_pgto not null, >ufgvirtual(# data_matricula date default CURRENT_DATE not null, >ufgvirtual(# primary key(id_aluno,id_curso,id_polo)); ... >I have tables pessoa, curso, polo, curso_polo and matricula. Primary key in >curso_polo are id_curso (references curso) and id_polo (references polo). In >table matricula I want as primary key id_pessoa (references pessoa), >id_curso (references curso_polo) and id_polo (references curso_polo). You can't use REFERENCES on a column if the target primary key is made up of more than one column; you have to use a FOREIGN KEY table constraint: create table matricula ( id_aluno char(15) references pessoa, id_curso int4 not null, -- I assume you want id_polo int2 not null, -- not null here local_prova varchar(50) not null, autorizado bool default 'f' not null, id_plano_pgto int2 references plano_pgto not null, data_matricula date default CURRENT_DATE not null, primary key(id_aluno,id_curso,id_polo), FOREIGN KEY (id_curso, id_polo REFERENCES curso_polo (id_curso,id_polo)); -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Delight thyself also in the LORD; and he shall give thee the desires of thine heart." Psalms 37:4
Re: [SQL] Classes, Inheritance, and Children
Thomas Swan wrote: >I think I may have asked this before... If I did I'm sorry, but maybe this >attempt, assuming a prior one, may be a little more clear. > >create table foo (id int8); >create table bar1 (name text) inherits (foo); >create table bar2 (data text) inherits (foo); >create table hybrid ( ) inherits (bar1, bar2); > >INSERT INTO foo VALUES (1); >INSERT INTO bar1 VALUES (2,'myname'); >INSERT INTO bar2 VALUES (3,'mydata'); >INSERT INTO hybrid VALUES (4,'morename','moredata'); > > >I want to do a SELECT * FROM foo*; but I only get the 'id' column as in : > >id >--- > 1 > 2 > 3 > 4 This is correct in object-oriented theory. foo only knows about its own features; it does not know about additional features of its descendants, nor should it. >What would be the query to get the following table or a magical way to >expand children? > >I had originally hoped that SELECT * FROM foo* would yield the following, >but it's not so. > >id | name | data >---++- > 1 | null | null > 2 | 'myname' | null > 3 | null | 'mydata' > 4 | 'morename' | 'moredata' You need to use a UNION of the four tables, with nulls supplied where necessary: select * from hybrid -- specify first to establish the -- column types union select id, null, null from foo union select id, name, null from bar1 union select id, null, data from bar2; Unfortunately, you can't make this a view, because views of unions are not yet supported. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But the wisdom that is from above is first pure, then peaceable, gentle, and easy to be intreated, full of mercy and good fruits, without partiality, and without hypocrisy." James 3:17
