Re: [SQL] Rename database?
No. Try instead CREATE DATABASE newname FROM TEMPLATE oldname; DROP DATABASE oldname; ""Wei Weng"" <[EMAIL PROTECTED]> wrote in message 000c01c2c32b$09526500$5301a8c0@monet">news:000c01c2c32b$09526500$5301a8c0@monet... > This is a multi-part message in MIME format. > > --=_NextPart_000_0009_01C2C301.1FA1DB80 > Content-Type: text/plain; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > Can I use a SQL statement to rename database? > > e.g Rename Database Bank to Database Bank_backup > > Thanks! > > Wei > > --=_NextPart_000_0009_01C2C301.1FA1DB80 > Content-Type: text/html; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > > > 2"> > > > > > Can I use a SQL statement to rename database? IV> > > e.g Rename Database Bank to Database Bank_backup= > > > Thanks! > > Wei > > > --=_NextPart_000_0009_01C2C301.1FA1DB80-- > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] automatic time/user stamp - rule or trigger?
You're doing update right ??? Just update the column... It's even easier if, when you do your updates... You just: UPDATE blah SET field = value,, updatestamp = 'Now()' WHERE condition... GP "Neal Lindsay" <[EMAIL PROTECTED]> wrote in message b1r864$2mpp$[EMAIL PROTECTED]">news:b1r864$2mpp$[EMAIL PROTECTED]... > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. > > Thanks, > -Neal > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Convert numeric to money
SELECT 1032::numeric(10,2); <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi. > I have searched in mailing-list archives about converting types, but I > couldn't > found a function or clause that convert a numeric type to money type. > How Can I convert this types? > > => select '1234'::money; > money > > R$1.234,00 > > => select '1234'::numeric::money; > ERROR: cannot cast type numeric to money > > The problem is becouse I have a table with "numeric" field, and I need to > show > it like "money" type (R$ 1.234,00). Is there a function to convert it??? > Or is > there a function to mask the numeric field to show like money (with > R$x.xxx,xx)??? > > Thanks. > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Breakdown results by month
You are looking for what is commonly referred to as a "pivot-table" or a "cross-tab". Pivot-tables are a fairly sophisticated, non-normalized view of a dataset, nd most commonly appear in spreadsheets and are used for financial or statistical analysis. These queries typically use an aggregate function (sum, min, max, etc) and a "group by" clause when generated from an SQL query... There are lots of decent tutorials / samples out there (Google for "Pivot Table SQL" and you'll see...) http://www.windowsitpro.com/Files/09/15608/Listing_01.txt http://mail.python.org/pipermail/python-list/2005-February/264233.html "Henry Ortega" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >I have the ff table: > > id |total| effective|end_date > john 6 01-01-200502-28-2005 > john 8 03-01-200506-30-2005 > > How can I return: > id |total| effective|end_date > john 6 01-01-200501-31-2005 > john 6 02-01-200502-28-2005 > john 8 03-01-200503-31-2005 > john 8 04-01-200504-30-2005 > john 8 05-01-200505-31-2005 > john 8 06-01-200506-30-2005 > > Any help would be appreciated. Thanks > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
"Ferindo Middleton Jr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Is there some reason why the SERIAL data type doesn't automatically have a
> UNIQUE CONSTRAINT. It seems that the main reason for using it is so that
> the value for this field keeps changing automatically and is never null so
> any one record can be identified using it- So why not imply that it is
> always be UNIQUE anyway. I mean, if you were to force another value on a
> SERIAL field that already had that same value, the would through the
> sequence tracking the the fields current value off any way, so it just
> makes sense to me to not let a serial field be duplicated. Let's take a
> poll. Is there anyone out there who actually uses the SERIAL data type who
> would not want it to be UNIQUE?
>
> Ferindo
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
You are correct... serials don't have unique constraints unless they are
also defined as a primary key...
It seems to me that all you need to do is make your serial value a primary
key in your DDL... (which is the same as defining a unique constraint...)
consider the following:
CREATE TABLE sys_test (
id serial NOT NULL PRIMARY KEY,
txt text not null
) WITH OIDS;
INSERT INTO sys_test(txt) VALUES ('A');
INSERT INTO sys_test(txt) VALUES ('B');
-- INSERT statement #3 throws an expected error
INSERT INTO sys_test(id, txt) VALUES (1, 'C'); // THROWS UNIQUE CONTRAINT
ERROR AS EXPECTED !!!
SELECT * FROM sys_test;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Sending function parametars within EXECUTE ''SELECT...
"Mario Splivalo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've learned that one can't use temporary tables within the function > unless > EXECUTE'd the SELECTS from that temp table. > > So, I have a function like this: > > CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType > AS > ' > DECLARE >aDataId ALIAS FOR $1; >aBid ALIAS FOR $2; >return myType; >rec record; > BEGIN >CREATE TEMP TABLE tmpTbl >AS >SELECT col1, col2 FROM t1 JOIN t2 ON t1.c1 = t2.c3 WHERE t1.c4 = > aDataId; > >FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE > col2 = aBid'' >LOOP >return.myType = rec.num; >END LOOP; > >RETURN NEXT return; >RETURN; > END > ' language 'pgplsql' > > > Now, when I try to call that function, i get an error that aBid is > unknown > column name. How can I pass the aBid value to the SELECT statement > inside > the EXECUTE? > > I'm using temp table because the tables from where to gather the data > are > huge. If I'd be using views instead, it'd take too much time. I tought > that > storing only a small fraction of the data (1/1000 of the data is put > into > the temp table), and then performing calculations on that temp table > would > be much faster. I just don't know how to pass parameters to the EXECUTE > SELECT. > > Any help here would be appreciated. > >Mike > > P.S. I tried above code on pg8.0.3 and 8.1beta2, with same results. > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2 = " || aBid || '' LOOP ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] using pg_tables and tablename in queries
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > solarsail <[EMAIL PROTECTED]> writes: >> I have a large number of tables with a common naming convention > >> mytable001, mytable002, mytable003 ... mytable00n > >> I would like to do a query across all of the tables, however I do not >> know >> all of the tables before hand, and I do not want to ( cant ) manually >> generate a query like > >> select * from mytable001, mytable002, mytable003 > >> I have a query that returns the names of the tables I want to query: > >> select tablename from pg_tables where tablename like 'mytable%' > > This looks to me like a situation in which you should rethink your > data design. Those tables should all get merged into one big table, > adding one extra column that reflects what you had been using to > segregate the data into different tables. > > regards, tom lane Sounds like a classic opportunity to use the postgreSQL inheritance model to me > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Design problemi : using the same primary keys for inherited objects.
I dont consider this to be a design problem... In fact... I do the inherited
table thing pretty routinely... It (table inheritance) works very well if
you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
system without the overhead of managing transactions.
Consider the relationship between a company (sys_client) and the company's
employees (sys_user). An inheritance model in postgreSQL is a very efficient
mechism to ensire that user entries "roll-up-to" or are "owned" by a client
entry in the parent table. Here's a short example:
If you wish -- you can execute the following DDL.
CREATE TABLE sys_client (
id SERIAL NOT NULL PRIMARY KEY,
c_name VARCHAR(72),
lu_client_type INTEGER NOT NULL DEFAULT 4 REFERENCES lu_client_type,
lu_support_program INTEGER NOT NULL REFERENCES lu_support_program(id),
create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
change_id INTEGER DEFAULT 0,
active_flag BOOLEAN NOT NULL DEFAULT TRUE
) WITH OIDS;
CREATE TABLE lu_user_type (
id serial NOT NULL PRIMARY KEY,
type_desc varchar(72) NOT NULL,
can_delete bool DEFAULT false,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id INTEGER NOT NULL DEFAULT 0,
active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
INSERT INTO lu_user_type(type_desc) VALUES ('Administrator');
INSERT INTO lu_user_type(type_desc) VALUES ('User');
INSERT INTO lu_user_type(type_desc) VALUES ('Restricted user');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo - admin');
CREATE TABLE sys_user (
sys_client_id INTEGER NOT NULL REFERENCES sys_client(id),
lu_client_group INTEGER references sys_client_group(id),
lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
f_name VARCHAR(50) NOT NULL,
m_name VARCHAR(50),
l_name VARCHAR(50) NOT NULL,
email_addr VARCHAR(120) NOT NULL,
uname VARCHAR(20) NOT NULL,
upwd VARCHAR(20) NOT NULL,
login_allowed BOOLEAN DEFAULT true,
reset_pwd BOOLEAN DEFAULT false,
pwd_change_reqd bool DEFAULT false,
lost_passwd bool DEFAULT false
) INHERITS (sys_client);
CREATE TABLE sys_user_history (
hist_id SERIAL NOT NULL PRIMARY KEY,
hist_dt TIMESTAMP NOT NULL DEFAULT NOW()
) INHERITS (sys_user);
CREATE OR REPLACE RULE sys_user_history AS ON UPDATE TO sys_user DO INSERT
INTO sys_user_history (SELECT * FROM ONLY sys_user WHERE id = OLD.id);
CREATE TABLE sys_user_login (
id serial NOT NULL PRIMARY KEY,
sys_client INTEGER NOT NULL REFERENCES sys_client(id),
login_date timestamp NOT NULL DEFAULT now(),
valid_until timestamp NOT NULL DEFAULT (now() + '00:20:00'::interval),
session_id varchar(32) NOT NULL UNIQUE,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id int4 NOT NULL DEFAULT 0,
active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
insert into sys_client(c_name) VALUES ('Mattel');
insert into sys_client(c_name) VALUES ('Hasbro');
insert into sys_client(c_name) VALUES ('Lego');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 1, 'Arnold', 'Antione', 'Aardvaark',
'[EMAIL PROTECTED]', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 2, 'Roberto', 'Guiterrez', 'Amendola',
'[EMAIL PROTECTED]', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 1, 'Albert', '', 'Einstien',
'[EMAIL PROTECTED]', 'albert', 'albert');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 2, 'David', 'C', 'Davidson',
'[EMAIL PROTECTED]', 'david', 'david');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 1, 'Marilyn', '', 'Monroe',
'[EMAIL PROTECTED]', 'mmonroe', 'mmonroe');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 2, 'Fred', 'E', 'Flintstone',
'[EMAIL PROTECTED]', 'fred', 'fred');
SET SQL_INHERITANCE = ON;
-- RUN EACH OF THESE QUERIES IN SUCESSION TO SEE HOW IT REALLY WORKS...
SELECT * FROM sys_client;
SELECT * FROM ONLY sys_client;
SELECT * FROM sys_user;
SELECT * FROM ONLY sys_user;
SELECT SC.id AS client_id, SC.c_name, SU.id AS employee_id, SU.f_name,
SU.l_name FROM sys_client SC JOIN sys_user SU ON SU.sys_client_id = SC.id;
"David Pradier" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi everybody,
>
> i've got a strange design question to ask you.
> It's something I couldn't answer to while feeling confusely it was an
> absolutely BAD thing to do.
>
> For our application, we have developed our own framework which sits on
> top of PostgreSQL. It uses object programming and implements heritage.
> Inherited objects use heritage links in
Re: [SQL] automatic update or insert
The following trigger procedure works for me you'd need to adjust this to manipulate YOUR table schema: DROP FUNCTION dmc_comp_plan_duplicates() CASCADE; CREATE OR REPLACE FUNCTION dmc_comp_plan_duplicates() RETURNS "trigger" AS $BODY$ DECLARE did integer; BEGIN SELECT COALESCE(id, 0) AS id FROM dmc_compensation_plan_quota WHERE dmc_compensation_plan = NEW.dmc_compensation_plan AND dmc_quota_item = NEW.dmc_quota_item INTO did; RAISE NOTICE 'DID: %', did; IF ((did = 0) OR (did IS NULL)) THEN RAISE NOTICE 'INSERT: DID: %', did; -- INSERT INTO dmc_compensation_plan_quota (dmc_compensation_plan, dmc_quota_item) VALUES (NEW.dmc_compensation_plan, NEW.dmc_quota_item); RETURN NEW; ELSE RAISE WARNING 'UPDATE: DID: %', did; UPDATE dmc_compensation_plan_quota SET active_flag = TRUE WHERE id = did; RETURN NULL; END IF; -- DEFAULT = DO NOTHING... RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION dmc_comp_plan_duplicates() OWNER TO datamosaics; CREATE TRIGGER dmc_comp_plan_duplicates BEFORE INSERT ON dmc_compensation_plan_quota FOR EACH ROW EXECUTE PROCEDURE dmc_comp_plan_duplicates(); "tobbe" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi. > > I have a little problem. > > In a system of mine i need to insert records into table [tbStat], and > if the records exist i need to update them instead and increase a > column [cQuantity] for every update. > > I.e. the first insert sets cQuantity to 1, and for every other run > cQuantity is increased. > > Currently i have implemented this as a stored procedure in the plpgsql > language. This means that in my stored procedure i first do a select to > find out if the row exists or not, then i do a insert or update > depending if the row existed. > > Unfortunately, stored procedures seems awfully slow. And i need the > application to go faster. > > One solution could be to implement the stored procedure in my program > instead. I think that this will be atleast 50% faster than my stored > procedure, so that would be ok. > > However, this has made me thinking. Couldn't this be done directly in > SQL? > > > Brgds Robert > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] handling money type
The SQL data type "money" in postgreSQL was deprecated several versions
ago... however -- it is still available in the system. The definitions is:
CREATE TYPE money
(INPUT=cash_in, OUTPUT=cash_out, DEFAULT='',
INTERNALLENGTH=4, ALIGNMENT=int4, STORAGE=PLAIN);
ALTER TYPE money OWNER TO postgres;
COMMENT ON TYPE money IS 'monetary amounts, $d,ddd.cc';
8.2. Monetary Types
Note
The money type is deprecated. Use numeric or decimal instead, in combination
with the to_char function.
The money type stores a currency amount with a fixed fractional precision;
see Table 8.3, "Monetary Types". Input is accepted in a variety of formats,
including integer and floating-point literals, as well as "typical" currency
formatting, such as '$1,000.00'. Output is generally in the latter form but
depends on the locale.
When you "SELECT money('1000');"
the type cast returns: $1,000.00
HTH...
""padmanabha konkodi"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
hello developers,
i have facing one major problem handling sql money dataType in the java
i have tried many permutation and combination but still i dint got correct
data type to use in java to pass money data
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Returning rows as columns
You're looking for a technique generally referred to as a "pivot table" which is really a non-normalized or aggregate view of relational data You'll find plenty of examples if you Google "pivot table". "Paul" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I'm looking for a way to return rows as columns. My problem is the > following. I have three tables: > suppliers,products and productprices. A supplier and a product make a > productprice. A certain product may have an x number of suppliers and > prices. > > Now I have the following query: > select > products.int_artnr,products.str_name,suppliers.str_naam,productprices.flt_price > from products,productprices,suppliers > WHERE products.int_artnr = productprices.int_artnr > AND suppliers.int_crednr=productprices.int_crednr > ORDER BY int_artnr > > This gives me a result but not the one I wanted. I want to have a > dataset that returns the supplierprice and suppliername as a column > behind every product for all suppliers. So you get an x number of > columns. > > Example: > > Number Name Suppl1 Price1 Suppl2 Price2 > > 1 Beer 10 cases Heineken 3.33 Amstel 1.55 > 2 Beer 2 cases Heikenen 1.22 > > > Could someone please give me some pointers if this is achievable and if > yes, how I should do this? > > Thx, > > Paul > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF table; language 'sql'
I think its cause you changed your procedure from being written in SQL to being writtern in PLPGSQL in your second implementation Sets of records are returned from a PLPGSQL function with a RETURN statement ... not a SELECT... Check out the sections of the manual that talk about PLPGSQL 35.7.1. Returning From a Function There are two commands available that allow you to return data from a function: RETURN and RETURN NEXT. 35.7.1.1. RETURN RETURN expression;RETURN with an expression terminates the function and returns the value of expression to the caller. This form is to be used for PL/pgSQL functions that do not return a set. When returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as described for assignments. To return a composite (row) value, you must write a record or row variable as the expression. The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. If you have declared the function to return void, a RETURN statement must still be provided; but in this case the expression following RETURN is optional and will be ignored if present. 35.7.1.2. RETURN NEXT RETURN NEXT expression;When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified in RETURN NEXT commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; in the latter case, an entire "table" of results will be returned. "Mario Splivalo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > When I issue something like this: > > SELECT * FROM ads WHERE id=1004; > > i get: > > id | vpn_id | service_id | ignore_length | start_time | > end_time| ad_text > --+++---+++ > 1004 | 1 |106 | f | 2005-01-01 00:00:00+01 | > 2005-12-31 00:00:00+01 | Probna reklama numera una! > > > Now, I create a function that does that: > > CREATE FUNCTION get_ads(int4) > RETURNS SETOF ads > AS > 'SELECT * FROM ads WHERE id=$1' > LANGUAGE 'sql' > > When I do: > > SELECT * FROM get_ads(1004); > > i get: > > ERROR: query-specified return row and actual function return row do not > match > > Why is that? > > Mike > > P.S. That's run on Postgres 7.4. > -- > Mario Splivalo > Mob-Art > [EMAIL PROTECTED] > > "I can do it quick, I can do it cheap, I can do it well. Pick any two." > > > > ---(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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] idea for a geographically distributed database: how best to implement?
Sounds like a mis-guided approach to me... You might want to consider using latitude and longitude or zip codes or taking more of a traditional "GIS" approach rather than duplicating data across redundant databases. Another issue is that you end up having to query every database to find proximity... Suppose you have 500 "cells" ? You now have to invoke some sort of RPC mechanism on 499 other RDBMS, manage 500 user names and passwords, retrieve potentially 500 recordsets, merge the data, etc... Your problems will continue to get more and more complex... You are better off with a single RDBMS and a single schema... "Andy Ballingall" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello, > > I've got a database for a website which is a variant of the 'show stuff > near > to me' sort of thing. > > Rather than host this database on a single server, I have a scheme in mind > to break the database up geographically so that each one can run > comfortably > on a small server, but I'm not sure about the best way of implementing it. > > Here's the scheme: > > > Imagine that the country is split into an array of square cells. > Each cell contains a database that stores information about people who > live > in the area covered by the cell. > > There's one problem with this scheme. What happens if you live near the > edge > of a cell? > > My solution is that any inserted data which lies near to the edge of cell > A > is *also* inserted in the database of the relevant neighbouring cell - > let's > say cell B. > > Thus, if someone lives in cell B, but close to the border with cell A, > they'll see the data that is geographically close to > them, even if it lies in cell A. > > > > Is this a common pattern? > > I could, of course, simply find every insert, update and delete in the > application and alter the code to explicitly update all the relevant > databases, but is there a more elegant way of simply saying: "Do this > transaction on both Database A and Database B" monotonically? > > I've had a look at some replication solutions, but they all seem to > involve > replicating an entire database. The advantage of my scheme is that if I > can > distribute my application over large numbers of small servers, I'll end up > with more bangs for the buck, and it'll be much easier to manage growth by > managing the number of servers, and number of cells hosted on each server. > > Thanks for any suggestions! > Andy Ballingall > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] timestamp SQL question
WHERE event_type < NOW() - interval '30 days'; "Brian Doyle" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >I have a table like: > > events > visitor_uid varchar(32) > event_type varchar(32) > event_type timestamp > > I would like to select events from the table that are older than 30 days > from right now. How would I do that in one query? Thanks. > > > > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to implement Microsoft Access boolean (YESNO) fieldtype in PostgreSQL ?
SELECT fieldlist, CASE WHEN myboolean = true THEN 1 ELSE 0 END FROM tblname WHERE condition; ""Jesper K. Pedersen"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > I am have some difficulties converting the Microsoft field of type > YESNO (which is a simple boolean true/false) to something that is > compatible with PostgreSQL. > > I have tried with both boolean, bit(1) and even integer to try and get > it to work but with no success - Access see the boolean and bit(1) as > "text" fields. > > This mean that I am not able to use the built-in form input fields for > the "YESNO" field type. > > Anyone with any ideas on how to get around this? > > Thank's > Jesper K. Pedersen > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] executing dynamic commands
Talk about obfuscated Are you trying to retrieve the table structure /
schema from the PG System Catalogs ?
If so -- you are better off using a VIEW instead of a manual procedure
because it will automatically kepp up with the current schema definition...
Try this:
-- DROP VIEW sys_table_schemas;
CREATE OR REPLACE VIEW sys_table_schemas AS
SELECT pc.oid AS tbl_oid, pc.relname::character varying AS table_name,
pa.attname::character varying AS column_name, pt.typname AS data_type,
CASE
WHEN substr(pt.typname::text, 1, 3)::name = 'int'::name THEN
'integer'::name
WHEN pt.typname = 'bool'::name THEN 'boolean'::name
ELSE pt.typname
END AS udt_name, pa.attnum AS ordinal_position, 254 AS str_length,
CASE
WHEN pa.attnotnull THEN false
ELSE true
END AS nulls_allowed,
CASE
WHEN substr(pa.attname::text, 1, 3) = 'lu_'::text THEN true
ELSE false
END AS lookup,
CASE
WHEN pd.description::character varying IS NOT NULL THEN
pd.description::character varying
WHEN pa.attname IS NOT NULL THEN pa.attname::character varying
ELSE NULL::character varying
END AS label
FROM ONLY pg_class pc
JOIN ONLY pg_attribute pa ON pc.oid = pa.attrelid AND pc.relnamespace =
2200::oid AND pc.reltype > 0::oid AND (pc.relkind = 'r'::"char" OR
pc.relkind = 'v'::"char")
JOIN ONLY pg_type pt ON pa.atttypid = pt.oid
LEFT JOIN ONLY pg_description pd ON pc.oid = pd.objoid AND pa.attnum =
pd.objsubid
WHERE pa.attnum > 0
ORDER BY pc.relname::character varying, pa.attnum;
ALTER TABLE sys_table_schemas OWNER TO "public";
SELECT * FROM sys_table_schemas;
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy
> rows from one table into another table with the same column definition.
> My first approach was to use something like:
>
> query_value := 'INSERT INTO ' || tabledest || ' SELECT * FROM ' ||
> tablesrc;
> EXECUTE query_value;
>
> This only works if the column definition AND the order between source and
> destination is the same !
> In my case I have always the same column definitions but they are not in
> the same order between source and destination table.
> What I tryed then is to loop through the column definition of the source
> and query the sourcetable for the value. For that I have to execut a query
> with dynamic tablename and dynamic columname to generate two stings one
> with the columndefinitin and one with the columnvalues to exececute
> something like: INSERT INTO tabelfoo (columndefinitinstring) VALUES
> (columnvaluesstring)
>
> see snip of function:
>
> fieldvalues RECORD;
> output RECORD;
> insertvalues VARCHAR;
> fieldname VARCHAR;
>
>
> -- Get Attribute List from Table and write it to output
> -- Read Values of Fieldname from source
> query_value := 'select * from ' || tablesrc ;
>
> FOR fieldvalues IN EXECUTE query_value LOOP
>
> FOR output IN SELECT a.attnum,
>a.attname AS field,
>FROM
> pg_class c, pg_attribute a, pg_type t
>WHERE
> c.relname = tablesrc AND
> a.attnum > 0 AND
> a.attrelid = c.oid AND
> a.atttypid = t.oid
>ORDER BY a.attnum LOOP
>
> -- Read Field Name from Out Table
> fieldname := output.field;
>
> -- Write Field Name into Variable
> IF insertcolumns IS NULL THEN
> insertcolumns := fieldname;
> ELSE
> insertcolumns := insertcolumns || ',' || fieldname;
> END IF;
>
> Until here everyting is fine ... but now I try to query the value from
> RECORD fieldvalues with the columname fieldname variable from the inner
> loop !
> I tryed the following ...
>
> query_value := 'select quote_ident(' || fieldvalues || ').quote_literal('
> || fieldname ||')';
>
> EXECUTE query_value;
>
>
> and I get the following error message ...
>
> ERROR: could not find array type for data type record
> CONTEXT: SQL statement "SELECT 'select quote_ident(' || $1 ||
> ').quote_literal(' || $2 ||')'"
> PL/pgSQL function "prx_db__appendtable" line 87 at assignment
>
>
> END LOOP;
>
>END LOOP;
>
> I know the function is not runnable, but my question is how can I
> dynamically combine "fieldvalues"."fieldname" to read the values column by
> colum out if a RECORD variable to generate the "columnvaluesstring"
> mentioned above ?!
> Maybe this approach is to complicated and there is a quick and easy
> solution ?!
>
> Any help is very much appreciated !!
>
> Thanx a lot & Regards
>
> Chris
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
---(end of broadcast)---
TIP 4: Have you searched our list archiv
Re: [SQL] executing dynamic commands
In your function why not create a temporary table then use that for your
processing ?
CREATE TEMPRORARY TABLE tabledest AS (SELECT * FROM tblsrc WHERE condition);
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy
> rows from one table into another table with the same column definition.
> My first approach was to use something like:
>
> query_value := 'INSERT INTO ' || tabledest || ' SELECT * FROM ' ||
> tablesrc;
> EXECUTE query_value;
>
> This only works if the column definition AND the order between source and
> destination is the same !
> In my case I have always the same column definitions but they are not in
> the same order between source and destination table.
> What I tryed then is to loop through the column definition of the source
> and query the sourcetable for the value. For that I have to execut a query
> with dynamic tablename and dynamic columname to generate two stings one
> with the columndefinitin and one with the columnvalues to exececute
> something like: INSERT INTO tabelfoo (columndefinitinstring) VALUES
> (columnvaluesstring)
>
> see snip of function:
>
> fieldvalues RECORD;
> output RECORD;
> insertvalues VARCHAR;
> fieldname VARCHAR;
>
>
> -- Get Attribute List from Table and write it to output
> -- Read Values of Fieldname from source
> query_value := 'select * from ' || tablesrc ;
>
> FOR fieldvalues IN EXECUTE query_value LOOP
>
> FOR output IN SELECT a.attnum,
>a.attname AS field,
>FROM
> pg_class c, pg_attribute a, pg_type t
>WHERE
> c.relname = tablesrc AND
> a.attnum > 0 AND
> a.attrelid = c.oid AND
> a.atttypid = t.oid
>ORDER BY a.attnum LOOP
>
> -- Read Field Name from Out Table
> fieldname := output.field;
>
> -- Write Field Name into Variable
> IF insertcolumns IS NULL THEN
> insertcolumns := fieldname;
> ELSE
> insertcolumns := insertcolumns || ',' || fieldname;
> END IF;
>
> Until here everyting is fine ... but now I try to query the value from
> RECORD fieldvalues with the columname fieldname variable from the inner
> loop !
> I tryed the following ...
>
> query_value := 'select quote_ident(' || fieldvalues || ').quote_literal('
> || fieldname ||')';
>
> EXECUTE query_value;
>
>
> and I get the following error message ...
>
> ERROR: could not find array type for data type record
> CONTEXT: SQL statement "SELECT 'select quote_ident(' || $1 ||
> ').quote_literal(' || $2 ||')'"
> PL/pgSQL function "prx_db__appendtable" line 87 at assignment
>
>
> END LOOP;
>
>END LOOP;
>
> I know the function is not runnable, but my question is how can I
> dynamically combine "fieldvalues"."fieldname" to read the values column by
> colum out if a RECORD variable to generate the "columnvaluesstring"
> mentioned above ?!
> Maybe this approach is to complicated and there is a quick and easy
> solution ?!
>
> Any help is very much appreciated !!
>
> Thanx a lot & Regards
>
> Chris
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Returning a set from an function
Keith: This is the general approach I use over and over and over -- This is a PLPGSQL function that returns a SETOF tablename%ROWTYPE If you need the full schema and table and everything that goes with this -- let me know --- CREATE OR REPLACE FUNCTION sys_aclsubmenu(int4) RETURNS SETOF sys_tree_components AS $BODY$ DECLARE MNU ALIAS FOR $1; DECLARE OUT sys_tree_components%ROWTYPE; DECLARE CHILD sys_tree_components%ROWTYPE; BEGIN RAISE NOTICE 'sys_aclsubmenu(integer, varchar) called for item: %', $1; FOR OUT IN SELECT * FROM sys_tree_components WHERE parent = $1 AND active_flag LOOP IF (OUT.id != OUT.parent) THEN FOR CHILD IN SELECT * FROM sys_aclsubmenu(OUT.id) LOOP RETURN NEXT CHILD; END LOOP; END IF; RETURN NEXT OUT; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM sys_aclsubmenu(1) ORDER BY parent, id ; ""Keith Hutchison"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > G'day, > > Looking for an example showing how to return a set from either a sql > function or a plpsqq function. > > Thanks > > -- > Keith Hutchison > http://balance-infosystems.com http://realopen.org > http://www.kasamba.com/Keith-Hutchison > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table design question
I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my PKEY's -- as an absolute rule -- I guess I am a purist... Everything else (the other columns) can have unique constraints, etcetera and be FOREIGN KEYS, etc... Try INSERTING your 100 character "natural" key into a table with 10M++ rows only to find out there there is already a duplicate talk about a performance hit or SELECT -- you end up using way too much RAM and bandwidth -- unecessarily... IMHO: You ought to use a numeric, auto-generated sequence (SERIAL) for you PKEY's ... ""David Clarke"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm reading Joe Celko's book SQL Programming Style for the second time > and although I've been an OO developer for quite a few years I'm > fairly green wrt SQL. Joe is obviously something of a curmudgeon and I > would fall squarely into his newbie OO developer ordinal scale and I'm > trying to avoid the slide into stupid newbie OO developer. > > So I'm designing a table and I'm looking for an appropriate key. The > natural key is a string from a few characters up to a maximum of > perhaps 100. Joe gets quite fierce about avoiding the use of a serial > id column as a key. The string is unique in the table and fits the > criteria for a key. So should I follow Joe's advice and use my natural > key as the primary key? It sounds reasonable but it will mean at least > one other table will have the string as a foreign key. My postgres > intro book has id columns all over the place but is it really that big > an issue these days to have a 100 character primary key? Are there > postgres-specific implications for either approach? > > Thanks > Dave > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Advanced Query
Personally: I think your posts are getting annoying. This isn't SQLCentral. Learn to write your own damn queries or even better - buy a book on SQL... <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > hi all, i posted this problem on the novice thread, > but it makes much more sense to post it here, instead. > sorry fo rthe double posting, i'll be sure to post > advanced SQL questions here in the future. > > i have the following two tables (trimmed down for > simplicity's sake): > > t_inspect > id, inspect_timestamp > > t_inspect_result > id, inspect_id, inspect_pass > > yes, i need both tables, although it might not be > obvious since i trimmed down the columns in this > simple example. > > inspect_pass (bool): pass = true, fail = false > > let's say i have the following values: > > t_inspect > 1, 2006-05-31... > 2, 2006-06-01... > > t_inspect_result > 1, 1, true > 2, 2, false > 3, 2, false > 4, 2, false > 5, 2, true > > iow, the first inspection passes the first time, the > second inspection (t_inspect.id = 2) had to be > inspected 4 times before it pass inspection. you can > assume it was reworked inbetween inspections and more > defects were found upon reinspection. > > i'm trying to develop a query that will provide the > first pass yield. iow, the yield generated by > counting *only* the results associated with the first > time a unit is inspected for a given inspect.id. > > t_inspect_result > 1, 1, *true* -- first inspect for t_inspect.id = 1 > 2, 2, *false* -- first inspect for t_inspect.id = 2 > 3, 2, false > 4, 2, false > 5, 2, true > > specifically, this case would yield 50% (1 pass / 2 > total) since the first inspection passed the first > time and the second inspection failed the first time. > > i think i can get the first pass results through a > given inspection by using "distinct on > (t_inspect.id)..." i say think b/c the actual query > is quite complex and i'm not 100% sure my results are > consistent with what i'm expecting. > > i think i can get the results of the entire > t_inspect_result table using the count function - get > #passes, get #total and do some math. > > what i can't seem to do is to get both - a count of > the total number of t_inspect_result.inspect_pass > where the value is true and a total count, by unique > t_inspect.id. > > any guidance would be much appreciated. > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(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: [SQL] Advanced Query
I would hope that your choice to use postgreSQL is because it is superior technology that scales well financially... not because you get a warm fuzzy from all your friends on the mailing lists... "Oisin Glynn" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Richard Broersma Jr wrote: >>> Personally: I think your posts are getting annoying. This isn't >>> SQLCentral. Learn to write your own damn queries or even better - buy a >>> book on SQL... >>> >> >> Personally: (being a newbie with an interest in developing a strong rdms >> skillset) I've enjoyed >> following threads like these. Even when the questions (to some) seems >> overly simplistic, the >> courteous respondents often share insightful solutions or nuances that >> are not found in an "off >> the self" SQL book. >> >> However, if questions like these are *really* off-topic for the pgsql-sql >> I would be interested in >> knowing what kind of threads are acceptable and on-topic for this list. >> >> Also, if there are other mailing lists (pg or other) that are better >> suited for threads like this, >> I would appreciate learning of them. >> >> Regards, >> >> Richard Broersma >> >> ---(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 >> > I would like to say that I have found the level of patience and help > offered by the members of the mailing lists to be a key factor in my > choice to use Postgres. And I feel that the response above would have been > the sort of thing that would have turned me off., even if it was not in > response to one of my own posts. Sometimes I may not understand the > questions being asked or the answers being given but it sure is great to > be able to search and find them later when you are the one hitting that > same wall. If someone on a list like this rubs you the wrong way, > personally I would ignore them, hence I expect to get zero responses to > this! ;) > > Just my 2c this is a great resource and I would hope all feel as welcome > here as I have, > > Oisin > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Can't find which return type is incorrect.
Can you show us the code for your SP ? I'd like to see what the RETURNS statement is in the sp declaration (CREATE OR REPLACE PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS ...) You might reconsider your SELECT * FROM sproc() AS () -- SELECT * retrieves ALL columns defined by the SP What happens when you drop the "AS (columns)" portion from your select ??? In other words -- what do you get when you simply "SELECT * FROM sp_whatever(1, 0, 3)" ??? ""Chris Lukenbill"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...Alright. I have a very large amount of columns being returned by this stored procedure that I ported from MS SQL to Postgres. Now the problem I'm having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, etc.) is executed the error "wrong record type supplied in RETURN NEXT CONTEXT". Now this immediately red flagged me to look at the types that I was returning and verify that those were the types that I was catching in the as statement. I went through to verify all of the variables and they are all correct as far as both name and the exact type. Therefore there are only a few things left that I'm thinking could be the problem. 1. Too many variables returned (there are 44 variables being returned).2. Some of the variables that are smallint in the select statement also do a if isnull type of logic that will return zero if they are null. (is that zero not coming back as a smallint then?) 3. What I'm declaring as a variable type in postgresql isn't the variable type in PHP. The following are the different types of variables that I use:INTSMALLINTBIGINT (when I do a count(*))VARCHAR(xx) TEXTTIMESTAMPNUMERIC(19,2)Now the two there that I'm skeptical about are the timestamp and the numeric.Thanks ahead of time for any ideas,Chris
Re: [SQL] to get DD-MM-YYYY format of data
17.10.2. Locale and Formatting DateStyle (string) Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD). These can be set separately or together. The keywords Euro and European are synonyms for DMY; the keywords US, NonEuro, and NonEuropean are synonyms for MDY. See Section 8.5, Date/Time Types for more information. The default is ISO, MDY. ""Penchalaiah P."" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi .. I am using date is data type to one of the field in my table .but when ever I am passing values to that field it is taking yyy-mm-dd format.. But I dont want like that .. I need like this DD-MM-.. for this wt I have to do Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] Subquery alternatives?
I dont think you need the double-left join SELECT * FROM STORIES ST LEFT JOIN TAGS TG ON TG.tagkey = ST.storykey WHERE TG.tag = "science" "MRKisThatKid" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, i've posted this in various places but I'm really struggling to > find an answer that works for me... > > Say I've got a table of stories and a table of tags which apply to the > stories. > > Say you've been using the tags science and unfinished, and you want all > the stories which are science based and are completed.. that is, all > the stories tagged with "science" but not "unfinished". > > I could do this using sub-queries quite easily, especially the exists / > not exists function in mysql. > > Unfortunately I'm implementing this kind of query into a web > application i'm developing and I want it to be usable on as many > different server set-ups as possible. > > select * from stories > left join tags as tags1 on (tags1.tagkey = storykey) > left join tags as tags2 on (tags2.tagkey = storykey) > where tags1.tag = "science" > and not tags2.tag = "unfinished" > > It would be wonderful if the above worked, but of course it doesn't > because when the db engine makes all the possible combinations it will > always find a match where tags2 doesn't contain "unfinished". > > Can anyone think of a way of achieving this without sub-queries? > > ... The best solution I've been offered so far is to use group_concat > and find_in_set, but these are mysql specific functions and are not > portable. Do any of you guys know who I should go about this? > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Query to return schema/table/columname/columntype
You mean like this: CREATE OR REPLACE VIEW sys_tabledef AS SELECT columns.table_catalog, columns.table_schema, columns.table_name, columns.column_name, columns.ordinal_position, columns.column_default, columns.is_nullable, columns.data_type, columns.character_maximum_length, columns.character_octet_length, columns.numeric_precision, columns.numeric_precision_radix, columns.numeric_scale, columns.datetime_precision, columns.interval_type, columns.interval_precision, columns.character_set_catalog, columns.character_set_schema, columns.character_set_name, columns.collation_catalog, columns.collation_schema, columns.collation_name, columns.domain_catalog, columns.domain_schema, columns.domain_name, columns.udt_catalog, columns.udt_schema, columns.udt_name, columns.scope_catalog, columns.scope_schema, columns.scope_name, columns.maximum_cardinality, columns.dtd_identifier, columns.is_self_referencing FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text ORDER BY columns.table_name, columns.ordinal_position; <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi all, > > I am trying to modify the dabo (a python wxpython > ide for database forms creation) code to allow the > selection of tables in any schema. I need a query > that will return records with schema, table, > columname and columne type. > > For background I am selecting table & schema by > the query: > SELECT schemaname || '.' || tablename AS tablename > FROM pg_tables ORDER BY tablename; > > Then I need to obtain the columns within that > table/schema. The original query was: > select c.oid,a.attname, t.typname > from pg_class c inner join pg_attribute a on > a.attrelid = c.oid inner join pg_type t on > a.atttypid = t.oid where c.relname = 'thetablename > and a.attnum > 0; > > Now my problem is how to create a new column to > replace c.relname so I can query on > "theschemaname.thetablename". > > Any suggestions will be welcomed. Thanks, > > Phil > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] select based on multi-column primary keys
--
AFAIK: You cannot have multiple primary keys. How would you know which one
is the actual key ?
FYI: What you are really talking about are table contraints... When you have
multiple unique column constraints -- they are generally referred to as
"table constraints" not multiple primary keys... Unique nmulti-column table
constraints generally use an internal set of rules and triggers and an
index.
My advice would be to alter your table structure so that you have a "real"
PK not table constraints -- that would make it searchable
There's this nifty little thing called a "sequence"... they make great
PK's BTW: If you implement a true PK... you can still retain your
UNIQUE(col1, col2, col3, ...) table constraints.
Regards,
Gregory P. Patnude
Vice President - Applications & Innovations Group
iDynaTECH, Inc
120 North Pine Street
STC - Suite 162
Spokane, WA 99202
(509) 343-3104 [voice]
http://www.idynatech.com
"mawrya" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I have set up a table with a multi-column primary key constraint:
>
> CREATE TABLE iopoints
> (
> enclosureid numeric(3) NOT NULL,
> pointid char(4) NOT NULL,
> equipmentgroup varchar(64) NOT NULL,
> deviceid varchar(8),
> devicetype varchar(24),
> operationdesc varchar(64) NOT NULL,
> entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
> time zone,
> systemid numeric(3) NOT NULL,
> CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
> )
> WITHOUT OIDS;
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND
> pointid=56
>
> I have searched high and low but can not find a syntax example of how to
> select based on a multi-column primary key, any tips?
>
> Thanks,
>
> mawrya
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] select based on multi-column primary keys
No... its a table constraint using a unique, implicit index on the listed columns. . PRIMARY KEY ( column [, ...] ) The PRIMARY KEY table constraint is similar to the PRIMARY KEY column constraint. As a table constraint, PRIMARY KEY allows multiple columns to be defined in a parenthetical expression, separated by commas. An implicit index will be created across columns. The combination of values for each column specified must therefore amount to only unique and non-NULL values, as with the PRIMARY KEY column constraint. hehehe "Andrew Sullivan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote: >> AFAIK: You cannot have multiple primary keys. How would you know which >> one >> is the actual key ? > > You can have a multi-column primary key, though. That's a perfectly > legitimate approach. > >> FYI: What you are really talking about are table contraints... When you >> have > > No, it's a multi-column primary key. > >> My advice would be to alter your table structure so that you have a >> "real" >> PK not table constraints -- that would make it searchable > > This is already searchable. What you are talking about is not a real > primary key, but an artificial one. The OP already has a real > primary key. SQL purists think artificial primary keys mean that you > haven't done enough normalisation. I'm going to remain silent on > that topic, though, so that we don't get a Thread That Does Not End > :) > > A > > > -- > Andrew Sullivan | [EMAIL PROTECTED] > If they don't do anything, we don't need their acronym. > --Josh Hamilton, on the US FEMA > > ---(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 > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] LEFT Join Question
Fisrt -- you probably want to start by doing fully qualified JOINS and then you want to allow joins with nulls on the columns that are allowed to be empty: I am doing this sort of off the top of my head ... but the thing you need to do generally is to COMPLETELY QUALIFY all of your joins and then use the "OR field IS NULL" trick. That should solve your problem. SELECT A.account_id, A.account_username, V.vendor_status,CN.name, CA.address,CE.email, CP.phone FROM account A LEFT JOIN contact_phone CP ON (CP.account_id = A.account_id OR CP.account_id IS NULL) LEFT JOIN contact_address CA ON (CA.account_id = A.account_id OR CA.account_id IS NULL), JOIN vendor V ON (V.account_id = A.account_id), JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_id IS NULL), JOIN contact_name CN ON (CN.account_id = A.account_id), JOIN domain_type DT ON (CE.domain_type_id = DT.domain_type_id ), JOIN account_type AT ON (AT.account_type_id = A..account_type_id) HAVING A.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' ""Rob V"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hello all, Ive been racking my brain for a few hours now and need some help, please!!! I have the following tables : account =account_id =account_type_id =account_username vendor =account_id =vendor_status contact_name =account_id =name =domain_type_id contact_address =account_id =address =domain_type_id contact_email =account_id =email =domain_type_id contact_phone =account_id =phone =domain_type_id account_type =account_type_id = account_type_tag records : 1 VENDOR 2 SELLER 3 CONTRACTOR domain_type =domain_type_id =domain_type_tag records : 1 PRIMARY 2 SECONDARY Im looking for a select that will give me all records where the account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY" even if the address, email and phone are blank. Users WILL HAVE records in the following tables : account, vendor contact_name contact_email they MAY or MAYNOT have records in the following tables : contact_address contact_phone I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. Im basically looking for this : account_username vendor_status name address email phone - Rob123 ACTIVE ROB 123 Test Drive[EMAIL PROTECTED]555-1212 BILL123 ACTIVE Bill NULL [EMAIL PROTECTED] 456- Steve1234 INACTIVE Steve 654 Hill St [EMAIL PROTECTED] NULL I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. (do I still need the = in the where clause when using a left join?) SELECT account.account_id, account.account_username, vendor.vendor_status, contact_name.name, contact_address.address, contact_email.email, contact_phone.phone FROM account a LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id) LEFT JOIN contact_address on (contact_address.account_id = a.account_id), vendor, contact_email, contact_name, domain_type, account_type, WHERE vendor.vendor_id = account.account_id AND contact_email.account_id = account.account_id AND contact_name.account_id = account.account_id AND account.account_type_id = account_type.account_type_id AND contact_email.domain_type_id = domain_type.domain_type_id AND contact_name.domain_type_id = domain_type.domain_type_id AND vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND account_type.account_type_tag = 'ACCOUNT_VENDOR' AND domain_type.domain_type_tag = 'VENDOR_PRIMARY' The problem Im having is b/c Im only looking for specific domain types I have a join on the contact address and phone tables where the domain types match - but if there are no records - it causes the entire record not to be show - so I need to do a left join on that table as well but If I try to do it - I get an error " table name "contact_phone" specified more than once" Any help would be great!!! Thanks -Rob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?
How about using a trigger to call a stored procedure ? [ON INSERT to user_item_history DO ...] and have your stored procedure count the records for that user and delete the oldest record if necessary... IF (SELECT COUNT(*) WHERE user_id = NEW.user_id) >= 50 THEN -- DELETE THE OLDEST RECORD END IF; RETURN NEW.* ""Jamie Tufnell"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I have a table that stores per-user histories of recently viewed items > and I'd like to limit the amount of history items to <= 50 per user. > I'm considering doing this with a query run from cron every so often > but I'm not happy with what I've come up with so far, and since it's a > quite active table I thought I'd ask here to see if there's a more > efficient way. > > Right now the table structure is as follows... > > user_item_history: id (PK), user_id (FK), item_id (FK), timestamp > > For user_ids that have more than 50 rows, I want to keep the most > recent 50 and delete the rest. > > The most obvious way of doing this for me is: > > -- > -- Get the user_ids with 50 or more history entries like this > -- > SELECT user_id, count(*) > FROM user_scene_history > GROUP BY user_id > HAVING count(*) > 50; > > -- > -- Then iterate the ids above (_user_id) > -- > DELETE FROM user_scene_history > WHERE user_id = _user_id AND id NOT IN ( >SELECT id FROM user_scene_history >WHERE user_id = _user_id >ORDER BY timestamp DESC >LIMIT 50); > > I've left out the simple logic tying the above two queries together > for clarity.. > > I haven't actually tested this but while I assume it would work I > imagine there is a neater and possibly more efficient way of attacking > this. I'm also open to different approaches of limiting the user's > history too ... perhaps with table constraints so they can simply > never exceed 50 entries? But I'm not sure how to do this.. > > Any help would be greatly appreciated.. > > Thanks, > Jamie > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Jamie: I think you are probably having slowdown issues in your "DELETE FROM WHERE NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit convoluted to me NOT IN is what is probably slowing you down the most ALSO: It looks to me like you have a column named "timestamp' ??? This is bad practice since "timestamp" is a reserved word... You really ought NOT to use reserved words for column names... different debate. Why bother deleting records anyway ? Why not alter your query that tracks the 50 records to LIMIT 50 ??? ""Jamie Tufnell"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi codeWarrior, > > codeWarrior wrote: >> > For user_ids that have more than 50 rows, I want to keep the most >> > recent 50 and delete the rest. >> How about using a trigger to call a stored procedure ? [ON INSERT to >> user_item_history DO ...] > > [snip] > > Thanks for your input! I've implemented this but I'm concerned about > performance. As I mentioned it's frequently being added to and this > function will be called maybe a couple of times a second. In my brief > initial tests it seems like this is running quite slowly... > > Just to make sure I haven't done anything obviously wrong, I've > included my implementation below.. if you could look over it I'd > really appreciate it. > > CREATE OR REPLACE FUNCTION user_item_history_limit() RETURNS TRIGGER >AS $_$ > DECLARE >threshold integer = 50; >numrows integer; > BEGIN >SELECT INTO numrows count(*) FROM user_item_history WHERE user_id > = new.user_id; >IF numrows > threshold THEN >DELETE FROM user_item_history WHERE user_id = new.user_id AND > id NOT IN ( >SELECT id FROM user_item_history >WHERE user_id = new.user_id >ORDER BY timestamp DESC LIMIT threshold); >RAISE NOTICE '% rows exceeds threshold of % for user_id %; > trimming..', numrows, threshold, new.user_id; >END IF; >RETURN new; > END; > $_$ >LANGUAGE plpgsql; > > CREATE TRIGGER user_item_history_limit AFTER INSERT ON user_item_history >FOR EACH ROW EXECUTE PROCEDURE user_item_history_limit(); > > Any suggestions greatly appreciated! > > Thanks again, > Jamie > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
