|
|||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||
Sequences are guaranteed to be unique and in numeric order, but they are not guaranteed to be sequential. Sometimes sequences are cached for the query and if they are not used, they are skipped. Rows could also be deleted in the middle of a sequence. This is all OK according to how sequences should work. Occasionally you may find that you want to know where the holes in your sequences are. This is pretty rare if the sequences are meeting your uniqueness and ordering needs. Of course, if skipped numbers are that important, you may want to use something other than a sequence... The SQL below should be able to find the sequence holes in your table. It is left as an exercise to the reader to write this solution as a plpython function, saving the previous value in SD[] eliminating the need for the EXISTS subquery. :-) SELECT CASE WHEN start = finish THEN start::text ELSE start || '-' || finish END AS gap FROM ( SELECT hole.id AS start, MIN(f.id)-1 AS finish FROM fred f, ( SELECT id+1 AS id FROM fred f1 WHERE NOT EXISTS (SELECT 1 FROM fred f2 WHERE f2.id = f1.id+1) ) AS hole WHERE f.id > hole.id GROUP BY 1 ) AS wilma; Here is the table I used to test with: CREATE TABLE fred ( id INTEGER PRIMARY KEY ); INSERT INTO fred VALUES (1); INSERT INTO fred VALUES (2); INSERT INTO fred VALUES (3); INSERT INTO fred VALUES (5); INSERT INTO fred VALUES (6); INSERT INTO fred VALUES (7); INSERT INTO fred VALUES (10); INSERT INTO fred VALUES (16); INSERT INTO fred VALUES (18); INSERT INTO fred VALUES (30);
Release notes come with the PostgreSQL latest release Administrator's Guide documentation with an installed PostgreSQL system or they can be found under the PostgreSQL Docs Link . Look for Appendix A in the Administrator's Guide. To see the latest greatest versions you can look in the latest development version of the docs. If you are trying to decide to upgrade, it is strongly suggested that you review the changes between releases so that you can make the most informed and timely choices.
When you have an alphanumeric field, natural sorting will be alphabetical. This means that 11 will come before 3, for example. If your table contained: select * from an_test order by code; name | code ---------+------ one | 1 eleven | 11 three | 3 five | 5 seven | 7 nine | 9 A-two | A1 A-four | A4 C-ten | C10 C-eight | C8 B-six | D6If you want to sort your column numerically for numbers and alphabetically with characters, use a subselect to create an integer column for the numbers and NULLs for the alphanumeric columns. Remember that NULLs sort to the end. Then, using a subselect, sort on the new column and the code column. select name, code from ( select *, (case when code ~ '^[0-9]+' then code::integer else NULL end) as number from an_test ) foo order by number, code; name | code ---------+------ one | 1 three | 3 five | 5 seven | 7 nine | 9 eleven | 11 A-two | A1 A-four | A4 C-ten | C10 C-eight | C8 B-six | D6To reverse the order so that the alphanumerics come before the numerics, use order by number desc, code instead.
You can use pg_hba.conf to restrict a user to a particular database. pg_hba.conf usually lives in your PGDATA directory and is usually owned by postgres. This is the file to change to enable and disable connections to your database. If you want to allow everyone else access but limit the user cool access to the cool database only, set up the pg_hba.conf file like this: # If you want to allow non-local connections, you need to add more # "host" records. Also, remember TCP/IP connections are only enabled # if you enable "tcpip_socket" in postgresql.conf. # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD # For user cool on db cool local cool cool password sameuser host all cool 127.0.0.1 255.255.255.255 reject host all cool x.x.x.x 255.255.255.224 reject host all cool 192.168.0.0 255.255.255.0 reject #For local others local all all password sameuser host all all 127.0.0.1 255.255.255.255 trust host all all x.x.x.x 255.255.255.224 trust #The order of the entries matters. When cool logs in, the first rows match the user name and database will apply. cool can access cool but nothing else. Others fall through and can access all of the database locally or on the specified (x.x.x.x) ip.
You can set and use your own SQL variable names within a psql session or SQL script run by psql. Displaying the SQL variables is not well documented (I couldn't find it.)
To see the SQL variable in an interactive session or for record, try \echo. Here we are setting foo to "harold" and echoing it. e=# \set foo harold e=# \echo Variable foo is set to :foo Variable foo is set to haroldYou can use the SQL variables in practical (and impractical ways). Here the SQL variable is used as a function name and then the function is executed using the SQL variable name. e=# \set when now() e=# \echo :when now() elein=# select :when; now ------------------------------- 2003-07-20 18:39:24.339276-07 (1 row) Of course SQL variables can be used in ordinary SQL statements. e=# \set newuser huey e=# insert into users (user_id, user_name) values (99,:newuser); INSERT 2037329 1 There is one warning however, and that is that if the SQL variable name :foo is enclosed in quotes, it will not be evaluated. Notice that when the users record was inserted above, the user_name is a text field but it did not require quotes. However, if you were creating, for example, a function, you could not use a SQL variable in the function because the body of the function is quoted. When more global replacement of variables is necessary but the task is simple and straight forward, you can also just write a shell script where the shell variables are substituted wherever you need them. You'll need to have the single quotes in that case. Also, if you wanted to create a plpgsql function in a bash script, you'll have to backslash the dollar signs for the plpgsql parameters in the body of the function. By using singleton selects you can query the database within the shell script by using the -Atc options in a backticked command. In the case below, some funky permission management is being done with the boolean function canupdateusers(). #!/bin/bash # validate the variables in real life user_id=$1 user_name=$2 # which database? echo "Enter database name (default is $USER):" read db [ -z $db ] && db=$USER perm=`psql -Atc "select canupdateusers( $USER );"`; if [ $perm != 't' ] then echo "$User is not allowed to update the users table." exit 1; fi pgsql $db << END insert into users (user_id, user_name) values ( $user_id , '$user_name' ); END exit 0; Both SQL variables and shell scripts with variables are particularly helpful in maintaining and updating schemas in multiple database.
|
|||||||||||||||||||||||||||||||||||
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in! Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009 |