RhodiumToad	"group" sounds much more appropriate to me
xinming	RhodiumToad: Because I have to type extra "" around the table name.
xinming	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
necroforest	I get this for my regular user and root
necroforest	nevermind, apparently i have to do initial setup as a postgres user
necroforest	next question:
necroforest	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:
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
agliodbs	can anyone think of a way to simplify this query?
agliodbs	http://pgsql.privatepaste.com/86db9a651a
rukubites	Is there a way to read from files using COPY (not psql \copy) without being superuser?
agliodbs	rukubites: no
agliodbs	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
agliodbs	so it's a file operation on the server
agliodbs	if regular users were allowed to do it, then they might use COPY to read files they weren't entitled to
agliodbs	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?
rukubites	Not having a whinge, just want to understand the reasoning.
agliodbs	there's no relationship between DB users and shell users
agliodbs	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?
rukubites	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
agliodbs	pguser: Numeric
agliodbs	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.
pguser	Mixed.
pguser	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.
rukubites	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:
pg_docbot_adz_	http://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-NUMERIC-DECIMAL
pguser	I don't really think it will have to be calculated...
agliodbs	pguser: yes
pguser	Just reported...
pguser	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:
pg_docbot_adz_	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
agliodbs	every time we talk about dropping it, someone swears that they'll finish it
agliodbs	RhodiumToad: any ideas on that query?
rukubites	I guess I would use a table with symbol, amount, decimal_places
RhodiumToad	what query?
agliodbs	http://pgsql.privatepaste.com/86db9a651a
agliodbs	I'd like to simplify it
agliodbs	anything with a double-subselect is problematic
agliodbs	especially for a NOT IN
Action: RhodiumToad not going to tackle that one tonight
agliodbs	hey masoodmortazavi! what are you doing here on a Friday night?
agliodbs	RhodiumToad: heh
Action: agliodbs enjoys abusing windowing functions
agliodbs	if only I could figure out how to invert the inner query
agliodbs	then I could just do a big nested wuery
agliodbs	which would be much faster
agliodbs	oh!
agliodbs	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
peerce	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
kemo	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 ....
masoodmortazavi	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?
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
mastermind	also http://www.postgresql.org/docs/current/static/functions-xml.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;
RhodiumToad	farciarz84: that'll give an error 'ERROR:  unsupported XML feature' if xml was not enabled
farciarz84	I got: ERROR:  invalid XML content
farciarz84	LINE 1: select ''::xml;
RhodiumToad	that means xml support is enabled.
RhodiumToad	try  select '<foo>foo</foo>'::xml;
farciarz84	looks good thx
farciarz84	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...
xintron	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
xintron	RhodiumToad: How do I escape the \r\n then
xintron	?
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
RhodiumToad	(and doesn't work if you enable standard_conforming_strings)
RhodiumToad	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
RhodiumToad	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
RhodiumToad	farciarz84: alternatively, if the query is a constant string, you might prefer this form:
RhodiumToad	farciarz84: query_to_xml($q$ select * from my_table where some field = '' $q$, ...
RhodiumToad	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
RhodiumToad	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
farciarz84	how can I force psql that produce pure xml without additional ('
farciarz84	(1 row)
farciarz84	and some '---')
farciarz84	and I still have errors with the '', giving the psql the query prepend and append by '$q$' the syntax error is shown.
farciarz84	inside shell everything works fine
RhodiumToad	you'll need to be more specific
RhodiumToad	pastebin an example
farciarz84	http://paste.pocoo.org/show/183408/
farciarz84	RhodiumToad: I did my best:)
RhodiumToad	farciarz84: use \a and \t in the psql shell to turn off the headers, footers and other decoration
RhodiumToad	farciarz84: on the command line, psql -At has the same effect
RhodiumToad	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
RhodiumToad	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
RhodiumToad	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.
heftig	okraits: look at http://www.postgresql.org/docs/current/interactive/populate.html
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:
okraits	pg_dump -a -D -f "/var/backup/backup_"`date +%d%m%Y`"_enders.sql" -F p -U postgres enders
okraits	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
mastermind	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
mastermind	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
okraits	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
okraits	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)
mastermind	you need to use pg_restore to restore a database dumped with -Fc
okraits	mastermind: i see, thanks
okraits	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
okraits	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)

Popular searches: undefined reference qbswap   django feedjack   perl Tk   what is the meaning of   

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