niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » PostgreSQL
to_char bug?
Post new topic   Reply to topic Page 70 of 71 [1061 Posts] View previous topic :: View next topic
Goto page:  Previous  1, 2, 3, ..., 68, 69, 70, 71 Next
Author Message
Bruno Wolff III
*nix forums Guru Wannabe


Joined: 11 Mar 2005
Posts: 262

PostPosted: Fri Jun 16, 2006 5:30 pm    Post subject: Re: table has many to many relationship with itself - how Reply with quote
On Wed, Jun 14, 2006 at 13:51:50 -0700,
SCassidy@overlandstorage.com wrote:
Quote:
Starting with this:

create sequence languages_seq increment by 1;
create table languages (
id integer primary key default nextval('languages_seq'),
language_name varchar(100)
);
insert into languages (id, language_name) values (1, 'English');
insert into languages (id, language_name) values (2, 'French');
insert into languages (id, language_name) values (3, 'Spanish');
insert into languages (id, language_name) values (4, 'Italian');

create table phrases(
id serial primary key,
language integer references languages(id),
content text
);
insert into phrases (language, content) values (1, 'the book');
insert into phrases (language, content) values (2, 'le livre');
insert into phrases (language, content) values (3, 'el libro');
insert into phrases (language, content) values (4, 'il libro');
insert into phrases (language, content) values (1, 'the room');
insert into phrases (language, content) values (4, 'la stanza');
insert into phrases (language, content) values (4, 'la camera');


For your translations table, I would go with something like this:


create sequence translations_seq increment by 1;
create table translations (
translation_id integer primary key default nextval('translations_seq'),
lang1_id integer references phrases(id),
lang2_id integer references phrases(id)
);

I think you are better off putting the equivalence information in the phrases
table. (This assumes that treating translations of a phrase into various
languages forms an equivalence class.) Under this model each phrase will
be in exactly one equivalence class, so that adding an equivalence class
column to the phrase table seems like a good solution.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org
Back to top
TJ O'Donnell
*nix forums beginner


Joined: 02 Apr 2005
Posts: 21

PostPosted: Thu Jun 22, 2006 7:45 pm    Post subject: aggregate of bitstrings Reply with quote
AHA! I hadn't encountered any null values in my bitstrings.
and having to include 'where xxx is not null' is rather
inconvenient and easy to forget.

indeed bitor(B'1000',null) returns null

but as a test, i nulled one row's data (the bitstring column portion only)
and my orsum without INITCOND returned the proper OR of the remaining values
when including all rows in the aggregate. it did not return null.

maybe the aggregator (whoever,whatever that is) handles null args differently,
not calling the func when the arg is null?

pg8.1.3

TJ


Florian G. Pflug wrote:
Quote:
TJ O'Donnell wrote:

create aggregate or_bit64(
basetype=bit,
sfunc=bitor,
stype=bit,
initcond=
'0000000000000000000000000000000000000000000000000000000000000000'
) ;

I'm using this in production, and it works fine. I didn't find a way to
make this length-agnostic, so I defined this for all lenghts of
bitstrings the my app uses (32 and 64).

greetings, Florian Pflug


I've created a similar aggregate using:
CREATE AGGREGATE gnova.orsum (
BASETYPE = bit,
SFUNC = bitor,
STYPE = bit
);
Notice, not using INITCOND allows bit of any length. While it may be
poor programming practice to not initialize, the docs say:
"If it is not supplied then the state value starts out null."
which is good enough for this old programmer. AND it works :)

The problem was, as far as I remember, that bitor returns NULL if any
of it's arguments is null. So not specifying an INITCOND makes the
aggregate work for any length, but always returns null then...

greetings, Florian Pflug

---------------------------(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
Back to top
Joe Conway
*nix forums beginner


Joined: 10 Mar 2005
Posts: 32

PostPosted: Thu Jun 22, 2006 8:30 pm    Post subject: Re: aggregate of bitstrings Reply with quote
TJ O'Donnell wrote:
Quote:

maybe the aggregator (whoever,whatever that is) handles null args
differently,
not calling the func when the arg is null?

see:

http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html

specifically:

"If the state transition function is declared "strict", then it cannot
be called with null inputs. With such a transition function, aggregate
execution behaves as follows. Null input values are ignored (the
function is not called and the previous state value is retained). If the
initial state value is null, then the first nonnull input value replaces
the state value, and the transition function is invoked beginning with
the second nonnull input value. This is handy for implementing
aggregates like max. Note that this behavior is only available when
state_data_type is the same as input_data_type. When these types are
different, you must supply a nonnull initial condition or use a
nonstrict transition function.

If the state transition function is not strict, then it will be called
unconditionally at each input value, and must deal with null inputs and
null transition values for itself. This allows the aggregate author to
have full control over the aggregate's handling of null values.

If the final function is declared "strict", then it will not be called
when the ending state value is null; instead a null result will be
returned automatically. (Of course this is just the normal behavior of
strict functions.) In any case the final function has the option of
returning a null value. For example, the final function for avg returns
null when it sees there were zero input rows."

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Back to top
Florian Pflug
*nix forums Guru Wannabe


Joined: 03 Mar 2005
Posts: 100

PostPosted: Fri Jun 23, 2006 1:04 am    Post subject: Re: aggregate of bitstrings Reply with quote
TJ O'Donnell wrote:
Quote:
AHA! I hadn't encountered any null values in my bitstrings.
and having to include 'where xxx is not null' is rather
inconvenient and easy to forget.

indeed bitor(B'1000',null) returns null

but as a test, i nulled one row's data (the bitstring column portion only)
and my orsum without INITCOND returned the proper OR of the remaining
values
when including all rows in the aggregate. it did not return null.
Hm.. strange.. I could have sworn that I added the initcond to fix some

null-related issue...
Maybe is was the aggregate returning null where there where no rows
to aggregate.. I'll test this again, I'd actually love to get rid of
that initcond.

greetings, Florian Pflug

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Back to top
Bruno Baguette
*nix forums beginner


Joined: 27 Mar 2006
Posts: 2

PostPosted: Mon Jun 26, 2006 7:43 am    Post subject: Re: "Ghost" colmumn with primary key Reply with quote
chrisek@poczta.neostrada.pl a écrit :
Quote:
Hello,
I have a table 'customers', with 2 records:

SELECT * FROM customers;
customerID | customerName
------------------+-------------------------
myFriend | myFriend's Name
test | testing user
(2 rows)

but when I'm asking about customerID column, I get the answer:

SELECT customerID FROM customers;
ERROR: column "customerid" does not exist

What happens? I'm using PostgreSQL 8.1.3

Hello,

You have to use double-quotes since your column contains some uppercase
characters. Try this :

SELECT "customerID" FROM customers;

Hope this helps,

--
Bruno BAGUETTE - pgsql-ml@baguette.net

"Nous n'avons pas à garantir la sécurité des
produits alimentaires génétiquement modifiés (OGM).
Notre intérêt est d'en vendre le plus possible."

Propos de Monsanto, in le Monde Diplomatique, Décembre 98.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Back to top
Franz.Rasper@izb.de
*nix forums beginner


Joined: 14 Oct 2005
Posts: 9

PostPosted: Fri Jun 30, 2006 2:42 pm    Post subject: Re: Notes on converting from MySQL 5.0.x to PostgreSQL Reply with quote
Yes indeed if it would be possible, it should go to the website.
Good comparison without saying such things like this dbms has xx features
and this one has xx features.

Some addtitional information for setcion "Further information:"
phpPgAdmin http://phppgadmin.sourceforge.net/

Gborg http://gborg.postgresql.org/

Greetings,

-Franz


-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Freitag, 30. Juni 2006 16:17
An: Jason McManus
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
8.1.4


"Jason McManus" <mcmanus.jason@gmail.com> writes:
Quote:
I am in the process of converting a couple of major sites from MySQL
5.0.22
to PostgreSQL 8.1.4, and I thought I would share some of my observations
on
this process and the two database systems in general.

Nice notes! I see only one small error:

Quote:
* Pg's default character set (in 8.1.4) is UTF8.

I don't believe there is any fixed "default character set". Each
installation will have a default locale and encoding, but these depend
on the locale under which initdb was run. From the above comment I
surmise that you initdb'd under some UTF8-using locale ...

Quote:
Thank you, and I hope that these notes prove helpful to others!

Perhaps they should go on the project website somewhere?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Back to top
Joko Siswanto
*nix forums beginner


Joined: 02 Jul 2006
Posts: 4

PostPosted: Sun Jul 02, 2006 4:01 am    Post subject: How to Backup like in mysql or ms sql server Reply with quote
Hi all,

I'm new bie in postgresql.
I use postgresql 8.1 windows version.
How to back-up database in postgresql? Usually i use pgAdmin III by back-up
and restore.
Is there any way to back-up database like mysql or sql server we just copy
and paste. Or maybe there is any tools to copy database when the service is
shutdown.

Where is postgresql put teh database files?

Thanks all,
Jokonet
Back to top
Mike G.
*nix forums beginner


Joined: 09 Feb 2006
Posts: 10

PostPosted: Sun Jul 02, 2006 4:14 am    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
On windows the database files are under C:\program files\postgresql\8.1
\data

There are a number of different ways a backup can be done and a file
level copy/paste is one of them.

See http://www.postgresql.org/docs/8.1/interactive/backup.html

Mike

On Sun, 2006-07-02 at 11:01 +0700, Joko Siswanto wrote:
Quote:


Hi all,

I'm new bie in postgresql.
I use postgresql 8.1 windows version.
How to back-up database in postgresql? Usually i use pgAdmin III by
back-up and restore.
Is there any way to back-up database like mysql or sql server we just
copy and paste. Or maybe there is any tools to copy database when the
service is shutdown.

Where is postgresql put teh database files?

Thanks all,
Jokonet




---------------------------(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
Back to top
Richard Broersma Jr
*nix forums beginner


Joined: 13 Mar 2006
Posts: 44

PostPosted: Sun Jul 02, 2006 4:17 am    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
Quote:
I'm new bie in postgresql.
I use postgresql 8.1 windows version.
How to back-up database in postgresql? Usually i use pgAdmin III by back-up
and restore.
Is there any way to back-up database like mysql or sql server we just copy
and paste. Or maybe there is any tools to copy database when the service is
shutdown.

Where is postgresql put teh database files?

I am not sure about mysql or sql Server, but the following link will how how it is done on the
command line of the PostgreSQL server:
http://www.postgresql.org/docs/8.1/interactive/backup.html

Is is a good place to start. Also, if you are interested in keeping your data backed up current
within the last few minutes, check out point in time recovery (PITR). But for simple backup and
restore, I expect that you will be satisfied with the simple pg_dump command.

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Back to top
Uwe C. Schroeder
*nix forums beginner


Joined: 09 Mar 2005
Posts: 40

PostPosted: Sun Jul 02, 2006 4:19 am    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
You can technically just copy & paste the postgresql data directory IF YOU
SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the
same version of postgresql. Also: this is not a good way to do it and I'd
encourage you not to use this as general means of backup (it's ok if you want
to create a quick clone of an existing database on a second machine -
provided that the platform and postgresql version on there is identical to
the source).

The proper way would be to use pg_dump (a tool that comes along every
postgresql installation - see the docs for usage), which will dump the data
and structure.
There is a second such utility program called pg_restore which will take the
dump-file created with pg_dump and restore it to a database of your choosing.
Those dumps will be functional between versions and platforms - so that's the
way to go.

UC


On Saturday 01 July 2006 21:01, Joko Siswanto wrote:
Quote:
Hi all,

I'm new bie in postgresql.
I use postgresql 8.1 windows version.
How to back-up database in postgresql? Usually i use pgAdmin III by back-up
and restore.
Is there any way to back-up database like mysql or sql server we just copy
and paste. Or maybe there is any tools to copy database when the service is
shutdown.

Where is postgresql put teh database files?

Thanks all,
Jokonet

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Back to top
Tino Wildenhain
*nix forums Guru Wannabe


Joined: 03 Mar 2005
Posts: 177

PostPosted: Sun Jul 02, 2006 8:42 am    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
Uwe C. Schroeder wrote:
Quote:
You can technically just copy & paste the postgresql data directory IF YOU
SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the
same version of postgresql. Also: this is not a good way to do it and I'd
encourage you not to use this as general means of backup (it's ok if you want
to create a quick clone of an existing database on a second machine -
provided that the platform and postgresql version on there is identical to
the source).


Well, err. thats not completely true with current postgres versions:

http://www.postgresql.org/docs/current/static/backup-online.html

....
Quote:
Is there any way to back-up database like mysql or sql server we just copy
and paste. Or maybe there is any tools to copy database when the service is
shutdown.

Where is postgresql put teh database files?

Well, thats in the docs Wink (or see above)

btw, just "copy and paste" w/o preparation is
dangerous with the above databases too.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org
Back to top
Uwe C. Schroeder
*nix forums beginner


Joined: 09 Mar 2005
Posts: 40

PostPosted: Sun Jul 02, 2006 7:00 pm    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
On Sunday 02 July 2006 01:42, Tino Wildenhain wrote:
Quote:
Uwe C. Schroeder wrote:
You can technically just copy & paste the postgresql data directory IF
YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for
the same version of postgresql. Also: this is not a good way to do it and
I'd encourage you not to use this as general means of backup (it's ok if
you want to create a quick clone of an existing database on a second
machine - provided that the platform and postgresql version on there is
identical to the source).

Well, err. thats not completely true with current postgres versions:

http://www.postgresql.org/docs/current/static/backup-online.html

...


Ok, you're correct on that one. However I'd rather not encourage someone to
mess with WAL and filesystem based backups when s/he hasn't even heard of
pg_dump yet, simply because I can already see the next question popping
up ... like in "I had a failure and wanted to restore my backup, but
everything is messed up now and I can't get it running - help please!" Smile
With a standard pg_dump that won't happen, so it's IMHO the safest way to deal
with the backup problem for a newbie.

On a side-note: that piece of documentation is pretty heavy reading and
assumes quite some knowledge about how a DB system like postgresql works
internally. For me it's always the least sophisticated approach that solves a
given problem. The good old KISS principle applies again :-)

Uwe



Quote:

Is there any way to back-up database like mysql or sql server we just
copy and paste. Or maybe there is any tools to copy database when the
service is shutdown.

Where is postgresql put teh database files?

Well, thats in the docs Wink (or see above)

btw, just "copy and paste" w/o preparation is
dangerous with the above databases too.

Regards
Tino

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

---------------------------(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
Back to top
Joko Siswanto
*nix forums beginner


Joined: 02 Jul 2006
Posts: 4

PostPosted: Mon Jul 03, 2006 6:22 pm    Post subject: Re: How to Backup like in mysql or ms sql server Reply with quote
Thanks for all,
It's really help me, I mean that give me a lot of clue

Btw, Now I still reinstall the postgres coz i dont know why suddenly i can't
start the services. that's why i ask how to backup the data when the
serviceis down.

I'll try it and thanks for help

Regard,
Jokonet
Back to top
Joseph Kiniry
*nix forums beginner


Joined: 04 Jul 2006
Posts: 2

PostPosted: Tue Jul 04, 2006 9:00 pm    Post subject: Re: Backing up and restoring a database with the SELinux pg_user problem. Reply with quote
Hi all,

Returning to a really old problem that is biting me again...

We upgraded our server (from Fedora Core 3 to Core 5), and thus
upgraded Postgres. Unfortunately, we did not know that the new
version of Postgres included with 5 uses a different data format than
that included with 3. Thus, we need to upgrade our database. But
one can only do that by doing a dump, and initdb, and a restore.

But... we cannot do a dump because our database is borked because the
original initdb partially failed due to the SELinux bug in FC3 (see
the discussion from back in Feb 2005 on this list and RedHat bugzilla
id 149237). Tom and I exchanged some emails about this back in
February, the last of which was:

On 4 Feb, 2005, at 15:19, Tom Lane wrote:

Quote:
Joseph Kiniry <kiniry@acm.org> writes:
I'm currently blocked on the system catalog schema "pg_catalog";
whence is it initialized?

That row in pg_namespace is missing, you mean? That's very odd ...
what
rows do you see in pg_namespace? That should be loaded as part of the
basic bootstrap operation, and if basic bootstrap had failed you'd
definitely not have failed to notice ;-)

I have looked though all initdb-related
scripts, SQL files, and BKI files and have found several
references to
pg_catalog, but I have not found its definition/initialization.

The definition/initialization is basically driven from macros in
src/include/catalog/pg_namespace.h; in particular all the rows defined
by DATA macros in that file should have been created during bootstrap.
You might look in the .bki file to verify that there is a section
creating and loading pg_namespace.

regards, tom lane

As I said above, I have re-examined, and executed if necessary, by
hand, all sql commands in initdb and postgres.bki, but it seems that
pg_catalog is still screwed up. Attempting to dump, or perform
several other actions results in failures of the form:

ERROR: 42P01: relation "pg_user" does not exist
LOCATION: RangeVarGetRelid, namespace.c:193
STATEMENT: SELECT (SELECT usename FROM pg_user WHERE usesysid =
datdba) as dba\
, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database
WHERE dat\
name = 'gforge'

Tom asked what pg_namespace looks like, and here it is:

gforge=# select * from pg_namespace;
nspname | nspowner | nspacl
------------+----------+---------------------------------------
pg_toast | 1 |
pg_temp_1 | 1 |
pg_catalog | 1 | {postgres=U*C*/postgres,=U/postgres}
public | 1 | {postgres=U*C*/postgres,=UC/postgres}
(4 rows)

pg_catalog has tons of stuff in it, so it looks like bki
initialisation worked.

Just to be clear, all database operations for our GForge install work
fine, we just cannot backup our database, and thus we cannot upgrade
postgres.

So why can I see pg_user and yet pg_dump fails?

gforge=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil| useconfig
----------+----------+-------------+----------+-----------+----------
+----------+-----------
postgres | 1 | t | t | t | ********
| |
gforge | 100 | t | f | f | ********
| |
(2 rows)

How do I get my data out of this database?

Thanks,
Joe
---
Joseph Kiniry
School of Computer Science and Informatics
UCD Dublin
http://secure.ucd.ie/
http://srg.cs.ucd.ie/




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq
Back to top
Tom Lane
*nix forums Guru


Joined: 24 Mar 2005
Posts: 2070

PostPosted: Tue Jul 04, 2006 9:12 pm    Post subject: Re: Backing up and restoring a database with the SELinux pg_user problem. Reply with quote
Joseph Kiniry <kiniry@acm.org> writes:
Quote:
As I said above, I have re-examined, and executed if necessary, by
hand, all sql commands in initdb and postgres.bki, but it seems that
pg_catalog is still screwed up. Attempting to dump, or perform
several other actions results in failures of the form:

ERROR: 42P01: relation "pg_user" does not exist
LOCATION: RangeVarGetRelid, namespace.c:193
STATEMENT: SELECT (SELECT usename FROM pg_user WHERE usesysid =
datdba) as dba\
, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database
WHERE dat\
name = 'gforge'

So why can I see pg_user and yet pg_dump fails?

gforge=# select * from pg_user;
[ works ]

Hmm ... you manually recreated the pg_user view you say? I wonder if
you mistakenly put it in the public schema instead of pg_catalog.
The quoted command from pg_dump is done after issuing
set search_path = pg_catalog;
so that nothing user-created will accidentally mess it up. If you
can still manually select from pg_user after issuing that same SET
command, then something is really seriously strange ...

If you find that indeed pg_user is in public, drop it there and
re-create it in pg_catalog. You'll need to be superuser to do
that but I don't think it'll require any more pushups than that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 70 of 71 [1061 Posts] Goto page:  Previous  1, 2, 3, ..., 68, 69, 70, 71 Next
View previous topic :: View next topic
The time now is Tue Dec 02, 2008 5:33 am | All times are GMT
navigation Forum index » Databases » PostgreSQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts to_char number format with optional decimal-point? Martin T. Oracle 3 Thu Jul 20, 2006 10:53 am
No new posts strange behaviour using to_char to return daynumber mich (at work) Oracle 4 Mon Apr 24, 2006 12:40 pm
No new posts to_char not returning 'day' value without leading zero Kevin Blount Oracle 3 Thu Apr 06, 2006 9:32 pm
No new posts to_char syntax Liz J Oracle 6 Thu Feb 23, 2006 12:10 am
No new posts to_char (33, '0009') adds an extra white space Günther De Vogelaere Oracle 2 Mon Jan 09, 2006 1:03 pm

Loans | Free Advertising | Debt Consolidation | Debt Consolidation | Mortgage Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 5.5652s ][ Queries: 16 (5.3812s) ][ GZIP on - Debug on ]