BrentHoover
BrentHoover
Reputation Top 1%
Brent Hoover
16 Snippets  (48th place)
Published
3 Channels
Created
15 Channels
Following
2004 points  (33rd place)
Reputation
Top 10% Publisher
Junior Code Generator
Serious Code Generator
Senior Code Generator
Serious Publisher
Senior Publisher
Junior Topic Creator
Junior Topic Hub
Junior Trend Maker
Junior Popular Coder
Serious Popular Coder
Senior Popular Coder
Junior Autobiographer
Senior Autobiographer
Master Autobiographer
Senior Famous Coder
Junior Wise Coder

Recent Snippets See all snippets by BrentHoover

public by BrentHoover  2561  0  6  3

Check for column before ALTER

You may want to check that a column doesn't exist before adding it (and not doing DROP IF EXISTS). The syntax is fairly straightforward.
IF EXISTS 
  (select column_name 
  FROM information_schema.columns 
  WHERE table_name='user_profile' and column_name='i_auth_user_id') 
THEN 
  ALTER TABLE ADD COLUMN 'yo' boolean;           
END IF;
;

public by BrentHoover  1955  0  5  1

Query for server config

Good for use in Dashboards, etc. where you are checking a series of servers for info. I have excluded fields I commonly don't need (because they are rarely changed from defaults)
SELECT
  'version'::text AS "name",
  version() AS "current_setting"
UNION ALL
SELECT
  name, current_setting(name) 
FROM pg_settings 
WHERE NOT source='default' 
AND NOT name IN
(
'log_timezone','DateStyle',
'lc_messages','lc_monetary', 'lc_numeric','lc_time', 'lc_collate', 'timezone_abbreviations', 'lc_ctype',
'application_name',
'transaction_deferrable','transaction_isolation', 'transaction_read_only'
);
;

public by BrentHoover  1867  2  5  0

Insert a value when another value doesn't exist

Often you need to make SQL idempotent so that running it does not have different effects, this is one trick I use
insert into Table (FieldNames)
select 'String Value', true, 1, 'string value to insert', 'string value to insert'
where not exists (select 1 from Table where Field = 'Possibly Existing Value');
;

public by BrentHoover  1886  0  6  1

Query for FK Constraints

This is a bit of Postgres Metaprogramming but it comes in handy when deconstructing a db
//substitute "core" with the name of your schema

SELECT rc.constraint_schema,  kcu.table_name as ltable, 
kcu.column_name as lcolumn, ccu.table_name as ftable, 
ccu.column_name as fcolumn
from information_schema.referential_constraints as rc
JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
JOIN information_schema.key_column_usage AS ccu 
    ON(ccu.constraint_catalog=rc.unique_constraint_catalog 
    AND ccu.constraint_schema=rc.unique_constraint_schema 
    AND ccu.constraint_name=rc.unique_constraint_name)
 where tc.constraint_schema = 'core' AND
 tc.constraint_type = 'FOREIGN KEY' order by tc.table_name
;

public by BrentHoover  2649  0  6  1

Reset a sequence based on the max value of the table

When a sequence gets out of sync, such as when a values gets manually inserted by mistake
// reset a sequence based on the max value of the table
PERFORM setval('<sequence_name>', 
(SELECT MAX(<sequence_field>) FROM <table>));
;