Re: [GENERAL] Create table if not exists ... how ??
Assuming you know the schema name, you could always check the catalog table, something like select count(*) from pg_tables where schemaname= 'foo' and tablename='bar' If it returns, then you know a table by the name foo.bar exists. if not you can create it. -Said Jennifer Trey wrote: No I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time. Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is. Cheers, Jen On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway wrote: On 07/19/2010 09:33 AM, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes Probably better to do: DROP TABLE IF EXISTS post_codes; CREATE TABLE post_codes(...); See: http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -- 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] Multicolumn primary key with null value
Primary keys are defined as 'unique not null' even if they are composite. So I believe postgres would not let you do that: 5.3.4. Primary Keys Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint. A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. (This is a direct consequence of the definition of a primary key. Note that a unique constraint does not, by itself, provide a unique identifier because it does not exclude null values.) This is useful both for documentation purposes and for client applications. http://www.postgresql.org/docs/8.1/static/ddl-constraints.html Szymon Guz wrote: Does any SQL standard allows for a multicolumn primary key where in one record there is a null in on of the primary key columns? regards Szymon Guz -- 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] tipo de dato
Miguel, Tenras mas exito si haces tus preguntas en la lista en espanol : pgsql-es-ay...@postgresql.org ya que ahi la majoria de la gente habla ingles y no espanol -Said Said Ramirez Miguel Angel Hernandez Moreno wrote: disculpen alguien tiene o sabe de algun documento que me pueda ayudar para defender la idea de que los tipos de datos si afectan en la velocidad de una consulta por ejemplo. tengo un tipo de dato timestamp y un numerico y lo que quiero dar a entender es que una consulta con un numerico con un tipo de indice B-TREE es mas rapido que uno con fechas y un indice tipo HASH si alguien puede aventarme un manual o un link hacia un web que no seas la de postgres o algo que apoye mi idea muchas gracias!! Si estoy mal hagan el comentario!!! -- 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 grant a user read-only access to a database?
if you don't want to search the archives, it could just be easier to look at the catalog tables yourself. If you have no experience with them, many times if you do pg_foo when you are interested in 'foo' you will get something, i.e pg_user also exists. #\d pg_tables View "pg_catalog.pg_tables" Column| Type | Modifiers -+-+--- schemaname | "name" | tablename | "name" | tableowner | "name" | tablespace | "name" | hasindexes | boolean | hasrules| boolean | hastriggers | boolean | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE c.relkind = 'r'::"char"; and then the sql just comes naturally: select 'grant select on '|| schemaname || '.' || tablename || ' to baz' from pg_tables where schemaname = 'bar' ; Note that it is important to select the schemaname because there could be two different tables in two different schemas with the same tablename. Also you should keep in mind that this will only work for tables, if you start adding views you have to add more to the generation of sql. -Said Said Ramirez Raymond O'Donnell wrote: On 02/03/2010 14:56, Thom Brown wrote: But I still need to define access to each table separately? Thanks, Antonio. As far as I'm aware. It's only in the upcoming version 9.0 that you can do things like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; Other folk on here may have some alternative suggestions though. I think people have in the past posted queries that extract the table names from the system catalogues and then grant privileges on them it might be worthwhile having a trawl through the archives. Ray. -- 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] Proxy for postgres
Have you tried sql relay? They support may platforms: http://sqlrelay.sourceforge.net/ -Said Martin Spinassi wrote: Hi list! I'm searching for something to use as a proxy for different instances of postgres. I've found pl/proxy, but it seems to work with (correct me if i'm wrong) functions, and some hard work would be needed in our applications in order to make them use those functions replacing direct access, an I'm trying to avoid this. Another option is pgpool, but I see too much lock in tables at transactional queries, and I'm pretty sure this will make my life a lot harder. So the question is, what can I use as a proxy queries to different instances of postgres? Cheers Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] IN limit
Is there a limit to the number of entries I can pass in an IN clause as part of a SELECT statement? As in SELECT baz FROM foo where id in ( 1, 2,... ) ; Thanks, -Said -- 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] Specifying text to substitute for NULLs in selects
I think you are more after something like
SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.
-Said
Ivan Sergio Borgonovo wrote:
On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
[EMAIL PROTECTED] wrote:
>
> Hi,
>
> I can specify the text used to represent a null value in output
> from copy, but I'd like to do something similar is select output,
> eg: all NULL values are represented by NA or NaN.
>
> I can't find anything in the docs about this.
>
> This could be managed using case statements around all the columns
> in the query, but is there a simpler way, like setting a system
> variable to specify this?
wtw_drupal=# create schema test;
CREATE SCHEMA
wtw_drupal=# create table test.test(c1 text);
CREATE TABLE
wtw_drupal=# insert into test.test values(null);
INSERT 0 1
wtw_drupal=# insert into test.test values('test');
INSERT 0 1
wtw_drupal=# \copy test.test to stdout null as 'BANANA'
BANANA
test
wtw_drupal=# drop schema test cascade;
NOTICE: drop cascades to table test.test
DROP SCHEMA
everything clearly explained in the COPY manual:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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
Re: [GENERAL] Must be table owner to truncate?
According to the documentation, http://www.postgresql.org/docs/current/interactive/sql-truncate.html , only the owner can truncate a table. Which means the non-owner must either log in/ switch roles as the owner, or they can just run a DELETE. -Said smiley2211 wrote: Hello all, I am trying to GRANT truncate permissions to a non-owner of table and it's not allowing me to: GRANT TRUNCATE ON stage01 TO jaime44; ERROR: unrecognized privilege type "truncate" How do I grant said permission? Thanks...Michelle. -- View this message in context: http://www.nabble.com/Must-be-table-owner-to-truncate--tp18697753p18697753.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
Re: [GENERAL] How to remove duplicate lines but save one of the lines?
Yes, here foo is a temp table. As others have pointed out, you could probably do a create table foo as select distinct * from orig_table. I would move the data back to orig_table, so that constraints and privileges are maintainited. After you have done this, you can put a uniq constraint on columns A & B. I am uncertain if you can do something like ALTER TABLE orig_table ADD UNIQUE (A,B) ON DUPLICATE DELETE. -Said A B wrote: > There is probably a more elegant way of doing it, but a simple way of doing > it ( depending on the size of the table ) could be: > > begin; > > insert into foo select distinct * from orig_table; > delete from orig_table; > insert into orig_table select * from foo; > > commit; Just to make it clear to me Here foo is a table that I have to create with the command CREATE TABLE foo (same columns as orig_table); ? Is it possible to add a unique constraint to the table, with a "delete" option so it will delete duplicates? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Said Ramirez Super Cool MySQL DBA cel: 732 425 1929 -- 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 duplicate lines but save one of the lines?
There is probably a more elegant way of doing it, but a simple way of doing it ( depending on the size of the table ) could be: begin; insert into foo select distinct * from orig_table; delete from orig_table; insert into orig_table select * from foo; commit; -Said A B wrote: I have a table with rows like this A 1 A 1 B 3 B 3 C 44 C 44 and so on. and I want it to be A 1 B 3 C 44 so how can I remove the all the duplicate lines but one? -- 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
Re: [GENERAL] regexp help
You can probably use [^0-9] -Said Garry Saddington wrote: I have the following simple regular expression: "SELECT substring(addressline1 from '(^[0-9]+)') from addresses" How could I find non-matches of this same pattern? regards Garry -- 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
