Re: [SQL] "TZ"/"tz" not supported
Are you basically saying that "TZ" can be used in an "output template string", but not in an "input template string" (in the terminology of the documentation)? If this is the case, shouldn't this be mentioned in the documentation? Also, is there a fundamental reason for this limitation or is it just the implementation waiting to be completed (nobody has had an itch intensive enough to scratch it)? Thanks Peter On Fri, Jun 20, 2008 at 8:50 AM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am Fri, dem 20.06.2008, um 8:35:10 +0200 mailte Peter Kovacs folgendes: >> Thank you, Andreas! Your advice is very useful to me. >> >> I would still be interested why "TZ" is not accepted in the format string. > > I think because TZ is only useful for displaying a timestamptz and not > for internal representation and/or calculation. > > For displaying you can use to_char(timestamptz, format-string). > > > 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-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
[SQL] Problem inserting data
Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but expression is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Cross Tab Functions
Hello All,
I have used the cross tab function to setup tables that display months'
data, however i need to display years data instead of the months.. so i
would display 2006 as a column, 2007 as a colum and 2008 as a column. when i
tried to modify the simple example of the cross tab (shown below)
SELECT i.item_name::text As row_name, to_char(if.action_date, 'year')::text
As bucket,
SUM(if.num_used)::integer As bucketvalue
FROM inventory As i INNER JOIN inventory_flow As if
ON i.item_id = if.item_id
WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL)
AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59'
GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part('year',
if.action_date)
ORDER BY i.item_name, date_part('year', if.action_date);
basically i changed the mon and the month to be year.. the system displayed
something that i am not sure about. is there a command that converts the
year similar to the mon
Help again...Please
Re: [SQL] "TZ"/"tz" not supported
"Peter Kovacs" <[EMAIL PROTECTED]> writes: > Are you basically saying that "TZ" can be used in an "output template > string", but not in an "input template string" (in the terminology of > the documentation)? If this is the case, shouldn't this be mentioned > in the documentation? Also, is there a fundamental reason for this > limitation or is it just the implementation waiting to be completed > (nobody has had an itch intensive enough to scratch it)? The latter, I believe. regards, tom lane -- 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] Problem inserting data
On Friday 20 June 2008 5:20 am, Tk421 wrote: >Hello everybody. > > I''ve just converted an access database to postgres. The conversion > was ok, but when i'm trying to insert data on some tables with boolean > types from a Visual Basic application i receive an error. A sample: > > table example, with firs field of integer, second of type text and > third of type boolean > > the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) > > When i used access this worked fine, in boolean types 0 is false and > 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: > > column "premarco" is of type boolean but expression is of type integer > > My question is, can i force postgres to accept integer values as > boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', > 0::boolean). This is a problem because if I have to do this i will have > to test about one hundred queryes > > Thank you very much Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html -- Adrian Klaver [EMAIL PROTECTED] -- 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] Problem inserting data
Adrian Klaver <[EMAIL PROTECTED]> writes: > Two options: > 1) Change the column in Postgres to an integer type. > 2) Change the assignment in pg_cast from explicit to implied for the > int4,bool cast. See for more details: > http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html Making it an assignment cast would be sufficient for the particular case shown, and would be less likely to introduce surprising behavior elsewhere. Of course, depending on how thoroughly the application confuses int and bool, you might be stuck with using the implicit cast. But it's a bit scary. I think I'd look at option (1) before trying (2) with an implicit cast. regards, tom lane -- 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] Problem inserting data
Adrian Klaver escribió: On Friday 20 June 2008 5:20 am, Tk421 wrote: Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but _expression_ is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html Thank you very much. I've choosen the second option, using this query: update pg_cast set castcontext='i' where castsource=23 and casttarget=16
Re: [SQL] "TZ"/"tz" not supported
On Thu, Jun 19, 2008 at 11:51 PM, Peter Kovacs <[EMAIL PROTECTED]> wrote: > The database version is 8.3.0. On a side note you should update to 8.3.3... -- 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] How to manage category-ids as array-fields ?
On Jun 13, 2008, at 11:02 AM, Andreas wrote:
I get from an outside source tables as simple textfiles to import.
Those are obviously results of views with joined tables.
Among the normal stuff are columns that have one or a list of id-
numbers devided by a semicolon.
In the next column there is the corresponding text for this ids.
It looks like this:
17, ... , "1; 2;", "cat; mouse;", ...
23, ..., "3;", "dog;", ...
42, ..., "2; 7;", "mouse; horse;", ...
Obviously the meaning is that some entity has those listed
attributes. Most likely they are stored as a n:m-relation like:
17, 1
17, 2
23, 3
42, 2
42, 7
Is there a way to reproduce the output in the form above (as array) ?
SELECT array_to_string(array(1,2), '; ');
Is there a way to import the data in the form above, so it gets
neately stored in a n:m ?
Well, you can easily turn it into an array:
SELECT string_to_array('1; 2', '; '); (You'll need to strip the
trailing ;'s.
After than you can convert the array to a recordset if you want.
There's some examples in the archives of how to do that (I think it's
in the archives for -general; I know I was in one of the threads so
searching for decibel might help narrow things down).
Is it seen as a conceptual good solution to store such information
within a text-column or array?
I'd rather doubt that PG would watch the integrity of those ids then.
I wouldn't do text. You could enforce some loose RI via triggers
pretty easily if you used arrays.
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] Cross Tab Functions
Try using extract instead of to_char. date_trunc might also be of use.
On Jun 20, 2008, at 8:05 AM, Chris Preston wrote:
Hello All,
I have used the cross tab function to setup tables that display
months' data, however i need to display years data instead of the
months.. so i would display 2006 as a column, 2007 as a colum and
2008 as a column. when i tried to modify the simple example of the
cross tab (shown below)
SELECT i.item_name::text As row_name, to_char(if.action_date,
'year')::text As bucket,
SUM(if.num_used)::integer As bucketvalue
FROM inventory As i INNER JOIN inventory_flow As if
ON i.item_id = if.item_id
WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL)
AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59'
GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part
('year', if.action_date)
ORDER BY i.item_name, date_part('year', if.action_date);
basically i changed the mon and the month to be year.. the system
displayed something that i am not sure about. is there a command
that converts the year similar to the mon
Help again...Please
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] Cross Tab Functions
I wrote a real simple trunc() function fashioned after oracles but
used some simple rules to let me have modulo date_trunc. I don't have
the code anymore. I might sit down and refigure it out...
On Fri, Jun 20, 2008 at 8:54 PM, Decibel! <[EMAIL PROTECTED]> wrote:
> Try using extract instead of to_char. date_trunc might also be of use.
>
> On Jun 20, 2008, at 8:05 AM, Chris Preston wrote:
>
>> Hello All,
>> I have used the cross tab function to setup tables that display months'
>> data, however i need to display years data instead of the months.. so i
>> would display 2006 as a column, 2007 as a colum and 2008 as a column. when i
>> tried to modify the simple example of the cross tab (shown below)
>> SELECT i.item_name::text As row_name, to_char(if.action_date,
>> 'year')::text As bucket,
>> SUM(if.num_used)::integer As bucketvalue
>> FROM inventory As i INNER JOIN inventory_flow As if
>> ON i.item_id = if.item_id
>> WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL)
>> AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59'
>> GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part('year',
>> if.action_date)
>> ORDER BY i.item_name, date_part('year', if.action_date);
>>
>> basically i changed the mon and the month to be year.. the system
>> displayed something that i am not sure about. is there a command that
>> converts the year similar to the mon
>>
>> Help again...Please
>
> --
> Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
