Thread: How to enter daterange into a raw?
Hi,
I have installed PostgreSQL 9.2 on my desktop machine, and
pgAdmin III version: 1.16.1.
I'm being creating a new database with daterange type.
When I'm trying to enter in the 'Edit Data - PostgreSQL' window in the
first raw a range of data:
'2013-09-01, 2013-11-01' without quotes, I get error message:
ERROR: array value must start with "{" or dimension information
LINE 1: INSERT INTO public.negyedev(ettol_eddig) VALUES ('2013-09-01...
^
I want inclusive start and end bounds.
Well, I dont know how to enter the start data and the end data of the
range in pgAdmin III?
--
Regards from Pal
Csanyi Pal wrote
> Hi,
>
> I have installed PostgreSQL 9.2 on my desktop machine, and
> pgAdmin III version: 1.16.1.
>
> I'm being creating a new database with daterange type.
>
> When I'm trying to enter in the 'Edit Data - PostgreSQL' window in the
> first raw a range of data:
>
> '2013-09-01, 2013-11-01' without quotes, I get error message:
> ERROR: array value must start with "{" or dimension information
> LINE 1: INSERT INTO public.negyedev(ettol_eddig) VALUES ('2013-09-01...
> ^
> I want inclusive start and end bounds.
>
> Well, I dont know how to enter the start data and the end data of the
> range in pgAdmin III?
Not tested with pgAdmin but I would presume entering a valid literal - as
defined here:
http://www.postgresql.org/docs/9.2/interactive/rangetypes.html#RANGETYPES-IO
should work.
Namely you have to tell it what kind of bound you desire by inputting "( or
[" and ") or ]".
No idea why it is trying to convert it to an array though. Are you positive
you defined the table correctly? Maybe you should provide the table
definition you are trying to use as well. You may also want to write a
simple "INSERT INTO table VALUES ('')" to make sure you understand what is
happening without involving pgAdmin.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-enter-daterange-into-a-raw-tp5765795p5765812.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
On Wed, Jul 31,Hi David,
2013 at 02:36:23PM -0700, David Johnston wrote:
> Csanyi Pal wrote
> > Hi,
> >
> > I have installed PostgreSQL 9.2 on my desktop machine, and
> > pgAdmin III version: 1.16.1.
> >
> > I'm being creating a new database with daterange type.
> >
> > When I'm trying to enter in the 'Edit Data - PostgreSQL' window in the
> > first raw a range of data:
> >
> > '2013-09-01, 2013-11-01' without quotes, I get error message:
> > ERROR: array value must start with "{" or dimension information
> > LINE 1: INSERT INTO public.negyedev(ettol_eddig) VALUES ('2013-09-01...
> > ^
> > I want inclusive start and end bounds.
> >
> > Well, I dont know how to enter the start data and the end data of the
> > range in pgAdmin III?
>
> Not tested with pgAdmin but I would presume entering a valid literal - as
> defined here:
>
> http://www.postgresql.org/docs/9.2/interactive/rangetypes.html#RANGETYPES-IO
>
> should work.
>
> Namely you have to tell it what kind of bound you desire by inputting "( or
> [" and ") or ]".
It works now. See bellow my mistake, why didn't work earlier.
> No idea why it is trying to convert it to an array though. Are you positive
> you defined the table correctly?
My mistake was that, that I set the column in the table as 'daterange[]'
which is an array of dateranges.
When I set it to 'daterange', then I can to enter a daterange value into
raw of tha table.
> Maybe you should provide the table definition you are trying to use as
> well.
Now, when it works, the table definition is:
-- Table: negyedev
-- DROP TABLE negyedev;
CREATE TABLE negyedev
( sorsz serial NOT NULL, -- A negyedév sorszáma. ettol_eddig daterange, -- A negyedév ettől a dátumtól eddig a dátumig
tart....CONSTRAINT negyedev_pkey PRIMARY KEY (sorsz)
)
WITH ( OIDS=FALSE
);
ALTER TABLE negyedev OWNER TO postgres;
COMMENT ON TABLE negyedev IS 'Ez a tábla az iskola négy negyedévének sorszámát, kezdő és befejező dátumát őrzi.';
COMMENT ON COLUMN negyedev.sorsz IS 'A negyedév sorszáma.';
COMMENT ON COLUMN negyedev.ettol_eddig IS 'A negyedév ettől a dátumtól
eddig a dátumig tart.
A kezdő és a befejező dátum is a negyedévbe tartozik.';
> You may also want to write a simple "INSERT INTO table VALUES ('')" to
> make sure you understand what is happening without involving pgAdmin.
I tried that also when I did searching the solution.
Now, I don't understand, why changes pgAdminIII the entered value,
namely from
[2013-09-01,2013-11-12]
to
[2013-09-01,2013-11-12)
automatically??
I want to enter [2013-09-01,2013-11-12] because I want to to set this
range with inclusive bounds.
--
Regards from Pal
Csanyi Pal wrote > Now, I don't understand, why changes pgAdminIII the entered value, > namely from > [2013-09-01,2013-11-12] > > to > > [2013-09-01,2013-11-12) > > automatically?? > > I want to enter [2013-09-01,2013-11-12] because I want to to set this > range with inclusive bounds. Are you positive the "after" value is [2013-09-01,2013-11-12) as opposed to [2013-09-01,2013-11-11). Since "date"s are discrete the later is equivalent to your input and so the internal representation used to store the data is the later - commonly deemed the "canonical" or "normalized" form. Otherwise I have no clue and cannot readily test it myself. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-enter-daterange-into-a-raw-tp5765795p5765884.html Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.