Re: [SQL] function expression in FROM may not refer to other relations of same query level
> > The problem now is that get_lines is being called twice per line. > > Is get_lines() defined as IMMUTABLE? Should it be? I have tried defining get_lines as "IMMUTABLE", or "WITH (iscachable)", but it keeps on getting called twice per line in the following query... select id, usr, code, (get_lines(code)).line1, (get_lines(code)).line2 from tbl; I wish there was a way to run the query like this: select id, usr, code, CAST(get_lines(code) as lines) from tbl; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Maybe install debian on your home pc?
Hi Marcus, Here's something interesting for you : http://www.gnoppix.org/ It looks like it may be more easy to install, as it's entirely geared towards installation rather than just booting. It says it's based on woody, so I don't know if the 2.6 kernel is a boot option here. Maybe check it out... Cheers Stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Maybe install debian on your home pc?
Erm .. sorry list people. This one slipped to the wrong address... Stef mentioned : => Hi Marcus, => => Here's something interesting for you : => http://www.gnoppix.org/ => => It looks like it may be more easy to install, as it's => entirely geared towards installation rather than just booting. => It says it's based on woody, so I don't know if the 2.6 kernel is => a boot option here. Maybe check it out... => => Cheers => Stefan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inserting into point[] type.
Title: Inserting into point[] type.
How do you insert into a column of type point[]
Sample table:
CREATE TABLE public.test
(
Column_a varchar(1000),
Column_b point[]
) WITHOUT OIDS;
I have tried without success:
insert into test (Column_a, Column_b) values ('Aargau','{47.383896,8.03400039673}')
Theo
__This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Inserting into point[] type.
Ð ÐÑÐ, 12.08.2004, Ð 09:58, Theo Galanakis ÐÐÑÐÑ:
> How do you insert into a column of type point[]
> I have tried without success:
>
> insert into test (Column_a, Column_b) values
> ('Aargau','{47.383896,8.03400039673}')
I'd guess '{(3.3, 4.4), (1.0, 4.3)}' or ARRAY[(3.3, 4.4), (1.0, 4.3)].
Maybe even something like '{''(3.3, 4.4)'', ''(1.0, 4.3)''}'
--
Markus Bertheau <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Sending errors from psql to error file
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. Thanks. -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982 ---(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] function expression in FROM may not refer to other relations of same query level
"Philippe Lang" <[EMAIL PROTECTED]> writes: > I wish there was a way to run the query like this: > select > id, > usr, > code, > CAST(get_lines(code) as lines) > from tbl; You can do something like this: regression=# create type complex as (r float8, i float8); CREATE TYPE regression=# create function fooey(float8) returns complex as regression-# 'select $1 + 1, $1 + 2' language sql; CREATE FUNCTION regression=# select f1, (fooey).r, (fooey).i from regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss; f1 | r | i ---+---+--- 0 | 1 | 2 -34.84 |-33.84 |-32.84 -1004.3 | -1003.3 | -1002.3 -1.2345678901234e+200 | -1.2345678901234e+200 | -1.2345678901234e+200 -1.2345678901234e-200 | 1 | 2 (5 rows) Note the odd-looking parenthesization --- you can't write just "fooey.r" because that looks like it should be a table and field name, not a field name that is selected from. If the sub-select is too simple, as it is in this case, the planner is likely to "flatten out" the query into select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl; thus defeating your purpose of not calling the function twice. The currently best hack for preventing this is to add "OFFSET 0" to the sub-select: select f1, (fooey).r, (fooey).i from (select f1, fooey(f1) as fooey from float8_tbl offset 0) ss; regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sending errors from psql to error file
Devin Whalen wrote: 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 Try something like: ... psql cli_post 2>import_errors STDOUT is file-handle 1, STDERR is file-handle 2. You might also want to read up on the "tee" utility. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Sending errors from psql to error file
On Thu, 2004-08-12 at 10:50, Oliver Elphick wrote: > 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 Ok sorry. I ran the command and there were errors printed to the screen but they did not get piped into grep and then redirected into the file import_errors. > > 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 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. > Oliver Elphick Thanks for the help BTW, it is a really nice command. -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982 ---(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] 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
[SQL] how to cast localtimestamp to bigint???
Hi, I am having a table something like this CREATE TABLE(PointId integer, PointName varchar(50),PointType integer, createtime bigint); where createtime is the current timestamp when the tuple is inserted. now how do I insert values into the above table. Is there a way to cast timestamp to bigint. Also can anyone suggest as to which date function to use - CURRENT_TIMESTAMP, LOCALTIMESTAMP, timeofday(), now > With Best Regards > Pradeep Kumar P J > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] function expression in FROM may not refer to other relations
On Thu, 12 Aug 2004, Philippe Lang wrote: > > > The problem now is that get_lines is being called twice per line. > > > > Is get_lines() defined as IMMUTABLE? Should it be? > > I have tried defining get_lines as "IMMUTABLE", or "WITH (iscachable)", > but it keeps on getting called twice per line in the following query... > > select > id, > usr, > code, > (get_lines(code)).line1, > (get_lines(code)).line2 > from tbl; > > I wish there was a way to run the query like this: > > select > id, > usr, > code, > CAST(get_lines(code) as lines) > from tbl; Note that select id, usr, code, get_lines(code) from tbl; should work in 8.0beta although you get the composite type as the last column, not two columns. sszabo=# select id, usr, code, get_lines(code) from tbl; id | usr | code | get_lines +---+--+--- 1 | one |1 | (A,B) 2 | two |2 | (Z,Z) 3 | three |1 | (A,B) (3 rows) You can get individual columns with a bit of work and a subselect in from but you need to trick the system to not pull up the subselect to not get the function called twice per row. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 11:09, Oliver Elphick wrote: > 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 Thanks for you explanations. They are a big help. Now that I understand it better I can modify it to suit my needs. I really only want the errors because if I keep track of all the inserts then the file will be too big. So I just have to use 2>, or rather 2>> so I can append to the file. The syntax for -f is "-f filename"; the filename "-" means standard > input. (Check the man page for psql!) I swear I read it like 15 times and I guess I just scanned over that part every time!! :) Later -- Devin Whalen Programmer Synaptic Vision Inc Phone-(416) 539-0801 Fax- (416) 539-8280 1179A King St. West Toronto, Ontario Suite 309 M6K 3C5 Home-(416) 653-3982 ---(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
