[SQL] Re: Auto incrementing an integer
Take a look at the SERIAL datatype as well as sequences (CREATE SEQUENCE, NEXTVAL, CURRVAL). good luck! On Mon, 14 May 2001, Sylte wrote: > How do I construct a datatype that autoincrement in postgreSQL? > > Thanks > Sylte > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Bit Mapping operation
In 7.1 it's there... using the |(or) &(and), etc.. operators. In 7.0.3 it's not although can be added easily. example: test=> select 2 | 1; ?column? -- 3 (1 row) test=> select 2 & 1; ?column? -- 0 (1 row) test=> select 3 & 1; ?column? -- 1 (1 row) test=> On Mon, 11 Jun 2001, Najm Hashmi wrote: > Hi all, I am just wondering if bit map operations are possible in > pl/pgsql. We are storing sixteen different (one or many ... i.e. check > boxes ) choices in a small int. For data cruchinging reasons, we need > to reverse the process to find out what what fields where selected or > checked. Is this info somewhere in the docs? > Thanks in advance. > Regards, > > -- > Najm Hashmi > Tel:514-271-9791 > www.mondo-live.com > www.flipr.com > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: drop table if exists
Just drop the table using "DROP TABLE mytable;" and ignore the error... I'm sure there are fancy ways of doing it by accessing system tables, but the above works for me. On Tue, 3 Jul 2001, Jason Watkins wrote: > How can I duplicate the behavior of: > > DROP TABLE IF EXISTS mytable; > > CREATE TABLE mytable ( > blah, > blah > ); > > INSERT INTO mytable > (blah) > VALUES > (blah); > > in other words, so that I have a single sql file that restores the database > to a known state. > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL downloads
Hi - http://www.us.postgresql.org/sites.html has a whole list of them... On Mon, 1 Oct 2001, [iso-8859-1] Miguel González wrote: > Does anyone where there is a web site or ftp site where i can download the > latest release of PostgreSQL? I have been trying to use the > ftp://ftp.postgresql.org but it doesnt work properly. > > Many thanks in advance > > Miguel > > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Aggregate binary AND
This worked for us in 7.something (don't need it anymore, but it should still work). You'll want to create one for INT8, INT2, etc.. if you are going to use those as well... CREATE AGGREGATE aggr_bitand ( BASETYPE = INT4, SFUNC1 = int4and, STYPE1 = INT4); On Thu, 1 Nov 2001, James Orr wrote: > Hi, > > Is there an aggregate binary AND function in postgres? If not, is there a > way to write your own aggregate functions? Examples? > > - James > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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
Re: [SQL] Holiday Calculations?
Not that this is the best solution, but I read in a book (SQL for smarties I think) the following (maybe I'm wrong, but this is what I remember :) Create a holidays table. Put in all the holidays. Do the math yourself and just put them in there manually for the next 10 years or so (and then remind yourself to do it again in 10 years :) Then just look them up in that table. The example I was reading about was how to determine "the next 3rd business day". his advice was to put all the weekends and holidays in that table and then it made the queries easy. Something like that anyway :) Check out the book for more info. -philip On Tue, 18 Sep 2001, Josh Berkus wrote: > Folks, > > I'm spec'ing a calendar app for PostgreSQL, and was wondering if anyone > had already solved the following problem: > > How can I calculate the dates of American holidays? > > Obviously, Christmas & New Year's are easy. As is July 4. > > However, Thanksgiving is the last Thursday in November, unless the month > ends on a Thursday or Friday, in which case it is the next-to-last. > Memorial Day and Labor Day are simpler, but also use the "First or Last > Monday in x month" idea. > > I was wondering if anyone had already figured out these calculations, in > any language (SQL would be terrific). > > Thanks! > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > ---(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] SQL formatter?
Looks to be windows based, but... http://www.techno-kitten.com/PBL_Peeper/Online_Manual/SQL_Formatter/sql_formatter.html first hit when searching on google for "sql formatter". there were a lot of other options... You might look at how some of those C code indenter's work. Seems like some of them support multiple languages which means they maybe have some sort of "language definition" so maybe you could just write a sql one and it would just work. Of course I've never used one and don't know anything about it really so I could be wrong :) -philip On Wed, 25 Sep 2002, Andrew Perrin wrote: > Does anyone know of a routine for formatting SQL statements in a > structured way? Standalone or for emacs would be fine. I'm thinking of > something that could take a long SQL text statement and format it, e.g.: > > select foo from bar where baz and bop and not boo; > > becomes > > SELECT foo > FROM bar > WHERE baz >AND bop >AND NOT boo > ; > > Thanks, > Andy > > -- > Andrew J Perrin - http://www.unc.edu/~aperrin > Assistant Professor of Sociology, U of North Carolina, Chapel Hill > [EMAIL PROTECTED] * andrew_perrin (at) unc.edu > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pg_dump without data
how do i get a dump of a postgresql database without the data? % pg_dump --help pg_dump dumps a database as a text file or to other formats. Usage: pg_dump [OPTION]... [DBNAME] . -s, --schema-onlydump only the schema, no data -philip ---(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
Re: [SQL] default value for select?
I want to update a column in myTable. The value this column is set to depends on a nested select statement which sometimes returns 0 rows instead of 1. This is a problem since the column I'm trying to update is set to refuse nulls. Here's a sample: update myTable set myColumn = (Select altColumn from altTable where altColumn != 'XXX' limit 1) where myColumn = 'XXX'; MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns 0 rows, and thus, the query fails. Is there a way to set a default value to be inserted into myColumn if and when "select altColumn ..." returns zero rows? COALESCE(value [, ...]) The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display, for example: SELECT COALESCE(description, short_description, '(none)') ... Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] insert only if conditions are met?
On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote: Ok. Here's TABLE A empdate hours type JSMITH 08-15-2005 5 WORK JSMITH 08-15-2005 3 WORK JSMITH 08-25-2005 6 WORK I want to insert the ff: 1.) JSMITH08-15-20058VAC 2.) DOE08-16-20058VAC #1 should fail because there is already 8 hours entered as being Worked on 08-15-2005 (same date). sorry, did not notice the duplicates before my previous reply. you could do something like insert into A select 'JSMITH','08-15-2005',8,'VAC' where 8 != (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm guessing he'd still want it to fail since adding that 8 hours ov VAC would result in a 15 hour day... so maybe something like? insert into A select 'JSMITH','08-15-2005',8,'VAC' WHERE 8 >= 8 + (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); ? ---(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] insert only if conditions are met?
On Fri, 2 Sep 2005, Henry Ortega wrote: Thanks for all your answers. Very helpful. What if after adding all those hours in one long transaction, I want to send a query to check the MONTHLY TOTAL HOURS (including those just entered) and if they exceed N number of hours, all those records added should *ROLLBACK*? BEGIN; insert.. insert. if sum(hours)>N then ROLLBACK END; Is that possible? Maybe with just plain SQL? (and one transaction) Just add in another where clause using AND and modify the values to sum the hours for the entire month instead of just the day. At least I think that would do it. On 8/31/05, Philip Hallstrom <[EMAIL PROTECTED]> wrote: On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote: Ok. Here's TABLE A emp date hours type JSMITH 08-15-2005 5 WORK JSMITH 08-15-2005 3 WORK JSMITH 08-25-2005 6 WORK I want to insert the ff: 1.) JSMITH 08-15-2005 8 VAC 2.) DOE 08-16-2005 8 VAC #1 should fail because there is already 8 hours entered as being Worked on 08-15-2005 (same date). sorry, did not notice the duplicates before my previous reply. you could do something like insert into A select 'JSMITH','08-15-2005',8,'VAC' where 8 != (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm guessing he'd still want it to fail since adding that 8 hours ov VAC would result in a 15 hour day... so maybe something like? insert into A select 'JSMITH','08-15-2005',8,'VAC' WHERE 8 >= 8 + (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); ? ---(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] column names, types, properties for a table
Is it possible to issue an SQL query that lists column names, types (int, varchar, boolean, etc.), properties (like NOT NULL or UNIQUE) for a given table name ? Start psql with the -E option. Then "\d yourtable". It will print out the queries that are run internally to show you the table info... for example: % psql -E cc_8004 Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit cc_8004=# \d rep_general; * QUERY ** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^rep_general$' ORDER BY 2, 3; ** * QUERY ** SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_catalog.pg_class WHERE oid = '21548032' ** * QUERY ** SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '21548032' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ** * QUERY ** SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = '21548032' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname ** * QUERY ** SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '21548032' ORDER BY inhseqno ASC ** Table "public.rep_general" Column |Type | Modifiers -+-+ id | integer | not null loc_id | integer | not null dt | timestamp without time zone | not null num_active_visits | integer | not null default 0 num_passive_visits | integer | not null default 0 min_visit_length| integer | not null default 0 max_visit_length| integer | not null default 0 total_visit_length | integer | not null default 0 total_time_before_touch | integer | not null default 0 total_time_of_touch | integer | not null default 0 num_coupons_printed | integer | not null default 0 num_passive_promos | integer | not null default 0 num_active_promos | integer | not null default 0 Indexes: "rep_general_pk" primary key, btree (id) "rep_general_dt_idx" btree (dt) "rep_general_loc_id_idx" btree (loc_id) cc_8004=# ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
I need to create some nearly identical copies of rows in a complicated table. Is there a handy syntax that would let me copy a existing row, but get a new primary key for the copy? I'd then go in an edit the 1 or 2 additional columns that differ. The duplicate would be in the same table as the original. This would save me a bunch of typing. Can it be done? INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123; Or something close to that... I suspect if you changed the '*' to the columns you wanted you could also work in the other columns you want to change as well... ---(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] retrieving a serial number
There's probably a way to get that serial number.
I think I remember reading that when you specify it as SERIAL,
postgresql actually creates a separate sequence. If that's the case
then you could do "SELECT currval('mysequence')" -- although I'm not
sure what "mysequence" should be set to.
The other way to do it would be to remove the SERIAL attribute from the
table, making it an INT4 (or whatever) and then create a separate
sequence...so...
SELECT nextval('mysequence');
insert into master... (using the value returned from above)
insert into slave... (using the value returned from above)
insert into slave... (using the value returned from above)
insert into slave... (using the value returned from above)
insert into slave... (using the value returned from above)
would work just fine.
In article <[EMAIL PROTECTED]>,
Lea, Michael <[EMAIL PROTECTED]> wrote:
>I have one table (call it "master") that, among other things, contains a
>serial number. I have a number of other tables (call them "slaves") that,
>among other things, contain a foreign key referring to the serial number in
>the first table. I will be inserting one row into the master table, and one
>row into zero or more slave tables in each transaction.
>What I want to do is use a sequence in the master table to let PostgreSQL
>automatically generate a unique serial number for me, but I need to know the
>serial number in order to insert the corresponding rows into the slave
>table(s). Is there any way of retrieving the serial number that will be used
>before the transaction has been committed? Or will I have to generate my own
>serial numbers?
>Michael Lea
>Information Security
>Manitoba Public Insurance
>Phone: (204) 985-8224
