Re: [GENERAL] Oracle 10g Express - any danger for Postgres?
On 11/1/05, Welty, Richard <[EMAIL PROTECTED]> wrote: > > Scott Marlowe writes: > > and PostgreSQL enjoys a MUCH richer and easier to use > >set of utilities. I find psql to be much much easier to drive than > >oracle's SQL*PLus, which makes my head hurt. > > i have to use the informix dbaccess tool here at the day job a lot. > hate it. psql is so much nicer... > > i do not have fond memories of SQL*Plus, either. Yes, sqlplus looks especially bad once you're used to banging around in psql. Although, I recently discovered rlwrap (a generic readline wrapper) which makes sqlplus almost tolerable. It's the best thing to happen to sqlplus since... well, since "quit" I suppose. http://www.dizwell.com/oracle/articles/cli_history.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] fine tuned database dump/reload?
On 10/11/05, Dan Armbrust wrote:: > Now I'm just filling the mailing list with mis-information. It actually> ignores all but the last -t flag - so this only allows me to specify one> table at a time, rather than several tables. There's always the low-tech option of: for table in `cat /tmp/table_list.txt` do pg_dump -t $table my_db >> /tmp/my_tables.dmp done It's far from elegant, but it does allow you to dump a specific list of tables.
Re: [GENERAL] Get all table names that have a specific column
On 9/30/05, Emi Lu <[EMAIL PROTECTED]> wrote: > Greetings, > > I am not very familiar with the system views/tables in postgreSQL. I'd > like to get all table names that have a column let's say named "col1". I'd use: select table_name from information_schema.columns where table_schema='my_schema' and column_name='col1' Lots of good info here: http://www.postgresql.org/docs/7.4/static/information-schema.htmlOn 9/30/05, Emi Lu <[EMAIL PROTECTED]> wrote: Greetings,I am not very familiar with the system views/tables in postgreSQL. I'dlike to get all table names that have a column let's say named "col1".For example,t1 (... col1 varchar(3) ... ) t2 (... col1 varchar(3) ... )t3 (... ...)After querying the system tables/views, I can get the result somethinglike :tables contain column "col1"- t1t2(2 rows)Thanks a lot,Emi---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Create a pg table from CSV with header rows
On 9/15/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Sep 15, 2005 at 06:48:59PM -0400, Robert Fitzpatrick wrote:> Anyone know a package that can do this? Perferrably a Unix/Linux> package.It would be trivial to write a script in a language like Perl to read the first line of a file and generate a CREATE TABLE statementfrom it, then issue a COPY command and send the rest of the file.Determining the columns' data types would be a different matter:if they weren't specified in the header then you'd have to guess or perhaps make them all text. I've attached a sample implementation of Michael's suggestion. loadCsv2.pl Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL and XML support
On 9/6/05, Andrey Fomichev <[EMAIL PROTECTED]> wrote: - Are there any of you who need to store and query XML data?- Do you already use PostgreSQL to store XML data or you are just thinking about it? The project I'm currently working on uses XML for both data extraction from Postgres and as a means to transmit data to Postgres for storage. - If you store XML data in PostgreSQL, how do you do it? What tool do you use? We're using Oracle's XSQL servlet (http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm) with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet container. The only change we've made to XSQL is the addition of a custom tag () in order to make use of Postgres functions returning REF CURSOR. Aside from that, we're using XSQL out-of-the-box. Granted, we're not really manipulating XML within the database, rather we're using XML as a sort of database-neutral interface. - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 No. - Do you need some advanced update capabilities? Like node-level updates No.
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"
Jim Nasby asked: What do people think about the Oracle method where bulk data operations can only occur in a specified directory? Making that restriction mightaddress some of the security concerns. I don't think we should changeCOPY in such a way that you *have* to use a specified directory, but if it was an option that helped with the security concerns... Oracle's new (well, since 9i) DIRECTORY object (see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5007.htm#sthref4678) has proven itself to be a flexible approach for me. A privileged account creates the DIRECTORY object like so: CREATE OR REPLACE DIRECTORY my_dir AS '/var/tmp/my_dir'; and then grants access to it: GRANT READ ON my_dir to db_username; I'd envision the COPY command using the DIRECTORY object something like: COPY my_table FROM my_dir:'file.txt';
Re: [GENERAL] Waiting on a transaction
Wow, non-blocking lock failure? Can I take this chance to say an overdue thanks to the Postgresql developers? A truly commercial grade feature set in a free database... On 8/16/05, Matt Miller <[EMAIL PROTECTED]> wrote: > On Tue, 2005-08-16 at 12:01 -0700, Bill Moseley wrote: > > I wondered if my application should set an alarm and timeout > > with an error if, by odd chance, an update hangs. > > Here's a way to handle this under the upcoming 8.1 release: > > Before you execute the update you can execute SELECT ... FOR UPDATE > NOWAIT to select anything (e.g. dummy constant) from the row that you > want to update. If no error is thrown then you have a lock on your row > and you can confidently proceed with your update. Otherwise someone has > that row locked. At that point you can loop and retry if you want. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] truncate all tables?
On 6/29/05, Zlatko Matić <[EMAIL PROTECTED]> wrote: > > How could I truncate, delete all content of all tables in one step ? You could use a query to generate the statements in psql: \t \o trunc_all.out SELECT 'TRUNCATE ' || table_name || ';' FROM information_schema.tables WHERE table_schema='my_schema_name' AND table_type='BASE TABLE'; \t \o ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Viewing non-system objects in psql
On 6/16/05, David Fetter <[EMAIL PROTECTED]> wrote: > On Thu, Jun 16, 2005 at 07:54:29PM -, Greg Sabino Mullane wrote: > > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > I recently submitted a patch that makes all the database objects > > behave in the same way as far as the backslash psql commands. > > Currently, tables work like this: \dt lists all non-system tables in > > your path, while \dtS shows only the system tables. The idea is to > > expand that functionality to other database objects, e.g. functions. > > Currently, \df will show you a list of *all* functions, including > > the system ones. Since there are currently over 1500 system > > functions, this limits its usefullness. The patch standardizes > > everything on the way we do tables and indexes - the user ones are > > shown by default, and you add a capital "S" if you really want to > > see the system ones. So the patch would have \df show all your > > functions, \dD show all your domains, \doS shows the system > > operators, etc. > > +1 :) > Thumbs up from me, too. It fails the test of "least astonishment" for me when I get a listing of all the system functions with a \df, no matter how many times I do it. I like the \dfS model! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Oracle's Virtual Private Database functionality
In the spirit of "tell us what you're trying to do..."
I'd like to mimic a subset of Oracle's Virtual Private Database
functionality (see
http://dba-oracle.com/oracle_news/2004_1_16_vpd_security.htm for an
overview) in Postgres: based on some per-connection setting, I'd like a
query to return a different set of rows. In VPD, the Oracle engine
actually applies a defined predicate (say, country_code='USA') to every
query. The idea is that a given set of users can only see rows in a
table that match this predicate, while the other rows are invisible to
them.
Now for the "how I tried to do it" part...
I thought I was on my way to doing this in Postgres by making use of
schemas and search_path:
CREATE USER user1 UNENCRYPTED PASSWORD 'user1';
CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA canada;
GRANT USAGE ON SCHEMA canada TO user1;
CREATE TABLE canada.row_limiter (country_code VARCHAR(3));
INSERT INTO canada.row_limiter VALUES('CAN');
GRANT SELECT ON canada.row_limiter TO user1;
CREATE SCHEMA usa;
GRANT USAGE ON SCHEMA usa TO user1;
CREATE TABLE usa.row_limiter (country_code VARCHAR(3));
INSERT INTO usa.row_limiter VALUES('USA');
GRANT SELECT ON usa.row_limiter TO user1;
\CONNECT - user1
SET SEARCH_PATH TO '$user',canada;
CREATE TABLE my_data (country_code VARCHAR(3), data NUMERIC);
INSERT INTO my_data VALUES('USA',11);
INSERT INTO my_data VALUES('USA',12);
INSERT INTO my_data VALUES('USA',13);
INSERT INTO my_data VALUES('CAN',21);
INSERT INTO my_data VALUES('CAN',22);
INSERT INTO my_data VALUES('CAN',23);
CREATE VIEW my_data_v AS
SELECT md.*
FROM my_data md,
row_limiter rl
WHERE rl.country_code=md.country_code;
SELECT * FROM my_data_v;
-- Looks great - I only see Canadian data!!
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_v;
-- Darn, I still only see Canadian data :-(
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
\d my_data_v
View definition:
SELECT md.country_code, md.data
FROM my_data md, CANADA.row_limiter rl -- <--ah, and here's the
reason...
WHERE rl.country_code::text = md.country_code::text;
It's apparent why: the view determines which table it's going to use at
view creation time, not at query time, so this method is no good.
Is there a "right way" to accomplish what I'm trying to do?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Oracle's Virtual Private Database functionality
[ Sorry if this appears twice on the list: sent it via a non-subscribed email
the first time around. Of course, while waiting for it to appear I figured out
a workaround; see the "Late breaking update" at the end. ]
In the spirit of "tell us what you're trying to do..."
I'd like to mimic a subset of Oracle's Virtual Private Database functionality
(see http://dba-oracle.com/oracle_news/2004_1_16_vpd_security.htm for an
overview) in Postgres: based on some per-connection setting, I'd like a query
to return a different set of rows. In VPD, the Oracle engine actually applies
a defined predicate (say, country_code='USA') to every query. The idea is that
a given set of users can only see rows in a table that match this predicate,
while the other rows are invisible to them.
Now for the "how I tried to do it" part...
I thought I was on my way to doing this in Postgres by making use of schemas
and search_path:
CREATE USER user1 UNENCRYPTED PASSWORD 'user1';
CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA canada;
GRANT USAGE ON SCHEMA canada TO user1;
CREATE TABLE canada.row_limiter (country_code VARCHAR(3));
INSERT INTO canada.row_limiter VALUES('CAN');
GRANT SELECT ON canada.row_limiter TO user1;
CREATE SCHEMA usa;
GRANT USAGE ON SCHEMA usa TO user1;
CREATE TABLE usa.row_limiter (country_code VARCHAR(3));
INSERT INTO usa.row_limiter VALUES('USA');
GRANT SELECT ON usa.row_limiter TO user1;
\CONNECT - user1
SET SEARCH_PATH TO '$user',canada;
CREATE TABLE my_data (country_code VARCHAR(3), data NUMERIC);
INSERT INTO my_data VALUES('USA',11);
INSERT INTO my_data VALUES('USA',12);
INSERT INTO my_data VALUES('USA',13);
INSERT INTO my_data VALUES('CAN',21);
INSERT INTO my_data VALUES('CAN',22);
INSERT INTO my_data VALUES('CAN',23);
CREATE VIEW my_data_v AS
SELECT md.*
FROM my_data md,
row_limiter rl
WHERE rl.country_code=md.country_code;
SELECT * FROM my_data_v;
-- Looks great - I only see Canadian data!!
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_v;
-- Darn, I still only see Canadian data :-(
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
\d my_data_v
View definition:
SELECT md.country_code, md.data
FROM my_data md, CANADA.row_limiter rl -- <--ah, and here's the reason...
WHERE rl.country_code::text = md.country_code::text;
It's apparent why: the view determines which table it's going to use at view
creation time, not at query time, so this method is no good.
Is there a "right way" to accomplish what I'm trying to do?
Late breaking update:
Looks like a function returning SETOF does the trick, so it must not resolve
the table early like the view does:
CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
SELECT md.country_code,
md.data
FROM my_data md, row_limiter rl
WHERE rl.country_code = md.country_code;
' LANGUAGE sql;
SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_f();
country_code | data
--+--
USA | 11
USA | 12
USA | 13
SET SEARCH_PATH TO '$user',canada;
SELECT * FROM my_data_f();
country_code | data
--+--
CAN | 21
CAN | 22
CAN | 23
Can I rely on this behavior? Is this the best way to do what I'm after?2 to
accomplish what I'm trying to do?
Late breaking update:
Looks like a function returning SETOF does the trick, so it must not resolve
the table early like the view does:
CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
SELECT md.country_code,
md.data
FROM my_data md, row_limiter rl
WHERE rl.country_code = md.country_code;
' LANGUAGE sql;
SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_f();
country_code | data
--+--
USA | 11
USA | 12
USA | 13
SET SEARCH_PATH TO '$user',canada;
SE
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Copy row from table to table
>>> Michael Fuhr <[EMAIL PROTECTED]> 12/14/04 1:45 PM >>>
>>On Tue, Dec 14, 2004 at 11:48:21AM -0600, Kall, Bruce A. wrote:
>> What is the easiest way to copy a particular row from one table to
>> another (assuming all the columns are exactly the same). I want to
>> maintain a table ('backup_table') that has rows I have (or will be)
>> deleting from my 'original_table'. (Both tables have the same 68
columns).
>>
>> I've tried
>>
>> INSERT INTO backup_table SELECT * FROM original_table WHERE
id='2305'
>That should work if the table definitions are identical or
compatible,
>including column positions being the same. Didn't it work? If not,
>what happened?
One cute way to guarantee that the table definitions are identical is:
CREATE TABLE backup_table AS SELECT * FROM original_table WHERE 1=0;
That ensures that subsequently doing the following is safe:
INSERT INTO backup_table SELECT * FROM original_table WHERE id='2305';
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] Equivalent to Oracle's inline CURSOR in a SELECT clause?
In Oracle I can do this: SELECT h.transaction_number CURSOR(SELECT d.detail_number FROM detail d WHERE d.transaction_number=h.transaction_number) FROM header h WHERE h.transaction_date='25-aug-2004' which comes in handy once in a while for nested parent/child relationships, especially when using Oracle's XSQL servlet to produce XML from queries. Is there an equivalent syntax in PostgreSQL? P.S. Half surprisingly, the Oracle XSQL servlet works just fine with PostgreSQL as a backend! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
