logo
IRC Archive / Freenode / #postgresql / 2010 / February / 27 / 3
RhodiumToad
"group" sounds much more appropriate to me
xinming
RhodiumToad: Because I have to type extra "" around the table name.
and make the all the code not consistent. :-)
RhodiumToad
well, the other way would be to make the name more specific: something_group
xinming
RhodiumToad: thanks, seems that's the last choice. >_<
kemo
0x20 means white space in ascii, or?
RhodiumToad
0x20 is a space character, yes
kemo
k thx
necroforest
Anyone here familiar with PostgreSQL on Ubuntu? I just installed it from APT and get the following error when doing creatdb: reatedb: could not connect to database postgres: FATAL: Ident authentication failed
I get this for my regular user and root
nevermind, apparently i have to do initial setup as a postgres user
next question:
is there a way to create a new per-session entry in pg_settings?
agliodbs
necroforest: can you be more explicit?
necroforest
ok, i want to create essentially a "session token" that is set per DB session thats used by stored procedures/packages
agliodbs
what will you use it for?
rukubites
??copy permissions
pg_docbot_adz_
Nothing found
rukubites
??copy
pg_docbot_adz_
For information about 'copy' see:
http://wiki.postgresql.org/wiki/How_to_use_PostgreSQL's_COPY_function_effectively
agliodbs
can anyone think of a way to simplify this query?
rukubites
Is there a way to read from files using COPY (not psql \copy) without being superuser?
agliodbs
rukubites: no
rukubites: and if you find one, you should report it
rukubites
agliodbs: For my edification, can you tell me why? If a user has set up permissions so postgres can read from a file, why is that a problem?
agliodbs
because, COPY reads from a file on the server
so it's a file operation on the server
if regular users were allowed to do it, then they might use COPY to read files they weren't entitled to
like /etc/passwd
rukubites
Is there a reason why it couldn't do the permission checks, so e.g. a database owned by rukubites could read rukubites' files?
Not having a whinge, just want to understand the reasoning.
agliodbs
there's no relationship between DB users and shell users
it's pretty much a hard rule that any command which can perform file operations on the server has to be the superuser
rukubites
agliodbs: "No relation" ... then why can I connect to a postgresql database owned by rukubites when logged in as rukubites, without any password/auth?
But okay, if that's the way it is, then that's the way it is.
agliodbs
that's called ident auth
pguser
Which type is recommended for reliably storing real-world currency? I imagine I would have one "float" column called "amount" and one varchar "currency" column with "USD" or "NOK" as the currency varies in this context.
rukubites
integer is better.
pguser
What about cents?
agliodbs
the database is just accepting from the OS that a user named "postgres" is allowed to login to the DB as DB user "postgres"
rukubites
Well the problem can occur with rounding errors.
agliodbs
but the relationship only goes that way
pguser: Numeric
pguser: Float is a PITA
rukubites
pguser: My opinion was based on standard computer science - floats are ... well... a PITA because sometimes 1+1 = 2.0000001.
xinming
shoe, RhodiumToad I finally get the word, I use "team" to replace the word "group" :-)
pguser
Hmm...
rukubites
But you can record your money in number of cents.
pguser
It must be able to hold any currency.
Mixed.
This is sort of a log used for legal purposes.
rukubites
pguser: Well you can record the decimal place... or use Numeric ... agliodbs seems to know his postgres.
I am just a hack
agliodbs
numeric is a fixed-length decimal
pguser
Would it be insane to actually use varchar for the amount field?
agliodbs
??numeric
pg_docbot_adz_
For information about 'numeric' see:
pguser
I don't really think it will have to be calculated...
agliodbs
pguser: yes
pguser
Just reported...
Why is the "money" type bad again?
agliodbs
'cause it was never fully implemented
RhodiumToad
no conversions to more useful numeric types
rukubites
??money
pg_docbot_adz_
For information about 'money' see:
http://alma.ch/blogs/bahut/2006/04/cannot-cast-type-money.html
RhodiumToad
locale-sensitive input and output
agliodbs
got bogged down in how to manage the currency/amount management
every time we talk about dropping it, someone swears that they'll finish it
RhodiumToad: any ideas on that query?
rukubites
I guess I would use a table with symbol, amount, decimal_places
RhodiumToad
what query?
I'd like to simplify it
anything with a double-subselect is problematic
especially for a NOT IN
RhodiumToad
(Action) not going to tackle that one tonight
agliodbs
hey masoodmortazavi! what are you doing here on a Friday night?
RhodiumToad: heh
(Action) enjoys abusing windowing functions
if only I could figure out how to invert the inner query
then I could just do a big nested wuery
which would be much faster
oh!
I can!
kemo
every time I want to start pg_bulkload on windows server 2003 I´m getting an error "Couldn´t start application because libpq.dll not found". Could one please help me getting pg_bulkload runing on win server 2003?
peerce
um, probably need to put the directory that has libpq.dll in the path so windows can find it
did you install postgres from the enterprisedb 'one click' installer, or via the PGDG MSI installs, or what?
kemo
used the One click installer for windows
seems like I´m really to dumb to use pg_bulkload on windows :\
masoodmortazavi
Hi agliodbs ... I've been trying to diagnose my WLAN network at home ... After 8 years of heavy use, including most recently streaming Netflix videos, it seems to have finally failed ....
I hope all is well with you.
farciarz84
I would like to get a xml from postgres db data. Is it possible somehow?
mastermind
farciarz84: xml as the result of a query?
??xml
pg_docbot_adz_
For information about 'xml' see:
http://www.hitsw.com/xml_utilites/ :: http://www.postgresql.org/docs/current/static/datatype-xml.html :: http://gborg.postgresql.org/project/xpsql/projdisplay.php :: http://www.throwingbeans.org/postgresql_and_xml_updated.html
farciarz84
mastermind: can I somehow add to postgres the xml functionality if I had it already instaled?
mastermind
farciarz84: what version of postgresql exactly and how did you install it?
farciarz84
sudo apt-get postgres-8.4 :/ uubntu
mastermind
farciarz84: I would expect the ubuntu packages to be compiled with xml support
xintron
seems like my mysql -> postgresql migration didn't work that well :(
farciarz84
mastermind: how to check it basicially if the xml is supported by my postgres installation?
RhodiumToad
farciarz84: select ''::xml;
farciarz84: that'll give an error 'ERROR: unsupported XML feature' if xml was not enabled
farciarz84
I got: ERROR: invalid XML content
LINE 1: select ''::xml;
RhodiumToad
that means xml support is enabled.
try select '<foo>foo</foo>'::xml;
farciarz84
looks good thx
RhodiumToad: how to use query_to_xml? I've tried select query_to_xml('select * from my_table limit 1;', False, False); but I got error
xintron
how do I escape " in postgres?
RhodiumToad
xintron: inside a string literal you don't escape it, unless you're dealing with the string representation of an array or composite type
xintron
RhodiumToad: Now I get this when trying to import a dump: WARNING: nonstandard use of escape in a string literal, LINE 1: ..."status", "type") VALUES (1,'2009-08-17 22:29:22','Hur formu...
with marker on the ' on 'Hur formu
RhodiumToad
where did the dump come from?
xintron
RhodiumToad: MySQL
RhodiumToad
does the string starting 'Hur formu contain any ' characters escaped as \' ?
xintron
Might it be something about "\r\n" in the middle of the string?
RhodiumToad
or that, yes
xintron
RhodiumToad: No, escaped as '' now
RhodiumToad: How do I escape the \r\n then
?
RhodiumToad
you can ignore the warning if you like, it's just pointing out that the use of \ as an escape inside '...' is not in accordance with the sql spec
(and doesn't work if you enable standard_conforming_strings)
farciarz84: what error?
xintron
RhodiumToad: so how do I escape them according to the sql specs?
RhodiumToad
xintron: according to the sql spec, there are no escapes allowed in '...' strings, with the sole exception of '' used to represent '
xintron
oh, ok
RhodiumToad
xintron: pg provides as an extension the form E'...' which allows \-escapes
xintron: the warning message highlights strings whose content will change if you enable standard_conforming_strings - if you never intend to enable that, and you have data from a source where it's not convenient to use E'...', you can disable the warning
xintron
hrmm... it seems I'm now having some trouble with the data not being in UTF-8 event though the dump is in utf8
farciarz84
RhodiumToad: it's about ''. select query_to_xml('select * from my_table where some field = '';', False, False, 'my_table'); so I have doubled '' and don't know how to avoid that or where to put E''
xintron
ah, nvm :)
RhodiumToad
farciarz84: doubling the ' is correct
farciarz84: alternatively, if the query is a constant string, you might prefer this form:
farciarz84: query_to_xml($q$ select * from my_table where some field = '' $q$, ...
farciarz84: the $q$...$q$ is a dollar-quoted string, which can contain any sequence of characters, with no escaping - but the specific $...$ string used to delimit it cannot appear in the content
farciarz84: so it's only really appropriate for constants where you know in advance that the delimiter string is not present
farciarz84
with your advice it's working correct, thank you
how can I force psql that produce pure xml without additional ('
(1 row)
and some '---')
and I still have errors with the '', giving the psql the query prepend and append by '$q$' the syntax error is shown.
inside shell everything works fine
RhodiumToad
you'll need to be more specific
pastebin an example
farciarz84
http://paste.pocoo.org/show/183408/
RhodiumToad: I did my best:)
RhodiumToad
farciarz84: use \a and \t in the psql shell to turn off the headers, footers and other decoration
farciarz84: on the command line, psql -At has the same effect
farciarz84: when using psql -c "...", your problem is that $ is special to the shell even inside " "
farciarz84
how to avoid that?
RhodiumToad
farciarz84: either quote the $s using \, or use different shell quoting
farciarz84: e.g. -c 'query_to_xml($q$ '"select * from table_name where some_field = '930277827'"'$q$, False, True, '"'table_name')" # note that this is very confusing
farciarz84: constructing the command from shell variables may be preferable
okraits
i have a question: is there a possibility to ignore contraints when doing inserts? for backup restoring
heftig
okraits: i believe you drop the constraints, load the data, and then restore the constraints.
okraits
heftig: thanks, i'll do
mastermind
okraits: pg_dump is creating that kind of dump automatically - are you using data-only dumps or something like that?
okraits
mastermind: the backup was created with:
pg_dump -a -D -f "/var/backup/backup_"`date +%d%m%Y`"_enders.sql" -F p -U postgres enders
mastermind: i do one dump of the schema and one of the data
mastermind
okraits: unless you have real good reasons, don't use that for regular backup/restore
also -D will slow your restore speed down significantly
okraits
mastermind: so you would do one complete dump?
mastermind
okraits: for backup&restore purposes doing a custom dump using -Fc is best
okraits
mastermind: my problem is the order of the data inserts so constraints are broken
mastermind
okraits: yes doing a full dump will avoid that problem
okraits
mastermind: you mean schema and data together, right?
mastermind
okraits: 9.0 will be a bit more clever in that regard but in general it might not even be theoretically possible to load data that way due to circular dependencies
okraits: yes because in that case pg_dump will create a dump that has the table definitions first then the data and the contraints at the end of the restore
okraits
mastermind: ah nice
mastermind: ok, so i will get the old data dir in place again, do one complete backup and then push that backup to the new data dir
mastermind
okraits: oh? you are actually trying to upgrade or such?
okraits
mastermind: no, i had to run initdb again because of characterset issues, set -E UTF8 explicit for comparisons with like
mastermind: one question: what means custom output format? (Fc)
mastermind
okraits: it is a postgresql custom dump format that is compressed as well - its structure allows advanced features like parallel restore and selective restore (though you can just get the plain SQL as well if you want it)
you need to use pg_restore to restore a database dumped with -Fc
okraits
mastermind: i see, thanks
mastermind: gonna blame my workmate for doing separate dumps :)
mastermind
okraits: heh well - there are reasons for doing them but not particulary in a dump&restore scenario
okraits
mastermind: yeah, we're running this data-only pg_dump call as a cronjob every morning
mastermind
okraits: for backup?
okraits
mastermind: i think there's no reason to spare the few bytes for the schema if we have problems with restoring afterwards
mastermind: yep
mastermind
okraits: yeah also consider what might happen if somebody changes the schema and you end up with a schemadump from last week and a datadump done with a different schema :)
okraits
mastermind: well, i'm the only one in charge of the schema but nevertheless it's better to have schema and data together :)
Co_Keren
halo
QtPlatypus
Hi
Co_Keren
how are u
QtPlatypus
Better till you said "u"
Co_Keren
u (you)