Category Archives: Uncategorized

T-SQL Tuesday #008: Educating New Hires

We recently hired a new Jr. SQL Developer and she started yesterday. She is fresh out of college with no real world experience and little SQL experience. We knew what we were getting into. We had the budget and job description for a Jr. person and we wanted someone who was passionate about learning more about SQL. We chose this person precisely because she was so excited about learning database development.

We now had a slight problem. What do we do with someone this inexperienced? We decided that for the first month, the mornings would be used to learn our environment, where things are, what the processes do, etc. The afternoons would be for her to study for the 70-433 SQL Developer Exam with the resources necessary to try all the exercises in the book. Instead of immediately expecting her to start offloading work from us, we figure that educating her as she is getting lab experience would be our best long-term strategy.

I am excited that I can directly take part in this opportunity to begin someone’s professional career. Ask yourself what could you or your company do to start or improve the career of an eager young mind.

Speaking at SQL Saturday #38 in Jacksonville, FL

I’ve been selected as a last minute speaker for SQL Saturday. I’ll be speaking on Object Relational Mappers (ORMs) and why they can be a good thing, if properly used. There is an intense debate between application developers that really want to use them and DBAs that despise their existence. Like every tool, it has its place, but can be misused.

If you’ll be in Jacksonville on Saturday, May 8th stop by for a great day of SQL learning.

Reset sequence values to max

This function resets all sequences to the max value + 1 in their respective tables.


CREATE OR REPLACE FUNCTION util_reset_sequences () RETURNS SETOF VARCHAR as $$
DECLARE
myrow RECORD;
max_id INTEGER;
seq_name VARCHAR;
mytable VARCHAR;
myquery VARCHAR;
BEGIN
FOR myrow IN select * from information_schema.columns where column_default like 'nextval(%' LOOP
mytable := myrow.table_schema || '.' || myrow.table_name;
myquery := 'SELECT coalesce(max(' || myrow.column_name || '),0)+1 FROM ' || mytable;
--RAISE NOTICE 'query = %', myquery;
EXECUTE myquery INTO max_id;
seq_name := SUBSTRING(substr(myrow.column_default, 10) FROM E'[[:alnum:]_\.]*');
----seq_name := SUBSTRING(myrow.column_default FROM '\\''([[:alnum:]_]*)');
--RAISE NOTICE 'seq_name = %', seq_name;
EXECUTE 'ALTER SEQUENCE ' || seq_name || ' RESTART ' || max_id;
RETURN NEXT '"' || seq_name || '","' || myrow.table_name || '","' || myrow.column_name || '",' || cast(max_id as varchar);
END LOOP;
RETURN;
END;
$$ language 'plpgsql';

Greetings

Hello, my name is Eric Humphrey and I work for a software company in Shreveport, LA. My primary focus is data. Currently migrating my skills and data from Microsoft’s SQL Server to PostgreSQL. My intention is to publish some things learned from this migration. I also tend to find useful software and will mention these finds.

Hopefully someone will get some information out of all this.