[GENERAL] unexpected (to me) sorting order
Hi! below are some commands to replicate a strange sorting order. I do not see why id:s 3-6 are in the middle of the result set. What am I missing? begin; create table T_SORT ( ID bigint default 1 not null , -- Primary Key NAME varchar(100) default ' ' not null ); alter table T_SORT add constraint T_SORTP1 primary key ( ID ); insert into T_SORT values ( 1,'FINISH_110_150_1'); insert into T_SORT values ( 2,'FINISH_110_200_1'); insert into T_SORT values ( 3,'FINISH_1.10_20.0_3'); insert into T_SORT values ( 4,'FINISH_1.10_20.0_4'); insert into T_SORT values ( 5,'FINISH_1.10_30.0_3'); insert into T_SORT values ( 6,'FINISH_1.10_30.0_4'); insert into T_SORT values ( 7,'FINISH_120_150_1'); insert into T_SORT values ( 8,'FINISH_120_200_1'); select * from T_SORT order by NAME ; rollback; id |name + 1 | FINISH_110_150_1 2 | FINISH_110_200_1 3 | FINISH_1.10_20.0_3 4 | FINISH_1.10_20.0_4 5 | FINISH_1.10_30.0_3 6 | FINISH_1.10_30.0_4 7 | FINISH_120_150_1 8 | FINISH_120_200_1 (8 rows) why is FINISH_1.10_20.0_3 between FINISH_110_200_1 and FINISH_120_150_1 ? That is why is '.' between 1 and 2 as in 110/120 ? pg_admin III reports the database is created like CREATE DATABASE bnl WITH OWNER = bnl ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; bnl= select version(); version -- PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit (1 row) psql says psql (9.3.5, server 9.3.3) It is an Amazon RDS-service client machine bnl@prod:~$ uname -a Linux prod 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u1 x86_64 GNU/Linux bnl@prod:~$ locale LANG=en_US.UTF-8 LANGUAGE= LC_CTYPE=en_US.UTF-8 LC_NUMERIC=en_US.UTF-8 LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_PAPER=en_US.UTF-8 LC_NAME=en_US.UTF-8 LC_ADDRESS=en_US.UTF-8 LC_TELEPHONE=en_US.UTF-8 LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=en_US.UTF-8 LC_ALL= -- /Björn
Re: [GENERAL] unexpected (to me) sorting order
On 2015-04-08 11:33, Glyn Astill wrote: The collation of your bnl database is utf8, so the . punctuation character is seen as a variable element and given a lower weighting in the sort to the rest of the characters. That's just how the collate algorithm works in UTF8. Try with LC_COLLATE = 'C' and it should sort how you expect. Ok. And as Chris Mair says in his answer, it looks like the '.' is ignored, and then I see the pattern Thanks -- Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected (to me) sorting order
On 2015-04-08 11:36, Chris Mair wrote: I don't know what's the rationale behin this, but it looks like Linux ignores the . when doing the sort. Yes, I see that now, and it makes sense Thanks. -- Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected (to me) sorting order
On 2015-04-08 13:10, Glyn Astill wrote: From: Chris Mair ch...@1006.org I think this is down to behaviour changes in glibc, there was a thread a while ago where somebody replicating via streaming rep between with different versions of glibc ended up in a bit of a pickle. http://www.postgresql.org/message-id/ba6132ed-1f6b-4a0b-ac22-81278f5ab...@tripadvisor.com interesting - thanks -- Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Uppercase to lowercase trigger?
Hmm, I was under the impression that if you create the table with quoted field names, you get case-sensitive names, But if you create the tables without quotes around the the field names, pg will make them lowercase, But case-insensitive That way you don't need views or rewrite. Do create table foo( bar int); Instead of Create table foo (bar int); /björn lundin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Post Install / Secure PostgreSQL
I can't seem to find out how to login to the database. I am using 'psql -U root' however during my installation there may have been a default password used which I am not aware of. I need to read the docs and see how to login to the database. I usually do like this on a new box sudo su - su - postgres createuser bnl exit exit createdb bnl psql That is, I create a user in the db with same name as my os user (linux here) then I log out from pg superuser account, and go back to my os user, and create a database with that os user name. That is the default db that psql tries to log in to... -- björn lundin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Missing rows in resultset
On 30 Aug, 00:02, björn lundin b.f.lun...@gmail.com wrote: Or is it just beeing late, and me being blind? eyetv=# select * from programmes where title like 'Star*'; It was of course me being blind... select * from programmes where title like 'Star%'; is the correct way. Wrong wildcard character '%' instead of '*' Sorry for the noise /Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Missing rows in resultset
I got a table holding tv air time but I got a unexpected (to me) behaviour. Using like, I do not get the recordset I'd like. I've installed it using mac-ports on an old mac-mini, ppc I'd expect to see the same rows with wildcard, as I see without, see below Or is it just beeing late, and me being blind? eyetv=# select version(); version --- - PostgreSQL 8.4.4 on powerpc-apple-darwin9.8.0, compiled by GCC powerpc-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5493) , 32-bit (1 row) Time: 0.724 ms eyetv=# select * from programmes where title like 'Star Wars'; start|stop | channel| title | category | episode | description -+-+--+--- +--+--+--- 2010-09-04 19:00:00 | 2010-09-04 19:30:00 | tv400.tv4.se | Star Wars | series | Del 7 av 22 säsong 2009 | Del 7 av 22. 2010-09-04 19:30:00 | 2010-09-04 20:00:00 | tv400.tv4.se | Star Wars | series | Del 8 av 22 säsong 2009 | Del 8 av 22. 2010-09-05 23:50:00 | 2010-09-06 00:20:00 | tv400.tv4.se | Star Wars | series | Del 7 av 22 säsong 2009 | Del 7 av 22. 2010-09-06 00:20:00 | 2010-09-06 00:45:00 | tv400.tv4.se | Star Wars | series | Del 8 av 22 säsong 2009 | Del 8 av 22. 2010-09-11 19:00:00 | 2010-09-11 19:30:00 | tv400.tv4.se | Star Wars | series | Del 9 av 22 säsong 2009 | Del 9 av 22. 2010-09-11 19:30:00 | 2010-09-11 20:00:00 | tv400.tv4.se | Star Wars | series | Del 10 av 22 säsong 2009 | Del 10 av 22. 2010-08-28 19:00:00 | 2010-08-28 19:30:00 | tv400.tv4.se | Star Wars | series | Del 5 av 22 säsong 2009 | Del 5 av 22. 2010-08-28 19:30:00 | 2010-08-28 20:00:00 | tv400.tv4.se | Star Wars | series | Del 6 av 22 säsong 2009 | Del 6 av 22. 2010-08-29 23:40:00 | 2010-08-30 00:10:00 | tv400.tv4.se | Star Wars | series | Del 5 av 22 säsong 2009 | Del 5 av 22. 2010-08-30 00:10:00 | 2010-08-30 00:40:00 | tv400.tv4.se | Star Wars | series | Del 6 av 22 säsong 2009 | Del 6 av 22. (10 rows) Time: 108.087 ms eyetv=# select * from programmes where title like 'Star*'; start | stop | channel | title | category | episode | description ---+--+-+---+--+-+- (0 rows) Time: 82.176 ms eyetv=# \d programmes Table public.programmes Column|Type | Modifiers -+-+--- start | timestamp without time zone | not null stop| timestamp without time zone | not null channel | text| not null title | text| not null category| text| episode | text| description | text| Indexes: programmes_pkey PRIMARY KEY, btree (start, stop, channel) eyetv=# /Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] server crash = libpq poll() hangs forever (Linux)
On 9 Juni, 16:37, t...@sss.pgh.pa.us (Tom Lane) wrote: Marinos Yannikos m...@geizhals.at writes: It seems that poll() never receives a connection closed notification under Linux (https://lists.linux-foundation.org/pipermail/bugme-new/2003-April/008...- very old report, very old report is right. What makes you think that has anything to do with modern kernel versions? Interesting. The bug report includes a short code snippet which compiles to a c program, that shows the bug is still present. I'm on b...@tova:~$ uname -a Linux tova 2.6.31-22-generic #60-Ubuntu SMP Thu May 27 00:22:23 UTC 2010 i686 GNU/Linux is it really so, that the bug is still valid, or does the code snippet show something else? /Björn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] newbie table design question
CREATE TABLE DataImport ( DataImportID serial NOT NULL PRIMARY KEY, Time timestamp without time zone NOT NULL, ID_ABC integer NOT NULL, ID_DEF integer NOT NULL, ID_HIJ integer NOT NULL, etc ); Perhaps you want to not use the around the table and column names. It makes them case sensitive -- Björn Lundin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Survey: renaming/removing script binaries (createdb, createuser...)
1) What type of names do you prefer? d) remove them - psql is the solution 2) How often do you use these tools? a) every day (e.g. in my cron) 3) What name of initdb do you prefer? d) pg_ctl -d dir init (replace initdb with pg_ctl new functionality) 4) How do you perform VACUUM? a) vacuumdb - shell command I like the idea of fewer tools. Will change to do VACUUM from psql instead /Björn Lundin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Export data to MS Excel
Ashish Karalkar wrote: Hello All, I want to export data from PostgreSQL tables to MS Excel. Is there any way? ODBC is one way to do it. Use the data import, that runs msquery -- /Björn [EMAIL PROTECTED] ---(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
Re: [GENERAL] DBI/DBD::Pg and transactions
On Thursday 19 July 2007 00:03:19 Roderick A. Anderson wrote: I'll post my solution ... when I figure one out! You could, in the script from cron: 1 - check for the presence of rows in a 'alive_scripts_table' if any , then exit, and go for the next run, alternativly, check that pid in 'ps -ef' so there actually is a script running 2 - create a record in a table, ie insert your pid into a alive_script_table, 3- do your thing 4 - delete your row from the alive_script_table or use above with a file instead or just check with ps/tasklist/wmic -- /Björn ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] using PREPAREd statements in CURSOR
Hello! I'm connecting via libpq and want to use prepared statements in a cursor. Is there a sample somewhere, since I cannot get it to work. sebjlun=# \d ssignal Table public.ssignal Column | Type | Modifiers -+---+--- ssignam | character(12) | not null ssigdes | character(30) | not null ssopcid | character(40) | sstatyp | integer | not null sstatid | integer | not null ssigtyp | integer | not null ssigadd | integer | not null ssigran | integer | not null Indexes: ssignalp1 PRIMARY KEY, btree (ssignam) ssignali3 btree (sstatyp, sstatid) sebjlun=# prepare test as sebjlun-# select * from SSIGNAL where SSTATYP=$1 and SSTATID=$2 order by SSIGNAM sebjlun-# ; PREPARE sebjlun=# execute test(4,6); ssignam|ssigdes | ssopcid | sstatyp | sstatid | ssigtyp | ssigadd | ssigran --+ + --+-+-+-+-+- CD_PLC_SS| Counter delay output PLC | | 4 | 6 | 2 |2103 | 16 CD_SS_PLC| Counter delay input SS | | 4 | 6 | 2 |2003 | 16 CN_PLC_SS| Counter number output PLC | | 4 | 6 | 2 |2102 | 16 CN_SS_PLC| Counter Number input SS| | 4 | 6 | 2 |2002 | 16 so far so good sebjlun=# declare cursor ctest for test(4,6); ERROR: syntax error at or near ctest LINE 1: declare cursor ctest for test(4,6); ^ sebjlun=# How would I express that? Combining cursors with parameter seems not to be the way either sebjlun=# Declare C240 cursor for select * from SSIGNAL where SSTATYP=$1 and SSTATID=$2 order by SSIGNAM; ERROR: there is no parameter $1 sebjlun=# /Björn
Re: [GENERAL] using PREPAREd statements in CURSOR
28 jun 2007 kl. 16.45 skrev Tom Lane: =?ISO-8859-1?Q?Bj=F6rn_Lundin?= [EMAIL PROTECTED] writes: I'm connecting via libpq and want to use prepared statements in a cursor. You can't. That explains why I could not find an example... If you're just interested in fetching a large query result in sections, No, I'm writing a sql-binding to libpq, for use with a current system, that is written with Oracle as db. The processes all use a sql-binding to Oracle, and in order not to rewrite them, I want to keep the cursor handling. The processes are filled with code snippets like this: prepare(Statement1,select col3,col4 from table_a where col1 = :COL_A and col2 = :COL_B); set(Statement1,COLA_A,10); set(Statement1,COLA_B,ABC); open_cursor(Statement1) loop fetch(Statement1, end_of_set); exit when end_of_set; get(Statement1,col3,var3); get(Statement1,col4,var4); end loop; close_cursor(Statement); --use var3 and var4 here So I redesigned, and use plain strings, that I pass to libpq. They are build on the fly. Since I prefer keeping the cursor, over the prepared statements, is there any performance gain I can do, besides fetching say 100 rowa at a time, rather than 1. (The fetch above is a wrapper to libpq's fetch) I'm thinking, is it better to explicitly cast the bind variables in the statement string? The above statement would be sent to libpq as declare cursor xyz as select col3,col4 from table_a where col1 = 10 and col2 = 'ABC' Would it be better to send it as declare cursor xyz as select col3,col4 from table_a where col1 = 10::integer and col2 = 'ABC::text' I will use integer, float, character(n), date, time w/o tz (should perhaps be 'ABC::character(3)') Or should I use say int4 instead of integer? The character(3) are constrained by the host language, ie Ada. A well-thought-out API proposal would probably be favorably received. Hmm, I would think that would be over my head... But, in a way I'm glad that the PQPrepare is not an option, passing variables in an array from Ada to C would give at least some headache. The 'set' approach would be easier, when interfacing from other languages, I think. (yes, DECLARE CURSOR is planned differently than a plain select). And which way is to be preferred? /Björn
Re: [GENERAL] PostgreSql embedded available?
8 maj 2007 kl. 21.07 skrev Rich Shepard: On Tue, 8 May 2007, Scott Marlowe wrote: Is there a version of PostgreSql that can be embedded with an application? Nope, and it's not real likely to happen. Take a look at sqllite. Yes, SQLite (http://www.sqlite.org/) is the way to go for embedded RDBMS. or perhaps Mimer http://www.mimer.com/ /Björn Björn Lundin
Re: [GENERAL] Documenting PGSQL database.
7 apr 2007 kl. 21.07 skrev RPK: I am using PGSQL 8.2.3 on Windows XP. I want to know whether there is any way of documenting the schema of PGSQL database so that I can get a printed copy of the same. Hmm, I did not get my own post , so I thought I'd reply on this one again Looking through what has happened since 2003, when I wrote that documentation thing, I decided to update a bit, translate it to english, provide a small sample of how it can look, and changing the toolchain. It is at http://lundin.homelinux.net/docbook_xml/target/html/ dbdocumentation.html /Björn Björn Lundin b dot f dot lundin at gmail dot com
Re: [GENERAL] Documenting PGSQL database.
7 apr 2007 kl. 21.07 skrev RPK: I am using PGSQL 8.2.3 on Windows XP. I want to know whether there is any way of documenting the schema of PGSQL database so that I can get a printed copy of the same. Some years ago (2003) I did that, and published it at http:// www.linuxbog.dk/dokumentation/dokumentation/c1187.html Now, that is written in swedish, but by looking at the examples and scripts you will get the idea. it's a pgtcl script that makes use of data from pg_catalog. It produces a xml-file, that is supposed to be put through an xsl transformation tool that will end up the data in docbook format. Also some information of setting up a docbook toolchain is provided, Now, at the time, PG 7.3.1 was I got, so it might not be still correct. In the end, there is a makefile example of genrating html and pdf of it. Given you are on windows, the makfile commands may be run by hand instead. I think pgtcl will run on windows, and the docbook tools I used were all java. /Björn Björn Lundin b dot f dot lundin at gmail dot com
Re: [GENERAL] Online update races
Lada 'Ray' Lostak wrote: I will also appreciate any links to web resources, talking about this problem. I didn't find anything usefull around. I'm working with developing a fairly big warehouse management system, and there we see this problem every day. We've settled (many years ago) for the following: loop begin select * from my_table where status = :status (read into record structure) update my_table set a=1, b=2 where and status = :status and another_status = :status2 and lates_updater =:record.latest_updater and latest_date = :record.latest_date and latest_time = :record.latest_time commit exit loop exception when transaction_conflict | no_such_row = rollback delay small time end loop; transaction_conflict | no_such_row should be interpreted as no row matched. The flaw this design has, is that you can't separate 'real transaction conflicts' with situation where other conditions failed, as no row had another_status = status2 However, we find the design good enough to keep using it. It has the advantage that no table is ever looked, we don't use select for update at all. -- /Björn --- http://lundin.homelinux.net Registered Linux User No. 267342 http://counter.li.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] error: insert has more expressions than target column
Dino Vliet wrote: MUCH better nowI did manage to get an insert into the table lessons with these adjustments...BUT now it seems the FOR LOOP didn't work because I only get 1 record and expected that I would get 8 records due to the i variabele. What could be wrong? My code is now: CREATE FUNCTION vulalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP INSERT INTO lessons (..) SELECT dayofweek,startdate + (i*7), enddate + (i*7),...; RETURN NEW; END LOOP; END; ' LANGUAGE plpgsql; Is the 'RETURN NEW' statement supposed to be _BEFORE_ end loop? To me, it looks like you are returning from the function in the first loop turn. /Njörn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Copying data from one table to another
Erwin Van de Velde wrote: Hi, I have to copy data from one table to another, and I was wondering if there is an easier way to do that than to have a lot of inserts one after another. insert into target_table select * from source-table where ...; Björn Lundin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] RE: Autocommit off in psql??
Yes, I meant turn off Autocommit for the entire psql session. Starting every manipulation of the database with a BEGIN is a bit tiresome, and i often catch myself with forgetting it. If it isn't possible, it would be a nice featue!? Björn Tim Mickol wrote: Actually, I think the original question alluded to SQL*PLUS behavior that allows one to disable autocommit for an ENTIRE session, not simply a discrete transaction. In psql, in order to enforce autocommit=off for an entire session, you must repeatedly issue a series of BEGIN directives, one following each ROLLBACK, COMMIT, or ABORT, yes? Yes, just start an explicit transaction with BEGIN -- this disables autocommit (so when you want to commit your transaction, you'll need to do COMMIT by hand). Cheers, Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Autocommit off in psql??
Hi! Is there a way of turning autocommit of in psql ? It would be nice for people used to Oracle's SQL*Plus. No fatal error has occurred yet, but some minor problems could have been avoided if rollback was possible when the fingers on the keyboard are faster than the brain :) Björn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Database shutdown
Christian Marschalek wrote: Hi all of you! :o) I guess I do have to shut down the database before shutting down the linux box? How would I accomplish this? Just by killing the postmaster per pid? Tia and regards! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Add a symbolic link to /etc/inid.d/postgresql in /etc/inid.d/rcd.6 and /etc/inid.d/rcd.0 (reboot and shutdown) ln -s /etc/init.d/postgresql /etc/inid.d/rcd.0/K15postgresql The K make the call with 'stop'. At boottime there's proberly a call to /etc/inid.d/rcd.0/S85postgresql where the S makes the call with 'start' To shutdown manually just su to root and run '/etc/init.d/postgresql stop' The links kan be accomplished by running (as root) tksysv (a gui to start and stop services) Bjrn ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
