Author Archives: Niklas - Page 38

My PostgreSQL cheat sheet

Posted by on December 21, 2012 No comments

Here I have collected all common commands and SQL for PostgreSQL that I use in my work

Help on psql slash commands
\?

Help on SQL commands
\h

List databases
\l

Connect to different database
\c database

Eqivavent to DESCRIBE table
\d+ tablename

List database settings
\set

List all tables
\dt

List users
\du

Quit psql command line
\q

Create user

CREATE USER niklas WITH PASSWORD 'myPassword';

Grant all privilegies on database to a user

GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

Grant alla privilegies on table to a user

GRANT ALL PRIVILEGES ON TABLE myTable to niklas;

Create table syntax (standard)

CREATE TABLE myTable (id serial, time integer);

Insert statement

INSERT INTO public.user (id, login, password) VALUES (1, 'test','test');# NOTE the single quotes!

Update statement

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';# NOTE the single quotes!

Delete statement

DELETE FROM films WHERE producer_id = 1;

I hope to come back to this sheet to add stuff as time goes by

How to drop a column in SQLite 3

Posted by on September 25, 2012 No comments

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11

Find all hosts on network with Nmap

Posted by on September 14, 2012 No comments

To find all pingable hosts on the newtwork you are currently on first find your own ip. In Linux/OSX you can run the command ifconfig (windows uses the ‘ipconfig’ command):

malen@LKGADEFB8:~$ sudo ifconfig
eth0      Link encap:Ethernet  HWaddr 00:1d:7e:ad:ef:b8  
          inet addr:192.168.0.77  Bcast:192.168.0.255  Mask:255.255.255.0
          inet6 addr: fe80::21d:7eff:fead:efb8/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:23647854 errors:0 dropped:83 overruns:0 frame:0
          TX packets:31522391 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:320343317 (305.5 MiB)  TX bytes:3340057852 (3.1 GiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:1736 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1736 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:159128 (155.3 KiB)  TX bytes:159128 (155.3 KiB)

You here see your IP at ‘inet addr’ on eth0: 192.168.0.77. To see if there are any other hosts on the 192.168.0.x net use: 

nmap -v -sP 192.168.0.1/24

This will ping all hosts on 192.168.0.x and show your result in a list

Host 192.168.0.1 appears to be down.
Host 192.168.0.2 appears to be down.
Host 192.168.0.3 appears to be down.
Host 192.168.0.4 appears to be down.
Host 192.168.0.5 appears to be down.
...
Host Slug (192.168.0.77) appears to be up.
...
Host 192.168.0.250 appears to be down.
Host 192.168.0.251 appears to be down.
Host 192.168.0.252 appears to be down.
Host 192.168.0.253 appears to be down.
Host 192.168.0.254 appears to be down.
Host 192.168.0.255 appears to be down.

Tested on OSX 10.7.4 and Debian Lenny