free blog   apache   shopping directory   php powered
RhodiumToad"group" sounds much more appropriate to me
xinmingRhodiumToad: Because I have to type extra "" around the table name.
xinmingand make the all the code not consistent. :-)
RhodiumToadwell, the other way would be to make the name more specific: something_group
xinmingRhodiumToad: thanks, seems that's the last choice. >_<
kemo0x20 means white space in ascii, or?
RhodiumToad0x20 is a space character, yes
kemok thx
necroforestAnyone 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
necroforestI get this for my regular user and root
necroforestnevermind, apparently i have to do initial setup as a postgres user
necroforestnext question:
necroforestis there a way to create a new per-session entry in pg_settings?
agliodbsnecroforest: can you be more explicit?
necroforestok, i want to create essentially a "session token" that is set per DB session thats used by stored procedures/packages
agliodbswhat will you use it for?
rukubites??copy permissions
pg_docbot_adz_Nothing found
rukubites??copy
pg_docbot_adz_For information about 'copy' see:
pg_docbot_adz_http://www.postgresql.org/docs/current/static/sql-copy.html
pg_docbot_adz_http://www.varlena.com/GeneralBits/116.php
pg_docbot_adz_http://wiki.postgresql.org/wiki/How_to_use_PostgreSQL's_COPY_function_effectively
agliodbscan anyone think of a way to simplify this query?
agliodbshttp://pgsql.privatepaste.com/86db9a651a
rukubitesIs there a way to read from files using COPY (not psql \copy) without being superuser?
agliodbsrukubites: no
agliodbsrukubites: and if you find one, you should report it
rukubitesagliodbs: 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?
agliodbsbecause, COPY reads from a file on the server
agliodbsso it's a file operation on the server
agliodbsif regular users were allowed to do it, then they might use COPY to read files they weren't entitled to
agliodbslike /etc/passwd
rukubitesIs there a reason why it couldn't do the permission checks, so e.g. a database owned by rukubites could read rukubites' files?
rukubitesNot having a whinge, just want to understand the reasoning.
agliodbsthere's no relationship between DB users and shell users
agliodbsit's pretty much a hard rule that any command which can perform file operations on the server has to be the superuser
rukubitesagliodbs: "No relation" ... then why can I connect to a postgresql database owned by rukubites when logged in as rukubites, without any password/auth?
rukubitesBut okay, if that's the way it is, then that's the way it is.
agliodbsthat's called ident auth
pguserWhich 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.
rukubitesinteger is better.
pguserWhat about cents?
agliodbsthe database is just accepting from the OS that a user named "postgres" is allowed to login to the DB as DB user "postgres"
rukubitesWell the problem can occur with rounding errors.
agliodbsbut the relationship only goes that way
agliodbspguser: Numeric
agliodbspguser: Float is a PITA
rukubitespguser: My opinion was based on standard computer science - floats are ... well... a PITA because sometimes 1+1 = 2.0000001.
xinmingshoe, RhodiumToad I finally get the word, I use "team" to replace the word "group" :-)
pguserHmm...
rukubitesBut you can record your money in number of cents.
pguserIt must be able to hold any currency.
pguserMixed.
pguserThis is sort of a log used for legal purposes.
rukubitespguser: Well you can record the decimal place... or use Numeric ... agliodbs seems to know his postgres.
rukubitesI am just a hack
agliodbsnumeric is a fixed-length decimal
pguserWould it be insane to actually use varchar for the amount field?
agliodbs??numeric
pg_docbot_adz_For information about 'numeric' see:
pg_docbot_adz_http://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-NUMERIC-DECIMAL
pguserI don't really think it will have to be calculated...
agliodbspguser: yes
pguserJust reported...
pguserWhy is the "money" type bad again?
agliodbs'cause it was never fully implemented
RhodiumToadno conversions to more useful numeric types
rukubites??money
pg_docbot_adz_For information about 'money' see:
pg_docbot_adz_http://alma.ch/blogs/bahut/2006/04/cannot-cast-type-money.html
RhodiumToadlocale-sensitive input and output
agliodbsgot bogged down in how to manage the currency/amount management
agliodbsevery time we talk about dropping it, someone swears that they'll finish it
agliodbsRhodiumToad: any ideas on that query?
rukubitesI guess I would use a table with symbol, amount, decimal_places
RhodiumToadwhat query?
agliodbshttp://pgsql.privatepaste.com/86db9a651a
agliodbsI'd like to simplify it
agliodbsanything with a double-subselect is problematic
agliodbsespecially for a NOT IN
Action: RhodiumToad not going to tackle that one tonight
agliodbshey masoodmortazavi! what are you doing here on a Friday night?
agliodbsRhodiumToad: heh
Action: agliodbs enjoys abusing windowing functions
agliodbsif only I could figure out how to invert the inner query
agliodbsthen I could just do a big nested wuery
agliodbswhich would be much faster
agliodbsoh!
agliodbsI can!
kemoevery 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?
peerceum, probably need to put the directory that has libpq.dll in the path so windows can find it
peercedid you install postgres from the enterprisedb 'one click' installer, or via the PGDG MSI installs, or what?
kemoused the One click installer for windows
kemoseems like Iīm really to dumb to use pg_bulkload on windows :\
masoodmortazaviHi 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 ....
masoodmortazaviI hope all is well with you.
farciarz84I would like to get a xml from postgres db data. Is it possible somehow?
mastermindfarciarz84: xml as the result of a query?
mastermind??xml
pg_docbot_adz_For information about 'xml' see:
pg_docbot_adz_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
mastermindalso http://www.postgresql.org/docs/current/static/functions-xml.html
farciarz84mastermind: can I somehow add to postgres the xml functionality if I had it already instaled?
mastermindfarciarz84: what version of postgresql exactly and how did you install it?
farciarz84sudo apt-get postgres-8.4 :/ uubntu
mastermindfarciarz84: I would expect the ubuntu packages to be compiled with xml support
xintronseems like my mysql -> postgresql migration didn't work that well :(
farciarz84mastermind: how to check it basicially if the xml is supported by my postgres installation?
RhodiumToadfarciarz84: select ''::xml;
RhodiumToadfarciarz84: that'll give an error 'ERROR:  unsupported XML feature' if xml was not enabled
farciarz84I got: ERROR:  invalid XML content
farciarz84LINE 1: select ''::xml;
RhodiumToadthat means xml support is enabled.
RhodiumToadtry  select '<foo>foo</foo>'::xml;
farciarz84looks good thx
farciarz84RhodiumToad: 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
xintronhow do I escape " in postgres?
RhodiumToadxintron: inside a string literal you don't escape it, unless you're dealing with the string representation of an array or composite type
xintronRhodiumToad: 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...
xintronwith marker on the ' on 'Hur formu
RhodiumToadwhere did the dump come from?
xintronRhodiumToad: MySQL
RhodiumToaddoes the string starting 'Hur formu  contain any ' characters escaped as \'  ?
xintronMight it be something about "\r\n" in the middle of the string?
RhodiumToador that, yes
xintronRhodiumToad: No, escaped as '' now
xintronRhodiumToad: How do I escape the \r\n then
xintron?
RhodiumToadyou 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
RhodiumToad(and doesn't work if you enable standard_conforming_strings)
RhodiumToadfarciarz84: what error?
xintronRhodiumToad: so how do I escape them according to the sql specs?
RhodiumToadxintron: according to the sql spec, there are no escapes allowed in '...' strings, with the sole exception of '' used to represent '
xintronoh, ok
RhodiumToadxintron: pg provides as an extension the form E'...' which allows \-escapes
RhodiumToadxintron: 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
xintronhrmm... it seems I'm now having some trouble with the data not being in UTF-8 event though the dump is in utf8
farciarz84RhodiumToad: 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''
xintronah, nvm :)
RhodiumToadfarciarz84: doubling the ' is correct
RhodiumToadfarciarz84: alternatively, if the query is a constant string, you might prefer this form:
RhodiumToadfarciarz84: query_to_xml($q$ select * from my_table where some field = '' $q$, ...
RhodiumToadfarciarz84: 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
RhodiumToadfarciarz84: so it's only really appropriate for constants where you know in advance that the delimiter string is not present
farciarz84with your advice it's working correct, thank you
farciarz84how can I force psql that produce pure xml without additional ('
farciarz84(1 row)
farciarz84and some '---')
farciarz84and I still have errors with the '', giving the psql the query prepend and append by '$q$' the syntax error is shown.
farciarz84inside shell everything works fine
RhodiumToadyou'll need to be more specific
RhodiumToadpastebin an example
farciarz84http://paste.pocoo.org/show/183408/
farciarz84RhodiumToad: I did my best:)
RhodiumToadfarciarz84: use \a and \t in the psql shell to turn off the headers, footers and other decoration
RhodiumToadfarciarz84: on the command line, psql -At has the same effect
RhodiumToadfarciarz84: when using psql -c "...", your problem is that $ is special to the shell even inside " "
farciarz84how to avoid that?
RhodiumToadfarciarz84: either quote the $s using \, or use different shell quoting
RhodiumToadfarciarz84: 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
RhodiumToadfarciarz84: constructing the command from shell variables may be preferable
okraitsi have a question: is there a possibility to ignore contraints when doing inserts? for backup restoring
heftigokraits: i believe you drop the constraints, load the data, and then restore the constraints.
heftigokraits: look at http://www.postgresql.org/docs/current/interactive/populate.html
okraitsheftig: thanks, i'll do
mastermindokraits: pg_dump is creating that kind of dump automatically - are you using data-only dumps or something like that?
okraitsmastermind: the backup was created with:
okraitspg_dump -a -D -f "/var/backup/backup_"`date +%d%m%Y`"_enders.sql" -F p -U postgres enders
okraitsmastermind: i do one dump of the schema and one of the data
mastermindokraits: unless you have real good reasons, don't use that for regular backup/restore
mastermindalso -D will slow your restore speed down significantly
okraitsmastermind: so you would do one complete dump?
mastermindokraits: for backup&restore purposes doing a custom dump using -Fc is best
okraitsmastermind: my problem is the order of the data inserts so constraints are broken
mastermindokraits: yes doing a full dump will avoid that problem
okraitsmastermind: you mean schema and data together, right?
mastermindokraits: 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
mastermindokraits: 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
okraitsmastermind: ah nice
okraitsmastermind: 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
mastermindokraits: oh? you are actually trying to upgrade or such?
okraitsmastermind: no, i had to run initdb again because of characterset issues, set -E UTF8 explicit for comparisons with like
okraitsmastermind: one question: what means custom output format? (Fc)
mastermindokraits: 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)
mastermindyou need to use pg_restore to restore a database dumped with -Fc
okraitsmastermind: i see, thanks
okraitsmastermind: gonna blame my workmate for doing separate dumps :)
mastermindokraits: heh well - there are reasons for doing them but not particulary in a dump&restore scenario
okraitsmastermind: yeah, we're running this data-only pg_dump call as a cronjob every morning
mastermindokraits: for backup?
okraitsmastermind: i think there's no reason to spare the few bytes for the schema if we have problems with restoring afterwards
okraitsmastermind: yep
mastermindokraits: 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 :)
okraitsmastermind: well, i'm the only one in charge of the schema but nevertheless it's better to have schema and data together :)
Co_Kerenhalo
QtPlatypusHi
Co_Kerenhow are u
QtPlatypusBetter till you said "u"
Co_Kerenu (you)
Popular searches: undefined reference qbswap   django feedjack   perl Tk   what is the meaning of   

Generated by irclog2html.pl 2.1mg by Jeff Waugh - return