Re: [GENERAL] [PERFORM] Very slow query in PostgreSQL 9.3.3
2014-03-14 4:26 GMT+09:00 fburg...@radiantblue.com: PostgreSQL 9.3.3 RHEL 6.4 Total db Server memory 64GB (...) effective_cache_size = 48MB I'm not sure if this will help directly, but is the value for 'effective_cache_size' intentional? 48 *GB* would be a more likely setting. Regards Ian Barwick -- 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] Materialized view from PG to Oracle?
2014-03-12 12:00 GMT+09:00 Roy Anderson roy.ander...@gmail.com: Good day. We are transitioning over one database to Postgres as a test but retain an Oracle presence. The PG db in question is (it is currently still running Oracle) feeds a couple other Oracle dbs via materialized view logs and materialized views. Is it possible to achieve the same MV functionality in PG (i.e., have it feed Oracle via MVs)? Not directly. The reverse would be possible at SQL level with the Oracle FDW (see: http://pgxn.org/dist/oracle_fdw/ ) but otherwise you'd need some kind of custom script/cronjob which reads the Postgres materialised view and imports it to Oracle. Regards Ian Barwick -- 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] Replacing Ordinal Suffixes
2014-03-01 8:16 GMT+09:00 George Weaver gwea...@shaw.ca:
- Original Message - From: Steve Atkins
On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:
Maybe this?
select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
'\1', 'gi');
Hi Steve,
Thanks, but no luck:
select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
E'\1', 'gi');
regexp_replace
300 North 126th Street
George
Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.
Hi Steve,
Without the E's:
development=# select regexp_replace('300 North 126th Street',
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');
WARNING: nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING: nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
regexp_replace
300 North 126th Street
(1 row)
Frustrating...
Per Steve Atkin's note about double backslashes:
postgres= select regexp_replace('300 North 126th Street',
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');
regexp_replace
--
300 North 126 Street
(1 row)
Regards
Ian Barwick
--
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] Moving data from M$ JetDB file to Postgres on Linux
2014-03-01 9:34 GMT+09:00 Rich Shepard rshep...@appl-ecosys.com: On Fri, 28 Feb 2014, Adrian Klaver wrote: Well JET is the database engine for Access and mdb is one of the file format extensions for Access database files. JET has been used in other MS products and Access has used different file formats(extensions) over the years. So you may be in for a chore depending on what files you actually have. Might be worth it to do some exploring and see if the data is available in a more useable form. Adrian, I'll try my mdbtools and see if there's a more current version. The data came from the USGS and they have it in only one format, unfortunately. Not sure if this will be of use, but there are a couple of links here: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_Access I'll post it anyway in case someone comes across this thread in the future. Regards Ian Barwick -- 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] How to turn off DEBUG statements from psql commends
2014-02-11 0:43 GMT+09:00 peterlen petera...@earthlink.net: We are using PostgreSQL 9.3. Something seems to have changed with our psql command-line output since we first installed it. When I run commands at my plsql prompt, I am getting a lot of debug statements which I was not getting before. I am just trying to find out how to tell psql not to display this output. As an example, if I were to create a new 'test' schema, the output looks like: gis_demo=# create schema test; DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: LOG: statement: create schema test; DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 15099/1/1, nestlvl: 1, children: CREATE SCHEMA gis_demo=# Before, all I would get is the CREATE SCHEMA feedback. Not sure what may have caused this change on our end but does anyone know how to turn it off? What does SHOW client_min_messages return? This has possibly been set to something other than the default in your .psqlrc file or the postgresql.conf file. Regards Ian Barwick -- 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] Fwd: lots of errors from fmgr.h when I try to write a C UDF
2014-02-01 Anh Pham atpham@gmail.com: Hi, I had Postgres 9.3 setup and run successfully in Eclipse (MacOSX) I am trying to write a C user-defined function: #include fmgr.h ... #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif ... However, when I tried to compile the code: gcc -fpic -c ... It gave me a lot of errors and warnings from fmgr.h: ... /Users/atpham/workspacecdt/pgsql/src/include/fmgr.h:59:2: error: unknown type name 'MemoryContext' MemoryContext fn_mcxt; /* memory context to store fn_extra in */ ^ /Users/atpham/workspacecdt/pgsql/src/include/fmgr.h:71:2: error: unknown type name 'Oid' Oid fncollation;/* collation for function to use */ ^ /Users/atpham/workspacecdt/pgsql/src/include/fmgr.h:72:2: error: unknown type name 'bool' boolisnull; /* function must set true if result is NULL */ … You'll need to include the appropriate header files. Certainly postgres.h. Regards Ian Barwick -- 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] fastest dump/restore
2014-01-27 Vincent Veyron vv.li...@wanadoo.fr: Le dimanche 26 janvier 2014 à 10:27 -0700, Scott Ribe a écrit : Is there an article anywhere which documents everything the current state of the art for the fastest dump/restore? What dump/restore format options? What things to tweak in the config? I've picked up a few bits here and there along the line, but was just wondering if there's a comprehensive source of current advice. pg_dump has its documentation : http://www.postgresql.org/docs/9.3/static/app-pgdump.html see also 14.4.9. Some Notes About pg_dump in : http://www.postgresql.org/docs/9.3/static/populate.html The wiki page for the 9.3 release has some notes and links to blog articles describing the new parallel dump feature for pg_dump: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Parallel_pg_dump_for_faster_backups Regards Ian Barwick -- 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] postgres-fdw questions
2014/1/24 Emmanuel Medernach meder...@clermont.in2p3.fr: Hello, I'm currently testing postgres_fdw feature on PostgreSQL 9.3.2 and I have some questions: - What are the limits to the number of foreign tables ? As far as I know, there is no limit (beyond the usual PostgreSQL limits). - What is the current status about foreign joins push-down ? Unless I'm mistaken (and no doubt someone will correct me if I am), joins cannot be pushed down yet, only WHERE clauses for individual foreign table rows. To quote this article about PostgreSQL 9.3: http://lwn.net/Articles/550418/ 'More work needs to be done in this area to make this a full big data solution, however; Hanada hopes to add push down of joins, sorts, and aggregates in future versions of PostgreSQL. See also this thread: http://www.postgresql.org/message-id/20131121150515.gc23...@momjian.us Regards Ian Barwick -- 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] How to list and describe tables in psql???
014/1/2 peterlen petera...@earthlink.net: I am using PostgreSQL 9.1 and have boon looking for psql commands to list all tables in the current database as well as how to describe a given table. I have come across some info on the web (older mostly) that lists commands like \d, \dt, or \d+ tablename but I get results like no relations found or column reltriggers does not exist. Not sure what is going on. Can someone enlighten me on the right commands to use? Those are the correct commands, but it sounds like you're using an older psql version against a newer server. Which psql version (psql -V) are you using? Regards Ian Barwick -- 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] How to list and describe tables in psql???
2014/1/2 peterlen petera...@earthlink.net: Thanks for the responses. I found that I was using an older version of psql (one that got installed when I installed Cygwin on my windows box. I changed it so now the \d+ gives me the description but \d and \dt still gives me No relations found so I must be using those incorrectly. Possibly there's an issue with your search path? Check if \dn lists schemas not listed by SHOW search_path, if so you can either explicitly include the schema name (\dt some_schema.*) or adjust your search path. See also: http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH Regards Ian Barwick -- 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] connect using quirrel sql client
2013/12/20 Adrian Klaver adrian.kla...@gmail.com: On 12/20/2013 01:08 AM, Arindam Mondal wrote: Hi , please let me know how I can connect postgresql using squirrel sql client 3.4.0. Make sure you have the Postgres JDBC driver: http://jdbc.postgresql.org/download.html Configure SQuirrel: http://squirrel-sql.sourceforge.net/index.php?page=screenshots The screenshot use Postgres as its example. Do be aware of this potential issue with this client: http://www.postgresql.org/message-id/1362077623.96612.yahoomail...@web162902.mail.bf1.yahoo.com which caused some pain the other day. Not saying it's necessarily bad per-se, but sharing the above link with sysadmins resulted in a please yes, let's get this uninstalled everywhere reaction. Ian Barwick -- 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] Q: regarding backends
2013/12/10 Stephan Fabel sfa...@hawaii.edu: Hi all, and sorry if I'm asking a question that has been answered before; has the PostgreSQL community ever considered different key/value backends (sort of like MySQL with its many different options)? We'd be very interested in seeing the effects of integrating LMDB [*] in terms of performance gains. Has this avenue been explored before? There was talk of pluggable storage at this year's PGCon (PGUncon?): https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage but even if the idea gains traction, it's not going to arrive any time soon. As Kevin mentions, FDWs might provide a good alternative. An example with key/value stores I've been citing recently is this one: http://blog.cloudflare.com/kyoto_tycoon_with_postgresql Regards Ian Barwick -- 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] pgadmin III query
2013/12/6 Peter Kroon plakr...@gmail.com: When you click on a table in the Object browser you'll see in the SQL pane the sql that is needed to create that table. Which function can I call to get that SQL? You can use the pg_dump command line function for this: pg_dump -s -t name_of_table name_of_database Regards Ian Barwick -- 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] Preserving the source code of views
2013/10/22 Stephen Frost sfr...@snowman.net: You also mentioned an external CMS. Any suggestions? I'm a big fan of git, but if you really want to keep things in-database and track dependencies, etc, it occurs to me that you might be able to use an actual table in the database to store the raw form of your view definitions and then have an SP or something which can link up that table to the actual views in the database and then you can look at dependencies through the PG system catalogs... Haven't really thought this through completely, but wanted to mention the idea as it might help you. At my current workplace, view definitions (which are very old-school with revision history etcetera recorded in comments) are stored in PL/PgSQL functions which are updated and executed whenever views are modified. I'm looking at adding a wrapper function to automatically detect and recreate dependencies, not sure how well that would work. We keep track of schema changes via pg_extractor[1] which integrates quite nicely with GIT. [1] https://github.com/omniti-labs/pg_extractor Regards Ian Barwick -- 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] Index creation fails with automatic names
2013/10/14 Florian Nigsch f...@nigsch.eu: Hi all, I am not sure if this is a bug or a misuse on my part. I am creating a number of indices in parallel on a table by using xargs. To do that, I write all my indices in a file indices.idx, and then have the indices build in parallel (in this case with 5 concurrent processes) cat indices.idx | xargs -P5 -I# psql -1 -c '#' indices.idx contains lines like this: ALTER TABLE schema.table1 ADD CONSTRAINT pk_activity PRIMARY KEY (field_sk); CREATE INDEX ON schema.table1 ((LOWER(field2))); CREATE INDEX ON schema.table1 ((LOWER(field3))); CREATE INDEX ON schema.table1 (field4, field5); CREATE INDEX ON schema.table1 (field4, field6, field5); Upon running the above command, I see the following error: ALTER TABLE CREATE INDEX ERROR: duplicate key value violates unique constraint pg_class_relname_nsp_index DETAIL: Key (relname, relnamespace)=(table1_lower_idx, 2064404) already exists. My question is then - where does this error come from? Is is because Postgres allocates the same name (table1_lower_idx) twice when the index begins building, because at that time there's no index present with that name? But if one index finishes earlier, then the second one can't be committed because it has the same name as an already present index? It works fine for me on Pg 9.3.1: postgres=# CREATE TABLE foo(val1 text, val2 text); CREATE TABLE postgres=# CREATE INDEX on foo((lower(val1))); CREATE INDEX postgres=# CREATE INDEX on foo((lower(val2))); CREATE INDEX postgres=# \d foo Table public.foo Column | Type | Modifiers +--+--- val1 | text | val2 | text | Indexes: foo_lower_idx btree (lower(val1)) foo_lower_idx1 btree (lower(val2)) Which PostgreSQL version are you using? Are you sure there's not an index with the offending name already? Regards Ian Barwick -- 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] Index creation fails with automatic names
2013/10/18 Kevin Grittner kgri...@ymail.com: Ian Lawrence Barwick barw...@gmail.com wrote: It works fine for me on Pg 9.3.1: postgres=# CREATE TABLE foo(val1 text, val2 text); CREATE TABLE postgres=# CREATE INDEX on foo((lower(val1))); CREATE INDEX postgres=# CREATE INDEX on foo((lower(val2))); CREATE INDEX You seem to be creating the indexes one at a time, all on the same connection. The OP's problem occurs when starting five CREATE INDEX statements in five different sessions all at the same time. (reads original email again) ah yes, brain was not properly engaged. -- 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] Comparing Oracle vs Postgres Fwd: [ADMIN] Database replication to 50-80 nodes
2013/10/4 Jaime Casanova ja...@2ndquadrant.com: what version of postgres is this? starting in 9.3 you can create foreign tables between postgres databases, before that you can create a foreing server and use the functions from th dblink module to connect to that server and query the table on it Read-only foreign tables have been available since 9.1, it's writeable foreign tables which were introduced in 9.3. Regards Ian Barwick -- 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] Column names for INSERT with query
2013/8/23 Robert James srobertja...@gmail.com: I would like to: INSERT INTO t SELECT * FROM r, (x + y) AS field3 How do I correlate the names of the fields? That is, how do I indicate which fields from r or field3 should be inserted into the right columns in t? INSERT INTO t (t_field1, t_field2, t_field3) SELECT r.field1, r.field2, (x+y) FROM 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] archive folder housekeeping
2013/8/9 ascot.m...@gmail.com ascot.m...@gmail.com: Is there any PG manual command available to remove archived files by (archive) date/time? pg_archivecleanup might be of use to you: http://www.postgresql.org/docs/current/static/pgarchivecleanup.html Regards Ian Barwick -- 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] DB transactions when browser freezes
2013/8/8 dafNi zaf dza...@gmail.com: Hello to everybody, I started to upload (via phpPgAdmin) to a local server a huge file (20GB) in order to fill up a database. 20GB is a lot to be uploading from a browser, even in this day and age. Is the web server configured to accept uploads of that size? -- 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] Fwd: Trigger on VIEW not firing
2013/7/30 Massimo Costantini massimo.costant...@gmail.com: Hi, I have a problem with Triggers on VIEW: suppose I have: CREATE TABLE work ( id integer NOT NULL, work TEXT, worktype TEXT ); CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate'; CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$ BEGIN RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME, TG_OP; END; $wrk_tg$ LANGUAGE plpgsql; CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub FOR EACH ROW EXECUTE PROCEDURE wrk_view(); nothing appen when I insert row in work table. The trigger is on the view worksub, not the work table. BTW the trigger function doesn't return anything, which will cause an error. (Also the view definition is missing column definitions in the SELECT clause). Regards Ian Barwick -- 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] Viewing another role's search path?
2013/7/23 Adrian Klaver adrian.kla...@gmail.com: On 07/23/2013 05:15 AM, Ian Lawrence Barwick wrote: Is there some simple way of viewing the search path (or other role-specific setting) for a role different to the current role? Apart from querying 'pg_db_role_setting' directly? Just wondering if I'm missing something obvious. http://www.postgresql.org/docs/9.2/interactive/app-psql.html \drds [ role-pattern [ database-pattern ] ] Lists defined configuration settings. These settings can be role-specific, database-specific, or both. role-pattern and database-pattern are used to select specific roles and databases to list, respectively. If omitted, or if * is specified, all settings are listed, including those not role-specific or database-specific, respectively. The ALTER ROLE and ALTER DATABASE commands are used to define per-role and per-database configuration settings. Thanks, I missed that one. Unfortunately it produces a false negative if a user hasn't had their search path explicitly set, e.g.: postgres=# CREATE ROLE sp_test LOGIN; CREATE ROLE postgres=# \drds sp_test No matching settings found. postgres=# \c - sp_test You are now connected to database postgres as user sp_test. postgres= SHOW search_path ; search_path $user,public (1 row) postgres= \c - postgres You are now connected to database postgres as user postgres. postgres=# ALTER ROLE sp_test SET search_path =$user,public, foo; ALTER ROLE postgres=# \drds sp_test List of settings Role | Database | Settings -+--+- sp_test | | search_path=$user, public, foo Also it's psql-specific, so doesn't really lend itself as a global solution. The custom view will have to do for now. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Viewing another role's search path?
Is there some simple way of viewing the search path (or other role-specific setting) for a role different to the current role? Apart from querying 'pg_db_role_setting' directly? Just wondering if I'm missing something obvious. Regards Ian Barwick -- 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] Viewing another role's search path?
2013/7/23 Michael Paquier michael.paqu...@gmail.com: On Tue, Jul 23, 2013 at 9:15 PM, Ian Lawrence Barwick barw...@gmail.com wrote: Is there some simple way of viewing the search path (or other role-specific setting) for a role different to the current role? Apart from querying 'pg_db_role_setting' directly? This one perhaps? select rolname, rolconfig from pg_roles; rolconfig contains all the user-specific settings. Thanks, but what I'm looking for is a more elegant (built-in?) way of extracting the contents of the configuration array which is useable for people who don't want to poke around in the system catalogues. I've created a view which does this, but I want to confirm if there's anything along the lines of SHOW search_path FOR ROLE foo which I've missed and would save the trouble of maintaining the view. Regards Ian Barwick -- 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] Concatenate table name in Pl/Pgsql
2013/6/26 Adarsh Sharma eddy.ada...@gmail.com: Hi , Today i m creating a function that includes dynamic concatenation of a partitioned table name as below :- test=# CREATE OR REPLACE FUNCTION tmp_trigger_function() test-# RETURNS TRIGGER AS $$ test$# DECLARE test$# tbl_name text; test$# abc varchar; test$# BEGIN test$# tbl_name := 'tmp'; test$# select to_char(NEW.a::timestamp,'mmdd') into abc ; test$# insert into tmp || abc values ( NEW.* ); test$# RETURN NULL; test$# END; test$# $$ test-# LANGUAGE plpgsql; ERROR: syntax error at or near || LINE 9: insert into tmp || abc values ( NEW.* ); ^ Time: 0.901 ms test=# test=# I tried with a statement variable also. Any ideas ? You'll need to create a string and use EXECUTE, something along the lines of: stmt := 'insert into ' || tmp || abc || ' VALUES ($1)' EXECUTE stmt USING NEW.*; http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN HTH Ian Barwick -- 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] Concatenate table name in Pl/Pgsql
Thanks On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick barw...@gmail.com wrote: 2013/6/26 Adarsh Sharma eddy.ada...@gmail.com: Hi , Today i m creating a function that includes dynamic concatenation of a partitioned table name as below :- test=# CREATE OR REPLACE FUNCTION tmp_trigger_function() test-# RETURNS TRIGGER AS $$ test$# DECLARE test$# tbl_name text; test$# abc varchar; test$# BEGIN test$# tbl_name := 'tmp'; test$# select to_char(NEW.a::timestamp,'mmdd') into abc ; test$# insert into tmp || abc values ( NEW.* ); test$# RETURN NULL; test$# END; test$# $$ test-# LANGUAGE plpgsql; ERROR: syntax error at or near || LINE 9: insert into tmp || abc values ( NEW.* ); ^ Time: 0.901 ms test=# test=# I tried with a statement variable also. Any ideas ? You'll need to create a string and use EXECUTE, something along the lines of: stmt := 'insert into ' || tmp || abc || ' VALUES ($1)' EXECUTE stmt USING NEW.*; http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 2013/6/26 Adarsh Sharma eddy.ada...@gmail.com: Thanks Ian for such a quick response. But my NEW.* will be 10-15 columns row. test=# CREATE OR REPLACE FUNCTION tmp_trigger_function() test-# RETURNS TRIGGER AS $$ test$# DECLARE test$# stmt text; test$# abc varchar; test$# BEGIN test$# select to_char(NEW.a::timestamp,'mmdd') into abc ; test$# stmt := 'insert into ' || tmp || abc || ' VALUES ($1)' test$# EXECUTE stmt USING NEW.*; test$# RETURN NULL; test$# END; test$# $$ test-# LANGUAGE plpgsql; ERROR: syntax error at or near EXECUTE LINE 9: EXECUTE stmt USING NEW.*; ^ Time: 0.737 ms test=# You'll need to specify each column explicitly if there's more than one. Regards Ian Barwick -- 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] Function tracking
2013/6/7 Rebecca Clarke r.clark...@gmail.com: Hi all I'm looking for suggestions on the best way to track the updates to a function. We have two databases, Dev Live, so I want to update Live with just the functions that have been modified in the DEV databas3e. Is there another, easier way to track the updates than manually recording it in a document? (I'm trying to eliminate human interference). You might want to take a look at Sqitch ( http://sqitch.org/ ), which is a little complex to set up, but works along similar lines to GIT. As others have said, you definitely need to use some kind of source control to track changes. With the systems I've managed, I've ensured that releases are tagged in the source control system with a release number, and that database script files exist for each release (if needed) which are applied when the release is rolled out to the respective environment. It's still a bit of a manual process and requires a bit of (self) discipline, but has worked quite well for me. Regards Ian Barwick -- 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] Differences in Unicode handling on Mac vs Linux?
2013/6/3 Tom Lane t...@sss.pgh.pa.us: Matt Daw m...@shotgunsoftware.com writes: Howdy, I loaded a client's DB on my Mac to debug an unrelated bug, but I'm blocked because my Mac is rejecting SQL that works on our Linux production servers. Here's a simple case: # select * from shots where sg_poznÁmka is NULL; ERROR: column sg_pozn�mka does not exist LINE 1: select * from shots where sg_poznÁmka is NULL; Hm ... what does \d shots say about the spelling of the column name? Anything else I could double-check? Or are there any known Mac-related Unicode issues? OS X's Unicode locales are pretty crummy. I'm suspicious that there's some sort of case-folding inconsistency here, but it's hard to say more (especially since you didn't actually tell us *which* locales you've selected on each machine). If it is that, as a short-term fix it might help to double-quote the column name. I can recreate something similar (OS X 10.7, 9.3beta1): postgres=# CREATE TABLE shots (id int); CREATE TABLE postgres=# SHOW client_encoding ; client_encoding - UTF8 (1 row) postgres=# select * from shots where col_ä is NULL; ERROR: column col_� does not exist LINE 1: select * from shots where col_ä is NULL; The corresponding log output is: ERROR: column col_E3A4 does not exist at character 27 STATEMENT: select * from shots where col_ä is NULL; Double-quoting the column name does seem to work: postgres=# select * from shots where col_ä is NULL; ERROR: column col_ä does not exist LINE 1: select * from shots where col_ä is NULL; The only language/locale settings I see in my environment are: LANG=en_GB.UTF-8 __CF_USER_TEXT_ENCODING=0x1F6:0:2 Regards Ian Barwick -- 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] Random numbers
2013/5/26 bricklen brick...@gmail.com: On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar kesco...@estudiantes.uci.cu wrote: I want to generate random numbers in Pl/pgSQL. How can I do this? To be more specific, I have to generate random numbers among 0 and 5. One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1; somewhat shorter: SELECT (random() * 5)::INT Ian Barwick -- 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] Help, How to start the server??
2013/5/24 YuChi tp6m4fu6250...@yahoo.com.tw: i use postgresql-9.2.4 install on ubuntu11.04(natty) and i use the command: *postgres@ubuntu:/$ postgres -D /usr/local/pgsql/data* or use *postgres@ubuntu:/$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data* to start the server but it's stopping at the message: *LOG: database system was shut down at 2013-05-24 15:07:34 CST LOG: database system is ready to accept connections LOG: autovacuum launcher started* for a long time it seems never continue again and i don't know how to do next... can anyone tell me is there anything wrong? or how to do next? That's perfectly normal log output after a server start. Have you tried connecting with psql? Ian Barwick -- 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] Success stories of PostgreSQL implementations in different companies
2013/5/25 Scott Marlowe scott.marl...@gmail.com: On Fri, May 24, 2013 at 9:10 AM, Bèrto ëd Sèra berto.d.s...@gmail.com wrote: The Greater London Authority is also ditching Oracle in favour of PG. I consulted them while they kick started their transition and the first new PG/PostGIS only project is already delivered. The number of companies ditching Oracle is probably much larger than it seems, giving the dynamics in salaries. The average PG based salary goes up steady, while working with Oracle is going down pretty quick. At least, so it would look from the UK. An Oracle DBA in average is currently offered some 15% less than a PG dba. Where I currently work we've been looking for a qualified production postgres DBA. They (we?) are hard to come by. I recently got hired for my good looks and passing acquaintance with Postgres by a finance-orientated company in Japan which is transitioning to Postgres. Ian Barwick -- 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] Upgrading postgresql from version 7.4.3
2013/5/9 Arun P.L aru...@hotmail.com: Hi, How can I upgrade to latest postgresql version 9.2 from my current version 7.4.3? How complicated this will be as the db contains large amount of data? I have installed the latest version 9.2 in new server and while restoring the dump from old version, data in some of the large tables are not copied and getting error. pg_restore: [archiver (db)] Error from TOC entry 1550 TABLE DATA table_name; pg_restore: [archiver (db)] COPY failed for table table_name: ERROR: invalid byte sequence for encoding UTF8: 0xa0 What else can I do for this issue, or in general how can I upgrade from a lower major version to higher version? Should I first upgrade to version 8 first and then go for the 9? Please provide your suggestion regarding this. The latest supported 8.x version is 8.4, which is quite a long way ahead of 7.4 in a whole number of ways. The gap between 8.4 and 9.2 is not quite so great, IMHO, so you might as well shoot for that. Providing you can solve the encoding problem, the largest potential issue you might face is the tightening of typecasting between 8.2 and 8.3: http://www.postgresql.org/docs/current/interactive/release-8-3.html#AEN124084 This is more something which will affect any applications which access your database and which rely on implicit casting. There are also some changes to PL/PgSQL which you will need to take into account if your database uses them. I suggest taking some time to go through the release notes. I have previously upgraded a 7.4 installation to 9.0 without too many problems; I did have to spend some time wading through the associated application code to isolate any potential casting issues. Regards Ian Barwick -- 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] OK to put temp tablespace on volatile storage or to omit it from backups?
2013/5/1 Yang Zhang yanghates...@gmail.com: On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan dar...@darrenduncan.net wrote: On 2013.04.30 4:55 PM, Yang Zhang wrote: I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash recovery? Also, if I were to omit the temp tablespace from the base backup, would that prevent proper backup recovery? Although it would be nice if what you said would work, I read in a recent blog post that losing any tablespace would prevent the database server from starting, even if it was only for temporary things. -- Darren Duncan That is unfortunate. Good thing I asked, I guess. Do you have a pointer to said blog post? I think this is the post in question: http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ (BTW please try not to top-post :) ) Regards Ian Barwick -- 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] pgAdmin shows two servers with the identical data
013/4/28 Bob Futrelle bob.futre...@gmail.com: I have two PG servers with the same data. I know the data is the same, because if I change a value in a table on one server, it changes the value in a table with the same name in the other server. in pgAdmin III: Properties for server Local (localhost:5432): Name: Local Host: localhost Port: 5432 Maintenance DB: postgres UserName: robertfutrelle Properties for server Local(local:.s.PGSQL.55432) Name: Local Host: Port: 5432 Maintenance DB: postgres UserName: robertfutrelle The second one is odd - no Host is listed. What is going on? Since they appear to be identical in most all respects, would be nice to remove one of them. Is that safe? If so, which? They are one and the same database, accessed via different methods: TCP/IP in the first case, Unix-domain sockets in the second case (hence the lack of host). Nothing to worry about, and you only have one copy of the database. Though it's for the command line client psql, the same principles described here apply: http://www.postgresql.org/docs/current/static/app-psql.html#R2-APP-PSQL-CONNECTING Regards Ian Barwick -- 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] list non alphanumeric
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: 12 April 2013 08:41 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] list non alphanumeric On 4/11/2013 11:23 PM, Baboo, Isa wrote: Firstly let me mention I am a noob to postgres,DB and sql anyway, I have a column with First names and need to list names that have non-alphanumeric characters in them like + or * and the list goes on. I did google a bit and tried: select * from TABLE where first_name not like '%[a-z0-9]%'; you'll probably have to use a regex for that. something like... (my regex is really rusty) ... WHERE first_name !~ '^[a-z0-9]*$'; 2013/4/12 Baboo, Isa iba...@fnb.co.za: Hi Thanks for the response but I tried that and it does not work here is an example of names which still display. test sadadds Hillary BEN NAME ALAN MCGARVEY + Something like: ... WHERE first_name ~ '[^\w]' might do the trick. Regards Ian Barwick -- 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] Update
2013/4/11 Joshua Berry yob...@gmail.com: Am 11.04.2013 10:29, schrieb jpui: Hi, I'm running a server using postgres 8.3 and i was adviced to update it... what i have to do in order to update it and don't stop the service? 8.3 is out of support so you will need to at a very minimum 8.4. This cannot be done without restarting. Please check for HowTo for upgrading postgres. As Frank has stated 8.3 is no longer supported.If you are upgrading anyway, you might as well upgrade to a version that still is supported. For upgrading from a major version (ie 8.3 to 8.4 or higher), you need to dump the database to a (large) file, upgrade postgres, then restore the database dump. These actions obviously do require that the database processes be stopped and started. Depending on your application and your schema, you may require no changes and everything will work. But it's probably worth testing this first on another machine to validate. the PG configuration file postgresql.conf is different from one major version to the next, so read the docs and tune carefully. Have a look at the release notes for helpful details. For example: http://www.postgresql.org/docs/9.2/static/release-9-1.html If have never used pg_dump or pg_dump_all to generate dumps, nor have restored them, you should read up on and be proficient at those tasks. http://www.postgresql.org/docs/9.2/static/backup.html pg_upgrade can also be used when upgrading to a new server version. Basically it converts the old version's data directory to the new version's format. You'll need to stop the database server while the upgrade is running, however pg_upgrade is usually much faster than the dump/restore method. See here for details: http://www.postgresql.org/docs/9.2/static/pgupgrade.html There are some notes on limitations when upgrading from 8.3: http://www.postgresql.org/docs/9.2/static/pgupgrade.html#AEN147114 I suggest you practice upgrading on a test computer. Even if using pg_upgrade, you should dump the original database using pg_dump beforehand as an additional backup should things go wrong. Regards Ian Barwick -- 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] Backup advice
2013/4/8 Johann Spies johann.sp...@gmail.com: I would appreciate some advice from the experts on this list about the best backup strategy for my database. (...) I have read about using pg_basebackup in an article from Shaun Thomas' booklet on Packt Publishers (I will probably buy the booklet). Get the booklet, it's worth it. Regards Ian Barwick -- 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] AWS and postgres issues
2013/4/9 Tatsuo Ishii is...@postgresql.org: While debugging this with a coworker we figured out that pg_ctl was attaching to the tty and then it clicked that we needed to be using '-t' where I was using -T or (neither). Are you sure? I checked the pg_ctl source code and could not find any place attaching to the tty. I think he means the ssh options -t and -T Regards Ian Barwick -- 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] bug in COPY implementation (all versions of Postgres)?
2013/4/5 Konstantin Izmailov pgf...@gmail.com: I came across an issue that looks like a bug in COPY. There are many similar posts, e.g. http://stackoverflow.com/questions/13485030/strange-postgresql-value-too-long-for-type-character-varying500, without a good unswer. Simplified steps to reproduce the issue: 1. CREATE TABLE TEST (description varchar(10)); 2. Insert value 'Galaxy\040Tab' using command COPY TEST(description) FROM stdin WITH DELIMITER '|' CSV. The following error is returned: value too long for type character varying(10) Of course real life scenarios are more complex and different characters were used (\042 and \005). Is this a bug, or an incorrect use of COPY/CSV? The latter. testdb= COPY vtest(descr) FROM STDIN WITH (DELIMITER '|', FORMAT CSV); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. Galaxy\040Tab \. ERROR: value too long for type character varying(10) CONTEXT: COPY vtest, line 1, column descr: Galaxy\040Tab testdb= COPY vtest(descr) FROM STDIN WITH (DELIMITER '|', FORMAT TEXT); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. abcdef\040xyz \. testdb= SELECT * from vtest; descr abcdef xyz (1 row) From the documentation: CSV Format This format option is used for importing and exporting the Comma Separated Value (CSV) file format used by many other programs, such as spreadsheets. Instead of the escaping rules used by PostgreSQL's standard text format, it produces and recognizes the common CSV escaping mechanism. http://www.postgresql.org/docs/current/static/sql-copy.html#AEN66712 i.e. the normal escaping rules don't apply with CSV format. Regards Ian Barwick -- 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] PostgreSQL: CPU utilization creeping to 100%
2013/4/3 David Noel david.i.n...@gmail.com: I'm running into a strange issue whereby my postgres processes are slowly creeping to 100% CPU utilization. I'm running postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the postgresql-9.2-1002.jdbc4 driver. (...) postgresql.conf, all standard/default except for: max_connections = 256 It's very likely the default settings are woefully inadequate for your server; some basic tuning (especially the value of shared_buffers and other memory-related parameters) should help. Any thoughts? What other information can I provide? Sample EXPLAIN/EXPLAIN ANALYZE output from the query in question, and if possible relevant table definitions etc. would certainly be useful. Regards Ian Barwick -- 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] Understanding behavior of SELECT with multiple unnested columns
2013/3/27 Ken Tanzer ken.tan...@gmail.com
I've been working on some queries involving multiple unnested columns. At
first, I expected the number of rows returned would be the product of the
array lengths, so that this query would return 4 rows:
SELECT unnest2(array['a','b']),unnest2(array['1','2']);
when in fact it returns 2:
unnest2 | unnest2
-+-
a | 1
b | 2
Which is all well and good. (Better, in fact, for my purposes.) But then
this query returns 6 rows:
SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
unnest2 | unnest2
-+-
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
Throw an unnested null column in and you get zero rows, which I also didn't
expect:
SELECT
unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);
unnest2 | unnest2 | unnest
-+-+
(0 rows)
After some head scratching, I think I understand what to expect from these
unnests, but I'm unclear of the logic behind what is going on. I'm hoping
someone can explain it a bit.
Basically you are getting Cartesian joins on the row output of
unnest() (and presumably
unnest2() - I guess this is a function you defined yourself?)
Effectively you are doing this:
CREATE TABLE t1 (val INT);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (val CHAR(1));
INSERT INTO t2 VALUES ('a'),('b'),('c');
CREATE TABLE t3 (val INT);
testdb=# SELECT * from t1, t2;
val | val
-+-
1 | a
1 | b
1 | c
2 | a
2 | b
2 | c
(6 rows)
testdb=# DELETE FROM t2 where val='c';
DELETE 1
testdb=# SELECT * from t1, t2;
val | val
-+-
1 | a
1 | b
2 | a
2 | b
(4 rows)
testdb=# SELECT * from t1, t2, t3;
val | val | val
-+-+-
(0 rows)
HTH
Ian Barwick
--
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] Understanding behavior of SELECT with multiple unnested columns
2013/3/27 Ken Tanzer ken.tan...@gmail.com Basically you are getting Cartesian joins on the row output of unnest() Well that's what I expected too. Except look at this example, after you delete c: testdb=# DELETE FROM t2 where val='c'; DELETE 1 testdb=# SELECT * from t1, t2; val | val -+- 1 | a 1 | b 2 | a 2 | b (4 rows) And compare to: SELECT unnest(array[1,2]),unnest(array['a','b']); unnest | unnest + 1 | a 2 | b (2 rows) You can see they are not the same! Ah yes, what I suggested is actually the equivalent to SELECT * FROM unnest(array[1,2]) u1,unnest(array['a','b']) u2; I seem to recall seeing the explanation for this before, although I'll be darned if I can remember what it is. FWIW this happens with other functions returning SETOF: testdb=# SELECT testdb-# generate_series(1,2) x, testdb-# generate_series(1,2) y; x | y ---+--- 1 | 1 2 | 2 (2 rows) testdb=# SELECT testdb-# generate_series(1,2) x, testdb-# generate_series(1,3) y; x | y ---+--- 1 | 1 2 | 2 1 | 3 2 | 1 1 | 2 2 | 3 (6 rows) Regards Ian Barwick
Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns
2013/3/27 Tom Lane t...@sss.pgh.pa.us: Gavin Flower gavinflo...@archidevsys.co.nz writes: The rule appears to be, where N_x N_y are the number of entries returned for x y: N_result = is the smallest positive integer that has N_x N_y as factors. Right: if there are multiple set-returning functions in a SELECT list, the number of rows you get is the least common multiple of their periods. (See the logic in ExecTargetList that cycles the SRFs until they all report done at the same time.) I guess there's some value in this for the case where they all have the same period, but otherwise it's kind of bizarre. It's been like that since Berkeley days though, so I doubt we'll consider changing it now. Rather, it'll just be quietly deprecated in favor of putting SRFs into FROM (with LATERAL where needed). Thanks for the clarification, I was half-worried there was some fundamental set theory or something which had passed me by. Regards Ian Barwick -- 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] How to use daterange type?
2013/3/17 Csanyi Pal csanyi...@gmail.com: Hi, I'm using postgresql 9.2. I'm trying to figure out how can I use daterange type in my database that is supposed to be a school calendar. I did the followings at the postgresql command prompt: create database schoolcalendar; create table semester_1 ( schooldays daterange ); insert into semester_1 values ( '[2012-09-01, 2012-12-24]' ); So how can I use this table further eg. to get dates of the school days but without Saturdays and Sundays? I don't think there's a built-in way of doing that. You could write a function which takes the daterange as an argument and iterates between the daterange's lower and upper bounds but skipping dates which are Saturdays and Sundays. Regards Ian Barwick -- 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] Using psql to feed a file line by line to a table column
2013/3/13 Alexander Farber alexander.far...@gmail.com:
Thank you, this was indeed the
(uneeded) semicolon at end of the COPY line.
May I ask another question -
(...)
When I add few more words to my text file
and then try to load it into my table again,
then the COPY command will fail,
because of the already stored words:
bukvy= \copy good_words(word) from WORDS
ERROR: duplicate key value violates unique constraint good_words_pkey
CONTEXT: COPY good_words, line 1: абажур
Can't I change the behaviour to silently
ignore inserting such words?
I also have an INSERT trigger on my table,
can I return a NULL from it or something similar?
Yes, if you test for the presence of the word you can return NULL
and the row will be discarded. See example below.
Regards
Ian Barwick
testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
testdb=#
testdb=# INSERT INTO foo VALUES('bar'),('baz');
INSERT 0 2
testdb=# INSERT INTO foo VALUES('bar');
ERROR: duplicate key value violates unique constraint foo_pkey
DETAIL: Key (word)=(bar) already exists.
CREATE OR REPLACE FUNCTION foo_check()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS
$$
BEGIN
PERFORM TRUE
FROM foo
WHERE word = NEW.word;
IF FOUND THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER tr_foo_check
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_check();
testdb=# INSERT INTO foo VALUES('bar');
INSERT 0 0
--
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] Using psql to feed a file line by line to a table column
2013/3/14 Alexander Farber alexander.far...@gmail.com:
Thank you -
On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick
barw...@gmail.com wrote:
I also have an INSERT trigger on my table,
can I return a NULL from it or something similar?
Yes, if you test for the presence of the word you can return NULL
and the row will be discarded. See example below.
testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
testdb=#
testdb=# INSERT INTO foo VALUES('bar'),('baz');
INSERT 0 2
testdb=# INSERT INTO foo VALUES('bar');
ERROR: duplicate key value violates unique constraint foo_pkey
DETAIL: Key (word)=(bar) already exists.
CREATE OR REPLACE FUNCTION foo_check()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS
$$
BEGIN
PERFORM TRUE
FROM foo
WHERE word = NEW.word;
IF FOUND THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER tr_foo_check
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_check();
testdb=# INSERT INTO foo VALUES('bar');
INSERT 0 0
so the return value of an insert trigger
is actually what get's inserted?
Yup, normally that would be the contents of the NEW record.
And it has to be an BEFORE trigger?
Yes, because an AFTER trigger is fired after the row is updated, so
the row can't be changed.
Regards
Ian Barwick
--
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] SELECT 1 = ANY (SELECT ARRAY[1,2,3]) - ERROR: operator does not exist: integer = integer[] ?
2013/3/13 Thor Michael Støre thormich...@gmail.com: Hello, Could someone make sense of this for me? $ /Library/PostgreSQL/9.2/bin/psql -d postgres postgres psql (9.2.3) Type help for help. postgres=# select 1 = ANY (ARRAY[1,2,3]); ?column? -- t (1 row) postgres=# select 1 = ANY (SELECT ARRAY[1,2,3]); ERROR: operator does not exist: integer = integer[] LINE 1: select 1 = ANY (SELECT ARRAY[1,2,3]); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. postgres=# select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]); ?column? -- t (1 row) Why do I have to add an explicit cast to int array on something that is an int array to begin with? Based on the error message containing integer = integer[] I'd say PostgreSQL manages to figure out the right type anyhow, and ::int[] shouldn't change anything, but I still get a message that doesn't make sense when I have an ANY there. A bit tricky to explain... select 1 = ANY (ARRAY[1,2,3]) - Is the integer value 1 contained in the specified array of integers? (YES) select 1 = ANY (SELECT ARRAY[1,2,3]) - Is the integer value 1 contained in the specified result set, which happens to be an array (which is not comparable with an integer)? (NO) select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]); - Is the value one contained in an array of integers which is derived by converting a result set into an array? (YES) Note: testdb= SELECT array[1,2,3] = ANY (SELECT ARRAY[1,2,3]); ?column? -- t (1 row) I hope that makes some kind of sense... Ian Barwick -- 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] indexing elements of a csv ?
2013/3/12 Gauthier, Dave dave.gauth...@intel.com:
Hi:
v9.0.1 on linux.
I have a table with a column that is a csv. Users will select records based
upon the existence of an element of the csv. There is an index on that
column but I'm thinking that it won't be of much use in this situation. Is
there a way to facilitate these queries?
Example:
create table foo (col0 text, col1 text);
create index foo_col1 on foo (col1);
insert into foo (col0,col1) values
('moe','aa,bbb,c'),('larry','x,bbb,yyy'),('curly','m,,oo');
now...
select col0 from foo where the csv element 'bbb' exists as a csv element of
col1
Some attempts, which get the right answers, but which probably won't be very
efficient...
select col0 from foo where string_to_array('bbb','') @
string_to_array(col1);
select col0 from foo where ','||col1||',' like '%,bbb,%';
select col0 from foo where ((col1 like 'bbb,%') or (col1 like '%,bbb,%') or
(col1 like '%,bbb'));
Long shot, but I thought I'd ask anyway.
A GIN index might do the trick:
CREATE INDEX ix_col1_ix ON foo USING GIN(string_to_array(col1,','));
(This is assuming the CSV values can be cleanly converted to
an array using string_to_array()).
You could then query it with:
SELECT col0 FROM foo WHERE string_to_array(col1,',') @ '{bbb}'::text[];
HTH
Ian Barwick
--
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] indexing elements of a csv ?
2013/3/13 Ian Lawrence Barwick barw...@gmail.com:
2013/3/12 Gauthier, Dave dave.gauth...@intel.com:
Hi:
v9.0.1 on linux.
I have a table with a column that is a csv. Users will select records based
upon the existence of an element of the csv. There is an index on that
column but I'm thinking that it won't be of much use in this situation. Is
there a way to facilitate these queries?
Example:
create table foo (col0 text, col1 text);
create index foo_col1 on foo (col1);
insert into foo (col0,col1) values
('moe','aa,bbb,c'),('larry','x,bbb,yyy'),('curly','m,,oo');
now...
select col0 from foo where the csv element 'bbb' exists as a csv element of
col1
Some attempts, which get the right answers, but which probably won't be very
efficient...
select col0 from foo where string_to_array('bbb','') @
string_to_array(col1);
select col0 from foo where ','||col1||',' like '%,bbb,%';
select col0 from foo where ((col1 like 'bbb,%') or (col1 like '%,bbb,%') or
(col1 like '%,bbb'));
Long shot, but I thought I'd ask anyway.
A GIN index might do the trick:
CREATE INDEX ix_col1_ix ON foo USING GIN(string_to_array(col1,','));
(This is assuming the CSV values can be cleanly converted to
an array using string_to_array()).
You could then query it with:
SELECT col0 FROM foo WHERE string_to_array(col1,',') @ '{bbb}'::text[];
Just out of interest, I populated the table with around 1,000,000 rows of
randomly generated data (three items of random upper case characters
in col1), results with and without index below (using an untuned 9.2
installation
on a laptop with a slow hard drive).
Note that adding the index doubled the total table size, which might
be something
to watch out for if the table is very big and you have a lot of unique
values in the
CSV column.
Regards
Ian Barwick
testdb=# SELECT * from foo where string_to_array(col1,',') @ '{PKRY}'::text[];
col0 | col1
+---
ARWC | JIJ,MBDVU,PKRY
FUNWOA | JKEK,PKRY,MQFUQTJ
PJGTD | KSO,HSTB,PKRY
(3 rows)
Time: 1325.536 ms
testdb=# explain SELECT * from foo where string_to_array(col1,',') @
'{PKRY}'::text[];
QUERY PLAN
--
Seq Scan on foo (cost=0.00..28400.42 rows=5021 width=76)
Filter: (string_to_array(col1, ','::text) @ '{PKRY}'::text[])
(2 rows)
testdb=# CREATE INDEX ix_col1_ix ON foo using gin(string_to_array(col1,','));
CREATE INDEX
Time: 170533.158 ms
testdb=# ANALYZE foo;
ANALYZE
Time: 1431.665 ms
testdb=# SELECT * from foo where string_to_array(col1,',') @ '{PKRY}'::text[];
col0 | col1
+---
ARWC | JIJ,MBDVU,PKRY
FUNWOA | JKEK,PKRY,MQFUQTJ
PJGTD | KSO,HSTB,PKRY
(3 rows)
Time: 0.906 ms
testdb=# explain SELECT * from foo where string_to_array(col1,',') @
'{PKRY}'::text[];
QUERY PLAN
Bitmap Heap Scan on foo (cost=20.79..389.58 rows=101 width=24)
Recheck Cond: (string_to_array(col1, ','::text) @ '{PKRY}'::text[])
- Bitmap Index Scan on ix_col1_ix (cost=0.00..20.76 rows=101 width=0)
Index Cond: (string_to_array(col1, ','::text) @ '{PKRY}'::text[])
(4 rows)
Time: 0.377 ms
--
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] Using psql to feed a file line by line to a table column
2013/3/13 Alexander Farber alexander.far...@gmail.com: Hello, I have a list of 40 non-english words, each on a separate line and in UTF8 format, which I'd like to put in the word column of the following table (also in UTF8 and 8.4.13): create table good_words ( word varchar(64) primary key, verified boolean not null default false, stamp timestamp default current_timestamp ); Is there maybe a psql trick for that (the psql --help doesn't mention an input field separator option)? Or do I have to write a Perl-script for that task? This should work from psql: \copy good_words(word) from '/path/to/file.txt' HTH Ian Barwick -- 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] Using psql to feed a file line by line to a table column
2013/3/13 Alexander Farber alexander.far...@gmail.com: Unfortunately doesn't work - On Tue, Mar 12, 2013 at 5:53 PM, Ian Lawrence Barwick barw...@gmail.com wrote: 2013/3/13 Alexander Farber alexander.far...@gmail.com: I have a list of 40 non-english words, each on a separate line and in UTF8 format, which I'd like to put in the word column of the following table (also in UTF8 and 8.4.13): create table good_words ( word varchar(64) primary key, verified boolean not null default false, stamp timestamp default current_timestamp ); This should work from psql: \copy good_words(word) from '/path/to/file.txt' I try: bukvy= \copy bukvy_good_words(word) from WORDS ; \copy: parse error at ; bukvy= \copy bukvy_good_words(word) from 'WORDS' ; \copy: parse error at ; bukvy= \copy bukvy_good_words(word) from WORDS ; \copy: parse error at ; bukvy= \copy bukvy_good_words(word) from '/home/afarber/WORDS' ; \copy: parse error at ; bukvy= \copy bukvy_good_words(word) from /home/afarber/WORDS ; \copy: parse error at ; (sorry, lied you about the table name :-) The file is in the home dir and readable: # ls -al WORDS -rw-rw-r-- 1 afarber afarber 8263539 Mar 12 2013 WORDS Any ideas, what is wrong there for 8.4.13 ? Yup: \copy: parse error at ; which is psql telling you it doesn't like the semicolon. Try leaving it out... -- 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] HwTo Foreign tables anybody?
2013/3/8 Frank Lanitz fr...@frank.uvena.de: Hi folks, I'm looking for a HowTo of Foreign Tables feature. We are thinking of connecting two postgres databases via this way and I wanted to try before I do say yes or no ;) However, I didn't find any good HowTo on via §search_engine. If it's postgres_fdw you're looking for, it's a new feature and will be available with the next 9.3 release. These blog posts might be of interest: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-postgres_fdw/ http://www.postgresonline.com/journal/archives/294-PostgreSQL-9.3-postgres_fdw-a-Test-Drive.html HTH Ian Barwick -- 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] ERROR: syntax error at or near :
2013/3/7 Graham Leggett minf...@sharp.fm: Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... That should work... Which psql version are you using, and what is the table definition? Does the same error occur if you attempt to insert data from a different text file? Regards Ian Barwick -- 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] ERROR: syntax error at or near :
Greg, 2013/3/7 Greg Williamson gwilliamso...@yahoo.com: Graham -- (...) The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. This is psql-specific syntax; the colon should cause the value of the psql variable 'content' to be interpreted; without it, the string 'content' would be inserted. See: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION Regards Ian Barwick -- 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] ERROR: syntax error at or near :
2013/3/7 Graham Leggett minf...@sharp.fm: (...) Which psql version are you using, and what is the table definition? Version as below, from RHEL6: psql (PostgreSQL) 8.4.13 Aha, there is your problem: testdb=# SELECT version(); version - PostgreSQL 8.4.16 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 64-bit (1 row) testdb=# \set content `cat /tmp/hello.txt` testdb=# CREATE TABLE interpolation (value TEXT); CREATE TABLE testdb=# INSERT INTO interpolation VALUES (:'content'); ERROR: syntax error at or near : LINE 1: INSERT INTO interpolation VALUES (:'content'); I.e. that syntax is not supported in 8.4. You'll need to do this: \set content `cat /tmp/certificates.txt` patricia=# update property set value = :content where key = 'patricia.home.security.cacerts'; See: http://www.postgresql.org/docs/8.4/interactive/app-psql.html#AEN71586 (...) Does the same error occur if you attempt to insert data from a different text file? I haven't tried. This is a long blob of PEM encoded certificates, so trying to narrow down a troublesome character will be hard. I was thinking more along the lines of using a small text file to identify whether the problem is with the data, or something else preventing you use this syntax (which as it turns out is the PostreSQL version). Is there some kind of restriction on character data that can be imported into psql? AFAIK only NUL bytes can't be imported this way, see: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION Regards Ian Barwick -- 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] How to remove an item from integer array type
2013/2/21 ChoonSoo Park luisp...@gmail.com
Hello Gurus,
Table A has integer[] column. I need to delete specific integer value from
that column.
f1 | f2
1 {100, 101, 102, 103}
2 {200, 300, 400}
I want to remove 101 from f2 and also preserve the order.
f1 | f2
1 {100, 102, 103}
2 {200, 300, 400}
I tried the following query and it did remove the 101 but it didn't preserve
the order.
update tableA set f2 = (select array_agg(X.id) from (select unnest(f2) id
except select 101 id) X) where f1 = 1;
What's the best way to do this?
Assuming you want to keep the values in numeric order, add an ORDER BY:
update tableA set f2 = (select array_agg(X.id) from (select unnest(f2)
id except select 101 id ORDER BY id) X) where f1 = 1;
HTH
Ian Barwick
--
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] How to remove an item from integer array type
2013/2/21 Russell Keane russell.ke...@inps.co.uk
Sorry,
It's not ordered by value. It's not sorted list unfortunately. It can be
'{100, 120, 102, 130, 104}'.
Do you have other suggestion?
Thank you,
Choon Park
This should work:
update a set f2 = (select array_agg(X.id) from (select f1, unnest(f2) id
from a where f1 = 1 except select 1 f1, 101 id) X) where f1 = 1;
And with the correct table name:
update tableA set f2 = (select array_agg(X.id) from (select f1, unnest(f2) id
from tableA where f1 = 1 except select 1 f1, 101 id) X) where f1 = 1;
I don't think that will work, except accidentally;
testdb=# CREATE TABLE tablea(f1 int, f2 int[]);
CREATE TABLE
testdb=# INSERT INTO tablea values(1, '{100,101,102,103,99,104,22}');
INSERT 0 1
testdb=# BEGIN ;
BEGIN
testdb=*# update tableA set f2 = (select array_agg(X.id) from (select
f1, unnest(f2) id from tablea where f1 = 1 except select 1 f1, 101 id)
x) where f1=1;
UPDATE 1
testdb=*# SELECT * from tablea ;
f1 | f2
+-
1 | {100,22,103,99,104,102}
(1 row)
testdb=*# ROLLBACK ;
ROLLBACK
Moving the exclusion operation up a level seems to do the trick:
testdb=# BEGIN ;
BEGIN
testdb=*# update tableA set f2 = (select array_agg(X.id) from (select
f1, unnest(f2) id from tablea where f1 = 1) x WHERE x.id != 101) where
f1=1;
UPDATE 1
testdb=*# SELECT * from tablea ;
f1 | f2
+-
1 | {100,102,103,99,104,22}
(1 row)
(It's a bit late where I am so I might be overlooking something)
Ian Barwick
--
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] Case insensitive hstore.
2013/2/16 Glenn Pierce glennpie...@gmail.com: Hi Does anyone know how one would select from a table with a hstore field treating the key of the hstore as case insensitive. ie SELECT id, lower(additional_info-'type') AS type FROM table I would like this to work even if if the store tyoe is 'Type' - 'original' As far as I can see from looking at the docs, it's not possible (I could be wrong though). failing that is there a way to lowercase the keys and values of the hstore field of the entire table ? You could create a function like this: CREATE OR REPLACE FUNCTION hstore_to_lower(val HSTORE) RETURNS HSTORE LANGUAGE plpgsql AS $function$ DECLARE hkey TEXT; BEGIN FOR hkey IN SELECT SKEYS(val) LOOP IF LOWER(hkey) != hkey THEN val := val || (LOWER(hkey) || '=' || LOWER((val-hkey::TEXT)))::HSTORE; val := val - hkey; END IF; END LOOP; RETURN val; END; $function$ No guarantee of suitability for a particular purpose or of it being the optimal way of doing this ;) Note that any keys converted to lower case will overwrite existing lower case keys. HTH Ian Barwick -- 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] pg_upgrade
2013/2/15 Ian Harding harding@gmail.com When I run pg_upgrade, it tries to start the old cluster with the -w flag, which waits a while and declares failure, even though it starts the server. If I start/stop without -w everything is great. Can I tell pg_upgrade not to use that flag, or is there a reason it is not working that I should look into? version -- PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit Which PostgreSQL version is the old cluster, and which version is the new cluster? What options are you supplying to pg_upgrade, and what output are you getting? Ian Barwick
Re: [GENERAL] pg_upgrade
2013/2/15 Ian Harding harding@gmail.com On Feb 14, 2013, at 9:50 PM, Ian Lawrence Barwick barw...@gmail.com wrote: 2013/2/15 Ian Harding harding@gmail.com When I run pg_upgrade, it tries to start the old cluster with the -w flag, which waits a while and declares failure, even though it starts the server. If I start/stop without -w everything is great. Can I tell pg_upgrade not to use that flag, or is there a reason it is not working that I should look into? version -- PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit Which PostgreSQL version is the old cluster, and which version is the new cluster? What options are you supplying to pg_upgrade, and what output are you getting? Old is 8.4, new is 9.2. I am not supplying an but the minimum options and --check succeeds. My pg_ctl fails when run by hand with -w (although the database does start) so I know that's the issue. Maybe try running pg_upgrade with the --retain option and check pg_upgrade_server.log for clues? Ian Barwick -- 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] Graphing query results from within psql.
2013/2/14 Aleksey Tsalolikhin atsaloli.t...@gmail.com: Below is an example of feeding query output to gnuplot without leaving psql. I'd like to call it as select graph(select * from example), just for fun. What do I need to learn to do that, please? Can I create a function that uses \o? I think not, because a function runs server-side and \o is a client side feature. You are correct, it is not possible for a backend function to interact directly with psql. You'd need to create a function in PL/Perl etc., and would have to have gnuplot available on the DB server. What you could do is create a small psql script along these lines: barwick@localhost:~$ cat tmp/plot.psql \set QUIET yes \t\a\f ' ' \unset QUIET \o | /usr/bin/gnuplot select 'set title My Graph; set terminal dumb 78 24; set key off; set ylabel Time; set xlabel Servers;' || 'plot ''-'' with lines;' ; :plot_query; \set QUIET yes \t\a\f \unset QUIET \o barwick@localhost:~$ psql -U postgres testdb psql (9.2.3) Type help for help. testdb=# \set plot_query 'SELECT * FROM plot' testdb=# \i tmp/plot.psql My Graph 4 ++-+---+--+--+---+-** + + + + + + + | | 3.5 ++ ++ | | | | 3 ++ ++ | | 2.5 ++*++ | | | | 2 ++ ++ | | | | 1.5 ++ ++ | | + + + + + + + 1 **-+---+--+--+---+-++ 1 1.5 2 2.5 3 3.5 4 Servers testdb=# HTH Ian Barwick
Re: [GENERAL] PostgreSQL 8.4 tar
2013/1/15 Martin Gainty mgai...@hotmail.com: Hi Guys anyone know where I can zip or tar version of PostgreSQL 8.4 ? Here? http://www.postgresql.org/ftp/source/v8.4.15/ Ian Lawrence Barwick -- 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] INSERT... WHERE
2013/1/14 Robert James srobertja...@gmail.com: I have a lot of VALUES I want to INSERT. But only a subset of them - only those that meet a JOIN criteria involving another table. I could INSERT them into a temp table, and then do a SELECT INTO. But do I need to do that? Is there any way to do a INSERT... VALUES ... WHERE... INSERT INTO ... SELECT is what you are looking for. Simple example: CREATE TABLE seltest (id INT); INSERT INTO seltest (id) SELECT 1; HTH Ian Lawrence Barwick -- 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] How can I detect if a schema exists?
2013/1/10 fe...@crowfix.com I was reviving an old test program which hasn't been used since 9.1 and found that SET search_path = xyzzy no longer fails if the schema xyzzy doesn't exist. Is there an approved or recommended way to tell if a schema exists? I can think of lots of ways, but none as easy as the 9.1 search_path. I suppose I shouldn't rely on undocumented features ... How about: SELECT TRUE FROM information_schema.schemata WHERE schema_name = 'xyzzy' ? (Though I notice this table only seems to show the schemas owned by the user if the user is not a superuser). Ian Lawrence Barwick
