[GENERAL] only the last 3 records
Hi, maybe it's a stupid question, but i do not remember how to query a table and to request only the latest 3 added records. How can i do that (i have a field time and a field date) ? thx. -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] only the last 3 records
am Sat, dem 27.09.2008, um 9:13:48 +0200 mailte Alain Roger folgendes: Hi, maybe it's a stupid question, but i do not remember how to query a table and to request only the latest 3 added records. How can i do that (i have a field time and a field date) ? ... order by this field desc limit 3; 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
[GENERAL] sequence... my nightmare :-(
Hi,
i have a schema called : test_survey
i have within this schema this table:
CREATE TABLE test_survey.accounts
(
ID bigint NOT NULL DEFAULT
nextval('test_survey.accounts_ID_seq'::regclass),
login character varying(300) NOT NULL,
pwd character varying(100) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT accounts_pkey PRIMARY KEY (ID)
)
WITH (OIDS=FALSE);
ALTER TABLE test_survey.accounts OWNER TO mysurvey;
and this sequence:
CREATE SEQUENCE test_survey.accounts_ID_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE test_survey.accounts_ID_seq OWNER TO mysurvey;
when i write the following query i get the error :ERROR: relation
accounts_id_seq does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'[EMAIL
PROTECTED]','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] sequence... my nightmare :-(
when i write the following query i get the error :ERROR:
relation
accounts_id_seq does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'[EMAIL
PROTECTED]','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
It's lowercasing the relation name, note accounts_id_seq not
accounts_ID_seq. Try double quoting it.
--
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] sequence... my nightmare :-(
if i double-quote it, postgre tells me that the column accounts_id_seq does
not exist.
On Sat, Sep 27, 2008 at 5:59 PM, Glyn Astill [EMAIL PROTECTED] wrote:
when i write the following query i get the error :ERROR:
relation
accounts_id_seq does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'[EMAIL PROTECTED]
','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
It's lowercasing the relation name, note accounts_id_seq not
accounts_ID_seq. Try double quoting it.
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] sequence... my nightmare :-(
you'll need to reference the column with the correct camel_case as
accounts_ID_seq != accounts_id_seq
hint: next time you create a table keep the table and column names lowercase
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business
of Sender. This transmission is of a confidential nature and Sender does not
endorse distribution to any party other than intended recipient. Sender does
not necessarily endorse content contained within this transmission.
Date: Sat, 27 Sep 2008 18:21:26 +0200
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] sequence... my nightmare :-(
CC: pgsql-general@postgresql.org
if i double-quote it, postgre tells me that the column accounts_id_seq does not
exist.
On Sat, Sep 27, 2008 at 5:59 PM, Glyn Astill [EMAIL PROTECTED] wrote:
when i write the following query i get the error :ERROR:
relation
accounts_id_seq does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'[EMAIL
PROTECTED]','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
It's lowercasing the relation name, note accounts_id_seq not
accounts_ID_seq. Try double quoting it.
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
_
Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
Re: [GENERAL] sequence... my nightmare :-(
On Sat, Sep 27, 2008 at 10:21 AM, Alain Roger [EMAIL PROTECTED] wrote:
if i double-quote it, postgre tells me that the column accounts_id_seq does
not exist.
You almost got it. You need to doublequote to tell nextval with
capitalization correctly, then single quote that so the query planner
doesn't say oh look! An identifier!
create sequence Abc;
CREATE SEQUENCE
select nextval('Abc');
ERROR: relation abc does not exist
select nextval('Abc');
nextval
-
1
(1 row)
--
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] Minor bug/inconveniance with restore from backup, using PITR base backup and archived wal files
Tom Lane wrote: Tommy Gildseth [EMAIL PROTECTED] writes: After a bit of looking around, and with some help from the fine people in #postgresql on freenode, I think I figured out what was going on. The last wal archive file was 00010003009F, and after finishing recovery, postgresql created the file 00020003009F (ie. 0002 instead of 0001) in pg_xlog. It's customary for PG to create new XLOG segments by recycling old ones. The wal-files were archived read-only, and this file permission seemed to be carried over to the new file created by postgresql in pg_xlog, causing the cluster to fall over and die. I would say that the bug is in your restore script: it should have made sure that the files it copies into the xlog directory are given the right ownership/permissions. Well, the restore command(script) is simply copied from the suggestion in the manual (restore_command = 'cp /path/to/my/archived/wal/files/%f %p'). In my opinion, it's not very obvious that the last wal file needs read/write permissions set, and it's certainly not documented anywhere on http://www.postgresql.org/docs/current/static/continuous-archiving.html that I can see. There's also the matter of the inconsistency that postgresql knows to recycle *and* chmod the file if it's originally located in pg_xlog/ folder, but not if it's originally located in the wal files archive folder. I guess it's more of a gotcha than a bug per se. -- Tommy Gildseth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
