Re: [SQL] SELECT all fields except two
This does not exist in SQL. However you could achieve similar functionality by doing a suitable query against the system info tables to find out what columns are available and then building your query appropriately. For an example, try psql -E -c '\d mytable' Drew Pit M. wrote: > select *,!Blob1,!Blob2 from MyTable > > Perhaps this is a construction that doesn't exist in the current SQL > standard at the moment. > Does anybody know the solution for that problem without explicitly > specifying the whole field list like: > > select Field1,Field2,...Field100 from MyTable > > > HP ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Function not return zero record
Hello,
I wrote a simple function who checks in a table an address and returns
the id and coordinates of the corresponding address.
The problem is when I run separately the SQL query, it gives me zero (0)
entry when I submit a new address - it is what I want as result - and
when I run my function, it gives me 1 entry with the same address.
Note: I create a pseudo-type to return the result. Is that my problem?
I run Postgresql 7.4 on i686-pc-cygwin (Windows XP), with PostGIS 1.0.
CREATE TABLE adresses (
id int4PRIMARY KEY DEFAULT nextval('seq_adresse'),
);
SELECT AddGeometryColumn('greffe1','adresses','geom', 4269, 'POINT', 2);
CREATE TYPE getCache AS (id integer,geocode double precision ARRAY[2]);
CREATE FUNCTION getCache(varchar)
RETURNS getCache AS '
SELECT id, ARRAY[x(geom),y(geom)] FROM adresses where adresse = $1;
' LANGUAGE sql;
Best regards
Sylvain Racine
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Function not return zero record
Sylvain Racine wrote: Hello, I wrote a simple function who checks in a table an address and returns the id and coordinates of the corresponding address. The problem is when I run separately the SQL query, it gives me zero (0) entry when I submit a new address - it is what I want as result - and when I run my function, it gives me 1 entry with the same address. Note: I create a pseudo-type to return the result. Is that my problem? The problem is the difference between a value of type getCache and a set of values of type getCache. Define your function as returning a SETOF getCache Call it with SELECT * FROM getCache(...) HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] alter column type from boolean to char with default
Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> >> Hmm ... the way I would have expected to work is
> >>
> >> alter table posts
> >> alter column deleted drop default,
> >> alter column deleted type char(1)
> >> using (case when deleted then 't' else 'f' end),
> >> alter column deleted set default 'f';
>
> > Perhaps it is easiest to allow the user to specify the new default after
> > USING?
>
> He already did --- I don't want to add some random new syntax for this.
>
> Maybe we could hack things so that if both an ALTER TYPE and a SET
> DEFAULT operation are present, we implicitly add a DROP DEFAULT at the
> start. But leave the timing of any explicitly specified DROP DEFAULT
> as-is.
That seems reasonable. I assume it'd throw a notice in that case.
Alternatively, you already have the USING clause to tell you how to
alter the data. How about using it to alter the default as well?
Replace instances of column references with the old default. In this
case, the default would go from
DEFAULT ('f'::boolean)
to
DEFAULT (case when ('f'::boolean) then 't' else 'f' end)
No syntax or grammar change involved, but I'm not sure the additional
semantics adhere to the rule of minimum surprise.
If you wanted to support the DROP CONSTRAINT, ADD CONSTRAINT (which
seems useful) while without confusing it with ADD, DROP (I can't
imagine a use for this), then perhaps tweaking the grammar would be the
answer so that either DROP CONSTRAINT must be the first or ADD
CONSTRAINT must be the last part of an ALTER.
Drew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Error: out of memory in create table as
I'm having memory problems. I needed to materialize a couple views (create table t as select * from v) to get acceptable performance from a small data storage system I'm building, but now I get out of memory issues on that statement. It is near the bottom of about a 40 line function, and it completes up to that point, but when I try to run the create with only about a million rows, it fails: ERROR: out of memory DETAIL: Failed on request of size 856. CONTEXT: SQL statement "create table gwt as select * from gworktick" PL/pgSQL function "processadd" line 30 at SQL statement I don't see any temp files being generated on disk. Attached is our postgresql.conf file. We have little Postgres experience (mostly either Oracle, Sybase IQ, etc..) -jason P.S., lack of an upsert is really killing us. # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security & Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - # varlena says to set to ~8% of RAM # shared_buffers = 30 # min 16 or max_connections*2, 8KB each shared_buffers = 8 # min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # work_mem = 20
Re: [SQL] Error: out of memory in create table as
jason nordwick <[EMAIL PROTECTED]> writes: > ERROR: out of memory > DETAIL: Failed on request of size 856. > CONTEXT: SQL statement "create table gwt as select * from gworktick" > PL/pgSQL function "processadd" line 30 at SQL statement What PG version? What does "explain select * from gworktick" say? Can you get explain analyze results, or does that fail with the same error? > work_mem = 512000 # min 64, size in KB This could well be excessive for complicated views. You're promising that you can provide half a gig of RAM for *each* sort or hash in the plan. regards, tom lane ---(end of broadcast)--- TIP 1: 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
Re: [SQL] alter column type from boolean to char with default
"Andrew Hammond" <[EMAIL PROTECTED]> writes: > Alternatively, you already have the USING clause to tell you how to > alter the data. How about using it to alter the default as well? The reasons not to do that are already set forth in the ALTER TABLE man page. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
