Re: [GENERAL] Using psql -f to load a UTF8 file
On 2012-09-21, Craig Ringer wrote: > I strongly disagree. The BOM provides a useful and standard way to > differentiate UTF-8 encoded text files what is stopping non utf8 files from starting with something that looks like a BOM? -- ⚂⚃ 100% natural -- 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] RFE: Column aliases in WHERE clauses
On Sat, 2012-09-22 at 20:00 -0700, Chris Travers wrote: > On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts > wrote: > On 2012-09-18, Rafal Pietrak wrote: [-] > > could be written by user as (3): > > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND > inverse > 20; > > but token/replaced to its form (2) before WHERE evaluation. > > > Macros are confusing: > > select random()*10 as confusion from generate_series(1,10) > where confusion > 5; No dought about that. And as I really cannot tell you if such processing-alias-as-macro (if available) would make me more error prone or not; I deffinitly know, that I often "upsss.." and rewrite an item from SELECT list into the WHERE clause - because as a common sql-user I do forget such nuances. Learning (for good :), that the " as " is *not* a "definition of a logical/local short-name for an expression" (e.g. it is, but only within the context of SQL statement evaluation sequence) is really counterintuitive for an sql-user like myself. > > Also you can already do this: > > > CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE > AS $$ > select case when $1.x = 0 then null else 1/$1.x end; > $$; > Hmmm, well. No. This is an overkill to a problem. I'd rather stay with SELECT list item copyed by hand into the WHERE clauses. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
Hi, I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted postgresql, it displayed my postgresql is 9.2 but when I log into postgresql, show version, it is still 8.4.13, see a) and b) below, a) * Restarting PostgreSQL 9.2 database server ...done. b) psql (8.4.13) Type "help" for help. postgres=# SELECT version(); version -- PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit (1 row) Anything I have missed in my upgrade? please help. Regards Arthur -- 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] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
On 2012-09-23 14:47, a...@hsk.hk wrote: Hi, I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted postgresql, it displayed my postgresql is 9.2 but when I log into postgresql, show version, it is still 8.4.13, see a) and b) below, a) * Restarting PostgreSQL 9.2 database server ...done. AFAIK, postgres does not have any similar message like that : * Restarting PostgreSQL 9.2 database server ...done. This message probably is from your OS upgrade tool, not from postgres. b) psql (8.4.13) Type "help" for help. postgres=# SELECT version(); version -- PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit (1 row) That is the true version that you have installed. Anything I have missed in my upgrade? please help. Check logs of your installation tool, the problems can be different: Like bug in your installation tool, bug in restarting part of that tool unable to restart server. Cheers, C. -- 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] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
On 09/23/2012 04:47 AM, a...@hsk.hk wrote: Hi, I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted postgresql, it displayed my postgresql is 9.2 but when I log into postgresql, show version, it is still 8.4.13, see a) and b) below, a) * Restarting PostgreSQL 9.2 database server ...done. b) psql (8.4.13) Type "help" for help. postgres=# SELECT version(); version -- PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit (1 row) Anything I have missed in my upgrade? please help. My guess is it installed a parallel version of Postgres for 9.2 and that 8.4.13 is listening to the default port of 5432 which is what you are connecting to. The 9.2 version is probably listening on another port(at a guess 5433). I would do a ps ax to confirm there is more than one instance of Postgres running. Regards Arthur -- Adrian Klaver adrian.kla...@gmail.com -- 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] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
On 23/09/12 11:48, Adrian Klaver wrote: On 09/23/2012 04:47 AM, a...@hsk.hk wrote: Hi, I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted postgresql, it displayed my postgresql is 9.2 but when I log into postgresql, show version, it is still 8.4.13, see a) and b) below, My guess is it installed a parallel version of Postgres for 9.2 and that 8.4.13 is listening to the default port of 5432 which is what you are connecting to. The 9.2 version is probably listening on another port(at a guess 5433). I would do a ps ax to confirm there is more than one instance of Postgres running. This is correct. Ubuntu (Debian really) installs the new vesion in parallel and provides the pg_upgradecluster tool to migrate your old data to the new version. Once you are satisfied that everything is working you can use pg_dropcluster to remove the old data and you can then uninstall 8.4. Martin -- 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] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
On 23/09/2012 19:31, Martin Collins wrote: > On 23/09/12 11:48, Adrian Klaver wrote: >> On 09/23/2012 04:47 AM, a...@hsk.hk wrote: >>> Hi, >>> >>> I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted >>> postgresql, it displayed my postgresql is 9.2 but when I log into >>> postgresql, show version, it is still 8.4.13, see a) and b) >>> below, >>> >> >> My guess is it installed a parallel version of Postgres for 9.2 and >> that 8.4.13 is listening to the default port of 5432 which is what >> you are connecting to. The 9.2 version is probably listening on >> another port(at a guess 5433). I would do a ps ax to confirm there is >> more than one instance of Postgres running. > > This is correct. Ubuntu (Debian really) installs the new vesion in > parallel and provides the pg_upgradecluster tool to migrate your old > data to the new version. Once you are satisfied that everything is > working you can use pg_dropcluster to remove the old data and you can > then uninstall 8.4. I've just discovered pg_lsclusters, another Debian tool, which lists all installed Postgres clusters, their ports, data directories, etc - really handy. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange dump/restore effect
Hi all, I just migrated to 9.2 and observing stange thing. While restoring 9.2 database to another server's empty database I'm getting several errors while restoring views: pg_restore: creating RULE _RETURN pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 5466; 2618 26660 RULE _RETURN postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near ")" LINE 2: ALTER VIEW v_vehicle SET (); ^ Command was: CREATE RULE "_RETURN" AS ON SELECT TO v_vehicle DO INSTEAD SELECT v.vehicle_id, v.vehicle_code, v.home_location_id, v.vehicl... pg_restore: creating RULE _RETURN pg_restore: [archiver (db)] Error from TOC entry 5487; 2618 26835 RULE _RETURN postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near ")" LINE 2: ALTER VIEW v_r_delivery SET (); There is lot of views, other are backuped/restored without problems. Those several views which are restored with errors are visible in new database as empty tables. Source database is produced by PostGIS 2.0 migration script from 9.1/PostGIS 1.5 database backup. Attempt to drop those strange views, recreate them and then run dump/restore doesn't change anything. Same backup/restore procedure worked fine in 9.1. Any ideas? Grazvydas
Re: [GENERAL] Strange dump/restore effect
On Mon, Sep 24, 2012 at 12:44 AM, Paul Ramsey wrote: > Try just loading the 1.5 dump directly into the 2.0 database without > the filtering step. It will be immensely noisy with lots of errors and > warnings, but with luck you should find your data is there waiting for > you when it's done. > > I don't have problems with data. Everything is in place. After droping those 4 tables that happened to be views, and recreating views everything seems ok (till next dump/restore).
Re: [GENERAL] Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
Hi thanks, I ran ps and found there were TWO postgresql postgres 1124 0.0 0.1 45116 5480 ?SSep23 0:01 /usr/lib/postgresql/8.4/bin/postgres -D /var/lib/postgresql/8.4/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf postgres 1133 0.0 0.0 45116 1984 ?Ss Sep23 0:03 postgres: writer process postgres 1134 0.0 0.0 45116 1292 ?Ss Sep23 0:03 postgres: wal writer process postgres 1135 0.0 0.0 45252 1560 ?Ss Sep23 0:01 postgres: autovacuum launcher process postgres 1136 0.0 0.0 13284 1252 ?Ss Sep23 0:01 postgres: stats collector process postgres 9429 0.0 0.1 43644 7140 ?SSep23 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf postgres 9431 0.0 0.0 43644 1272 ?Ss Sep23 0:00 postgres: checkpointer process postgres 9432 0.0 0.0 43644 1408 ?Ss Sep23 0:00 postgres: writer process postgres 9433 0.0 0.0 43644 1212 ?Ss Sep23 0:00 postgres: wal writer process postgres 9434 0.0 0.0 44040 2148 ?Ss Sep23 0:00 postgres: autovacuum launcher process postgres 9435 0.0 0.0 13868 1140 ?Ss Sep23 0:00 postgres: stats collector process Regards Arthur On 24 Sep 2012, at 4:45 AM, Raymond O'Donnell wrote: > On 23/09/2012 19:31, Martin Collins wrote: >> On 23/09/12 11:48, Adrian Klaver wrote: >>> On 09/23/2012 04:47 AM, a...@hsk.hk wrote: Hi, I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted postgresql, it displayed my postgresql is 9.2 but when I log into postgresql, show version, it is still 8.4.13, see a) and b) below, >>> >>> My guess is it installed a parallel version of Postgres for 9.2 and >>> that 8.4.13 is listening to the default port of 5432 which is what >>> you are connecting to. The 9.2 version is probably listening on >>> another port(at a guess 5433). I would do a ps ax to confirm there is >>> more than one instance of Postgres running. >> >> This is correct. Ubuntu (Debian really) installs the new vesion in >> parallel and provides the pg_upgradecluster tool to migrate your old >> data to the new version. Once you are satisfied that everything is >> working you can use pg_dropcluster to remove the old data and you can >> then uninstall 8.4. > > I've just discovered pg_lsclusters, another Debian tool, which lists all > installed Postgres clusters, their ports, data directories, etc - really > handy. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fetching json: best way to do this?
Say I want output similar to this:
{
"id":73,
"name":"LolShirt 1",
"uuid":"afe3526818",
"thumbnails":[
{
"filename":"file.png",
"width":200,
"height":199,
"id":79
},
{
"filename":"file.png",
"width":200,
"height":199,
"id":79
}
],
"channels":[
{
"id":8,
"name":"Animals",
"slug":"animals"
},
{
"id":12,
"name":"Hidden",
"slug":"hidden"
}
]
}
Is this the best way to get that?
create type image_listing as (filename text, width int, height int,
id int);
create type channel_listing as (id integer, name text, slug text);
create type product_listing as (
id integer,
name text,
uuid text,
thumbnails image_listing[],
channels channel_listing[]);
create function product_listing_json(product_id integer) returns json
language sql stable as $$
select row_to_json(
row(
products.id,
products.name,
products.uuid,
array_agg((m.filename, m.width, m.height, m.id)::image_listing),
array_agg((c.id, c.title, c.slug)::channel_listing)
)::product_listing
)
from products
join product_medias m on m.media_of_id = products.id
left join channels_products cp on cp.product_id = products.id
join channels c on c.id = cp.channel_id
where products.id = $1
group by products.id
$$;
select product_listing_json(id) from products order by id desc;
(https://gist.github.com/377345 contains the above code and expected output)
I'm really looking forward to being able to slurp up a complex json
object in a single sql query, so I'm exploring ways to do that.
Thanks,
Joe
--
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] Strange dump/restore effect
=?UTF-8?Q?Gra=C5=BEvydas_Valeika?= writes: > I just migrated to 9.2 and observing stange thing. > While restoring 9.2 database to another server's empty database I'm getting > several errors while restoring views: > pg_restore: creating RULE _RETURN > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 5466; 2618 26660 RULE > _RETURN postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error > at or near ")" > LINE 2: ALTER VIEW v_vehicle SET (); > ^ Could you provide a self-contained example of a view that causes this? regards, tom lane -- 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] foreign key from array element
On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote: > Rafal Pietrak wrote: > > On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: > > > On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak > > > wrote: [] > > > > Who can review that patch? > > You :^) > I did what I could - the review is on the hackers list. [] > http://wiki.postgresql.org/wiki/Reviewing_a_Patch > > Remember that even a review that does not cover everything > is valuable. Should I do anything else to finilize this review, like "linking" it to the pending patch? somehow? -R -- 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] foreign key from array element
Hi Rafal, Il 24/09/12 07:54, Rafal Pietrak ha scritto: I did what I could - the review is on the hackers list. Thanks! That's much appreciated. Should I do anything else to finilize this review, like "linking" it to the pending patch? somehow? -R You should update the commitfest.postgresql.org website. For now, I have updated that for you and the patch now reports you as a reviewer and links the message to the patch. Here it is: https://commitfest.postgresql.org/action/patch_view?id=900 Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need to run a job in PgAdmin-III
I have pgAdmin-III.It has version 1.12.3.I would like to run a job. I have schedule an SQL Job. But when i run there is no output.Can anybody let me know what needs to be done?.Also, Can any one tell, where i need to look for the errors, when i run a job?. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-to-run-a-job-in-PgAdmin-III-tp5725093.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Need to run a job in PgAdmin-III
On 09/23/12 11:24 PM, pavithra wrote: I have pgAdmin-III. It has version 1.12.3. I would like to run a job. I have schedule an SQL Job. But when i run there is no output. Can anybody let me know what needs to be done?. Also, Can any one tell, where i need to look for the errors, when i run a job? by 'job', do you mean run a SQL script file?I'd suggest using psql for that. psql -h hostname -d dbname -u username -f scriptfile.sql >outfile.txt -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
