Re: [GENERAL] Linked server query problem
Anderson, Steven wrote: > I have SQL Server 2005 that is linked to Postgresql 8.3.1-1 Upgrade to the latest version in 8.3 at your soonest convenience. > The following query does not work > > SELECT id FROM ALERT.novastar.[public].[point] > > Error: > "SELECT "Tbl1002"."id" "Col1004" FROM "novastar"."public"."point" > "Tbl1002"" What's this supposed to do? Is "novastar" a database name? That's not going to work - you'll need to connect to "novastar" and select from "public.point". Are you using the MS-SQL "Linked Server" feature? I've never tried it with non-MS targets. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] about privileges on pg_stat_activity columns
Jaime Casanova wrote: > Hi, > > there is an auditor that want to monitor our database activity to see > the session and the ip they come from, if they are waiting and so > on... pg_stat_activity and pg_locks views come to my mind... > > we created an user to him and give him privileges to pg_locks and > pg_stat_activity (and the functions pg_stat_activity is calling) but > still he see the columns that comes from the functions as null... is > there a way to give him access to that data without give him > superuser? I'd create a view or some functions with "security definer" privileges. That way you can provide precisely the access needed. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Executing a user created function twice give an error
Did try by encapsulating the create and drop queries within EXECUTE, but it seems to be giving the same error. The changed function is as follows: /// CREATE OR REPLACE FUNCTION func_snappointtonetwork(p_pointtobesnapped "varchar", p_currentboundingbox "varchar") RETURNS text AS $BODY$ DECLARE v_geom bytea; v_snappedPoint varchar; v_HAPMSSection varchar; v_road varchar; v_area varchar; v_cWay varchar; v_cWayDirection varchar; BEGIN EXECUTE 'CREATE TEMPORARY TABLE __distances__temp (link_Id varchar,calc_distance float8)'; INSERT INTO __distances__temp(link_Id,calc_distance) SELECT hapms2.sect_label as link_Id,distance(hapms2.geom,ST_GeomFromEWKT(p_pointtobesnapped)) as distance FROM hapms_road hapms2 WHERE (hapms2.geom && ST_box2d(ST_GeomFromEWKT(p_currentboundingbox))); SELECT INTO v_geom,v_HAPMSSection,v_road,v_area,v_cWay,v_cWayDirection asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_code FROM hapms_road hapms1 WHERE (hapms1.geom && ST_box2d(GeomFromEWKT(p_currentBoundingBox))) AND distance(hapms1.geom, GeomFromEWKT(p_pointToBeSnapped)) < all (SELECT calc_distance FROM __distances__temp WHERE hapms1.sect_label <> link_Id); SELECT INTO v_snappedPoint ST_AsEWKT(ST_line_interpolate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_line_locate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_GeomFromEWKT(p_pointToBeSnapped; EXECUTE 'DROP TABLE __distances__temp'; RETURN v_snappedPoint || '|' || v_HAPMSSection || '|' || v_road || '|' || v_area || '|' || v_cWay || ' ' || v_cWayDirection; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION func_snappointtonetwork(p_pointtobesnapped "varchar", p_currentboundingbox "varchar") OWNER TO postgres; /// - Original Message - From: "A. Kretschmer" <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: 02 December 2008 12:50:44 o'clock (GMT) Europe/London Subject: Re: [GENERAL] Executing a user created function twice give an error am Tue, dem 02.12.2008, um 12:36:26 + mailte Wajid Khattak folgendes: > Thank for your reply. > > Could you please elaborate it a little bit further by referring to the > fucntion as I am quite new to Postgres. Sure, read the doku: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN PG cached the plan and the also the OID for affected tables. To avoid this, use EXECUTE 'insert your query here' for create or delete tables within plpgsql. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Executing a user created function twice give an error
am Thu, dem 04.12.2008, um 9:23:31 + mailte Wajid Khattak folgendes: > Did try by encapsulating the create and drop queries within EXECUTE, but it > seems to be giving the same error. The changed function is as follows: You need to execute the insert-statement also: test=# create or replace function tmp_table() returns int as $$begin execute 'create temporary table tmp_foo(i int)'; insert into tmp_foo values(1);drop table tmp_foo; return 1;end;$$language plpgsql; CREATE FUNCTION test=*# test=*# test=*# select * from tmp_table(); tmp_table --- 1 (1 row) test=*# select * from tmp_table(); ERROR: relation with OID 187431854 does not exist CONTEXT: SQL statement "insert into tmp_foo values(1)" PL/pgSQL function "tmp_table" line 1 at SQL statement test=!# rollback; ROLLBACK test=# create or replace function tmp_table() returns int as $$begin execute 'create temporary table tmp_foo(i int)'; execute 'insert into tmp_foo values(1)';drop table tmp_foo; return 1;end;$$language plpgsql; CREATE FUNCTION test=*# select * from tmp_table(); tmp_table --- 1 (1 row) test=*# select * from tmp_table(); tmp_table --- 1 (1 row) test=*# select * from tmp_table(); tmp_table --- 1 (1 row) test=*# Peculiar, the drop table works without execute... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Executing a user created function twice give an error
On Thu, Dec 4, 2008 at 9:45 AM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > test=*# select * from tmp_table(); > ERROR: relation with OID 187431854 does not exist that's a known problem, it was fixed in 8.3. I would strongly advice you to upgrade, shall you depend on temporary tables in plpgsql. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql verbose mode
yup, looks like I'll have to implement it myself :P -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql verbose mode
2008/12/4 Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> > yup, looks like I'll have to implement it myself :P > After a bit of investigation I think it's not so easy. -v switch is reserved in psql for "set variable". So the patch would have to inctroduce a new switch or use VERBOSITY environment variable, which actually means something different. There is another way - much simpler I think: psql -c '\l+' -- Filip Rembiałkowski
Re: [GENERAL] psql verbose mode
On Thu, Dec 4, 2008 at 11:36 AM, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: > There is another way - much simpler I think: > > psql -c '\l+' you're spoiling all the fun mate :P I think there ought to be some sort of --verbose mode, otherwise they wouldn't even bother implementing -l , with -c '\whatever' . I'll scribble something for folks on -hackers, and see how would they l(like|augh) the idea. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] column name order matters in insert into foo from bar
hey, it looks , as if when I have two tables, say create table foo( a int, b varchar, c float ); and : create table bar( b varchar, a int, c float ); migration of data from one to the other using insert into foo select * from bar; will fail. How can I walk around that please ? -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] column name order matters in insert into foo from bar
to answer myself, and also make matter a bit more complicated. the table has like 20 columns, so - yes, I can probably name all of them one by one, but - is there any way to do it without need to name all of them ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] column name order matters in insert into foo from bar
2008/12/4 Grzegorz Jaśkiewicz <[EMAIL PROTECTED]>: > to answer myself, and also make matter a bit more complicated. > the table has like 20 columns, so - yes, I can probably name all of > them one by one, but - is there any way to do it without need to name > all of them ? > no, regards Pavel Stehule p.s. you can try use stored procedure for generating these statements > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] serial
I created this table:
create table se (n serial);
Column | Type | Modifiers
-+-+
n | integer| not null default nextval('se_n_seq'::regclass)
I inserted two record, later select, but column n (serial) no
auto-incremented
banco=# select * from se;
n
---
0
0
(2 rows)
Why?
Thanks
Gustavo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] column name order matters in insert into foo from bar
On Thu, Dec 4, 2008 at 1:27 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > p.s. you can try use stored procedure for generating these statements yeah, I ended up generating it using: select array_to_string(ARRAY(select column_name::text FROM information_schema.columns where table_name='foo'), ','); This was one time thing anyway, so than simple copy and paste, etc .. thanks folks. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Thu, Dec 4, 2008 at 2:42 PM, Gustavo Rosso <[EMAIL PROTECTED]> wrote:
> I created this table:
>
> create table se (n serial);
>
in case you did just insert into se(n) values(0); twice, it won't work
if you want to add series of numbers, don't define it as serial.
Serial is for a different purpose.
if you want a series of generated numbers, please use generate_series();
so :
create table se(n int not null);
insert into se(n) select generate_series(1,100);
serial is used for different purposes. Say, you need an auto
incremented id on a row:
create table foo(
id serial,
name varchar(128)
);
and than:
insert into foo(name) values('Gustavo'), ('Grzegorz') returning id;
;]
hth
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Thu, Dec 4, 2008 at 3:42 PM, Gustavo Rosso <[EMAIL PROTECTED]> wrote:
> I created this table:
>
> create table se (n serial);
>
> Column | Type | Modifiers
> -+-+
> n | integer| not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>
> Why?
> Thanks
> Gustavo
>
You inserted values in a table with only a serial column?
[GENERAL] pg_stat_activity
my querys is very biggest ,pg_stat_activity dont show full How increase length from pg_stat_activity ? Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Re: [GENERAL] about privileges on pg_stat_activity columns
On Thu, Dec 4, 2008 at 4:02 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > Jaime Casanova wrote: >> >> we created an user to him and give him privileges to pg_locks and >> pg_stat_activity (and the functions pg_stat_activity is calling) but >> still he see the columns that comes from the functions as null... is >> there a way to give him access to that data without give him >> superuser? > > I'd create a view or some functions with "security definer" privileges. > That way you can provide precisely the access needed. > exactly what i did... thanks to both -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
2008/12/4 Gustavo Rosso <[EMAIL PROTECTED]>:
> I created this table:
>
> create table se (n serial);
>
> Column | Type | Modifiers
> -+-+
> n | integer| not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>
what is your insert statement?
postgres=#
postgres=# create table se(n serial);
NOTICE: CREATE TABLE will create implicit sequence "se_n_seq" for
serial column "se.n"
CREATE TABLE
postgres=# insert into se values(default);
INSERT 0 1
postgres=# insert into se values(default);
INSERT 0 1
postgres=# select * from se;
n
---
1
2
(2 rows)
regards
Pavel Stehule
> Why?
> Thanks
> Gustavo
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <[EMAIL PROTECTED]> wrote:
> I created this table:
>
> create table se (n serial);
>
> Column | Type | Modifiers
> -+-+
> n | integer| not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>
> Why?
Because MySQL taught you bad habits? You told the db to insert a 0,
so it inserted a 0. If you told it to insert a NULL, it would proceed
to do that too. There are several ways to have it use the serial /
sequence properly... You can use the default keyword, or leave out
the field altogether (assuming you have > 1 field I guess), or you can
insert from the sequence yourself:
smarlowe=# create table test (i serial primary key, t text);
NOTICE: CREATE TABLE will create implicit sequence "test_i_seq" for
serial column "test.i"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (t) values ('this is text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (DEFAULT,'this is more text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (nextval('test_i_seq'),'even
more text');
INSERT 0 1
smarlowe=# select nextval('test_i_seq');
nextval
-
4
(1 row)
smarlowe=# insert into test (i,t) values (4,'last bit of text');
INSERT 0 1
smarlowe=# select * from test;
i | t
---+---
1 | this is text
2 | this is more text
3 | even more text
4 | last bit of text
(4 rows)
Hope that helps.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Thu, Dec 4, 2008 at 7:06 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <[EMAIL PROTECTED]> wrote:
>> I created this table:
>>
>> create table se (n serial);
>>
>> Column | Type | Modifiers
>> -+-+
>> n | integer| not null default nextval('se_n_seq'::regclass)
>>
>> I inserted two record, later select, but column n (serial) no
>> auto-incremented
>> banco=# select * from se;
>> n
>> ---
>> 0
>> 0
>> (2 rows)
>>
>> Why?
>
> Because MySQL taught you bad habits? You told the db to insert a 0,
> so it inserted a 0. If you told it to insert a NULL, it would proceed
> to do that too. There are several ways to have it use the serial /
> sequence properly... You can use the default keyword, or leave out
> the field altogether (assuming you have > 1 field I guess), or you can
> insert from the sequence yourself:
>
> smarlowe=# create table test (i serial primary key, t text);
> NOTICE: CREATE TABLE will create implicit sequence "test_i_seq" for
> serial column "test.i"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test_pkey" for table "test"
> CREATE TABLE
> smarlowe=# insert into test (t) values ('this is text');
> INSERT 0 1
> smarlowe=# insert into test (i,t) values (DEFAULT,'this is more text');
> INSERT 0 1
> smarlowe=# insert into test (i,t) values (nextval('test_i_seq'),'even
> more text');
> INSERT 0 1
> smarlowe=# select nextval('test_i_seq');
> nextval
> -
> 4
> (1 row)
>
> smarlowe=# insert into test (i,t) values (4,'last bit of text');
> INSERT 0 1
> smarlowe=# select * from test;
> i | t
> ---+---
> 1 | this is text
> 2 | this is more text
> 3 | even more text
> 4 | last bit of text
> (4 rows)
>
> Hope that helps.
>
Last way, forgot about it:
smarlowe=# select nextval('test_i_seq');
nextval
-
5
smarlowe=# insert into test (i,t) values (currval('test_i_seq'),'last
bit of text');
INSERT 0 1
--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_stat_activity
2008/12/4 paulo matadr <[EMAIL PROTECTED]>: > my querys is very biggest ,pg_stat_activity dont show full > How increase length from pg_stat_activity ? I don't think you can (easily). You can, however, log long running queries and that should log the whole thing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
The esteemed Scott Marlowe said: > > On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <[EMAIL PROTECTED]> wrote: > > I created this table: > > <...> > > Because MySQL taught you bad habits? You told the db to insert a 0, > so it inserted a 0. With respect, sir, let me point out that Informix usage says to insert a zero to trigger a serial column, so don't be indicting Gustavo on MySQL use -- he might be coming from a real database where usage is different. Those of us who came to postgres from Informix-land made [perhaps] the same mistakes. Not a bad *habit* unless you can prove to me that the spec says otherwise. (Maybe you can -- the SQL formal definitions seem to be deliberately opaque and often do not define _how_ a given behavior should be implemented). Just a matter of indoctrination into the local ways of doing serials. Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Linked server query problem
Hi.
Probably, it need the new feature of Ver 8.4.
See,
http://archives.postgresql.org/pgsql-committers/2008-02/msg00172.php
Regards,
Hiroshi Saito
- Original Message -
I have SQL Server 2005 that is linked to Postgresql 8.3.1-1
The following query does not work
SELECT id FROM ALERT.novastar.[public].[point]
Error:
OLE DB provider "MSDASQL" for linked server "ALERT" returned message
"ERROR: syntax error at or near ""Col1004"";
Error while executing the query".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query
"SELECT "Tbl1002"."id" "Col1004" FROM "novastar"."public"."point"
"Tbl1002""
against OLE DB provider "MSDASQL" for linked server "ALERTIII".
This query does work from MS SQL:
EXEC ('SELECT id FROM point') at ALERT
Any ideas as what the problem is
Thank you
Steven Anderson
City of Dallas
Streets Department
Flood Control
2255 Irving Blvd.
Dallas, TX 75207-6201
(214)671-0424
fax - (214)670-6526
web http://fc.dallascityhall.com
wireless web http://fc.dallascityhall.com/wap
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On Thu, Dec 4, 2008 at 7:47 AM, Gregory Williamson <[EMAIL PROTECTED]> wrote: > The esteemed Scott Marlowe said: > >> >> On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <[EMAIL PROTECTED]> >> wrote: >> > I created this table: >> > > <...> >> >> Because MySQL taught you bad habits? You told the db to insert a 0, >> so it inserted a 0. > > With respect, sir, let me point out that Informix usage says to insert a > zero to trigger a serial column, so don't be indicting Gustavo on MySQL use > -- he might be coming from a real database where usage is different. I really shoulda had a smiley up there with my comment. However, mea cupla, mea maxima culpa. Note that it is quite possible to learn bad habits from many commercial databases, not just MySQL. Like an Oracle DBA who told me pgsql was broken because the output of group by wasn't properly ordered like it was on Oracle 9. Admittedly, MySQL has a much larger set of bad habits to teach the average user than any other DB, but it ain't the only one. > Those of us who came to postgres from Informix-land made [perhaps] the same > mistakes. Not a bad *habit* unless you can prove to me that the spec says > otherwise. I'm pretty sure that if you say to insert a value, the spec says that the value should be inserted unaltered. 0 is a value. DEFAULT, or leaving it out of the column list is a different thing entirely. > (Maybe you can -- the SQL formal definitions seem to be > deliberately opaque and often do not define _how_ a given behavior should be > implemented). Just a matter of indoctrination into the local ways of doing > serials. True++. The sql spec is usually quite simple, but in some areas it's worse than the tax code. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] spanish
Please, exists postgres forum in spanish? Thanks Gustavo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
On Monday 01 December 2008 22:09:08 Scott Marlowe wrote: > On Mon, Dec 1, 2008 at 7:49 PM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > > which reminds me, of my favourite recent quote: > > "Think I'll go fix this while I'm watching the football game ..." > > We really need a favorite Tom Lane quotes thread. Mine is (roughly): > http://archives.postgresql.org/pgsql-hackers/2006-04/msg00288.php I remember after reading this post wondering whether Tom uses caffeinated soap... -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] spanish
On Thu, Dec 4, 2008 at 4:44 PM, Gustavo Rosso <[EMAIL PROTECTED]> wrote: > Please, exists postgres forum in spanish? http://archives.postgresql.org/pgsql-es-ayuda/ -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
2008/12/4 Robert Treat <[EMAIL PROTECTED]>: > http://archives.postgresql.org/pgsql-hackers/2006-04/msg00288.php > I remember after reading this post wondering whether Tom uses caffeinated > soap... > well, some ppl come up with ideas on wc-thone ;) What do you suggest they do there ... ;> -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Job scheduling in Postgre
On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: > am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: > > I have certain jobs to be executed automatically at a given interval of > > time in the postgre SQL database. Is their any utility/feature available > > in Postgre to do so. > > No, use the scheduler from the OS, CRON for example (UNIX). > There is a database level schedular called (iirc) pgAgent, which comes bundled with pgAdmin. I think it's so well hidden because it comes as a part of a tool which is only used by a small subset of the community. I had hopes that it might follow autovacuums path and get moved into a contrib module and possibly integrated into the backend some day, but I haven't seen much push in that direction. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switch off PITR
On Wednesday 03 December 2008 14:22:28 Joshua D. Drake wrote: > On Wed, 2008-12-03 at 13:16 -0500, Bill Moran wrote: > > In response to "Joey K." <[EMAIL PROTECTED]>: > > > How do I turn off PITR in the mean time? I commented archive_command > > > and issued a pg_ctl reload and postgres is *still* archiving logs to > > > the backup server. > > > > Pretty sure you're going to need a full restart -- reload won't cause > > that parameter to be re-evaluated. > > You can change archive_command to something like /bin/true and reload. > However you will have to do a full base backup to get postgresql doing > log shipping again. > You can probably avoid this by having your archive command put the xlogs somewhere local, and then once you restart setting up the archive command to push back to your backup and then moving the missing logs manually. (This is more/less fragile depending on exactly how you've set things up, but should be doable) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Limit on number of databases in a Cluster ?
On Wednesday 03 December 2008 23:49:00 Scott Marlowe wrote: > On Wed, Dec 3, 2008 at 11:05 AM, Josh Harrison <[EMAIL PROTECTED]> wrote: > > On Wed, Dec 3, 2008 at 11:51 AM, Scott Marlowe <[EMAIL PROTECTED]> > > > > wrote: > >> On Wed, Dec 3, 2008 at 8:43 AM, Josh Harrison <[EMAIL PROTECTED]> wrote: > >> > Hi, > >> > > >> > 1. Is there a limit on the number of databases that can be in a single > >> > postgres cluster? > >> > >> No. I'm sure there's a practical limit into the thousands where > >> things start to get slower. > >> > >> > 2. Is there any performance impacts associated with having too many > >> > databases in a cluster? > >> > >> Define too many. I've run a couple hundred before without it being a > >> problem. > >> > >> > 3. Is there a good magical number for this limit ? > >> > >> Only the one that your testing tells you there is. Got a rough guess > >> of how many you want to run? How busy they'll be? that kind of > >> thing. > > > > About 10-15 ? > > That's hardly any really. At that point it's more about whether or > not your server can support all the users / access going on at once. > 15 or 1 db in the cluster, if you've got 200 users hitting it hard > you'll need a big server. OTOH, 100 dbs in a cluster with a dozen or > fewer average users is just fine. Right. This becomes most important when you tune postgresql.conf parameters, which will apply cluster wide so need to be calculated across all databases. The fsm settings are a good example (tracking pages across all databases), but also things like work_mem need to account for all connections to all databases when you think about how high you can set these. Don't forget some of these settings (like work_mem) can be set per database using the ALTER DATABASE command, just be careful becuase the support for backing up those changes is spotty at best. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Job scheduling in Postgre
On Thu, Dec 4, 2008 at 3:50 PM, Robert Treat <[EMAIL PROTECTED]> wrote: > On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: >> am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: >> > I have certain jobs to be executed automatically at a given interval of >> > time in the postgre SQL database. Is their any utility/feature available >> > in Postgre to do so. >> >> No, use the scheduler from the OS, CRON for example (UNIX). >> > > There is a database level schedular called (iirc) pgAgent, which comes bundled > with pgAdmin. I think it's so well hidden because it comes as a part of a > tool which is only used by a small subset of the community. I had hopes that > it might follow autovacuums path and get moved into a contrib module and > possibly integrated into the backend some day, but I haven't seen much push > in that direction. It would need to be ported to C first (it's currently wxWidgets/C++). We are packaging it separately now though. http://www.pgadmin.org/docs/1.8/pgagent.html http://www.pgadmin.org/download/pgagent.php -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
On Thu, Dec 4, 2008 at 8:49 AM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > 2008/12/4 Robert Treat <[EMAIL PROTECTED]>: >> http://archives.postgresql.org/pgsql-hackers/2006-04/msg00288.php >> I remember after reading this post wondering whether Tom uses caffeinated >> soap... >> > > well, some ppl come up with ideas on wc-thone ;) What do you suggest > they do there ... ;> One of my favorite tinwhistle tunes is named Dusty Windowsill. It's so named because Johnny Harling (sp?) was sitting on the toilet when the tune came to him, and having nothing else to write on, he wrote it out on a dusty windowsill. Genius strikes when it strikes. I've woken up at two in the morning with the answer to a complex programming problem at work, grabbed my laptop, pounded out a hundred or so lines, then spent the next two days trying to figure out how it worked. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] spanish
On Thu, 2008-12-04 at 13:44 -0300, Gustavo Rosso wrote: > Please, exists postgres forum in spanish? http://archives.postgresql.org/pgsql-es-ayuda/ > Thanks > Gustavo > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] spanish
Gustavo Rosso wrote: > Please, exists postgres forum in spanish? > Thanks > Gustavo > aqui se habla español : http://archives.postgresql.org/pgsql-es-ayuda/ Saludos .. Leonel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Job scheduling in Postgre
am Thu, dem 04.12.2008, um 10:50:38 -0500 mailte Robert Treat folgendes: > On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: > > am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: > > > I have certain jobs to be executed automatically at a given interval of > > > time in the postgre SQL database. Is their any utility/feature available > > > in Postgre to do so. > > > > No, use the scheduler from the OS, CRON for example (UNIX). > > > > There is a database level schedular called (iirc) pgAgent, which comes > bundled > with pgAdmin. I think it's so well hidden because it comes as a part of a How does it work? Independent from the OS? On the server, within PostgreSQL? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Eweek-Sun-Monty-MySQL
Eweek Article above is article on eweek discussing Monty's blog on the poor shape MySql 5.1 is in
Re: [GENERAL] Job scheduling in Postgre
On Thu, Dec 4, 2008 at 4:21 PM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am Thu, dem 04.12.2008, um 10:50:38 -0500 mailte Robert Treat folgendes: >> On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: >> > am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: >> > > I have certain jobs to be executed automatically at a given interval of >> > > time in the postgre SQL database. Is their any utility/feature available >> > > in Postgre to do so. >> > >> > No, use the scheduler from the OS, CRON for example (UNIX). >> > >> >> There is a database level schedular called (iirc) pgAgent, which comes >> bundled >> with pgAdmin. I think it's so well hidden because it comes as a part of a > > How does it work? Independent from the OS? On the server, within > PostgreSQL? It runs as a service on Windows or daemon on *nix, and connects to a pgagent schema in a database which may or may not be on the same machine. You can define jobs using pgAdmin (the definitions of which are stored in the database) which consist of one or more SQL or batch/shell steps. A job can be targetted at a particular server, so you can ensure batch steps run on a Windows box, and shell on a unix, or can be run by the first (or only) pgAgent instance that picks it up. SQL steps currently target a specific database, and in future releases will be able to target databases on alternate servers - a feature required for use with hot standby servers. Each job can have one or more schedules attached to it. Schedules are defined in a vaguely cron-like way, and include date/time based exception rules (so you can do things like 'run every day except for the 25/12/2008'. See http://www.pgadmin.org/docs/1.8/pgagent.html for more info, and screenshots. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Q: inheritance
hi, I think I misunderstand the concept of inheritance. I was under the impression that inheriting from an existing table inherits all of the parent's columns. But: create table t1 (id serial primary key); create table t2 (num int) inherits (t1); create table t3 (t1 int references t1(id)); insert into t2 (id, num) values (1,1); -- so far , so good -- the next one fails: insert into t3 (t1) values (1); fails with an error (translated from german): insert or update in table "t3" violates foreign key constraint "t3_t1_fkey" DETAIL: key(t1)=(1) is not present in table "t1" but: select * from t2; id | num +- 1 | 1 can anyone explain this behaviour? My database setup relies on inherited tables that share a primary key. cheers, Rüdiger. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: inheritance
Rüdiger Sörensen wrote: > can anyone explain this behaviour? My database setup relies on inherited > tables that share a primary key. I'm afraid inherited tables don't share a primary key. See the manuals for full details. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Job scheduling in Postgre
am Thu, dem 04.12.2008, um 16:40:38 + mailte Dave Page folgendes: > >> There is a database level schedular called (iirc) pgAgent, which comes > >> bundled > >> with pgAdmin. I think it's so well hidden because it comes as a part of a > > > > How does it work? Independent from the OS? On the server, within > > PostgreSQL? > > It runs as a service on Windows or daemon on *nix, and connects to a > pgagent schema in a database which may or may not be on the same > machine. > > You can define jobs using pgAdmin (the definitions of which are stored > in the database) which consist of one or more SQL or batch/shell > steps. A job can be targetted at a particular server, so you can > ensure batch steps run on a Windows box, and shell on a unix, or can > be run by the first (or only) pgAgent instance that picks it up. SQL > steps currently target a specific database, and in future releases > will be able to target databases on alternate servers - a feature > required for use with hot standby servers. Thx for the explanation. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Q: inheritance
On Thu, Dec 4, 2008 at 8:40 AM, Rüdiger Sörensen <[EMAIL PROTECTED]> wrote: > insert or update in table "t3" violates foreign key constraint "t3_t1_fkey" > DETAIL: key(t1)=(1) is not present in table "t1" > select * from t2; > id | num > +- > 1 | 1 > can anyone explain this behaviour? Yes, PostgreSQL table inheritance is really just Horizontal Table partitioning with a nifty feature that makes the upper node table behave more like hierarchical UNION ALL views when SELECTed rather than actual tables. So in your case, the record you added to t2 doesn't really exits in t1 so referential integrity throws an error in t3. > My database setup relies on inherited > tables that share a primary key. In this case, I would recommend you use a vertically partitioned table design that simulates what you are trying to achieve. I recently developed a presentation on this subject if you are interested: http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to echo statements in sourced file?
Thank you all. From your replies I was able to figure out what I needed: "\set ECHO queries" Kynn On Wed, Dec 3, 2008 at 7:14 PM, Kynn Jones <[EMAIL PROTECTED]> wrote: > Hi. I have a collection of SQL statements stored in a file that I run > periodically via cron. Running this "script" takes a bit too long, even for > a cron job, and I would like to profile it. > I learned from Andreas Kretschmer (in another thread, in the > pgsql-performance list) about the \timing directive, which is useful for > this. > > Now, after turning timing on, when I "source" the script from within psql, > with > > mydb=> \i /path/to/my/script > > ...I get output lines like this > > Time: 38.519 ms > > right in my psql terminal after each statement in the file gets executed. > > But now I need a way to have these statements that are being timed > themselves echoed to the terminal. Is there a way to do this? > > TIA! > > Kynn > >
[GENERAL] cumulative count
Hi list, This is my first post to pgsql, so hopefully I'm not asking something that has been answered a thousand time before. I've looked online, and through the archives, but I haven't found anything that answers my question specifically: Say I have a table like this: date | user --+- 20050201 | Bill 20050210 | Steve 20050224 | Sally 20050311 | Martha 20050316 | Ryan 20050322 | Phil 20050330 | William 20050415 | Mary 20050428 | Susan 20050503 | Jim and I want to run a query that returns a *count* of the number of users *each month*, ordered by year and *month*, with an additional column that is a *running total of the count*, as in: year|month |count| run_count ---+++- 2005 | 02 | 3 | 3 2005 | 03 | 4 | 7 2005 | 04 | 2 | 9 2005 | 05 | 1 |10 I can get almost everything I want with: SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total FROM (SELECT EXTRACT(year from added_date) AS year, EXTRACT(month FROM added_date) AS month, TO_CHAR(added_date, 'Month') AS month_name, COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a, (SELECT EXTRACT(year FROM added_date) AS year, EXTRACT(month FROM added_date) AS month, TO_CHAR(added_date, 'Month') AS month_name, COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS b WHERE a.year >= b.year AND a.month >= b.month GROUP BY 1, 2, 3, 4 ORDER BY a.year, a.month asc; but I can't quite figure out the running total of the count. The above example works right up to the end of the first year, then the values no longer make sense. My guess is it's something to do with my WHERE clause, but I can't think of a better way to do things. Any ideas? Cheers, Carson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cumulative count
In article <[EMAIL PROTECTED]>,
Carson Farmer <[EMAIL PROTECTED]> writes:
> date | user
> --+-
> 20050201 | Bill
> 20050210 | Steve
> 20050224 | Sally
> 20050311 | Martha
> 20050316 | Ryan
> 20050322 | Phil
> 20050330 | William
> 20050415 | Mary
> 20050428 | Susan
> 20050503 | Jim
> and I want to run a query that returns a *count* of the number of
> users *each month*, ordered by year and *month*, with an additional
> column that is a *running total of the count*, as in:
> year|month |count| run_count
> ---+++-
> 2005 | 02 | 3 | 3
> 2005 | 03 | 4 | 7
> 2005 | 04 | 2 | 9
> 2005 | 05 | 1 |10
> I can get almost everything I want with:
> SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total
> FROM (SELECT EXTRACT(year from added_date) AS year,
> EXTRACT(month FROM added_date) AS month,
> TO_CHAR(added_date, 'Month') AS month_name,
> COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a,
> (SELECT EXTRACT(year FROM added_date) AS year,
> EXTRACT(month FROM added_date) AS month,
> TO_CHAR(added_date, 'Month') AS month_name,
> COUNT(*) AS count
> FROM users_table GROUP BY 1, 2, 3) AS b
> WHERE a.year >= b.year AND a.month >= b.month
> GROUP BY 1, 2, 3, 4
> ORDER BY a.year, a.month asc;
> but I can't quite figure out the running total of the count. The above
> example works right up to the end of the first year, then the values
> no longer make sense. My guess is it's something to do with my WHERE
> clause, but I can't think of a better way to do things.
Yes, your WHERE condition is the problem. It should be
WHERE a.year > b.year OR a.year = b.year AND a.month > b.month.
You could simplify the date logic by doing the year/month split later, e.g.
CREATE TEMP TABLE tmp AS
SELECT date_trunc('month', date) AS dt, count(*) AS count
FROM users_table
GROUP BY dt;
SELECT extract(YEAR FROM t1.dt) AS year,
extract(MONTH FROM t1.dt) AS month,
t1.count,
sum(t2.count) AS run_count
FROM tmp t1
LEFT JOIN tmp t2 ON t2.dt <= t1.dt
GROUP BY year, month, t1.count
ORDER BY year, month;
(AFAIK the WITH clause in PostgreSQL 8.4 would let you get rid of
the temp table.)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cumulative count
On Thu, Dec 04, 2008 at 07:32:59PM +0100, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Carson Farmer <[EMAIL PROTECTED]> writes:
>
> > date | user
> > --+-
> > 20050201 | Bill
> > 20050210 | Steve
> > 20050224 | Sally
> > 20050311 | Martha
> > 20050316 | Ryan
> > 20050322 | Phil
> > 20050330 | William
> > 20050415 | Mary
> > 20050428 | Susan
> > 20050503 | Jim
>
> > and I want to run a query that returns a *count* of the number of
> > users *each month*, ordered by year and *month*, with an additional
> > column that is a *running total of the count*, as in:
>
> > year|month |count| run_count
> > ---+++-
> > 2005 | 02 | 3 | 3
> > 2005 | 03 | 4 | 7
> > 2005 | 04 | 2 | 9
> > 2005 | 05 | 1 |10
>
> > I can get almost everything I want with:
>
> > SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total
> > FROM (SELECT EXTRACT(year from added_date) AS year,
> > EXTRACT(month FROM added_date) AS month,
> > TO_CHAR(added_date, 'Month') AS month_name,
> > COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a,
> > (SELECT EXTRACT(year FROM added_date) AS year,
> > EXTRACT(month FROM added_date) AS month,
> > TO_CHAR(added_date, 'Month') AS month_name,
> > COUNT(*) AS count
> > FROM users_table GROUP BY 1, 2, 3) AS b
> > WHERE a.year >= b.year AND a.month >= b.month
> > GROUP BY 1, 2, 3, 4
> > ORDER BY a.year, a.month asc;
>
> > but I can't quite figure out the running total of the count. The above
> > example works right up to the end of the first year, then the values
> > no longer make sense. My guess is it's something to do with my WHERE
> > clause, but I can't think of a better way to do things.
>
> Yes, your WHERE condition is the problem. It should be
> WHERE a.year > b.year OR a.year = b.year AND a.month > b.month.
>
> You could simplify the date logic by doing the year/month split later, e.g.
>
> CREATE TEMP TABLE tmp AS
>
> SELECT extract(YEAR FROM t1.dt) AS year,
> extract(MONTH FROM t1.dt) AS month,
> t1.count,
> sum(t2.count) AS run_count
> FROM tmp t1
> LEFT JOIN tmp t2 ON t2.dt <= t1.dt
> GROUP BY year, month, t1.count
> ORDER BY year, month;
What about:
SELECT extract(YEAR FROM t1.dt) AS year,
extract(MONTH FROM t1.dt) AS month,
t1.count,
sum(t2.count) AS run_count
FROM (
SELECT date_trunc('month', date) AS dt, count(*) AS count
FROM users_table
GROUP BY dt
) AS t1
LEFT JOIN tmp t2 ON t2.dt <= t1.dt
GROUP BY year, month, t1.count
ORDER BY year, month;
Regards,
Gerhard
signature.asc
Description: Digital signature
[GENERAL] Automatic insert statement generator?
Greetings!
I was going to make this a question, but I poked around a bit and came
up with an answer, which I'll share here in case anyone else is
interested.
I occasionally need to add test records to a database table. For
example, I want a new charge that is identical to charge 18000, so I
need coils in inventory that match those in charge 18000:
insert into inventory
select * from inventory where charge = 18000
The problem, of course, is that the inventory table has a unique key
constraint that gets violated. So, to do this, I'm going to have to
write an insert query that lists every field in this table (all 62 of
them), except for the primary key, which I'll have to force to something
I know is unique. I would like a database function that would generate
a string that would be a concatenation of all fields in a given table.
Then, I could use the resulting string as the starting point for
building an insert statement that will avoid the key field(s).
So, if I have a table named 'small_table' that contains columns
'column1', 'column2' and 'column3', I would be able to execute:
SELECT get_fields('small_table')
And I would get back:
'column1, column2, column3'.
Here's what I did:
-- Function: list_fields("varchar")
-- DROP FUNCTION list_fields("varchar")
CREATE OR REPLACE FUNCTION list_fields("varchar")
RETURNS "varchar" AS
$BODY$
declare
Tablename ALIAS for $1;
Attributes record;
Result varchar;
begin
Result := '';
FOR Attributes IN SELECT attname FROM pg_attribute
where attrelid = (select oid from pg_class where
relname = Tablename)
and attstattarget <> 0
LOOP
if length(Result) <> 0 then
Result = Result || ', ';
end if;
Result = Result || Attributes.attname;
END LOOP;
raise notice '%', Result;
return Result;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION list_fields("varchar") OWNER TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO public;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] serial
On 04/12/2008 14:47, Gregory Williamson wrote: > With respect, sir, let me point out that Informix usage says to insert a > zero to trigger a serial column, so don't be indicting Gustavo on MySQL So what do you do if you just want to insert a zero? - just curious... In fairness, I suppose you wouldn't often be doing this on an auto-incrementing columnbut just say you did - how do you do it? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cumulative count
2008/12/4 Carson Farmer <[EMAIL PROTECTED]>: > Hi list, > > This is my first post to pgsql, so hopefully I'm not asking something that > has been answered a thousand time before. I've looked online, and through > the archives, but I haven't found anything that answers my question > specifically: > > Say I have a table like this: > > date | user > --+- > 20050201 | Bill > 20050210 | Steve > 20050224 | Sally > 20050311 | Martha > 20050316 | Ryan > 20050322 | Phil > 20050330 | William > 20050415 | Mary > 20050428 | Susan > 20050503 | Jim > > and I want to run a query that returns a *count* of the number of users > *each month*, ordered by year and *month*, with an additional column that is > a *running total of the count*, as in: > >year|month |count| run_count > ---+++- >2005 | 02 | 3 | 32005 > | 03 | 4 | 7 >2005 | 04 | 2 | 9 >2005 | 05 | 1 |10 > > I can get almost everything I want with: > > SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total > FROM (SELECT EXTRACT(year from added_date) AS year, > EXTRACT(month FROM added_date) AS month, > TO_CHAR(added_date, 'Month') AS month_name, > COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a, > (SELECT EXTRACT(year FROM added_date) AS year, > EXTRACT(month FROM added_date) AS month, > TO_CHAR(added_date, 'Month') AS month_name, > COUNT(*) AS count > FROM users_table GROUP BY 1, 2, 3) AS b > WHERE a.year >= b.year AND a.month >= b.month > GROUP BY 1, 2, 3, 4 > ORDER BY a.year, a.month asc; > > but I can't quite figure out the running total of the count. The above > example works right up to the end of the first year, then the values no > longer make sense. My guess is it's something to do with my WHERE clause, > but I can't think of a better way to do things. > > Any ideas? > hate selfjoins. It is really slow for any bigger datasets. Write SRF function (stored function that returns table). Regards Pavel Stehule > Cheers, > > Carson > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cumulative count
On Thu, Dec 04, 2008 at 05:53:06PM +, Carson Farmer wrote: > Hi list, > > This is my first post to pgsql, so hopefully I'm not asking something > that has been answered a thousand time before. I've looked online, and > through the archives, but I haven't found anything that answers my > question specifically: > > Say I have a table like this: > > date | user > --+- > 20050201 | Bill > 20050210 | Steve > 20050224 | Sally > 20050311 | Martha > 20050316 | Ryan > 20050322 | Phil > 20050330 | William > 20050415 | Mary > 20050428 | Susan > 20050503 | Jim > > and I want to run a query that returns a *count* of the number of users > *each month*, ordered by year and *month*, with an additional column > that is a *running total of the count*, as in: In 8.4, you'll have direct SQL support for this using OLAP a.k.a. windowing functions, so don't build too many of these dodgy hacks into your application. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Job scheduling in Postgre
Robert Treat wrote: On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: I have certain jobs to be executed automatically at a given interval of time in the postgre SQL database. Is their any utility/feature available in Postgre to do so. No, use the scheduler from the OS, CRON for example (UNIX). There is a database level schedular called (iirc) pgAgent, which comes bundled with pgAdmin. I think it's so well hidden because it comes as a part of a tool which is only used by a small subset of the community. I had hopes that it might follow autovacuums path and get moved into a contrib module and possibly integrated into the backend some day, but I haven't seen much push in that direction. What is everyone using instead of pgAdmin? In production I only use ssh and psql, but in development I do use pgAdmin.
Re: [GENERAL] Job scheduling in Postgre
On Thu, Dec 4, 2008 at 3:54 PM, Jason Long <[EMAIL PROTECTED]> wrote: > > What is everyone using instead of pgAdmin? > In production I only use ssh and psql, but in development I do use pgAdmin. I do it all with cron and psql / pg_backup / bash -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY error with null date
Using 8.3.3 I am trying to import a CSV file using the following copy command. copy billing.contact from 'c:/export/contact.csv' with delimiter as ',' null as '' csv quote as '"'; The following record record causes an error because the third field, "", is a null date and causes the error following the record. How can I change the copy command above so that a null date or number will be imported as null? I do not care if empty strings are imported as an empty string or a null. Bill "IASAcctSys","09/09/1995","",... ERROR: invalid input syntax for type date: "" CONTEXT: COPY contact, line 6, column date: "" ** Error ** ERROR: invalid input syntax for type date: "" SQL state: 22007 Context: COPY contact, line 6, column date: "" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY error with null date
On Thu, 2008-12-04 at 19:35 -0700, Bill Todd wrote: > Using 8.3.3 I am trying to import a CSV file using the following copy > command. > > copy billing.contact from 'c:/export/contact.csv' > with delimiter as ',' > null as '' > csv quote as '"'; > > The following record record causes an error because the third field, "", > is a null date and causes the error following the record. How can I > change the copy command above so that a null date or number will be > imported as null? I do not care if empty strings are imported as an > empty string or a null. null as IS NULL > > Bill > > "IASAcctSys","09/09/1995","",... > > > ERROR: invalid input syntax for type date: "" > CONTEXT: COPY contact, line 6, column date: "" > > ** Error ** > > ERROR: invalid input syntax for type date: "" > SQL state: 22007 > Context: COPY contact, line 6, column date: "" > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
