Re: How to autoincrement a primary key...

Lists: pgsql-sql
From: "Ezequias Rodrigues da Rocha" <ezequias(dot)rocha(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Group by minute
Date: 2006-09-22 13:52:00
Message-ID: 55c095e90609220652t356c8591u412046aed091a52f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

Hil list,

I have a query but my IDE (Delphi) does not accept "to_char" capability. Is
there a way to reproduce the same query without using to_char function ?

Here is my query:
SELECT to_char(quando,'dd/MM/yyyy HH24:MI'),count(id)
FROM base.tentativa
WHERE (SESSAO_ID = 15)
GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')
order by 1

Regards ...

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

From: Niklas Johansson <spot(at)tele2(dot)se>
To: "Ezequias Rodrigues da Rocha" <ezequias(dot)rocha(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Group by minute
Date: 2006-09-22 14:10:54
Message-ID: F65F089C-5162-49EE-9D17-0F8ACA48ADE6@tele2.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql


On 22 sep 2006, at 15.52, Ezequias Rodrigues da Rocha wrote:
> I have a query but my IDE (Delphi) does not accept "to_char"
> capability. Is there a way to reproduce the same query without
> using to_char function ?
>
> ...
> GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')

To group by minute, you can try

GROUP BY date_trunc('minute', quando)

or even

GROUP BY EXTRACT(EPOCH FROM quando)::integer / 60

Sincerely,

Niklas Johansson

From: Mezei Zoltán <mezei(dot)zoltan(at)telefor(dot)hu>
To: Ezequias Rodrigues da Rocha <ezequias(dot)rocha(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Group by minute
Date: 2006-09-22 14:14:41
Message-ID: 4513EFD1.1010500@telefor.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000099">
Ezequias Rodrigues da Rocha wrote:
<blockquote
cite="mid55c095e90609220652t356c8591u412046aed091a52f(at)mail(dot)gmail(dot)com"
type="cite">Hil list,<br>
<br>
I have a query but my IDE (Delphi) does not accept "to_char"
capability. Is there a way to reproduce the same query without using
to_char function ?<br>
<br>
Here is my query:<br>
SELECT  to_char(quando,'dd/MM/yyyy HH24:MI'),count(id)
<br>
FROM base.tentativa<br>
WHERE  (SESSAO_ID = 15) <br>
GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')<br>
order by 1<br>
<br>
</blockquote>
That seems like a valid query in Oracle :-)<br>
<br>
Postgres have a slightly different mechanism for handling date and
time. The modified query:<br>
<br>
SELECT  date_trunc('minute', quando),count(id)
<br>
FROM base.tentativa<br>
WHERE  (SESSAO_ID = 15) <br>
-- GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')<br>
GROUP BY date_trunc('minute', quando)<br>
order by 1<br>
<br>
You should read:<br>
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html">http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html</a><br>
<br>
Zizi<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.3 KB
From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Ezequias Rodrigues da Rocha <ezequias(dot)rocha(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Group by minute
Date: 2006-09-22 14:56:02
Message-ID: 1158936962.5218.4.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

On Fri, 2006-09-22 at 10:52 -0300, Ezequias Rodrigues da Rocha wrote:
> Hil list,
>
> I have a query but my IDE (Delphi) does not accept "to_char"
> capability. Is there a way to reproduce the same query without using
> to_char function ?
>
> Here is my query:
> SELECT to_char(quando,'dd/MM/yyyy HH24:MI'),count(id)
> FROM base.tentativa
> WHERE (SESSAO_ID = 15)
> GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')
> order by 1
>

Assuming that your IDE has the same issues with date_trunc, you could
always put things into a view...

in psql do something like:

create view frozen_caveman_ide as
SELECT to_char(quando,'dd/MM/yyyy HH24:MI'),
count(id) FROM base.tentativa
GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI');

then just call the view when you need that.

From: "Doug Hyde" <doug(dot)hyde(at)e-cocreate(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: How to autoincrement a primary key...
Date: 2006-09-22 19:13:46
Message-ID: 000001c6de7b$3a211c70$6501a8c0@laptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
not sure how to do this. I have read the documentation, and tried "nextval"
as the default - I have searched for the datatype SERIAL, but I am using
navicat and this datatype is not supported. Can someone tell me how to do
this - I just want the integer value for a primary key to autoincrement by
one.

Thanks,

Doug

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Doug Hyde <doug(dot)hyde(at)e-cocreate(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: How to autoincrement a primary key...
Date: 2006-09-22 19:25:41
Message-ID: 20060922192541.46464.qmail@web31815.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
> not sure how to do this. I have read the documentation, and tried "nextval"
> as the default - I have searched for the datatype SERIAL, but I am using
> navicat and this datatype is not supported. Can someone tell me how to do
> this - I just want the integer value for a primary key to autoincrement by
> one.

CREATE TABLE bar (id SERIAL PRIMARY KEY);

Is just shorthand notation for:

CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));

Also see:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html

Regards,

Richard Broersma Jr.

From: "Andrew Chilton" <andychilton(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: How to autoincrement a primary key...
Date: 2006-09-22 22:00:29
Message-ID: a364f6fa0609221500k2ebd88c1q934385ee6856cb50@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

On 23/09/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> Is just shorthand notation for:
>
> CREATE SEQUENCE foo START 1;
> CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));

Doug, just a slight typo correction in that second line Richard gave
you (bar should be foo), it should read in full:

CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('foo'));

Andy

--
name: Andrew Chilton
web: http://kapiti.geek.nz/

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: How to autoincrement a primary key...
Date: 2006-09-22 22:12:00
Message-ID: ef1n3g$ra2$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

Richard Broersma Jr wrote on 22.09.2006 21:25:
>> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
>> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
>> not sure how to do this. I have read the documentation, and tried "nextval"
>> as the default - I have searched for the datatype SERIAL, but I am using
>> navicat and this datatype is not supported. Can someone tell me how to do
>> this - I just want the integer value for a primary key to autoincrement by
>> one.
>
> CREATE TABLE bar (id SERIAL PRIMARY KEY);
>
>
> Is just shorthand notation for:
>
> CREATE SEQUENCE foo START 1;
> CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));
>

Well the shorthand notation has a minor gotcha: you cannot drop the sequence
that has been created automatically. Only if you drop the column itself. Should
not be a problem, but it is a difference between a SERIAL PRIMARY KEY definition
and the "verbose" mode

Thomas

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: "Doug Hyde" <doug(dot)hyde(at)e-cocreate(dot)com>
Subject: Re: How to autoincrement a primary key...
Date: 2006-09-23 06:27:57
Message-ID: 200609230927.58000.aarni@kymi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

On Saturday 23 September 2006 01:12, Thomas Kellerer wrote:
> Richard Broersma Jr wrote on 22.09.2006 21:25:
> >> I am sure this is simple, but I don't get it. I am new to PGSQL, coming
> >> from MySQL - in mysql, you can autoincrement the primary key; in
> >> postgre, I am not sure how to do this. I have read the documentation,
> >> and tried "nextval" as the default - I have searched for the datatype
> >> SERIAL, but I am using navicat and this datatype is not supported. Can
> >> someone tell me how to do this - I just want the integer value for a
> >> primary key to autoincrement by one.
> >
> > CREATE TABLE bar (id SERIAL PRIMARY KEY);
> >
> >
> > Is just shorthand notation for:
> >
> > CREATE SEQUENCE foo START 1;
> > CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('foo'));
>
> Well the shorthand notation has a minor gotcha: you cannot drop the
> sequence that has been created automatically. Only if you drop the column
> itself. Should not be a problem, but it is a difference between a SERIAL
> PRIMARY KEY definition and the "verbose" mode
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

Verbosily you can have even more control over the sequence.

With SERIAL the default is something like

CREATE SEQUENCE foo
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

By hand you can define e.g.

CREATE SEQUENCE foo
START n
INCREMENT BY n
MAXVALUE n
MINVALUE n
CACHE 1;

BR,

Aarni
--
Aarni Ruuhimäki

**Kmail**
**Fedora Core Linux**