[SQL] cast bool/int
Hi, I need a casting operator from boolean to integer, tu put in ALTER TABLE statment after USING. Any ideas? Thanks. Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > Hi, > I need a casting operator from boolean to integer, > tu put in ALTER TABLE statment after USING. > How about CASE WHEN 't' THEN 1 ELSE 0 END > Any ideas? Thanks. > > Zdravko > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > Hi, > I need a casting operator from boolean to integer, > tu put in ALTER TABLE statment after USING. > Sorry in the above email i meant smth like CASE WHEN column='t' THEN 1 ELSE 0 END however SELECT 't'::boolean::int; int4 -- 1 (1 row) and SELECT 'f'::boolean::int; int4 -- 0 (1 row) work in 8.3 > Any ideas? Thanks. > > Zdravko > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bash & postgres
Erik Jones writes: > On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: >> How do I use \c (or any other psql commands beginning with a "\") in a >> bash script? > For multi-line input to a psql call in a bash (or any decent shell) > script, I'd use a here document: Or echo/cat the script into psql's stdin, if you prefer that type of notation. The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
On Mon, Mar 23, 2009 at 10:18:31AM +0200, Achilleas Mantzios wrote: > Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > > > Hi, > > I need a casting operator from boolean to integer, > > tu put in ALTER TABLE statment after USING. > > > > Sorry in the above email i meant smth like > CASE WHEN column='t' THEN 1 ELSE 0 END Or just CASE WHEN column THEN 1 ELSE 0 END. - Josh / eggyknap signature.asc Description: Digital signature
Re: [SQL] cast bool/int
This: SELECT true::integer, false::integer also works on 8.1 -- regards mk 2009/3/23 Achilleas Mantzios > Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > > > Hi, > > I need a casting operator from boolean to integer, > > tu put in ALTER TABLE statment after USING. > > > > Sorry in the above email i meant smth like > CASE WHEN column='t' THEN 1 ELSE 0 END > > however > SELECT 't'::boolean::int; > int4 > -- >1 > (1 row) > > and > SELECT 'f'::boolean::int; > int4 > -- >0 > (1 row) > > work in 8.3 > > > Any ideas? Thanks. > > > > Zdravko > > > > > > > > -- > Achilleas Mantzios > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] cast bool/int
CASE WHEN column='t' THEN 1 ELSE 0 END Or just CASE WHEN column THEN 1 ELSE 0 END. In the mean time I've got an elegant solution: alter ... ... column TYPE smallint USING column::boolean::int::smallint; It works, you wouldn't beleive it. ;) Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] pg_restore error - Any Idea?
Eric, There was no change in the version, we are using postgres v8.3.5 Thanks Deepak On Sun, Mar 22, 2009 at 11:17 PM, Erik Jones wrote: > > On Mar 22, 2009, at 10:44 PM, DM wrote: > > Hi All, >> >> I am facing an error on executing the below command >> >> dump name: pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing >> databae name: pnqd_test >> >> $pg_restore -U postgres -p 5433 -d pnqd_test >> pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing >> >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 3715; 0 0 ACL monitor >> postgres >> WARNING: errors ignored on restore: 1 >> >> I am not able to figure out this issue. Any idea guys. >> > > > TOC -> Table of Contents > > A dump made with pg_dump's -Fc will contain a table of contents of all of > the database objects in the dump file. Something in that is causing an > error for pg_restore. Does the version of pg_restore match up with the > version of pg_dump that you used to make the dump? > > Erik Jones, Database Administrator > Engine Yard > Support, Scalability, Reliability > 866.518.9273 x 260 > Location: US/Pacific > IRC: mage2k > > > > > >
Re: [GENERAL] [SQL] bash & postgres
On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: Erik Jones writes: On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: How do I use \c (or any other psql commands beginning with a "\") in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: Or echo/cat the script into psql's stdin, if you prefer that type of notation. The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. Hmm... Apparently it does recognize them as long as the backslash is the first character in the command string: $ psql -c '\d' postgres No relations found. $ psql -c ' \d' postgres ERROR: syntax error at or near "\" LINE 1: \d ^ Is that expected behavior? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [GENERAL] [SQL] bash & postgres
Erik Jones writes: > On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: >> The reason you have to do this is that psql doesn't recognize >> backslash commands in a -c string. There's a school of thought that >> doesn't want us to allow multiple commands in a -c string, even. > Hmm... Apparently it does recognize them as long as the backslash is > the first character in the command string: Hmm, maybe I was just misremembering the details. What's certainly true is that psql doesn't run a -c string through the same extensive parsing that data from stdin gets. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
