[SQL] Function To Log Changes
Hi All, I have been tinkering with a function to log the changes made on any column through a function and trigger. However, I cant think of a way to make this work through pl/pgsql. Any one have any ideas, or is it just not possible? SNIP create or replace function logchange2() returns OPAQUE as ' DECLARE columnname record; c2 VARCHAR(64); BEGIN /* Cycle through the column names so we can find the changes being made */ FOR columnname IN SELECT attname FROM pg_attribute, pg_type WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP c2 := CAST(columnname.attname AS VARCHAR(64)); /* here lies the problem. How would I make plpgsql see OLD.columnname in a dynamic fashion. I know this wont work whats below, but I am just trying to express what I am trying to do */ IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN /* IF CHANGED DO SOMETHING */ RAISE NOTICE ''Update on column %'', c2; END IF; END LOOP; return NULL; END; ' LANGUAGE plpgsql; create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE PROCEDURE logchange2(); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] left outer join on more than 2 tables?
At the risk of being wrong (I'm always ready to learn something new) - and seemingly I'm only too happy to be wrong!... And... it might even be that it is exactly the same result - but I would have proposed; SELECT R.region_name, Count(*) AS RegionComplaints FROM Region AS R LEFT JOIN City AS Ci LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id ON R.id = C.region_id GROUP BY R.region_name; Gavin. On 17/06/2009, at 7:25 AM, Richard Broersma wrote: On Tue, Jun 16, 2009 at 1:59 PM, Carol Cheung wrote: I would like to find the counts of complaints by region and I would like all regions to be displayed, regardless of whether or not complaints exist for that region. Is left outer join what I'm looking for? SELECT R.region_name, Count(*) AS RegionComplaints FROM Region AS R LEFT JOIN City AS Ci ON R.id = C.region_id LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id GROUP BY R.region_name; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql As always Please contact me if I can be of any further assistance. Gavin "Beau" Baumanis Senior Application Developer PalCare P/L 657 Nicholson Street Carlton North Victoria, Australia, 3054 E: b...@palcare.com.au P: +61 -3 9380 3513 M: +61 -438 545 586 W: http://www.palcare.com.au -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] extracting from epoch values in pgsql
Hi folks,
I have a db that I need to draw some stats from. The db itself is from the
web application moodle which, perhaps to be cross-platform, uses unix epoch
times stored as integers throughout (see table description at end of mail).
I'd like to query some stats based on the appearance of objects over time,
ideally per month.
If the "time" were a pgsql timestamp, I'd probably do:
SELECT count(id), EXTRACT('month' FROM TIMESTAMP time) AS logmonth,
EXTRACT('year' FROM TIMESTAMP time) AS logyear
FROM mdl_log
WHERE action='login'
GROUP BY logmonth,logyear;
but it's an epoch time, so I need to convert to a datestamp and then run
EXTRACT on that (as far as I can see. I can do the conversion easily
enough but I can't then pass that to extract(). I've tried:
SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time *
INTERVAL '1 second'))
FROM mdl_log;
SELECT time, to_timestamp(time) AS ts, EXTRACT('months',to_timestamp(time))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
I also tried doing the extract on the alias "ts".
Am I doing something wrong here? Is this possible or do I need to approach
it in a different way? I've looked through the manual but I can't see a
way to convert epoch->timestamp and then use it further.
Thanks in advance,
Gavin
moodle-01-01-2009=# \d mdl_log
Table "public.mdl_log"
Column | Type | Modifiers
++--
id | integer| not null default
nextval('mdl_log_id_seq'::regclass)
time | integer| not null default 0
userid | integer| not null default 0
ip | character varying(15) | not null default ''::character varying
course | integer| not null default 0
module | character varying(20) | not null default ''::character varying
cmid | integer| not null default 0
url| character varying(100) | not null default ''::character varying
info | character varying(255) | not null default ''::character varying
action | character varying(40) | not null default ''::character varying
Indexes:
"mdl_log_pkey" PRIMARY KEY, btree (id)
"mdl_log_act_ix" btree (action)
"mdl_log_cmi_ix" btree (cmid)
"mdl_log_coursemoduleaction_idx" btree (course, module, action)
"mdl_log_tim_ix" btree ("time")
"mdl_log_usecou_ix" btree (userid, course)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting from epoch values in pgsql
On Thu, 17 Sep 2009, Frank Bax wrote:
> Gavin McCullagh wrote:
>> SELECT time, to_timestamp(time) AS ts,
>> EXTRACT('months',to_timestamp(time)) FROM mdl_log;
>> ERROR: syntax error at or near ","
>> LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
>
> Try replacing extract('month',value) with extract('months' from value)
Makes no difference whether month or months:
moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
extract('month','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...ct time, to_timestamp(time) AS ts, extract('month','to_times...
^
moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
extract('months','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months','to_times...
^
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting from epoch values in pgsql
On Thu, 17 Sep 2009, Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Frank Bax wrote:
>
> > Gavin McCullagh wrote:
> >> SELECT time, to_timestamp(time) AS ts,
> >> EXTRACT('months',to_timestamp(time)) FROM mdl_log;
> >> ERROR: syntax error at or near ","
> >> LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
> >
> > Try replacing extract('month',value) with extract('months' from value)
>
> Makes no difference whether month or months:
Actually, I pasted a different query, but based on the one in my last
email:
moodle-01-01-2009=# SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' +
mdl_log.time * INTERVAL '1 second'))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + ...
^
moodle-01-01-2009=# SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' +
mdl_log.time * INTERVAL '1 second'))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + m...
^
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting from epoch values in pgsql
On Thu, 17 Sep 2009, Osvaldo Kussama wrote:
> From manual:
> http://www.postgresql.org/docs/current/interactive/functions-datetime.html
>
> date_part('month',to_timestamp(time))
> or
> extract(month from to_timestamp(time))
Gah. I don't know I missed that. This works fine.
SELECT extract(month from to_timestamp(time))
FROM mdl_log;
Many thanks,
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] extracting from epoch values in pgsql
Hi folks,
I have a db that I need to draw some stats from. The db itself is from the
web application moodle which, perhaps to be cross-platform, uses unix epoch
times stored as integers throughout (see table description at end of mail).
I'd like to query some stats based on the appearance of objects over time,
ideally per month.
If the "time" were a pgsql timestamp, I'd probably do:
SELECT count(id), EXTRACT('month' FROM TIMESTAMP time) AS logmonth,
EXTRACT('year' FROM TIMESTAMP time) AS logyear
FROM mdl_log
WHERE action='login'
GROUP BY logmonth,logyear;
but it's an epoch time, so I need to convert to a datestamp and then run
EXTRACT on that (as far as I can see. I can do the conversion easily
enough but I can't then pass that to extract(). I've tried:
SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time *
INTERVAL '1 second'))
FROM mdl_log;
SELECT time, to_timestamp(time) AS ts, EXTRACT('months',to_timestamp(time))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
I also tried doing the extract on the alias "ts".
Am I doing something wrong here? Is this possible or do I need to approach
it in a different way? I've looked through the manual but I can't see a
way to convert epoch->timestamp and then use it further.
Thanks in advance,
Gavin
moodle-01-01-2009=# \d mdl_log
Table "public.mdl_log"
Column | Type | Modifiers
++--
id | integer| not null default
nextval('mdl_log_id_seq'::regclass)
time | integer| not null default 0
userid | integer| not null default 0
ip | character varying(15) | not null default ''::character varying
course | integer| not null default 0
module | character varying(20) | not null default ''::character varying
cmid | integer| not null default 0
url| character varying(100) | not null default ''::character varying
info | character varying(255) | not null default ''::character varying
action | character varying(40) | not null default ''::character varying
Indexes:
"mdl_log_pkey" PRIMARY KEY, btree (id)
"mdl_log_act_ix" btree (action)
"mdl_log_cmi_ix" btree (cmid)
"mdl_log_coursemoduleaction_idx" btree (course, module, action)
"mdl_log_tim_ix" btree ("time")
"mdl_log_usecou_ix" btree (userid, course)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Which version of PostgreSQL should I use.
Hi there, Let me first preface this with, I am not a PostgreSQL admin. I am a web developer who happens to use PSQL as the back-end for my company's app. We did have a dedicated DBA / system admin - but he has recently resigned. I know enough about psql - to be able to create / drop databases... and enough about SQL to get stuff in and out of the database. Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail. We're currently using psql 8.1 and are on the way to upgrading to 8.4. This is a process that the last DBA had us start. And we've slowly been going through our code, getting rid of implicit casts as errors appear. I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement. And subsequently have some questions, please. Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases. My initial thought is; We should upgrade to the latest stable version - whatever that is; But is the answer that simple? What we do we also need to take into account? I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that we had to upgrade to 8.3 first. So I guess I am hoping that someone might just simply know - or be able to pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1 AS always - thanks in advance for any assistance you might be able to give us! Gavin. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Which version of PostgreSQL should I use.
Just thought I would update this myself. Last night I managed to have a chat with some people about this on IRC. Turns out we're in pretty good shape. We already know about the changes to implicit casting and we have always used UTF-8 for encoding, too. So the changes from 8.4 don't seem to be of a worry for us. (I am sure that we will find SQL hidden in our app somewhere that will need to be modified for implicit casting...and we'll just fix those as we find them.) Otherwise it seems like a simple case of using the 9.04 pg_dump / (pg_dumpall) application and restoring. I also read chapters 15 / 24 of the excellent documentation about running both 8.1 and 9.04 at the same time and migrating a database at a time. So, I suppose unless there is something obvious, (to someone else) - we have all the information we need. Gavin "Beau" Baumanis On 17/05/2011, at 9:04 AM, Gavin Baumanis wrote: > Hi there, > > Let me first preface this with, I am not a PostgreSQL admin. > I am a web developer who happens to use PSQL as the back-end for my company's > app. > We did have a dedicated DBA / system admin - but he has recently resigned. > I know enough about psql - to be able to create / drop databases... and > enough about SQL to get stuff in and out of the database. > Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail. > > > We're currently using psql 8.1 and are on the way to upgrading to 8.4. > This is a process that the last DBA had us start. > And we've slowly been going through our code, getting rid of implicit casts > as errors appear. > > I have now been asked to start replicating our databases between servers - as > a hot-copy / redundancy improvement. > And subsequently have some questions, please. > > Is there are a particular version of PostgreSQL that we should be "aiming" to > upgrade to that provides for synching of databases. > My initial thought is; > We should upgrade to the latest stable version - whatever that is; > But is the answer that simple? > > What we do we also need to take into account? > I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that > we had to upgrade to 8.3 first. > > So I guess I am hoping that someone might just simply know - or be able to > pint me in the correct direction for some information about what's in what > version and any upgrade requirements to get to XXX from 8.1 > > AS always - thanks in advance for any assistance you might be able to give > us! > > Gavin. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
How about this (that does not require special functions nor triggers: DROP TABLE IF EXISTS val; CREATE TABLE val ( id int, ts timestamp ); INSERT INTO val VALUES (0, '1-Jan-2010 20:00'), (1, '1-Jan-2010 20:03'), (1, '1-Jan-2010 20:04'), (0, '1-Jan-2010 20:05'), (1, '1-Jan-2010 20:05'), (0, '1-Jan-2010 20:08'), (1, '1-Jan-2010 20:09'), (0, '1-Jan-2010 20:10'); WITH val_first AS ( SELECT id, min(ts) AS ts FROM val GROUP BY id ) SELECT v.id, v.ts::time FROM val v, val_first vf WHERE v.id = vf.id AND EXTRACT(EPOCH FROM v.ts - vf.ts)::int % 300 = 0 ORDER BY id, ts; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
On 06/07/11 01:52, John Fabiani wrote: Hi, I have a special need to create a sequence like function. "O-20110704 -2" which is "O" for order (there are other types) "20110704" is for July 4, 2011 '2' the second order of the day for July 4, 2011 I of course can get the type and date. What I don't know is how to get is the last number. It would seem to be that I would need a loop to determine if the next number existed. LOOP --Check to see if the string exist in a table -- count = count +1 -- until I don't find the string END LOOP; but then I thought I could do something like for $1 in (select string from sometable) LOOP count = count + 1 or something like this for i in 1..999 LOOP -- check for the existence of the string in a table using 'i' -- there will never be 999 orders in one day. END LOOP So here is the question what would be the best way for a multi-user system? If someone has a better thought - it would be helpful. BTW I did NOT design the number - in fact it seems silly to me. Johnf Hi John, How about using a table to hold the latest sequence for each order type and date, along with a function to insert a new order? (I've included the code to test the idea and the results, I am using 9.1beta2, but it should not make any difference - I think!): DROP TABLE IF EXISTS my_order; DROP TABLE IF EXISTS order_sequence; CREATE TABLE my_order ( order_num text PRIMARY KEY, payload text ); CREATE TABLE order_sequence ( typeint, day date, seq int NOT NULL, PRIMARY KEY (type, day) ); CREATE OR REPLACE FUNCTION create_my_order ( IN typeint, IN day date, IN payload text ) RETURNS VOID AS $$ DECLARE v_order_num text; v_seq_old int; v_seq_new int; BEGIN SELECT os.seq FROM order_sequence os WHERE os.type = create_my_order.type AND os.day = create_my_order.day INTO v_seq_old; IF v_seq_old IS NULL THEN v_seq_new := 1; INSERT INTO order_sequence(type, day, seq) VALUES (type, day, v_seq_new); ELSE v_seq_new := v_seq_old + 1; UPDATE order_sequence AS os SET seq = v_seq_new WHERE os.type = create_my_order.type AND os.day = create_my_order.day; END IF; v_order_num := type::text || '-' || to_char(day, 'YYMMDD') || '-' || v_seq_new::text; INSERT INTO my_order(order_num, payload) VALUES (v_order_num, payload); END; $$ LANGUAGE plpgsql VOLATILE ; SELECT create_my_order (0, '2010-03-24', 'order #1 details'); SELECT create_my_order (0, '2010-03-24', 'order #2 details'); SELECT create_my_order (0, '2010-06-15', 'order #3 details'); SELECT create_my_order (5, '2010-03-24', 'order #4 details'); SELECT create_my_order (0, '2010-06-15', 'order #5 details'); SELECT create_my_order (3, '2010-06-14', 'order #6 details'); TABLE order_sequence; TABLE my_order; // This outputs the following: type |day | seq --++- 0 | 2010-03-24 | 2 5 | 2010-03-24 | 1 0 | 2010-06-15 | 2 3 | 2010-06-14 | 1 (4 rows) order_num | payload +-- 0-100324-1 | order #1 details 0-100324-2 | order #2 details 0-100615-1 | order #3 details 5-100324-1 | order #4 details 0-100615-2 | order #5 details 3-100614-1 | order #6 details (6 rows)
Re: [SQL] interesting sequence (Correctin)
On 06/07/11 21:47, Gavin Flower wrote: I forgot the format required of the order number, so to get the full yesr, I should have used: to_char(day, 'MMDD') [...] v_order_num := type::text || '-' || to_char(day, 'YYMMDD') || '-' || v_seq_new::text; [...] Cheers, Gavin
Re: [SQL] using explain output within pgsql
On 11/07/11 08:18, Pavel Stehule wrote: 2011/7/10 Uwe Bartels: Hi Pavel, is it posible to get this running even with dynamic sql? I didn't write that. I'm using execute to run this create table probably yes postgres=# do $$ declare x text; begin execute e'explain(format yaml) select * from data where value = \'a\'' into x; raise notice '%', x; end; $$ language plpgsql; NOTICE: - Plan: Node Type: "Seq Scan" Relation Name: "data" Alias: "data" Startup Cost: 0.00 Total Cost: 23.38 Plan Rows: 5 Plan Width: 46 Filter: "((value)::text = 'a'::text)" DO [...] I find that I understand things better if I rephrase things, so I took Pavel's code and converted it to use variables so I could see more clearly what is happening. I think using variables makes the use of 'execute' more understandable. I hope this version is of value to to others, I have included all the code required to run it as a working example. CREATE TABLE data ( id int, value text ); INSERT INTO data (id, value) VALUES (1, 'a'), (2, 'b'); do $$ declare v_sql_querytext; v_sql_explain text; v_result text; begin v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\''; v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query; execute v_sql_explain into v_result; raise notice 'v_result: %', v_result; end; $$ language plpgsql; Cheers, Gavin
Re: [SQL] sorting months according to fiscal year
On 23/08/11 01:27, Enzen user wrote:
Hi
I have to rearrange the months according to the fiscal year i.e from April
to march and use the same in the order by clause of a query.
I have written the following postgresql function for the same, but to_number
is returning an error.
Can you please tell me where i'm going wrong?
Instead of the function to_number can you suggest any other function that
will convert a particular month to its corresponding month number(ex:
april=4 or jan=1)
CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$
DECLARE
BEGIN
CASE WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 4
WHEN 8 THEN 5
WHEN 9 THEN 6
WHEN 10 THEN 7
WHEN 11 THEN 8
WHEN 12 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
WHEN 3 THEN 12
ELSE 0
END;
$$ LANGUAGE plpgsql;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
You might find the following faster...
DROP FUNCTION IF EXISTS
sort_mont
(
to_number int
) ;
CREATE FUNCTION
sort_mont
(
to_number int
)
RETURNS numeric
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN 1 + (to_number + 8) % 12;
END;
$$;
SELECT sort_mont(1);
SELECT sort_mont(12);
/// output..
gavin=> \i modulus_stored_proc.sql
DROP FUNCTION
CREATE FUNCTION
sort_mont
---
10
(1 row)
sort_mont
---
9
(1 row)
gavin=>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On 13/01/12 05:56, David Johnston wrote: [...] Contrary to my earlier advice assigning a sequential ID (thus using a numeric TYPE) is one of the exceptions where you can use a number even though you cannot meaningfully perform arithmetic on the values. The reason you would use a numeric value instead of a character is that the value itself is arbitrary and the space required to store a number is less than the space required to store a string of the same length. There are many points-of-view regarding whether to use "serial" PRIMARY KEYs but regardless of whether you add one or not you should try and define a UNIQUE constraint on the table by using meaningful values. However, for things like Orders this is generally not possible and so you would want to generate a sequential identifier for every record. David J. Hmm... In any database I design, I deliberately keep primary keys quite separate from any user visible values. In order to minimise changes to the database resulting from business format changes, such as redoing the format of customer numbers for marketing purposes. Also, in a chain of parent child tables, the child only needs to know how to get its parent, it does not need to know its grandparents! One insurance package I worked on, had the primary key of a child table a concatenation of its parent's primary key with a unique field. So some child tables had multiple character field as their primary keys, potentially have keys of some 45 or more characters! I normally use integers for the primary key type. This makes keeping track of records in a program much easier. However, I do not usually expose these keys to users, and it would be rare (if ever) to have them as fields in search boxes. Cheers, Gavin
Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
On 13/09/12 09:44, Rodrigo Rosenfeld Rosas wrote: This is my first message in this list :) I need to be able to sort a query by column A, then B or C (which one is smaller, both are of the same type and table but on different left joins) and then by D. How can I do that? Thanks in advance, Rodrigo. I created a script 'variable_sort_order.sql'... DROP TABLE IF EXISTS tabc; CREATE TABLE tabc ( id serial PRIMARY KEY, a int, b int, c int, d int ); INSERT INTO tabc (a, b, c, d) VALUES (generate_series(1, 6), 3 * random(), 3 * random(), generate_series(1, 5)); SELECT * FROM tabc t ORDER BY t.a, LEAST(t.b, t.c), t.d /**/;/**/ gavin=> \i variable_sort_order.sql DROP TABLE psql:variable_sort_order.sql:10: NOTICE: CREATE TABLE will create implicit sequence "tabc_id_seq" for serial column "tabc.id" psql:variable_sort_order.sql:10: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tabc_pkey" for table "tabc" CREATE TABLE INSERT 0 30 id | a | b | c | d +---+---+---+--- 25 | 1 | 0 | 3 | 5 7 | 1 | 1 | 1 | 2 1 | 1 | 3 | 2 | 1 13 | 1 | 2 | 3 | 3 19 | 1 | 2 | 2 | 4 8 | 2 | 0 | 2 | 3 14 | 2 | 0 | 2 | 4 26 | 2 | 2 | 1 | 1 20 | 2 | 1 | 2 | 5 2 | 2 | 2 | 2 | 2 3 | 3 | 0 | 2 | 3 21 | 3 | 1 | 1 | 1 27 | 3 | 1 | 3 | 2 15 | 3 | 3 | 1 | 5 9 | 3 | 3 | 2 | 4 4 | 4 | 0 | 1 | 4 10 | 4 | 3 | 0 | 5 16 | 4 | 1 | 3 | 1 22 | 4 | 1 | 1 | 2 28 | 4 | 2 | 3 | 3 11 | 5 | 0 | 1 | 1 17 | 5 | 0 | 3 | 2 23 | 5 | 1 | 1 | 3 5 | 5 | 3 | 1 | 5 29 | 5 | 3 | 2 | 4 18 | 6 | 2 | 0 | 3 12 | 6 | 1 | 1 | 2 24 | 6 | 3 | 1 | 4 30 | 6 | 1 | 3 | 5 6 | 6 | 3 | 2 | 1 (30 rows)
Re: [SQL] checking the gaps in intervals
On 06/10/12 11:42, Anton Gavazuk wrote:
Hi dear community,
Have probably quite simple task but cannot find the solution,
Imagine the table A with 2 columns start and end, data type is date
start end
01 dec. 10 dec
11 dec. 13 dec
17 dec. 19 dec
.
If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
Thanks,
Anton
If the periods _NEVER_ overlap, you can also use this this approach
(N.B. The indexing of the period table here, can be used in my previous
solution where I had not considered the indexing seriously!)
Cheers,
Gavin
DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS target;
CREATE TABLE period
(
start_date date,
end_datedate,
PRIMARY KEY (start_date, end_date)
);
CREATE INDEX ON period (end_date);
INSERT INTO period (start_date, end_date) VALUES
('2012-11-21', '2012-11-29'),
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');
TABLE period;
CREATE TABLE target
(
start_date date,
end_datedate
);
INSERT INTO target (start_date, end_date) VALUES
('2012-12-01', '2012-12-01'),
('2012-12-02', '2012-12-02'),
('2012-12-09', '2012-12-09'),
('2012-12-10', '2012-12-10'),
('2012-12-01', '2012-12-09'),
('2012-12-01', '2012-12-10'),
('2012-12-01', '2012-12-12'),
('2012-12-01', '2012-12-13'),
('2012-12-02', '2012-12-09'),
('2012-12-02', '2012-12-12'),
('2012-12-03', '2012-12-11'),
('2012-12-02', '2012-12-13'),
('2012-12-02', '2012-12-15'),
('2012-12-01', '2012-12-18');
SELECT
t.start_date,
t.end_date
FROM
target t
ORDER BY
t.start_date,
t.end_date
/**/;/**/
SELECT
t1.start_date AS "Target Start",
t1.end_date AS "Target End",
(t1.end_date - t1.start_date) + 1 AS "Duration",
p1.start_date AS "Period Start",
p1.end_date AS "Period End"
FROM
target t1,
period p1
WHERE
(
SELECT
SUM
(
CASE
WHEN p2.end_date > t1.end_date
THEN p2.end_date - (p2.end_date - t1.end_date)
ELSE p2.end_date
END
-
CASE
WHEN p2.start_date < t1.start_date
THEN p2.start_date + (t1.start_date -
p2.start_date)
ELSE p2.start_date
END
+ 1
)
FROM
period p2
WHERE
p2.start_date <= t1.end_date
AND p2.end_date >= t1.start_date
) = (t1.end_date - t1.start_date) + 1
AND p1.start_date <= t1.end_date
AND p1.end_date >= t1.start_date
ORDER BY
t1.start_date,
t1.end_date,
p1.start_date
/**/;/**/
Re: [SQL] checking the gaps in intervals
On 06/10/12 11:42, Anton Gavazuk wrote:
Hi dear community,
Have probably quite simple task but cannot find the solution,
Imagine the table A with 2 columns start and end, data type is date
start end
01 dec. 10 dec
11 dec. 13 dec
17 dec. 19 dec
.
If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
Thanks,
Anton
How about something like the following?
Cheers,
Gavin
DROP TABLE IF EXISTS period;
CREATE TABLE period
(
id serial PRIMARY KEY,
start_date date,
end_datedate
);
INSERT INTO period (start_date, end_date) VALUES
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');
WITH RECURSIVE
slot (start_date, end_date) AS
(
SELECT
p1.start_date,
p1.end_date
FROM
period p1
WHERE
NOT EXISTS
(
SELECT
1
FROM
period p2
WHERE
p1.start_date = p2.end_date + 1
)
UNION ALL
SELECT
s1.start_date,
p3.end_date
FROM
slot s1,
period p3
WHERE
p3.start_date = s1.end_date + 1
AND p3.end_date > s1.end_date
)
SELECT
s3.start_date,
MIN(s3.end_date)
FROM
slot s3
WHERE
s3.start_date <= '2012-12-01'
AND s3.end_date >= '2012-12-18'
GROUP BY
s3.start_date
/**/;/**/.
Re: [SQL] checking the gaps in intervals
On 07/10/12 14:30, Jasen Betts wrote:
On 2012-10-05, Anton Gavazuk wrote:
Hi dear community,
Have probably quite simple task but cannot find the solution,
Imagine the table A with 2 columns start and end, data type is date
start end
01 dec. 10 dec
11 dec. 13 dec
17 dec. 19 dec
.
If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
perhaps you can do a with-recursive query ?
create temp table Gavazuk
(id serial primary key, start date ,fin date);
insert into Gavazuk (start,fin)
values ('2012-12-01','2012-12-10')
,('2012-12-11','2012-12-13')
,('2012-12-17','2012-12-19');
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f+1 between start and fin and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as non-contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
Cunning, also much more elegant and concise than my solutions!
Cheers,
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strange corruption?
On 28/12/12 03:27, John Fabiani wrote: Hi, I have the following statement in a function. UPDATE orderseq SET orderseq_number = (orderseq_number + 1) WHERE (orderseq_name='InvcNumber'); All it does is update a single record by incrementing a value (int). But it never completes. This has to be some sort of bug. Anyone have a thought what would cause this to occur. To my knowledge it was working and does work in other databases. Johnf It might help if you give the table definition. Definitely important: is the exact version of PostgreSQL used, and the operating system. Cheers, Gavin
Re: [SQL] strange corruption?
On 28/12/12 05:44, John Fabiani wrote:
On 12/27/2012 08:21 AM, Gavin Flower wrote:
On 28/12/12 03:27, John Fabiani wrote:
Hi,
I have the following statement in a function.
UPDATE orderseq
SET orderseq_number = (orderseq_number + 1)
WHERE (orderseq_name='InvcNumber');
All it does is update a single record by incrementing a value (int).
But it never completes. This has to be some sort of bug. Anyone
have a thought what would cause this to occur. To my knowledge it
was working and does work in other databases.
Johnf
It might help if you give the table definition.
Definitely important: is the exact version of PostgreSQL used, and
the operating system.
Cheers,
Gavin
9.1.6 updated 12.22.2012, openSUSE 12.1 64 bit Linux
CREATE TABLE orderseq
(
orderseq_id integer NOT NULL DEFAULT
nextval(('orderseq_orderseq_id_seq'::text)::regclass),
orderseq_name text,
orderseq_number integer,
orderseq_table text,
orderseq_numcol text,
CONSTRAINT orderseq_pkey PRIMARY KEY (orderseq_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE orderseq
OWNER TO admin;
GRANT ALL ON TABLE orderseq TO admin;
GRANT ALL ON TABLE orderseq TO xtrole;
COMMENT ON TABLE orderseq
IS 'Configuration information for common numbering sequences';
Johnf
I had a vague idea what the problem might be, but your table definition
proved I was wrong! :-)
This won't sole your problem, but I was wondering why you don't use a
simpler definition like:
CREATE TABLE orderseq
(
orderseq_id SERIAL PRIMARY KEY,
orderseq_name text,
orderseq_number integer,
orderseq_tabletext,
orderseq_numcol text
);
SERIAL automatically attaches the table's own sequence and does a
DEFAULT nextval
PRIMARY KEY implies NOT NULL & UNIQUE
OIDS=FALSE is the default
My personal preference is just to use the name 'id' for the tables own
primary key, and only prepend the table name when it is foreign key -
makes them stand out more.
Cheers,
Gavin
Re: [SQL] Advice for index design
On 11/04/13 10:30, JORGE MALDONADO wrote: I have a table of artists with fields like the ones below: * Name * Birthday * Sex (male/female) Our application offers a catalog of artists where a user can select a range of birthdays and/or sex. For example, a user can get an artists catalog for those male artists who were born between May 1, 1970 and May 1, 1990 ordered by birthday and, within each birthday date, ordered by name. I can think of defining one index for birthday, one index for name, and one index for sex. Also, I can think of defining a compound index for birthday + name. Also there could be a compound index for sex + name. Another option could be a compound index for birthday + sex + name. There are many possible combinations. What is a good index design approach? Maybe, setting simple separate indexes (one for each field) would work fine if I need to retrieve data in different combinatios, but I am not sure. Maybe compound indexes is better. I will very much appreciate your advice. Respectfully, Jorge Maldonado W.r.t. sex what about those people who: 1. are neither 2. are both 3. not specified 4. don't want to tell you 5. have changed their gender mid career About 0.5% children are born in the folowing categories: 1. ambiguous genitalia 2. both 3. none 4. genitalia that doesn't match their brain wiring 5. born looking like a female, but change to male at puberty I once saw an article about an island were about 10% of males were born looking like a female, but changed to male at puberty. It was so common and well known that parents simply changed their clothes renamed them, and started treating them as male. So I did a bit of research, exact percentages depend on definitions & fashions at the time of birth and what research you read. Fortunately, as far as I know, no one in my immediate family falls into this group. Cheers, Gavin
Re: [SQL] DateDiff() function
On 11/07/13 17:17, Huan Ruan wrote:
Hi Guys
We are migrating to Postgres. In the current system, we use datediff()
function to get the difference between two dates, e.g. datediff
(month, cast('2013-01-01' as timestamp), cast('2013-02-02'
as timestamp) returns 1.
I understand that Postgres has Interval data type so I can achieve the
same with Extract(month from Age(date1, date2)). However, I try to
make it so that the existing SQL can run on both databases without
changes. One possible way is to add a datediff function to Postgres,
but the problem is that month/day/year etc is a keyword not a string
like 'month'. I noticed that Postgres seems to convert Extract(month
from current_timestamp) to date_part('month', current_timestamp), you
can also do Extract('month' from current_timestamp). So it seems
internally, Postgres can do the mapping from month to 'month'. I was
wondering if there is a way for me to do the same for the datediff()
function? Any other ideas?
Thanks
Huan
Purely out of curiosity, could you tell us what database software you
are moving from, as well as a rough idea of the size of database, type
and volume of database queries?
It would also be of interest to know what postgres features in
particular were the biggest motivations for change, and any aspects that
gave you cause for concern - obviously overall, it must have come across
as being better .
I strongly suspect that answering these questions will have no direct
bearing on how people will answer your query! :-)
Cheers,
Gavin
[SQL] SELECT MAX returns wrong value
Hi Everyone, Sorry if I am missing something obvious but I think I have found a bug. If I perform the following SQL SELECT MAX(column) FROM table WHERE expression and there is no match, Postgres returns a record count of 1. There is no value in max, it is NULL. Thus trying to perform some "other" tasks based on if a record was returned or not is proving a little difficult in this instance. We are using 8.1.9 on Debian. Thanks for any help you might be able to offer. -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis eClinic Pty Ltd ABN 80 092 450 274 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.eclinic.com.au begin:vcard fn:Beau n:Baumanis;Gavin org:PalCare Pty. Ltd. adr:;;657 Nicholson Street;Carlton North;Melbourne;3054;Australia email;internet:[EMAIL PROTECTED] title:Application Developer tel;work:+61-3 9381-4567 tel;cell:+61-438-545-586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Microsoft Certificed Systems Engineer (MCSE)=0D=0A= Post Graduate Certificate in IT=0D=0A= Certificate III Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] UTF characters compromising data import.
Hi Everyone, I am trying to import some data (provided to us from an external source) from a CSV file using "\copy " But I get the following error message; invalid byte sequence for encoding "UTF8": 0xfd HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". I understand the error message - but what I don't know is what I need to set the encoding to - in order to import / use the data. As always - thanks in advance for any help you might be able to provide. Gavin "Beau" Baumanis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UTF characters compromising data import.
Hi and thanks for the replies, I have had some luck. I did find the encoding used originally to create the text files I am trying to import. I have managed to use the client_encoding environmental variable and then successfully did manage to import the data. Gavin. On 12/02/2011, at 8:15 PM, Jasen Betts wrote: > On 2011-02-08, Gavin Beau Baumanis wrote: > >> I understand the error message - but what I don't know is what I >> need to set the encoding to - in order to import / use the data. > > if you run it through > > iconv --from-code=ASCII -to-code=UTF8 -c > > it'll strip out all the non-ascii symbols, without knowing the > encoding it's impossible to assign any useful meaning to them. > This step may render your data useless, it would be much better to > find out what the encoding should be. > > perhaps you can figure it out by observation? > > -- > ⚂⚃ 100% natural > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function description
Hi Bart, Bart Degryse wrote: >>>> Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> >>> To see how e.g. \dC etc work start psql with -E >>> >> I'm sorry, but I don't have commandline access to the database. That would >> require an amount of trust and a level of competence our ICT department is incapable of. > >Well, if you have access to prt 5432 (or whatever you are using) on the >DB host you can run psql locally: psql -h All I have is my desktop Windows pc. I work on the database using EMS SQL Manager for PostgreSQL. There I can run whatever statement I want, but it's not psql. What do you exactly mean with "run psql locally"? Should I be able to run some psql.exe from my windows cmd environment. Or should I start something else first? The windows installer, available at; http://www.postgresql.org/ftp/win32/ Allows you to install the DB and / OR the tools including psql So you can just install the psql command line tool onto your local machine. Also, Could you ask for a restricted account on the box that runs the DB? Ie an account that only has access to the DB tools and DB you require to use? I use ssh with a limited account to access a postgesql DB running on debian. My account on the server only allows me access to my "home" directory (think your own documents and settings folder - if you're a windows only user...) on the server and the DB's that I have a requirement to use. >As a last resort you can always see how they work on a local copy, then >cut + paste the SQL. Here too, I don't know what you mean exactly with "a local copy". Download the the whole PostreSQL package; http://www.postgresql.org/ftp/win32/ The following link however is a good place to start it contains some really good information and the same link above to download the windows version of PostgreSQL. http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html Then run an instance of PostgreSQL on your desktop PC. That way you have "local" access to the DB and all of it's tools too. You can "play around" with it to your hearts content, run SQL "scenarios" / test functions etc on your own copy of the DB. Then when you're happy - perform the "locally tested" SQL upon your production DB via your admin tool of choice. Hope this helps. -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis PalCare Pty Ltd 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.palcare.com.au begin:vcard fn:Gavin 'Beau' Baumanis n:Baumanis;Gavin 'Beau' org:PalCare Pty. Ltd adr:;;657 Nicholson Street;Carlton North;;3054;Australia email;internet:[EMAIL PROTECTED] title:Senior Application Developer tel;work:+61 -3 9381 4567 tel;cell:+61 -438 545 586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Certified Microsoft Engineer (MCSE)=0D=0A= Post Graduate Certificate( IT Systems)=0D=0A= Trade Qualified: Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Function description
Bart, You just need to put forward an appropriate case. It isn't a case of I would like these things. It is, I MUST have these things in order to perform my job. I MUST have a local / development database for testing and educational / learning purposes - unless of course you would like me to use the production server for testing? It isn't up to ICT to tell you what you can and can't have... Sure, they have a role to play - to keep the servers / desktops running - but they don't make policy - just enforce it. you can bet that in order to perform their tasks they have a collection of tools and programs they use. You NEED / MUST have these things in order to successfully do yours. I would speak to my supervisor / manager and get him/her to direct the ICT group to perform the necessary tasks so as to allow you to competently complete your duties - if you don't get any joy out of talking to them yourself first. Always give them the benefit of doubt and ask first. You just may well be surprised. Not to mention it you gives some ammunition with your manager to say; "I have already asked ICT myself but they are unwilling to assist - can you direct them to provide the things I need?". Bart Degryse wrote: >>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 12:46 >>> >The windows installer, available at; >http://www.postgresql.org/ftp/win32/ >Allows you to install the DB and / OR the tools including psql >So you can just install the psql command line tool onto your local machine. I can't install applications on my desktop pc ICT won't install that application on my desktop pc... that would require an amout of trust and... >Also, >Could you ask for a restricted account on the box that runs the DB? >Ie an account that only has access to the DB tools and DB you require to use? I can certainly ask, but I will not get it...that would require an amout of trust and... -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis PalCare Pty Ltd 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.palcare.com.au begin:vcard fn:Gavin 'Beau' Baumanis n:Baumanis;Gavin 'Beau' org:PalCare Pty. Ltd adr:;;657 Nicholson Street;Carlton North;;3054;Australia email;internet:[EMAIL PROTECTED] title:Senior Application Developer tel;work:+61 -3 9381 4567 tel;cell:+61 -438 545 586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Certified Microsoft Engineer (MCSE)=0D=0A= Post Graduate Certificate( IT Systems)=0D=0A= Trade Qualified: Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Select into
Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. Thanks in advance for anything you might come up with. Warmest regards, Gavin Baumanis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
HI Gurjeet, You're right. But what information do you need to know? The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I am happy enough to give you a table schema, if that's required... but I just don't see why it would be needed - but of course am happy to be told something new! Thanks again. Gavin Baumanis On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote: On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <[EMAIL PROTECTED] > wrote: am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: > Hi Everyone, > > I have asked our DBA at work and h is not too sure either... so I > thought it best to on the list. > > Basically, what I am after is a way to copy the contents of one record > into another. > Something like select into; but where the destination record already > exists, as opposed to creating a new record. insert into select from where ... He specifically asked for where the destination record already exists, as opposed to creating a new record. I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns. Can you post your exact requirement? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Hi Everyone, I want to thank everyone for their help / suggestions... I really appreciate it. Though I think I have found a winner. craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Very elegant, very clean... Very nice! Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] dateformat issue
Hi Everyone,
I have a question about dates, please.
I am using Coldfusion and Postgres
I have a function that takes the ate entered in a form and converts it
into an odbc date format.
So the string ends up being;
{d '2008-04-10'}
Strangely, the function is used throughout the application and causes
no issues but on one particular template.
And this same templates is replicated for other clients - that
seemingly have no issue .
The SQL is simply
Insert into table (datefield) values ({d '2008-04-10'})
Now, I have read the docs and do not see the {d '2008-04-10'} format
as being a valid date data type...
But it works elsewhere in the application / on other sites - thus my
current state of confusion.
Thanks in advance for any ideas you might have.
Please contact me if you should have any questions.
Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.
E: [EMAIL PROTECTED]
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dateformat issue
Hi Aarni,
just so you know I am using the CF built-in function,
createodbcdate
here is the info from livedocs.
CreateODBCDate
Description
Returns a date in ODBC date format.
Category
Date and time functions
Syntax
CreateODBCDate(date)
so I pass in 2008-01-23
and I get back
{d '2008-01-23'}
The weirdness for me is that it works elsewhere in the application, I
am really stumped.
-Gavin.
On 10/04/2008, at 4:17 PM, Aarni Ruuhimäki wrote:
On Thursday 10 April 2008 03:14, Gavin 'Beau' Baumanis wrote:
Hi Everyone,
I have a question about dates, please.
I am using Coldfusion and Postgres
I have a function that takes the ate entered in a form and converts
it
into an odbc date format.
So the string ends up being;
{d '2008-04-10'}
Strangely, the function is used throughout the application and causes
no issues but on one particular template.
And this same templates is replicated for other clients - that
seemingly have no issue .
The SQL is simply
Insert into table (datefield) values ({d '2008-04-10'})
Hi,
The above is not a valid value for a date field. What function do
you use for
the conversion ?
#DateFormat(form.date, "-MM-DD")# ? Or perhaps a custom function /
conversion script ?
Which client (browser?) / platform produces the error ?
And just out of general interest, which cf-version and platform are
you
using ? Pg version ?
I use pg 8.x's on CentOS and Fedora with CF 5 Pro Linux and CFMX7
Standard. I
also heard that CFMX7+ would install and run ok on Ubuntu.
Best regards,
--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Please contact me if you should have any questions.
Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.
E: [EMAIL PROTECTED]
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Add a ROWCOUNT to the output of a select.
Hi Everyone, After spending some time searching through our good friend Mr. Google and the mailing list I found a post that provided me with a query that does just what I need. However, the query takes FOREVER and although this is stated in the original mail I thought I would ask if there was any advice that you might be able to provide to speed things up a little. And while the query does return over 27,000 rows in my case, I didn't expect it to take 9 minutes and 11 seconds! Please find the query below - and of course - thanks in advance for any assistance you might be able to provide me with! select (select count(*) from myTable as myCount where myCount.contactdate <= myTable.contactdate ) as rownum, contactdate from myTable where contactdate > '2007-06-30 23:59:59' order by contactdate; -Gavin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Find all instances of a column in the entire database.
Hi Everyone, I am hoping that you might be able to give me some assistance with the following task! I have a database with nearly 200 tables and I need to find all tables that contain a column of myColumnName. I was hoping there might be a built-in function for this task, but I have been unable to find any information through our good friend Mr. Google or by perusing the fine manual. Thanks in advance for any thoughts you might have. - Beau -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find all instances of a column in the entire database.
Hi depesz, Thanks very much! That works wonderfully well... Is this listed in the manual anywhere? because after two hours of reading, I didn't find it anywhere! None the less - thanks again. - Beau On 16/05/2008, at 11:56 PM, hubert depesz lubaczewski wrote: On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote: I am hoping that you might be able to give me some assistance with the following task! I have a database with nearly 200 tables and I need to find all tables that contain a column of myColumnName. select * from information_schema.columns where column_name = 'myColumnName'; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] distinct / group by assistance.
Hi Everyone, I am having some issues trying to create the required SQL that will allow me to return the results I am after. I have been trying various incarnations, using group by, sub-queries - albeit to no avail - for the past three hours. Consider the following simple SQL; select a.foo, b.bar, c.something from table1 a, inner join table2 b on b.id =a.id left outer join table3 on c.id = a.id If there a multiple rows of the same id in table1, I get all (multiple) rows - as you would expect - of course. What I need however, is only one row returned per instance a.id that is returned by the above query. I thought of using group by - but there are no calculated fields... and the real query contains 32 fields, which according to the errors I ran into while trying to get this working, would all need to be included in the group by clause. So my understanding of group by is obviously a little dodgy - and obviously not quite what I was expecting. I tried using a sub query and select distinct a.id. but that didn't exactly help either. I have deliberately included in my example the fact there is an outer join too - I am not sue if that matters or not... but just in case thought it prudent to include it here. Thanks in advance for any help you might have. Beau. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinct / group by assistance.
Hi Tom I am a nut. please find below my correct requirements. select a.foo, b.bar, c.something from table1 a, inner join table2 b on b.id =a.id left outer join table3 on c.id = a.id If there a multiple rows of the same id in table2,(one (a) to many (b) relationship) I get all (multiple) rows - as you would expect - of course. What I need however, is only one row returned per instance of a.id that is returned by the above query - the one with the greatest b.primaryKey would be ok. I thought of using group by - but there are no calculated fields... and the real query contains 32 fields, which according to the errors I ran into while trying to get this working, would all need to be included in the group by clause. So my understanding of group by is obviously a little dodgy - and obviously not quite what I was expecting. I tried using a sub query and select distinct a.id. but that didn't exactly help either. I have deliberately included in my example the fact there is an outer join too - I am not sue if that matters or not... but just in case thought it prudent to include it here. On 29/06/2008, at 1:43 AM, Tom Lane wrote: "Gavin 'Beau' Baumanis" <[EMAIL PROTECTED]> writes: ... If there a multiple rows of the same id in table1, I get all (multiple) rows - as you would expect - of course. What I need however, is only one row returned per instance a.id that is returned by the above query. You need GROUP BY a.id. I thought of using group by - but there are no calculated fields... and the real query contains 32 fields, which according to the errors I ran into while trying to get this working, would all need to be included in the group by clause. No, you wouldn't want to do that, because then you'd be back to multiple rows per a.id value. The problem here is that for any one a.id value there could be multiple values of the other variables (coming from different rows) and so the query results are not well defined if you just add "GROUP BY a.id". What you need to do is decide which of those values you want and use an aggregate function to get it. So your query might end up looking like select a.id, min(a.foo), avg(b.bar), ... from ... group by a.id; regards, tom lane Please contact me if you should have any questions. Gavin 'Beau' Baumanis Senior Application Developer PalCare Pty. Ltd. E: [EMAIL PROTECTED] T: +61 -3 9381 4567 M: +61 -4 38 545 586 W: http://www.palcare.com.au -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
