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
